[MOD] SQLite Auth [sauth]

shivajiva
Member
 
Posts: 21
Joined: Fri Nov 27, 2015 09:41
In-game: shivajiva

[MOD] SQLite Auth [sauth]

by shivajiva » Sat Oct 07, 2017 17:10

This is an alternative authorisation handler using a sqlite database instead of a text file. It was created to bridge the gap between mysql_auth and the current method employed by Minetest. It seeks to create a more robust handling of the data on server crashes than Minetest, based on exploiting the way databases validate the data write cycle to ensure partial valid records cannot exist.

Installation requirements

This mod depends on the lua module lsqlite3 (https://github.com/LuaDist/lsqlite3) A thin wrapper for the SQLite database engine.
The preferred installation method is to install lsqlite3 using luarocks (https://luarocks.org/)

Features:

  • Automatically imports auth entries on first run and renames the file as a backup
  • Seamlessly integrates into Minetest, simply add it to the mod folder and it takes over from the built in handler

If you are running mod security(recommended) you will need to add sauth to the list of trusted mods.

Enjoy!

download the zip
Browse code on Github
 

User avatar
rubenwardy
Moderator
 
Posts: 5500
Joined: Tue Jun 12, 2012 18:11
Location: United Kingdom
GitHub: rubenwardy
IRC: rubenwardy
In-game: rubenwardy

Re: [MOD] SQLite Auth [sauth]

by rubenwardy » Sat Oct 07, 2017 17:31

I've been using this on CTF, and it works quite well. Thanks!
 

Byakuren
Member
 
Posts: 812
Joined: Tue Apr 14, 2015 01:59
GitHub: raymoo
IRC: Hijiri
In-game: Raymoo + Clownpiece

Re: [MOD] SQLite Auth [sauth]

by Byakuren » Sat Oct 07, 2017 17:35

This breaks mod security somewhat since other mods can use the global sqlite3 library to create sqlite databases in places they normally would not be allowed to write to.

You should assign the sqlite3 table to a local and then nil the global variable.
Every time a mod API is left undocumented, a koala dies.
 

shivajiva
Member
 
Posts: 21
Joined: Fri Nov 27, 2015 09:41
In-game: shivajiva

Re: [MOD] SQLite Auth [sauth]

by shivajiva » Sat Oct 07, 2017 19:22

well spotted Byakuren, repo updated :)

Okay, some time has passed and a couple of bugs have been traced and removed. Testing on Skyblocks with 211,000 players in the database and 3 players in-game we have a max_lag figure 15 mins after server start of 0.1 which is totally unheard of for that server, my experience with it previously was an unhealthy 4-6 seconds. The hub now functions as expected with no delays, giving a smooth player experience.

Any server owner with a large player database currently using auth.txt should see a marked effect in playability after this mod is installed and the records imported. Make auth.txt file corruption a thing of the past!
 

Shara
Moderator
 
Posts: 152
Joined: Sat Aug 20, 2016 15:18
GitHub: ezhh
IRC: Shara

Re: [MOD] SQLite Auth [sauth]

by Shara » Thu Oct 12, 2017 18:14

Just to add some weight to the above. This mod managed to reduce Dark Lands Survival max_lag values from sitting around 5 to 6, right back down to under 1. We'd suspected for a while that the slowly growing lag was auth.txt related, and this both managed to confirm and fix it.
 

sofar
Developer
 
Posts: 1747
Joined: Fri Jan 16, 2015 07:31
GitHub: sofar
IRC: sofar
In-game: sofar

Re: [MOD] SQLite Auth [sauth]

by sofar » Tue Oct 17, 2017 17:03

If you use a sqlite player db, you can do some really cool stuff on the cmdline to prune your databases.

One of the quickest prunes you can do is to remove players from sauth.sqlite that have never actually successfully logged into a server, for instance because they failed protocol version or timed out logging in. This is especially common if your server has large media files as this causes tablet players to time out early.

I run this script every server restart, essentially, and the first time it ran it deleted 80% of the entries in sauth.sqlite:

Code: Select all
#!/bin/bash

AUTHDB=/home/minetest/.minetest/worlds/world/sauth.sqlite
PLAYERSDB=/home/minetest/.minetest/worlds/world/players.sqlite

/usr/bin/sqlite3 ${AUTHDB} "attach database '${PLAYERSDB}' as players" "delete from auth where auth.name not in (select name from players.player)"
 

User avatar
fireglow
Member
 
Posts: 93
Joined: Fri Mar 28, 2014 11:36
Location: Germany
IRC: fireglow
In-game: fireglow

Re: [MOD] SQLite Auth [sauth]

by fireglow » Fri Feb 23, 2018 12:31

Having a bit of trouble with the mod:

I’m trying to install it with an existing server, the auth.txt file has around 660 entries.
The serverlog prints "ACTION[Main]: sauth: Registered auth handler" but nothing more.

The mod renames auth.txt to auth.txt.bak,
creates the import=true setting,
but doesn't actually import any players into the `auth` table.

When I log in (with an existing auth.txt user) I can specify any password and the login succeeds. Subsequent logins are only valid using the new password.

The privs, however, are the ones in auth.txt.
The privs from /privs are the auth.txt ones, and do not match the ones saved to the `auth` table.

Is this expected behaviour?
How do I import my existsing players (passwords, privs and all) to sauth.sqlite?
 

sofar
Developer
 
Posts: 1747
Joined: Fri Jan 16, 2015 07:31
GitHub: sofar
IRC: sofar
In-game: sofar

Re: [MOD] SQLite Auth [sauth]

by sofar » Fri Feb 23, 2018 17:50

fireglow wrote:I’m trying to install it with an existing server, the auth.txt file has around 660 entries.
The serverlog prints "ACTION[Main]: sauth: Registered auth handler" but nothing more.

The mod renames auth.txt to auth.txt.bak,
creates the import=true setting,
but doesn't actually import any players into the `auth` table.


Best to open a ticket at the github project page, and post the full server logs. Make sure to check that your relevant requirements are met (e.g. minetest.conf changes are made and lsqlite3 is functional).
 

BuckarooBanzay
Member
 
Posts: 111
Joined: Tue Apr 24, 2018 05:58
Location: Switzerland
GitHub: thomasrudin-mt
In-game: BuckarooBanzai

Re: [MOD] SQLite Auth [sauth]

by BuckarooBanzay » Tue Oct 16, 2018 09:16

I have profiled several lag issues the last few days and most of them point to sauth as the cause :(
In particular:
I have some mods that give and revoke the fly priv depending on what height you are in (space/moon/earth)
They call minetest.get_player_privs() and minetest.set_player_privs() repeatedly over several seconds.
This works quite well but there are rare events (a couple times per hour) where this call blocks in sauth for several seconds...
Are there any other reports/issues with this?

EDIT: other than that the mod works quite well :)
 

neoh4x0r
New member
 
Posts: 6
Joined: Wed Aug 29, 2018 20:16
GitHub: neoh4x0r

Re: [MOD] SQLite Auth [sauth]

by neoh4x0r » Tue Oct 16, 2018 16:57

Byakuren wrote:This breaks mod security somewhat since other mods can use the global sqlite3 library to create sqlite databases in places they normally would not be allowed to write to.

You should assign the sqlite3 table to a local and then nil the global variable.


I think an even better and more secure method would be to create minetest wrapper library (or use a set of native engine calls)for sqlite3....this way the minetest engine could sandbox the calls to the library.
 

shivajiva
Member
 
Posts: 21
Joined: Fri Nov 27, 2015 09:41
In-game: shivajiva

Re: [MOD] SQLite Auth [sauth]

by shivajiva » Tue Oct 16, 2018 17:14

No other reports of this issue to date, the record is cached after first retrieval, giving the same read performance or better due to the reduced table size on subsequent calls, so this seems to point to the db save hitting a busy status occasionally.
 


Return to WIP Mods



Who is online

Users browsing this forum: No registered users and 9 guests