Skip to main content

Authentication

By the end of this page you will have a role with a password, a working psql connection over it, and a host-based rule file that controls who gets asked for that password in the first place.

Password authentication: SCRAM-SHA-256 and md5​

ScramDB authenticates a password-protected connection with SCRAM-SHA-256, the same mechanism PostgreSQL itself defaults to, and with the md5 challenge for a role whose stored password is an md5 secret. There is no LDAP, Kerberos, GSSAPI, or OAuth integration, and no client-certificate authentication. A plaintext password is hashed before storage (PBKDF2-HMAC-SHA256 with a random salt and scram_iterations rounds, 4096 by default); the plaintext password is never persisted, and pg_roles never exposes the stored secret (it shows ********).

A password reaches the role as PostgreSQL stores it, so a dump from PostgreSQL restores into ScramDB and the same client logs in:

Password givenStored as
PlaintextA SCRAM-SHA-256 secret at scram_iterations, or md5 followed by the md5 of the password and the role name under SET password_encryption = 'md5'
A SCRAM-SHA-256 secret (SCRAM-SHA-256$4096:salt$StoredKey:ServerKey)The secret as given, with its own iteration count
An md5 secret (md5 and 32 lowercase hex digits)The secret as given
The empty string, or the secret of the empty stringNo password, with the notice empty string is not a valid password, clearing password

An md5 secret cannot outlive the name it was made from: renaming a role that holds one clears its password and answers MD5 password cleared because of role rename, while a SCRAM-SHA-256 secret is kept. UNENCRYPTED PASSWORD is refused (0A000), as in PostgreSQL.

A role can only authenticate by password if all of these are true: it was created (or altered) with LOGIN (the default for a bare CREATE ROLE is no login), it has a password set, and, if it has VALID UNTIL set, that timestamp has not passed. Every one of those cases, plus a wrong password and an unknown role name, is refused with the exact same client-visible message: password authentication failed. This is deliberate: a message that varied by cause would let a client discover whether a given role name exists, or is locked out, without actually knowing its password. The real reason is logged on the server, never sent to the client.

Step by step: create a role and connect​

  1. Connect as the bootstrap superuser. A fresh ScramDB instance creates a superuser role named scramdb automatically on first startup:

    bash
    psql -h localhost -p 5432 -U scramdb scramdb

    No password prompt: the superuser starts with no credential at all, and the default host-based rule trusts 127.0.0.1 outright. If your only path to the node isn't localhost, for example a Kubernetes pod reachable solely through kubectl exec, see Seeding a password for the bootstrap superuser below.

  2. Create a role with a password and permission to log in:

    sql
    CREATE ROLE app_user LOGIN PASSWORD 'a-real-password';

    Expected output: CREATE ROLE.

    Under SET password_encryption = 'md5', or when the password is given as an md5 secret, CREATE ROLE and ALTER ROLE answer PostgreSQL's warning setting an MD5-encrypted password (01P01, with PostgreSQL's DETAIL and HINT) and the role logs in with the md5 challenge. md5 is deprecated in PostgreSQL; prefer the default.

  3. Confirm it landed, without exposing the password:

    sql
    SELECT rolname, rolcanlogin, rolpassword FROM pg_roles WHERE rolname = 'app_user';

    Expected output: one row, rolcanlogin is t, rolpassword is ******** (ScramDB never surfaces the hash through this view).

  4. Connect as the new role from a fresh terminal:

    bash
    psql -h localhost -p 5432 -U app_user scramdb

    psql prompts for the password you set in step 2. On success you land at the scramdb=> prompt.

If it fails: connecting from a non-localhost address before you've set up a password or a matching rule fails with password authentication failed. Either set a password on the role (step 2) or add a rule for that address in hba_file (below). If the server itself is running with --pg-no-auth, every connection is trusted with no password prompt at all, roles and passwords notwithstanding; see Security overview and Docker: Securing a node.

Host-based rules (hba_file)​

hba_file (under [general] in the config TOML) points at a pg_hba.conf-style file that decides, per incoming connection, whether it's trusted outright, asked for a password, or rejected. Rules are evaluated top to bottom; the first matching line wins, and a connection matching no line is rejected.

Each line has the shape:

