Sign in to your database as yourself: OAuth for Postgres 18 on SnoutData Cloud

What it is

Until now a SnoutData Cloud database had one way in: a role and a password. Everyone who worked on a project shared that password. It sat in .env files, in chats and in password managers, and the database could not tell one of them from another.

Postgres 18 added a second way in. A login can present an OAuth token instead of a password, and the server checks the token itself. We built the two pieces that make that work on our platform: the module inside your database that checks a token, and the sign-in that issues one. So the person who opens psql against your project is now a person, signed in with the same account they use for the dashboard.

This is not Auth, the product that signs the users of your application in to your application. This is about the people who work on the database: you, your team, a contractor running a migration.

Why you would want it

• No shared password. Nothing to paste into a chat, and nothing in a .env file that outlives the person who wrote it.
• A role per person. Each person signs in as their own role, and select system_user answers with who they are. The database's own logs name them.
• Removing someone takes seconds. Take their access away in the dashboard or with the CLI, and nobody has to rotate a password and tell everyone else the new one.
• A sign-in is short-lived. A token lasts at most an hour and opens one project as one role.
• Your company's single sign-on reaches the database. On Business, where your team signs in to SnoutData through your own identity provider, that is the sign-in that approves a database login.

How it works

Three parts, two of them ours. Postgres 18 brings the hook: a pg_hba.conf line with the method oauth, and a slot for a validator module that decides whether a token may open the role it asks for. libpq 18, the library under psql, brings the client side: it runs the OAuth device flow, which is the one where a program prints a code and you approve it in a browser. We wrote the validator, in Rust, as a Postgres extension built into every Postgres 18 project, and we added the issuer to the sign-in service that already signs you in to the dashboard.

A few choices in there are worth saying out loud, because each one is about what happens when something goes wrong.

• The validator never makes a network call. The issuer's public keys reach your database as a file our host agent writes and refreshes, and the validator reads that file. A login does not wait on an HTTP request, and our sign-in being down does not take database logins down with it.
• A token opens one project and one role. The server tells psql which project it is (db:<ref> in its scope), the issuer puts that project and your role in the token, and the validator refuses anything else: another project's token, another person's role, an expired token, another issuer, a key it has not been given.
• Database tokens have their own signing key. They can never be mistaken for a dashboard session in either direction: a database will not take a session token, and our APIs will not take a database token.
• Passwords keep working. OAuth applies only to the roles we make for people, so the project password, and any role you made yourself, sign in exactly as before.

Using it

The project's owner gives a person access, in the dashboard under the project's Settings (Database access), or from a terminal:

snoutdata db access grant [email protected] --level read

Access is for the people who can already see the project: its owner and, on a team project, the team's members. Each of them gets a database role of their own within a few seconds, and nothing restarts. There are two levels:

The person then connects with the connection string the dashboard or the CLI gives them:

psql "host=<ref>.db.snoutdata.com port=5432 dbname=<ref>
      user=oauth_<your id> sslmode=require
      oauth_issuer=https://accounts.snoutdata.com/auth/v1
      oauth_client_id=psql"

Visit https://dashboard.snoutdata.com/#/db-device
and enter the code: ABCD-EFGH-JKMN

They open the page, check the project, the role and the program it shows, and approve. psql connects by itself:

select session_user, system_user;
--   session_user   |     system_user
-- -----------------+----------------------
--  oauth_<your id> | oauth:<your user id>

What we measured

Our target was that an OAuth login should cost the database no more than a password login. It costs about a tenth of one. We measured on a development machine (Postgres 18.6, arm64, in Docker on an Apple-silicon Mac, a release build of the validator), using the setup durations Postgres 18 itself writes with log_connections, over 30 logins of each kind on the same server:

Most of that difference is SCRAM's key derivation, which is deliberately slow; one ES256 signature check is not. The check itself, run in process, takes 34.5 microseconds, and a token that is not even shaped like one is turned away in 51 nanoseconds.

What a person actually waits for is their own click. In our end-to-end rig (psql 18 over verified TLS, through our real front door, to a database running the validator), psql connected 1.6 seconds after the approve button was pressed, with psql checking for the token every 2 seconds. Before that, getting from starting psql to the printed code took about 105 ms. Every step of the issuer answered in under a millisecond at the median on a laptop.

Speed is the smaller half of it. The validator is tested against every way a token should be refused (expired, another project, another role, a forged signature, an unknown key, another issuer, a dashboard session presented as a database token, one person's token presented by another), with keys rotated while the server runs and with the key file missing or broken, which refuses every login rather than letting one through. Its token parser ran under a fuzzer for five minutes, 16.1 million inputs, without a crash.

Three things we found on the way

1. Every successful sign-in starts with a failure

libpq connects once without a token, to learn which issuer to ask, and the server ends that attempt with FATAL: OAuth bearer authentication failed. Then it runs the device flow and connects again. That is how the protocol works, and it collided with our front door, which counts short connections that end at once as failed attempts. Every successful OAuth sign-in would have fed it one failure. The front door now reads which sign-in method the server offered (never anything the client sends), so the first half of a sign-in is not held against you. The FATAL line still appears in your database's log before each sign-in; it is half of a normal sign-in, not an attack.

2. Our first version named the person behind a forged token

Postgres lets a validator report who a token belongs to even when it refuses the login, so an administrator can match a failure to a person. Our first version recorded that name before it had checked the signature, so a forged token wrote a real person's identity into the log. The shipped validator names someone only after the signature and the issuer check out, and our tests assert that a forged token, an unknown key or another issuer logs no identity.

3. A person whose access was removed is asked for a password

Postgres reads pg_hba.conf top to bottom and stops at the first line that matches. Our OAuth line matches members of one group, and a person whose access is removed is no longer in it, so their next attempt falls to the password line and psql says no password supplied. It is correct, and it is confusing, so the documentation says what it means. Matching the OAuth line by role name instead would have been clearer and worse: it would let a role somebody else created under a person's name be opened with that person's token.

Where it stops today

• Only libpq 18 programs built with OAuth support can sign in this way. That is psql 18 (on Debian and Ubuntu, postgresql-client-18 and libpq-oauth from the PostgreSQL apt repository) and libraries on libpq 18 such as psycopg. node-postgres, JDBC and most BI tools cannot do it yet. They keep connecting with the project password, which keeps working.
• A token cannot be recalled. From the moment you take someone's access away, no new token is issued to them. One already issued is valid until it expires, at most an hour, but it names a role, and our host drops that role within seconds on a running project (when it next wakes, on a paused one). Once the role is gone the token opens nothing.
• It needs Postgres 18. A project made on Postgres 17 keeps working exactly as it does, with its password, and there is nothing you need to do.
• An account with a second factor cannot approve from the dashboard yet, because the dashboard cannot verify that factor.
• The read level is narrower than the password. Reads your project's own tables, including rows row-level security would hide, and writes nothing. It never reads the auth or storage schemas, or the ones SnoutData keeps for itself, so no app user's password hash, token or second-factor secret.

What is next (not shipped)

None of these is available today.

• The same token, for every driver. Every Postgres driver can already send a password, so the plan is a second door: the same database token, sent in the password field and checked by the same validator. Until it exists, a program that is not on libpq 18 uses the project password.
• SnoutData Studio signing in as you. Studio connects to a Cloud project with the project password today.
• Your app's own users in the database. Each project's Auth could issue database tokens for its own users, so a user of your app could connect directly under the same row-level security your REST API applies. It needs its own design first.