[MOD] SQLite Auth [sauth]

Post Reply
User avatar
shivajiva
Member
Posts: 32
Joined: Fri Nov 27, 2015 09:41
In-game: shivajiva

[MOD] SQLite Auth [sauth]

by shivajiva » Post

This mod is an alternative authorisation handler using sqlite.

It employs caching, which in it's basic form keeps all players logged in to the server in memory but is also extensible to a user specified cap (default: 500 records). The cache is primed on server start with players who recently logged in. It aims to provide a balance of memory consumption against lag associated with join events for popular servers.

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/)
If you are running mod security(recommended) you will need to add sauth to the list of trusted mods because of it's dependency on lsqlite3.

License: MIT license


Features:
  • Import auth.txt(0.4.x) or auth.sqlite(0.5.x). Check tools folder and readme for more info.
  • Seamlessly integrates into Minetest, simply add it to the mod folder and it takes over from the built in handler
Enjoy!

Download latest for Minetest version 0.4.x
Download latest for Minetest version 0.5.x
Browse code on Github
Last edited by shivajiva on Wed Apr 22, 2020 15:57, edited 3 times in total.

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

Re: [MOD] SQLite Auth [sauth]

by rubenwardy » Post

I've been using this on CTF, and it works quite well. Thanks!
Renewed Tab (my browser add-on) | Donate | Mods | Minetest Modding Book

Hello profile reader

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

Re: [MOD] SQLite Auth [sauth]

by Byakuren » Post

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.

User avatar
shivajiva
Member
Posts: 32
Joined: Fri Nov 27, 2015 09:41
In-game: shivajiva

Re: [MOD] SQLite Auth [sauth]

by shivajiva » Post

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: 179
Joined: Sat Aug 20, 2016 15:18
GitHub: ezhh
IRC: Shara

Re: [MOD] SQLite Auth [sauth]

by Shara » Post

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: 2146
Joined: Fri Jan 16, 2015 07:31
GitHub: sofar
IRC: sofar
In-game: sofar

Re: [MOD] SQLite Auth [sauth]

by sofar » Post

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
IRC: fireglow
In-game: fireglow
Location: Germany

Re: [MOD] SQLite Auth [sauth]

by fireglow » Post

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: 2146
Joined: Fri Jan 16, 2015 07:31
GitHub: sofar
IRC: sofar
In-game: sofar

Re: [MOD] SQLite Auth [sauth]

by sofar » Post

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).

User avatar
BuckarooBanzay
Member
Posts: 435
Joined: Tue Apr 24, 2018 05:58
GitHub: BuckarooBanzay
IRC: BuckarooBanzai
In-game: BuckarooBanzai

Re: [MOD] SQLite Auth [sauth]

by BuckarooBanzay » Post

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 :)
¯\_(ツ)_/¯ Not active here anymore, contact me on the minetest discord, irc, lemmy or github (for programming issues)

neoh4x0r
Member
Posts: 82
Joined: Wed Aug 29, 2018 20:16
GitHub: neoh4x0r

Re: [MOD] SQLite Auth [sauth]

by neoh4x0r » Post

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.

User avatar
shivajiva
Member
Posts: 32
Joined: Fri Nov 27, 2015 09:41
In-game: shivajiva

Re: [MOD] SQLite Auth [sauth]

by shivajiva » Post

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.

User avatar
shivajiva
Member
Posts: 32
Joined: Fri Nov 27, 2015 09:41
In-game: shivajiva

Re: [MOD] SQLite Auth [sauth]

by shivajiva » Post

Latest version has performance enhancements, I recommend updating asap if you use sauth as your authorisation handler. The difference is quite obvious in the performance of the mod from the moment it is updated. Enjoy!

User avatar
bhree
Member
Posts: 169
Joined: Tue Jun 19, 2018 07:45
GitHub: bhree
IRC: bhree
In-game: bhree
Location: Alone In The Wild Land
Contact:

Re: [MOD] SQLite Auth [sauth]

by bhree » Post