text
<conn_type> <database> <user> <address> <method>
FieldAccepted values
conn_typehost, hostssl (TLS required), hostnossl (TLS forbidden), or local. ScramDB listens on TCP only, so a local line (local <database> <user> <method>, no address) is read and kept in order but matches no connection, and one warning in the log names it.
databaseall, sameuser (the database named like the connecting role), one exact database name, or a comma list of these (a line matches when any entry does). samerole, replication, +group, @file and quoted names are not supported.
userall, one exact role name, or a comma list of these. sameuser belongs to the database field only. +group, @file and quoted names are not supported.
addressAn IPv4 or IPv6 CIDR, for example 10.0.0.0/8 or ::/0. No hostname form.
methodtrust (no password), password or md5 (the password is proved in the form the role's stored secret allows: the SCRAM-SHA-256 exchange for a SCRAM secret, the md5 challenge for an md5 secret), scram-sha-256 (the SCRAM-SHA-256 exchange alone: a role that holds an md5 secret is refused), or reject. Use only these method keywords. A line takes no authentication option after the method.

Step by step: write and load a rule file​

  1. Create a rule file that trusts your office network outright and requires a password (over TLS) from everywhere else:

    text
    # /etc/scramdb/pg_hba.conf
    host all all 10.0.0.0/8 trust
    hostssl all all 0.0.0.0/0 password
    hostssl all all ::/0 password
  2. Point hba_file at it in your config TOML:

    toml
    [general]
    hba_file = "/etc/scramdb/pg_hba.conf"
  3. Restart ScramDB and check the log for a clean startup. A missing or unreadable path is a fatal error, not a silent fallback: the server logs FATAL: hba_file '<path>' unreadable: <reason> and exits immediately, so a typo'd path is loud rather than a surprise open door. A line the server cannot read stops the start the same way, with the line's number and the reason: FATAL: hba_file '<path>' cannot be read, line <n>: <reason>.

  4. Test from a client inside 10.0.0.0/8: connect with psql and confirm you land at the prompt with no password request. Test from outside that range over sslmode=require: confirm you're prompted for a password.

If it fails: a connection that matches no line at all is rejected outright. If a client you expect to work is being rejected, check the file top to bottom for the first line that could match its address, database, and user, and remember a field takes all, a name or a comma list of names, never a group.

What happens with no hba_file set​

Leaving hba_file empty (the default) is not "no rules": ScramDB installs a built-in default equivalent to:

text
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 0.0.0.0/0 password
host all all ::/0 password

In other words: localhost is trusted, everything else needs a password. This is the same built-in ruleset the Docker deployment guide describes.

What happens with --pg-no-auth​

--pg-no-auth (or pg_no_auth = true in [general]) disables authentication entirely and ignores hba_file completely, even if one is configured. Every connection, from any address, to any database, as any existing role, is trusted with no password check. A user name that names no role is refused with SQLSTATE 28000, as PostgreSQL refuses it. This is the flag the published Docker image sets by default; see Security overview for why that matters and how to turn it off.

Seeding a password for the bootstrap superuser​

The connection in step 1 above works with no password because of the default hba_file rule above: 127.0.0.1 is trusted, nothing else is. That's a safe default, not a broken one, right up until you need to reach the superuser from somewhere that isn't localhost. The most common reason is a container orchestrator: a Helm or kustomize install has no localhost to run psql from except kubectl exec, so the first connection to a chart-driven cluster needs a credential seeded in before anyone can reach it.

Three environment variables seed that first credential, read once at startup and never again once the superuser has one:

  • SCRAMDB_INITIAL_PASSWORD_FILE (recommended): a path to a file whose first line is the password, typically a mounted Kubernetes Secret or Docker secret. The value itself never appears in the pod spec or /proc/<pid>/environ.
  • SCRAMDB_INITIAL_PASSWORD: the literal password value. Simpler to wire up, but weaker: it's visible in kubectl describe pod and /proc/<pid>/environ.
  • SCRAMDB_RANDOM_INITIAL_PASSWORD: set to exactly yes to have ScramDB generate a strong random password and log it exactly once at first boot, the same convention MySQL's --initialize uses.

Exactly one may be set. More than one fails startup rather than picking a winner: more than one initial superuser password source is set (...); unset all but one - ambiguity about which credential is live is refused, never resolved by precedence.

The guarantee that matters most for a security-sensitive credential: seeding only ever runs while the superuser has no credential yet. ScramDB checks that before it looks at any of the three variables, so a redeployed pod, a rotated Secret, or a leftover environment variable can never reset a password you've already set, and can never lock you out. Every restart after the first is a no-op.

For the full precedence, the exact file-parsing rules, the empty-password refusal, and copy-pasteable Kubernetes and Docker examples, see Environment Variables: Seeding the superuser password.

Managing roles and passwords​

sql
-- Change a role's password
ALTER ROLE app_user PASSWORD 'a-new-password';

-- Clear a role's password (it can no longer authenticate by password)
ALTER ROLE app_user PASSWORD NULL;

-- Rename a role
ALTER ROLE app_user RENAME TO app_svc;

-- Remove a role entirely
DROP ROLE app_svc;

DROP ROLE nonexistent and ALTER ROLE nonexistent ... both error loudly rather than silently doing nothing; use DROP ROLE IF EXISTS for an idempotent teardown script. Dropping a role that still holds grants succeeds; the grants are simply removed along with it.

Role attributes (SUPERUSER, CREATEDB, CREATEROLE, LOGIN, REPLICATION, BYPASSRLS), role membership, and what each attribute actually gates are covered in Roles and privileges.