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.

Connections

TCP

The server takes TCP connections at the hosts or addresses of SERVER.HOST, by commas, 127.0.0.1 by default, on SERVER.PORT, 7422; port 0 lets the system choose one, which the server says as it starts. SERVER.HOST empty takes no TCP connection. Over TCP, a password is proved only once TLS secures the connection (Passwords).

Unix sockets and named pipes

SERVER.SOCKET names a Unix socket the server takes connections at too, on Linux and macOS; on Windows, a named pipe. Only this machine reaches either, so a connection over one needs no TLS, and a password may be proved over it.

$ turboreld --dir db --set SERVER.SOCKET=/run/turborel/socket
$ turborel --socket /run/turborel/socket -U ann -W

On Windows, a pipe's name begins \\.\pipe\:

> turboreld --dir db --set SERVER.SOCKET=\\.\pipe\turborel
> turborel --socket \\.\pipe\turborel -U ann -W

The server makes the pipe as the first of its name, so it does not start if another process holds the name (cannot listen at \\.\pipe\turborel), and while it listens no other process can take the name. It refuses clients of other machines. Who of this machine may connect is as Windows secures a pipe by default: the server's own user, the administrators and the system fully; any other user may open the pipe to read only, so can send nothing, and is closed once SERVER.LOGIN_TIMEOUT_MS runs out. Each such client holds a connection until then, under SERVER.MAX_CONNECTIONS; where other users of the machine are not trusted, keep SERVER.TRUST_LOCAL OFF, as it is by default, so every client proves who it is.

A client waits for a pipe busy as long as its connect timeout; once the pipe is open, its reads and writes are not bounded: --read-timeout does not apply to a named pipe.

TLS

SERVER.TLS ON offers TLS on the TCP port; REQUIRED takes TCP connections over TLS alone. SERVER.TLS_CERTIFICATE names the PEM file of the server's certificate chain, SERVER.TLS_KEY that of its private key. A client trusts the certificates of the PEM file it is given, --tls, and the platform's authorities only if asked, --tls-system-roots (The Command-Line Client); it checks the server's certificate against the host it names, or --server-name.

$ turboreld --dir db --set SERVER.TLS=REQUIRED \
      --set SERVER.TLS_CERTIFICATE=/etc/turborel/cert.pem --set SERVER.TLS_KEY=/etc/turborel/key.pem
$ turborel -h localhost -U ann -W --tls /etc/turborel/ca.pem

A client that does not use TLS is refused, and so is one that does not trust the certificate:

turborel: SQLCODE -919 (SQLSTATE 08004): this server takes TCP connections over TLS only
turborel: SQLCODE -919 (SQLSTATE 08006): the TLS handshake failed: invalid peer certificate: UnknownIssuer

The server's certificate is one a certificate authority issued, as the client trusts: a self-signed certificate used as its own authority is refused. The client trusts the authorities of its file alone, not the platform's.

Over TLS, a password proved by SCRAM is bound to the server's certificate, so a server in the middle cannot relay it (Passwords).

SERVER.TLS_CLIENT_CA names the PEM file of the authorities whose certificates clients may present, each then logging in as the user its common name names (Certificates); empty, its default, no client is asked for one.

Certificates renewed

The server takes its certificate, key and client authorities again without a restart, for the connections secured after; those secured go on as they are, SCRAM's binding of each by tls-server-end-point to the certificate its handshake presented:

  • when SERVER.TLS_CERTIFICATE, SERVER.TLS_KEY or SERVER.TLS_CLIENT_CA is changed, as by the owner's SET DATABASE, as the next connection begins TLS;
  • on SIGHUP, on Unix, from the same files, for a certificate renewed in place (Signals).

Files that cannot be taken, as a key that does not fit its certificate, leave the server with those it had; it tells the diagnostics, tls_reload_failed, once for each change, and tls_reloaded once it takes new ones (Diagnostics). So a certificate and its key changed by two statements, one after the other, may be refused between them, and taken after the second. SERVER.TLS itself, and so whether TLS is offered at all, takes effect as the server starts.

Limits on connections

Setting Limit
SERVER.MAX_CONNECTIONS connections at once, 100
SERVER.WORKERS statements run at once, four per processor
SERVER.MAX_STATEMENTS prepared statements of a connection, 1,024
SERVER.MAX_MESSAGE_BYTES the longest message a client sends, 16 MiB
SERVER.BATCH_BYTES about the bytes of rows in a batch sent, 64 KiB
SERVER.LOB_INLINE_BYTES the longest large object a result sends whole, a longer one sent by its locator, 64 KiB
SERVER.LOGIN_TIMEOUT_MS the milliseconds a client has to begin TLS and authenticate, the two together, from when the connection is taken, 60,000
SERVER.MAX_LOGINS logins checked at once, apart from SERVER.WORKERS, 4
SERVER.LOGIN_DELAY_MS the longest a login waits after failed ones from its address, 10,000; 0, no wait
SERVER.IDLE_TIMEOUT_MS the milliseconds the server waits on a client before closing it, its transaction rolled back; 0, no limit
SERVER.IDLE_IN_TRANSACTION_TIMEOUT_MS the milliseconds the server waits for the next request of a client whose transaction is active, before closing it, its transaction rolled back; 0, no limit

