On this page
Companion to Roles, users, and permissions (SQL surface). This page is the operator procedure.
1. Bootstrap (first run)#
Start the server with an explicit secret (never the built-in on remote):
cargo run --bin plomid-server -- --data ./data --host 127.0.0.1 \
--username plomid --password '<unique-bootstrap-secret>' --auth scramThe first SCRAM login as plomid binds that secret to the role and promotes it to superuser. Verify:
SELECT has_table_privilege('any_table', 'SELECT'); -- true (superuser)
SELECT * FROM plomid_stat_security(); -- auth_failures=0Rotate the bootstrap secret afterwards (ALTER ROLE plomid PASSWORD ...) and restart the server with the new value — the catalog verifier governs logins from then on.
2. Least-privilege onboarding#
CREATE ROLE app_reader WITH LOGIN PASSWORD '<secret>';
CREATE ROLE app_writer WITH LOGIN PASSWORD '<secret>';
CREATE ROLE etl WITH LOGIN PASSWORD '<secret>' CREATEDB;
GRANT USAGE ON SCHEMA public TO app_reader; -- public USAGE is default; explicit is fine
GRANT SELECT ON orders TO app_reader;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_writer;Checklist per role: can it log in? (LOGIN), does it hold exactly its tables? (has_table_privilege), is anything PUBLIC-granted beyond public.USAGE? (default-deny everything else).
3. Separation patterns#
- Read-only service:
LOGIN+SELECTgrants only. DDL/DML/file-COPY all deny with42501. - Team lead: own schema (
CREATE SCHEMA team; GRANT ...), ownership gives full rights on created objects;CREATEROLEto onboard members (cannot promote to superuser). - Revocation drill:
REVOKEthe grant → reconnect → operation denies. Dropping a table wipes its grants; renaming carries them.
4. Lockout and audit#
- 5 consecutive failures → 5-minute lockout (
account_lockouts+ log). In-memory: restarts clear. Deliberate (no durable self-lockout of the last superuser). - Every denial:
securitylog line (security_decision user=… action=… object=… decision=… reason=…, no secrets, no catalog/grant/value leaks — denial messages readpermission denied for {object}: role "{user}" lacks {privilege}) + counters. Audited actions: authenticate, authorize, fileCOPY, admin operations, lockouts. - Watch all seven counters:
SELECT auth_failures, authz_denials, account_lockouts, legacy_auth_uses, tls_connections, tls_handshake_failures, tls_plaintext_refusals FROM plomid_stat_security(); - The JSONL
--audit-log(see Configuration reference) records the same allow/deny decisions durably with size-based rotation; without it, auditing lives in the tracing log + in-memory counters only.
5. Connection hygiene#
Privilege/role/catalog changes take effect on next connection (per-connection catalog images — same rule as all DDL). Poolers should recycle connections after administrative changes. Long-lived sessions keep their connect-time view.
6. What RBAC does NOT cover (do not rely on it for these)#
Transport without TLS (see TLS runbook), at-rest encryption, column/row-level control, grant delegation, client certificates, durable lockout, or any certification. MD5 is deprecated: disabled unless --allow-legacy-md5, single-use-per-role migration funnel, counted and warned — removal planned (Beta 3 target: delete the method, the flag, and the md-5 dependency). Crypto primitives are audited RustCrypto; this is evidence, not FIPS validation.
Next: Configuration · TLS