Just-in-time production DB access with expiring Postgres credentials

TL;DR — You can implement just-in-time production database access in plain PostgreSQL without buying a PAM product: create a no-login group role for permissions, mint per-user login roles only when needed, and set VALID UNTIL so they expire automatically. The most common failure is granting object permissions directly to the temporary login user instead of to a reusable group role, which makes revocation messy and inconsistent. Reading time: ~5 min

Goal

When you finish, engineers can get time-boxed access to the production PostgreSQL database by creating a personal login role that expires automatically, inherits only the minimum required privileges through a group role, and can be revoked immediately with a single SQL command.

Prerequisites

• PostgreSQL 14+ on the production database server — check with psql --version
• A database admin account that can create roles and grant privileges, typically a role with CREATEROLE and ownership of the target schema/tables
• Network path to the production database from your admin workstation or bastion host
• psql client installed locally or on the bastion — check with psql --version
• TLS connection details for production: host, port, database name, and CA cert path if your deployment requires certificate verification
• The engineer's corporate identifier to embed in the temporary role name, for example alice or jdoe
• A ticket/change/request ID to include in the role comment, for example INC-4821
• A decision on the access window, for example 2 hours

Steps

Step 1: Create a reusable read-only group role

Run this once per database permission set. Replace appdb and public if your database or schema names differ.

psql "host=prod-db.example.internal port=5432 dbname=appdb user=postgres sslmode=require" <<'SQL'
CREATE ROLE prod_readonly NOLOGIN;
GRANT CONNECT ON DATABASE appdb TO prod_readonly;
GRANT USAGE ON SCHEMA public TO prod_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO prod_readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO prod_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO prod_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO prod_readonly;
SQL

You should see CREATE ROLE, then a series of GRANT and ALTER DEFAULT PRIVILEGES lines with no ERROR: output.

Step 2: Create a JIT login role with an expiry time

This creates a personal login that expires in 2 hours. Replace alice, the password, and the timestamp. Use UTC to avoid timezone mistakes.

psql "host=prod-db.example.internal port=5432 dbname=appdb user=postgres sslmode=require" <<'SQL'
CREATE ROLE jit_alice_20261030 LOGIN
  PASSWORD 'replace-with-32-random-chars'
  VALID UNTIL '2026-10-30 16:00:00+00'
  INHERIT;
GRANT prod_readonly TO jit_alice_20261030;
COMMENT ON ROLE jit_alice_20261030 IS 'owner=alice ticket=INC-4821 purpose=prod-debug expires=2026-10-30T16:00:00Z';
SQL

You should see CREATE ROLE, GRANT ROLE, and COMMENT.

Step 3: Hand the user an exact connection command

Give the engineer a literal command. If your environment requires full certificate validation, use sslmode=verify-full and include sslrootcert.

PGPASSWORD='replace-with-32-random-chars' psql "host=prod-db.example.internal port=5432 dbname=appdb user=jit_alice_20261030 sslmode=require"

You should see a psql prompt like this:

