[MOD] SQLite Auth [sauth]

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

[MOD] SQLite Auth [sauth]

by shivajiva » Sat Oct 07, 2017 17:10

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.

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 Sun Mar 10, 2019 21:45, edited 2 times in total.
 

User avatar
rubenwardy
Moderator
 
Posts: 5835
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: 816
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: 28
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: 179
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: 2066
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: 2066
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: 293
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
Member
 
Posts: 63
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: 28
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.
 

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

Re: [MOD] SQLite Auth [sauth]

by shivajiva » Tue Jan 08, 2019 22:29

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: 151
Joined: Tue Jun 19, 2018 07:45
Location: Alone In The Wild Land
GitHub: bhree
IRC: bhree
In-game: bhree

Re: [MOD] SQLite Auth [sauth]

by bhree » Wed Mar 06, 2019 04:25

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.
 

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

Re: [MOD] SQLite Auth [sauth]

by shivajiva » Thu Mar 07, 2019 09:15

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.
 

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

Re: [MOD] SQLite Auth [sauth]

by shivajiva » Sun Mar 10, 2019 20:42

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


Return to WIP Mods



Who is online

Users browsing this forum: No registered users and 6 guests