Implement SQL-backed user accounts

[simonw]

Again following the example of https://github.com/simonw/datasette-auth-tokens/blob/d6f45da578419177c260628db5f8352a839e45f2/README.md#tokens-from-your-database

[simonw]

The trick here is making it so that hashed passwords are protected by default, without leaving people room to mess things up.

To do that, the default configuration should work as follows:

• User accounts live in an independent database, e.g. users.db - which is specified by name in the plugin configuration
• The plugin sets up default permission handlers that deny ALL access to tables in that database
• The plugin adds a canned query for inserting users into the database, which is only available to root
• That canned query includes a call to a datasette_auth_passwords_hash(password) custom SQLite function which generates the hash when the user account is created

This is the default way things work. Advanced users can customize SQL queries and even (by jumping through some "I know this is not safe" hoops) completely customize the SQL that loads the password hash.

[simonw]

It's only the password hashes that need to be kept secure - not the other details of the user account (whatever shape that might be).

So the configuration SQL should be "given this username, how do I retrieve the corresponding password hash".

These hashes can then be stored in a separate database entirely.

[simonw]

Configuration design:

{
    "plugins": {
        "datasette-auth-passwords": {
            "query": {
                "sql": "select password_hash from passwords where username = :username",
                "database": "passwords"
            }
        }
    }
}

This will "protect" the entire passwords.db database. It will add permissions that lock down that database entirely.

You can disable the default protections by adding "protect_database": False in that block.

Maybe also support an update_password_sql key, which would look something like this:

"query": {
    "sql": "select password_hash from passwords where username = :username",
    "update_password_sql": "update passwords set password_hash = hash_password(:password) where username = :username",
    "database": "passwords"
}

This would then become a canned query.

Is this necessary though? The existing canned query mechanism can be used for that maybe? Except that doesn't get setup with the right root-only permissions by default.

Ensuring people use the right default configuration is really important here, so I think the plugin should lean towards doing that.

[simonw]

Idea: if you leave off the sql key it could create a default table shape and set up default queries for you. So you'd just do this:

"query": {
    "database": "passwords"
}

Your only responsibility would be attaching a passwords.db database to Datasette. It looks like a 0 byte file will work for this, so users can run touch passwords.db to create one.

[simonw]

Came up in office hours today!