psql (16.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

appdb=>

Step 4: Confirm the role is actually time-boxed and least-privileged

From an admin session, inspect the role attributes and membership.

psql "host=prod-db.example.internal port=5432 dbname=appdb user=postgres sslmode=require" <<'SQL'
\du+ jit_alice_20261030
SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolname = 'jit_alice_20261030';
SELECT r.rolname AS member_of
FROM pg_auth_members m
JOIN pg_roles r ON r.oid = m.roleid
JOIN pg_roles u ON u.oid = m.member
WHERE u.rolname = 'jit_alice_20261030';
SQL

You should see Valid until set to the UTC timestamp and membership in prod_readonly only.

Step 5: Revoke access immediately when the work is done

Use this even if the expiry window has not elapsed yet.

⚠️ DROP ROLE will fail if the temporary role still owns objects or has active sessions. Terminate sessions first, then drop the role.

psql "host=prod-db.example.internal port=5432 dbname=appdb user=postgres sslmode=require" <<'SQL'
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'jit_alice_20261030'
  AND pid <> pg_backend_pid();
DROP ROLE IF EXISTS jit_alice_20261030;
SQL

You should see one row per terminated session, then DROP ROLE.

Step 6: Optional hardening in pg_hba.conf

If your production database is reachable from many networks, restrict JIT logins to the bastion or VPN CIDR. Add a line like this on the database host, then reload PostgreSQL.

hostssl appdb all 10.20.30.0/24 scram-sha-256

Reload:

sudo systemctl reload postgresql

You should see no output from systemctl reload, and new connections outside 10.20.30.0/24 should fail with no pg_hba.conf entry.

Verify it works

Run these checks end to end.

1. Connect as the temporary user and read a table:

PGPASSWORD='replace-with-32-random-chars' psql "host=prod-db.example.internal port=5432 dbname=appdb user=jit_alice_20261030 sslmode=require" -c "SELECT current_user, now();"

Expected shape:

      current_user      |              now
------------------------+-------------------------------
 jit_alice_20261030     | 2026-10-30 14:07:11.123456+00
(1 row)

1. Confirm writes are blocked for a read-only role:

PGPASSWORD='replace-with-32-random-chars' psql "host=prod-db.example.internal port=5432 dbname=appdb user=jit_alice_20261030 sslmode=require" -c "CREATE TABLE jit_test(id int);"

Expected error:

ERROR:  permission denied for schema public
LINE 1: CREATE TABLE jit_test(id int);
                     ^

1. After the expiry time passes, test login again:

PGPASSWORD='replace-with-32-random-chars' psql "host=prod-db.example.internal port=5432 dbname=appdb user=jit_alice_20261030 sslmode=require" -c "SELECT 1;"

Expected error:

psql: error: connection to server at "prod-db.example.internal" (10.20.30.15), port 5432 failed: FATAL:  password authentication failed for user "jit_alice_20261030"

On some builds you may instead see:

FATAL:  role "jit_alice_20261030" is not permitted to log in

Common pitfalls

Granting table permissions directly to each temporary user

Mistake: running GRANT SELECT ON ... TO jit_alice_20261030 instead of granting a reusable group role.

Symptom: every access request needs custom grants, revocation leaves orphaned permissions, and new tables are not covered consistently.

Fix: create one NOLOGIN role like prod_readonly, grant object privileges to that role, then grant that role to each JIT login.

Forgetting ALTER DEFAULT PRIVILEGES

Mistake: granting SELECT ON ALL TABLES once and assuming future tables inherit it.

Symptom: the JIT user can query old tables but gets ERROR: permission denied for table ... on tables created later.

Fix: run ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO prod_readonly; as the object-creating owner role.

Using local time in VALID UNTIL

Mistake: setting VALID UNTIL '2026-10-30 16:00:00' without timezone context.

Symptom: access expires earlier or later than expected after DST changes or when the admin shell and DB server use different timezones.

Fix: always use UTC with an offset, for example VALID UNTIL '2026-10-30 16:00:00+00'.

Dropping the role while sessions are still open

Mistake: running DROP ROLE jit_alice_20261030; first.

Symptom: PostgreSQL returns ERROR: role "jit_alice_20261030" cannot be dropped because some objects depend on it or the session stays active until disconnect.

Fix: terminate active backends with pg_terminate_backend, then run DROP ROLE IF EXISTS ....

Relying on expiry alone for emergency revocation

Mistake: assuming VALID UNTIL is enough when an incident requires immediate lockout.

Symptom: the user keeps working in an already-open session until they disconnect.

Fix: revoke now by terminating sessions and dropping the role immediately.

Allowing password auth without TLS verification

Mistake: handing out a password with sslmode=disable or weak network controls.

Symptom: credentials are exposed to interception on untrusted networks, or compliance review flags the access path.

Fix: use sslmode=require at minimum; use sslmode=verify-full sslrootcert=/path/to/ca.pem where your PKI supports it, and restrict source CIDRs in pg_hba.conf.

This article was written by an AI system and published pending human review. Verify anything you intend to act on.