A connection past SERVER.MAX_CONNECTIONS is refused, told why once its TLS, if any, is begun:

turborel: SQLCODE -919 (SQLSTATE 08004): the server has as many connections as it takes

At most SERVER.MAX_REFUSALS (16) such refusals are made at once; a connection past them too is closed at once, told nothing, so clients that flood the server hold no more of it than that. Either way the diagnostics are told, as connections_refused, at most once a minute (Diagnostics).

A connection, served or refused, gives back its place as the server closes it, before its client can see it closed: a client that ends its connection with the server at its limit may connect again at once. Over TLS the place is given back before the server's closing message (close_notify) is sent; a client that does not take that message keeps its socket open, outside the limit, for SERVER.IDLE_TIMEOUT_MS at most, or until the server hurries to stop if that is 0.

Logins

A client logging in holds little of the server. Until it is authenticated, it may send no message longer than 16 KiB, or SERVER.MAX_MESSAGE_BYTES if that is less, and a message's memory is taken as its bytes arrive, not as its length says. Its password is checked, a slow hash on purpose, on threads of the server's apart from those statements run on, at most SERVER.MAX_LOGINS at once: clients that log in, or try to, never keep the statements of those logged in waiting.

After a login fails, the next from the same address waits before it is checked: 100 ms, doubled with each failure after, up to SERVER.LOGIN_DELAY_MS. Logins of one address waiting take turns, one a delay, so connections in parallel guess no faster. A login accepted forgets the address's failures, and so do ten minutes without one. An IPv6 address counts by its /64; the clients of a Unix socket or named pipe count as one address. A user is never locked out: that would let anyone lock out anyone.

Clients gone

A client whose machine or network fails never closes its connection, and its session would hold its transaction's locks while the server waited. The server turns TCP keepalive on for each connection it takes, so the system finds such a client out, and the server rolls its session back. The system's own times apply, on Linux two hours idle, then nine probes 75 seconds apart, unless the server's settings say otherwise, each read as a connection is taken, 0 for the system's own:

Setting What
SERVER.KEEPALIVE_IDLE_MS how long a connection is idle before the first probe
SERVER.KEEPALIVE_INTERVAL_MS how long between probes unanswered
SERVER.KEEPALIVE_PROBES the probes unanswered after which the connection is closed

A client gone is found out after about the idle time, then the interval times the probes. The systems count these times in whole seconds, so a time is rounded up to one; a time is at most 32767 seconds and the probes at most 127, Linux's limits. Linux, macOS and Windows set all three, as do the BSDs and illumos. Windows sets the idle time and the interval together: given one, the other is Windows' own default (two hours idle, a second between probes), whatever its registry says; its probes are set from Windows 10 version 1703 only, before which it always sends ten. On a system that cannot set a time given, every TCP connection fails as it is taken, and the failure is told as the event accept_failed (Diagnostics). SERVER.IDLE_TIMEOUT_MS closes any client that keeps the server waiting longer; SERVER.IDLE_IN_TRANSACTION_TIMEOUT_MS closes, sooner, one that sends nothing while its transaction is active, its locks held:

turborel: SQLCODE -919 (SQLSTATE 08006): the connection went past SERVER.IDLE_IN_TRANSACTION_TIMEOUT_MS, 60000 ms, with its transaction active and nothing from the client: closed

Ending a connection

The database's owner ends a connection by its ID in INFORMATION_SCHEMA.CONNECTIONS, END CONNECTION, or cancels the statement it runs, CANCEL CONNECTION; a program that serves a database does so through Server::operator, taken before it serves, Operator::end(ID) and Operator::cancel(ID). Ending one cancels the statement it runs, as a client's cancel does, then closes it, its session rolled back; the client is told:

turborel: SQLCODE -919 (SQLSTATE 08006): the server's operator ended the connection

A connection whose user is dropped ends at its next request (Users).

The database's owner sees the connections served, and the server's counts, in INFORMATION_SCHEMA.CONNECTIONS and INFORMATION_SCHEMA.SERVER (Information Schema). Of a server whose one connection is the owner's own, over TCP without TLS, its statement the one running:

SELECT ID, USER_NAME, SECURE, STATE FROM INFORMATION_SCHEMA.CONNECTIONS ORDER BY ID;
SELECT WORKERS_BUSY, STATEMENTS_WAITING, CONNECTIONS, REFUSED, CLOSED_UNTOLD FROM INFORMATION_SCHEMA.SERVER;
                   ID | USER_NAME                                                    | SECURE | STATE
----------------------+--------------------------------------------------------------+--------+---------
                    1 | ANN                                                          | NO     | RUNNING
(1 row)
         WORKERS_BUSY |   STATEMENTS_WAITING |          CONNECTIONS |              REFUSED |        CLOSED_UNTOLD
----------------------+----------------------+----------------------+----------------------+----------------------
                    1 |                    0 |                    1 |                    0 |                    0
(1 row)