Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Users and Authentication (extension)

Users and the owner

A user is a name a client of the server may connect as, proving it by a password, or by a certificate (Certificates). The database keeps, of each, not the password but a verifier made of it: PBKDF2 of SERVER.PASSWORD_ITERATIONS iterations, 600,000 by default, with a salt of its own, and of that two keys, SCRAM-SHA-256's StoredKey and ServerKey. Nothing in the database tells the password, nor lets anyone log in who has read the verifier.

One user is the database's owner: the first made, unless another is made it. The owner alone manages the users in SQL, by CREATE USER, ALTER USER and DROP USER, and sets the database's settings. The owner cannot be dropped until another owns the database.

The owner hands the database on to another user, on a running server, by ALTER DATABASE:

ALTER DATABASE OWNER TO bob;

No session is ended; the owner's go on as the owner no more. With no server running, turboreld user owner NAME does the same (Managing users from turboreld).

What a user may do is the privileges granted to its name, as of any authorization identifier (GRANT).

A user's sessions

A session lasts while its user does. Once a user is dropped, each session of it ends at its next statement, which is refused, its transaction rolled back and its connection closed:

turborel: SQLCODE -919 (SQLSTATE 28000): the user BOB was dropped: its connections end

A new password ends no session: it proved who it is as it logged in, and the next login takes the new password. To end a session at once, its operator ends its connection (Connections).

Passwords

A client proves a password by one of these mechanisms, which the server all takes:

Mechanism How
scram-sha-256-plus SCRAM-SHA-256-PLUS (RFC 5802, RFC 7677), over TLS: as scram-sha-256, the exchange bound to the TLS connection. Over TLS 1.3 by tls-exporter (RFC 9266), keying material of that connection alone; over TLS 1.2 by tls-server-end-point (RFC 5929), the hash of the certificate the server presented. The command-line client uses it over TLS.
scram-sha-256 SCRAM-SHA-256 (RFC 5802, RFC 7677): the client proves it knows the password, and the server that it knows the verifier, by a challenge and response; the password never travels. The command-line client uses it over a Unix socket or named pipe.
password the password is sent, inside TLS or over a Unix socket or named pipe, and checked against the verifier

Each is taken only over TLS, a Unix socket or a named pipe, never in the clear over TCP: the client refuses to begin so, and the server to take it. A SCRAM exchange does not give the password away, but one overheard lets the eavesdropper try passwords against it at leisure. SERVER.SCRAM_WITHOUT_TLS ON lets the server take scram-sha-256 over plain TCP all the same, for a network its operator trusts; it is OFF by default, and a client sends it so only if its program says.

Over TLS, the binding keeps a server in the middle from relaying the exchange: it holds one TLS connection to the client and another to the server, so what the client binds is not what the server binds, and the login is refused as a wrong password is. The client names the binding in its first message, and the server takes the one it names where the connection has it:

Binding Where What is bound
tls-exporter TLS 1.3; the client's choice there 32 bytes exported of the connection's own keys
tls-server-end-point TLS 1.2 and 1.3; the client's choice over TLS 1.2 the hash of the certificate the server presented

Over TLS 1.2 there is no tls-exporter. Each connection is bound to the certificate its handshake presented, a certificate taken again included (Certificates renewed). A certificate whose signature uses no hash, as Ed25519's, or two, defines no tls-server-end-point: over TLS 1.2 the client then asks for scram-sha-256, saying it could bind, and the server takes it. A client saying so where the server does offer a binding is refused, as a server in the middle hiding the binding would make it:

turborel: SQLCODE -919 (SQLSTATE 28000): SCRAM: the client believes the server offers no channel binding, which it does: a downgrade

A password is taken as its UTF-8 bytes, without the SASLprep of RFC 5802: the same of an ASCII password.

A client gives a wrong user or password the same answer, after as long, by either mechanism: the user or the password is wrong, SQLSTATE 28000. After a failed login, the next from the same address waits, longer with each failure, up to SERVER.LOGIN_DELAY_MS, ten seconds by default (Logins); no user is ever locked out.

Certificates

With SERVER.TLS_CLIENT_CA naming the PEM file of the authorities it trusts, the server asks each TLS client for a certificate. A client presenting one of those authorities' logs in, by the mechanism certificate, as the user its subject's common name names, read as an identifier, as a user's name is: CN=ann logs in as ANN, if ANN is a user. One name, compared whole, is the mapping: whoever the authorities certify as ANN may log in as ANN, and no one else by that certificate. A certificate of other authorities fails the TLS handshake; a client presenting none is served as before, by a password. A certificate as another user, or of a name that is no user's, is refused:

turborel: SQLCODE -919 (SQLSTATE 28000): the certificate is not of the user named
$ turboreld --dir db --set SERVER.TLS=ON --set SERVER.TLS_CERTIFICATE=/etc/turborel/cert.pem \
      --set SERVER.TLS_KEY=/etc/turborel/key.pem --set SERVER.TLS_CLIENT_CA=/etc/turborel/clients.pem
$ turborel -h localhost -U ann --tls /etc/turborel/ca.pem --certificate ann.pem --key ann.key

Trusted local clients

With SERVER.TRUST_LOCAL ON, the server takes a client of its own machine as the user it names, with no password, a user or not. It is OFF by default: every client proves who it is. LOCAL suits a machine of one operator; on a machine of many accounts, any of them could connect as the owner.

Managing users from turboreld

turboreld --dir PATH user COMMAND manages the users of a database no server holds; it sets a database up before it is served. A password is typed without its echo at a terminal, or else read from standard input.

Command Does
user add NAME [--owner] adds a user, the owner if asked
user password NAME gives a user a new password
user drop NAME drops a user
user owner NAME makes a user the owner
user list lists the users, the owner marked
$ turboreld --dir /var/lib/turborel/main user add ann --owner
user ANN added, the owner
$ turboreld --dir /var/lib/turborel/main user add bob
user BOB added
$ turboreld --dir /var/lib/turborel/main user list
ANN (owner)
BOB

A name is an identifier, so ann is ANN. Each change of a user, or of the owner, is told to the diagnostics, user_changed and owner_changed; no verifier ever is.

The owner of a database kept nowhere

A database kept nowhere, served by turboreld --memory, has no directory to set up first. --owner NAME makes NAME its first user, and so its owner, as the server starts; its password is typed without its echo at a terminal, or else read from standard input, as user add reads one. Without it, such a database has no user, so no owner: no one could manage its users or set its settings in SQL.

$ turboreld --memory --owner ann --set SERVER.SOCKET=/run/turborel/socket
password:
turboreld: listening at 127.0.0.1:7422, /run/turborel/socket

--owner is for --memory alone: a database kept in a directory is given its owner by user add NAME --owner.