Roles, users, and permissions

CREATE ROLE/USER, GRANT/REVOKE, ALTER ROLE, enforced RBAC, privilege functions, pg_roles.

Version
Latest
v0.1.0 · latest 3 min read
On this page
  1. Model in one paragraph
  2. Quick start (operator)
  3. CREATE ROLE / USER
  4. GRANT / REVOKE
  5. ALTER ROLE (rotation + administration)
  6. Authentication contract
  7. Privilege introspection (real answers)
  8. File COPY
  9. Limits (Beta 2, documented non-goals)

Model in one paragraph#

Beta 2 has authentication (SCRAM by default against per-role catalog verifiers, with first-run bootstrap from the server secret) and enforced RBAC (table SELECT/INSERT/UPDATE/DELETE/TRUNCATE, schema USAGE/CREATE, database CONNECT, DDL by ownership, file COPY superuser-only, admin operations by attribute). Denials fail with 42501 insufficient_privilege and emit audit events. has_*_privilege returns real answers from the statement's privilege view.

Warning

Open bypass (verified): CREATE DATABASE / DROP DATABASE skip authorization — any authenticated user can run them. Until a fix lands, treat every server as single-user: do not share credentials or expose multi-user access. See Beta status.

Quick start (operator)#

sqlsource
-- as the bootstrap superuser (server --username/--password, first login binds it)
CREATE ROLE analyst WITH LOGIN PASSWORD 'unique-secret-1';
CREATE TABLE reports (id INTEGER PRIMARY KEY, body TEXT);
GRANT SELECT ON reports TO analyst;
-- analyst can now SCRAM-login and read, nothing else.

CREATE ROLE / USER#

textsource
CREATE ROLE|USER name [LOGIN|NOLOGIN] [PASSWORD 'x']
  [SUPERUSER|NOSUPERUSER] [CREATEDB|NOCREATEDB] [CREATEROLE|NOCREATEROLE]
sqlsource
CREATE ROLE analyst LOGIN PASSWORD 'secret';
CREATE USER etl LOGIN PASSWORD 'secret';
CREATE ROLE readonly NOLOGIN;
CREATE ROLE ops LOGIN CREATEDB CREATEROLE;

Passwords never persist — a verifier (salt, iterations, hash) is stored at creation. New roles hold no privileges (fail closed); the creator needs CREATEROLE (or superuser). Creating an elevated role (SUPERUSER/CREATEDB/CREATEROLE) requires superuser — no creation-time self-escalation.

GRANT / REVOKE#

textsource
GRANT role TO member | REVOKE role FROM member
GRANT privs ON [TABLE] t TO role | REVOKE privs ON [TABLE] t FROM role
GRANT USAGE|CREATE ON SCHEMA s TO role
GRANT CONNECT|CREATE ON DATABASE d TO|FROM role
sqlsource
GRANT analyst TO etl;                       -- membership (inheritance)
GRANT SELECT, INSERT ON reports TO analyst; -- object rights
GRANT USAGE ON SCHEMA finance TO analyst;
REVOKE DELETE ON reports FROM analyst;
REVOKE CONNECT ON DATABASE geeks FROM analyst;

Table privs: SELECT INSERT UPDATE DELETE TRUNCATE (+ ALL). Granting needs ownership or superuser; GRANT ... TO PUBLIC addresses every role. Dropping a table/view clears its rights (recreate starts empty); renames carry rights along. CONNECT is default-allow (PostgreSQL default) and withdrawable; USAGE on public is default-allow.

ALTER ROLE (rotation + administration)#

textsource
ALTER ROLE name [WITH] [PASSWORD 'new'] [LOGIN|NOLOGIN]
  [SUPERUSER|NOSUPERUSER] [CREATEDB|NOCREATEDB] [CREATEROLE|NOCREATEROLE]
sqlsource
ALTER ROLE analyst PASSWORD 'rotated-secret-2';  -- self-service or admin
ALTER ROLE ops SUPERUSER;                        -- superusers only

Rotation takes effect immediately (old password stops working). Attribute changes require superuser. There is no DROP ROLE in Beta 2 — deprive via NOLOGIN + revoke memberships/grants.

Authentication contract#

Method (--auth) Who can use it
scram (default) Every role with a stored verifier (audited sha2/hmac/pbkdf2, CSPRNG salts)
password/cleartext Every role with a verifier; bootstrap + legacy roles
md5 Deprecated downgrade path: disabled unless --allow-legacy-md5; bootstrap/legacy use only, single-use-per-role (first success upgrades to verifier), every login counted (legacy_auth_uses) + warned. Removal planned — see below.
none Loopback only: trusts the startup identity, authorization still enforced

First login as the configured --username with --password binds the server secret to that role and promotes it to superuser (first-run setup). Five consecutive failures lock the account for five minutes (in-memory; restarts clear). All failures emit audit events + auth_failures; lockouts emit account_lockouts.

Privilege introspection (real answers)#

sqlsource
SELECT has_table_privilege('orders', 'SELECT');
SELECT has_table_privilege('analyst', 'orders', 'SELECT');
SELECT has_schema_privilege('finance', 'USAGE');
SELECT has_database_privilege('geeks', 'CONNECT');
SELECT * FROM pg_roles;

Answers reflect the statement's privilege view (grants visible to the connection). Enforcement never consults these functions — it checks the live catalog directly, so tooling answers can never bypass a check.

File COPY#

Server-filesystem COPY ... FROM/TO <file> is superuser-only (PostgreSQL default), plus the matching table privilege. Stream COPY FROM STDIN needs INSERT; COPY TO STDOUT needs SELECT.

Limits (Beta 2, documented non-goals)#

No grant-option, no column privileges, no row-level security, no SET ROLE, no DROP ROLE, no client certificates, privilege changes take effect on next connection (catalog images load per connection — same as all DDL). MD5 is deprecated (disabled by default, --allow-legacy-md5 only, removal planned — Phase 3 replaced all other crypto with audited crates). Crypto primitives are audited RustCrypto (sha2/hmac/pbkdf2/subtle/base64/md-5-legacy-only); this is evidence, not FIPS validation. See the RBAC runbook for deployment guidance.

Related: RBAC runbook · Run the server · pg_catalog

Was this page helpful?