postgres-security

name	postgres-security
description	PostgreSQL security: authentication, pg_hba.conf, SCRAM, SSL/TLS, roles, privileges, grants, default privileges, row-level security, auditing, pgaudit, encryption, secrets, search_path safety, and hardening.

PostgreSQL Security

Prefer least privilege, explicit schemas, secure authentication, encrypted transport, and auditable changes. Distinguish object ownership from access roles.

How To Approach A Security Request

1. Establish the trust boundary: who can reach the port (network), how they authenticate (pg_hba.conf), and what they can do once in (roles/privileges). Most findings live at one of these three layers.
2. Audit before advising: run the scripts to see actual roles, memberships, grants, HBA rules, and RLS state rather than assuming the intended model is the deployed one.
3. Separate three role functions: login roles (people/apps, LOGIN, no ownership), group roles (privilege bundles, NOLOGIN), and owner roles (own objects, used only for migrations/DDL).
4. When asked "why can X access Y", trace the full chain: role memberships (including inherited), object grants, PUBLIC grants, default privileges, and ownership.
5. Treat RLS and SECURITY DEFINER advice as correctness-critical: both have sharp bypass semantics (see pitfalls).

Privilege Model Quick Reference

• Grants attach to the object, defaults come from ALTER DEFAULT PRIVILEGES — a GRANT SELECT ON ALL TABLES IN SCHEMA covers only tables that exist now; default privileges cover future tables, but only those created by the role the default was defined for.
• PUBLIC is an implicit role everyone belongs to. Before PG 15, PUBLIC could CREATE in the public schema; on upgraded clusters, revoke it explicitly.
• Schema USAGE gates everything inside; table grants are meaningless without it.
• Ownership trumps grants: owners can do anything to their objects including bypassing their own RLS unless FORCE ROW LEVEL SECURITY is set.
• PG 14+ predefined roles cover common needs without superuser: pg_read_all_data, pg_write_all_data, pg_monitor, pg_signal_backend; PG 17 adds the MAINTAIN privilege / pg_maintain.

Authentication Methods Ranked

Method	Use	Notes
scram-sha-256	Default for password auth	Set password_encryption = scram-sha-256; re-hash any md5-era passwords
cert	Machine-to-machine, replication	clientcert=verify-full can also stack with other methods
Cloud IAM (RDS/Cloud SQL/Azure tokens)	Managed platforms	Short-lived tokens replace passwords
ldap / radius	Centralized enterprise auth	LDAP simple bind sends credentials to the server — require TLS
md5	Never for new work	Replaced by SCRAM
trust	Never beyond local development	Full access with no credential
peer / ident	Local OS-socket admin access	Reasonable for local superuser on the socket only

HBA rules match first-match-wins, top to bottom. A permissive early rule silently shadows everything below it — audit order, not just presence (scripts/04).

Common Pitfalls

1. Table owners silently bypass their own RLS policies; BYPASSRLS roles and superuser bypass all RLS. Test policies as the actual application role.
2. SECURITY DEFINER functions without SET search_path = pg_catalog, pg_temp (or a pinned schema) are privilege-escalation vectors via object shadowing.
3. CREATEROLE (pre-16) could grant membership in almost any role — near-superuser. PG 16 tightened it; treat it as sensitive everywhere.
4. Granting to a login role directly instead of a group role, making rotation and offboarding a grant-archaeology project.
5. Assuming ssl = on forces encryption — clients choose unless HBA uses hostssl (and rejects host) or sslmode=verify-full is enforced client-side. Client sslmode=prefer/require without certificate verification still allows MITM.
6. Forgetting ALTER DEFAULT PRIVILEGES is per-creating-role: migrations run by a different role produce tables the app can't read.
7. RLS policies that reference current settings (current_setting('app.tenant_id')) without missing_ok/validation, failing open or erroring under pooled connections that lose session state.
8. Logging log_statement = 'all' into world-readable logs, capturing passwords from ALTER ROLE ... PASSWORD (use \password or ensure log hygiene).

References

• references/security-hardening.md - auth setup, TLS, role architecture, RLS patterns, pgaudit, and a hardening checklist.
• ../postgres/references/best-practices.md - imported domain-expert pg_hba.conf, role management, auditing, and hardening material.
• ../postgres/references/versions/postgresql-15.md - public schema permission changes.
• ../postgres/references/versions/postgresql-17.md - MAINTAIN privilege notes.

Scripts

• scripts/01-role-audit.sql - roles and risky attributes.
• scripts/02-permissions-summary.sql - schema/table privilege summary.
• scripts/03-rls-audit.sql - row-level security posture.
• scripts/04-hba-and-auth.sql - parsed HBA rules and auth-related settings.
• scripts/05-default-privileges.sql - default privilege grants.
• scripts/06-security-definer-functions.sql - security definer functions and search path risk.
• scripts/07-public-schema-and-ownership.sql - public schema, owners, and broad grants.
• scripts/08-ssl-audit.sql - SSL posture and active SSL connections.

Cross-Skill Routing

• Managed-platform IAM auth mechanics (RDS/Cloud SQL/Azure tokens, Supabase keys) go to postgres-cloud.
• pg_hba.conf file mechanics and reloads go to postgres-infrastructure.
• Replication connection auth and certificates pair with postgres-ha-replication.
• Audit-log volume and performance impact go to postgres-monitoring.
• RLS policy performance (per-row subplans) goes to postgres-engineering.