fireglow wrote: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?
I have the same problem and i'm using mod with the same sqlite3 instance so I can't set it to nil. The consequece maybe mods should be scrutinized for sqlite3 access I'm sure current mod using sqlite3 is safe. It also means I have lsqlite3 requirement met to run sauth but still experiencing problem maybe I miss something. Currently using builtin 5.0.0 sqlite3 backend.

User avatar
shivajiva
Member
Posts: 32
Joined: Fri Nov 27, 2015 09:41
In-game: shivajiva

Re: [MOD] SQLite Auth [sauth]

by shivajiva » Post

just to be clear sauth runs it's own instance of sqlite, setting sqlite3 global to nil just means no other mod can use this mods instance of it, if you are using mods that need that global you should load them after sauth to prevent any issues.

Keep in mind the backend uses sqlite and is in no way affected by sauth clearing the global...

I will check the importer is functional and report back at some point today.

User avatar
shivajiva
Member
Posts: 32
Joined: Fri Nov 27, 2015 09:41
In-game: shivajiva

Re: [MOD] SQLite Auth [sauth]

by shivajiva » Post

...days later finally got round to checking...found a bug introduced when I objectified read/write functionality to settings. Fixed importer in the 0.4 branch and not required for the 0.5 branch of sauth, thanks for reporting the issue :)

@bhree: use the correct branch and the import_db.sql file in /tools

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

Re: [MOD] SQLite Auth [sauth]

by sofar » Post

This mod has run for years without issues on my server. But I'm no longer interested in keeping it around because the default sqlite3 auth backend (*) replaces the files backend and that's why I used sauth originally.

Sauth supports memory caching of auth requests and the default backends (AFAIK) do not, so there may be a reason to remain using sauth for many server operators.

I've migrated my own server away to the regular (builtin) sqlite3 auth backend. I used the script at the following location to do this:

https://github.com/shivajiva101/sauth/issues/16

The script just copies all the entries over into an existing (but emptied) auth.sqlite. After which you may need to edit world.mt so that the backend isn't files but sqlite3.

I had a few duplicate names inside my sauth db, and the script tries to work around it. Maybe it can be useful to someone else besides me.

(*) Edited for correctness to omit speculative statement.

User avatar
shivajiva
Member
Posts: 32
Joined: Fri Nov 27, 2015 09:41
In-game: shivajiva

Re: [MOD] SQLite Auth [sauth]

by shivajiva » Post

Oh does the regular auth backend support memory caching of recent players now? Not sure where you get the idea that sauth is possibly no longer maintained but as always everyone is free to do whatever they wish, maybe just try sticking to known facts...

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

Re: [MOD] SQLite Auth [sauth]

by sofar » Post

shivajiva wrote:
Sun Feb 26, 2023 12:33
Oh does the regular auth backend support memory caching of recent players now? Not sure where you get the idea that sauth is possibly no longer maintained but as always everyone is free to do whatever they wish, maybe just try sticking to known facts...
I edited my statement to rectify this. I'll try and be more precise in my wording the next time.

User avatar
shivajiva
Member
Posts: 32
Joined: Fri Nov 27, 2015 09:41
In-game: shivajiva

Re: [MOD] SQLite Auth [sauth]

by shivajiva » Post

Thanks Sofar, you can appreciate as a MT developer, you expressing it in the form you did sends out a strong signal as to the viability of a mod, and it seemed a bit harsh given sauth does actually add a useful feature for a popular server, one that's unlikely to make it into the core anytime soon.

I made this mod at a point in time I was fully invested in MT and running a server. I wanted minetest to adopt a db strategy for auth as the file approach had it's own issues once it reached a certain size. There's no better way of encouraging change than proving it could be stable and accomplished within a mod. It serves as a testament to the power of the minetest API. Can't have a mod out performing the core in a simple but key task now can we, and that was very much the driving force of me creating it!

Whilst I wasn't the only person weighing in pressure to add the capability to the core I was instrumental and if/when the core offers caching I'll be happy to retire the mod. In the meantime my plan is to keep it functional with mt releases and deal with any issues that crop up.

Send my regards to the devs that know me and please do have a chuckle about my unwavering approach, somethings never change ;)

Post Reply

Who is online

Users browsing this forum: No registered users and 11 guests