PostgreSQL Security Guide: Roles, Authentication and Hardening

Most PostgreSQL compromises come from configuration rather than code: a permissive pg_hba.conf line, an application connecting as a superuser, a schema anyone can write to, or a table that relies on row level security nobody switched on. PostgreSQL ships with sensible defaults in recent releases, but upgraded clusters, container images and managed services often carry older or looser settings forward. This guide goes engine deep on PostgreSQL 14 to 18: the role model, client authentication, transport encryption, schema and function safety, row level security, audit logging and patching. It is written for DBAs, platform engineers and security engineers who own self-managed or managed PostgreSQL. For engine-neutral principles such as the database threat model and database activity monitoring, see the Database Security Guide.

Key takeaways

• pg_hba.conf is the front door. Every line is a rule matched top to bottom; one stray trust or 0.0.0.0/0 entry overrides all your careful password policy.
• SCRAM, not MD5. password_encryption defaults to scram-sha-256, and the PostgreSQL 18 documentation states that MD5 password support is deprecated and will be removed, so migrate stored hashes now.
• Superuser is not an application role. Applications should connect as a login role with no ownership of the objects they use, and dangerous predefined roles such as pg_execute_server_program should be granted to nobody routinely.
• Schemas and search_path are a privilege boundary. Clusters upgraded from before PostgreSQL 15 may still let every role create objects in public, which enables trojan objects and function hijacking.
• Row level security must be enabled per table. Policies do nothing until ENABLE ROW LEVEL SECURITY is set, and owners and roles with BYPASSRLS skip them unless you force them.

The role model: logins, groups, ownership and predefined roles

PostgreSQL has one concept, the role, which acts as a user when it has the LOGIN attribute and as a group when other roles are members of it. Security depends on four things: role attributes, object ownership, granted privileges and membership in predefined roles.

• Attributes: SUPERUSER bypasses every permission check, CREATEROLE lets a role create and manage other roles, CREATEDB creates databases, REPLICATION allows streaming replication connections and BYPASSRLS skips row level security. Inventory them with SELECT rolname FROM pg_roles WHERE rolsuper OR rolcreaterole OR rolbypassrls OR rolreplication;.
• Ownership: the owner of a table can alter, drop and grant on it and is exempt from its row level security by default. An application that owns its schema can therefore drop its own tables.
• Privileges: grant SELECT, INSERT, UPDATE, DELETE and USAGE on schemas explicitly to group roles, then add login roles as members. Use ALTER DEFAULT PRIVILEGES so tables created by the migration role are automatically readable by the application group.
• Predefined roles: PostgreSQL ships built-in roles that grant broad capability without superuser. Some are safe for monitoring, others are equivalent to operating system access.

A practical layout for an application database uses four roles: an owner role with NOLOGIN that owns the schema, a migration login that is a member of the owner role and is used only by the deployment pipeline, an application login granted DML only through a group role, and a read-only reporting login. See the Database Credential and Access Security Guide for how these service credentials are vaulted and rotated.

Client authentication with pg_hba.conf

PostgreSQL decides how a client authenticates by reading pg_hba.conf from top to bottom and using the first line whose connection type, database, user and address match. There is no fall-through: if the first match uses trust, no password is checked. If no line matches, the connection is refused.

A hardened file follows a simple pattern: local socket lines with peer for the administrator, explicit reject lines for superuser and replication roles from the network, then hostssl lines (not host) naming each database, each role or group (prefix a group with +) and the narrowest CIDR, with scram-sha-256 or cert. Add clientcert=verify-full on password lines where you also want certificate binding. Finish with no catch-all all all 0.0.0.0/0 line. Set listen_addresses to the specific private interface rather than *, keep port 5432 behind a firewall or security group. Internet scanners index exposed database ports continuously, so assume an open listener will be found.

Passwords and SCRAM migration

• Find legacy hashes: SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'; (superuser only).
• Re-set them: with password_encryption = 'scram-sha-256', reset each password (from psql use \password, which hashes client side so the cleartext never reaches the server log).
• Then switch rules: change md5 lines to scram-sha-256 once every client driver supports SCRAM, which current libpq, JDBC, Npgsql and psycopg releases do.
• Never put passwords in SQL logs: CREATE ROLE ... PASSWORD 'x' can appear in server logs and in pg_stat_statements; use client-side hashing or a secrets manager that issues dynamic credentials. See the Secrets Management Guide.

Encryption in transit and at rest

Set ssl = on with a certificate from your internal CA and keep ssl_min_protocol_version at its default of TLSv1.2 or raise it to TLSv1.3 once clients support it. Server-side settings are only half of the control: libpq clients default to sslmode=prefer, which silently falls back to clear text and does not check the server identity. Require sslmode=verify-full with sslrootcert in every application connection string, and use hostssl in pg_hba.conf so the server refuses unencrypted sessions. See the How TLS Works Guide.

Community PostgreSQL does not include transparent data encryption. At rest, the options are volume or disk encryption (LUKS, cloud provider volume encryption), column-level encryption with the pgcrypto extension or application-side encryption, and encrypted backups. Volume encryption protects against stolen disks and snapshots but not against anyone who can log in. Column encryption with keys held outside the database protects specific fields from DBAs and from a dumped table, at the cost of indexing and querying on those columns. Treat WAL archives, base backups and logical dumps as copies of the full dataset: encrypt them and store their keys separately from the backup repository. See the Encryption Guide.

Schemas, search_path and function safety

PostgreSQL resolves unqualified names through search_path. If an untrusted role can create objects in a schema that appears in another role's search path, it can plant a function or operator with the same name as one the victim calls and have it run with the victim's privileges. The PostgreSQL documentation describes secure schema usage patterns and notes that in PostgreSQL 15 and later the default no longer grants CREATE on public to everyone, but databases upgraded from earlier versions keep the old grant.

• Fix upgraded databases: run REVOKE CREATE ON SCHEMA public FROM PUBLIC; in every database and check template1 so new databases inherit the fix. Then look for objects in public named like objects in pg_catalog.
• Database owners are trusted: the documentation is explicit that no secure pattern exists if an untrusted user owns the database or holds ADMIN OPTION on a relevant role. Do not hand database ownership to tenants or applications.
• SECURITY DEFINER functions: these run as their owner, so they are privilege escalation points. Give each one a fixed SET search_path = pg_catalog, pg_temp (or a trusted schema), schema-qualify every reference, revoke EXECUTE from PUBLIC and grant it only to the roles that need it.
• Extensions and languages: only trusted languages (plpgsql, sql) should be available to ordinary roles. Untrusted languages such as plpython3u run with database server OS rights and can only be used by superusers; leave them uninstalled unless there is a reviewed need.
• COPY and file access: COPY ... TO/FROM a server file and COPY ... PROGRAM are limited to superusers and the file or program predefined roles. Persistence here maps to ATT&CK T1505.001.

Row level security and multi-tenant data

Row level security (RLS) adds per-row predicates to every query on a table. It is the usual way to isolate tenants in a shared schema and underpins platforms such as Supabase that expose PostgreSQL to browser clients. Three rules decide whether it actually protects anything.

1. Enable it on every exposed table: ALTER TABLE t ENABLE ROW LEVEL SECURITY;. Policies created on a table where RLS is not enabled are ignored. When RLS is enabled and no policy applies, access is default deny.
2. Force it where the owner connects: table owners bypass RLS unless you add ALTER TABLE t FORCE ROW LEVEL SECURITY;. Superusers and roles with BYPASSRLS always bypass it, so the application must never connect as either.
3. Write both USING and WITH CHECK: USING filters rows that can be read or targeted; WITH CHECK stops a user inserting or updating rows into another tenant. Base the predicate on a value the client cannot forge, such as a role name or a setting applied by a trusted pooler, not a column the client supplies.

The Moltbook database exposure shows the failure mode: a Supabase key embedded in public JavaScript was intended to be safe, but Row Level Security had not been configured, so the key gave unauthenticated read and write access to every table, including agent API keys. Add a pipeline check that fails a deployment when any table in an exposed schema has relrowsecurity = false in pg_class.

Audit logging and detection

Native logging covers connections and statements; the open source pgaudit extension adds structured session and object audit records. pgaudit must be listed in shared_preload_libraries, and its pgaudit.log setting selects statement classes such as read, write, ddl, role and misc. Object-level audit is configured by granting privileges to the role named in pgaudit.role, which gives finer detail on sensitive tables than logging every read.

Alert on: failed authentication bursts against one role (ATT&CK T1110 Brute Force); successful logins by superuser or replication roles from unexpected addresses (T1078 Valid Accounts); ALTER ROLE ... SUPERUSER, GRANT pg_execute_server_program or CREATE EXTENSION outside change windows; COPY ... PROGRAM or COPY TO a file; bulk reads by an application role far above its baseline (T1213.006 Databases collection); and DROP DATABASE or mass TRUNCATE (T1485 Data Destruction). Ship logs off host as described in the Linux Logging and auditd for Defenders Guide.

Patching, versions and managed services

The PostgreSQL project supports each major version for five years and ships minor releases at least every three months, with out-of-cycle releases for critical fixes. At the time of writing, versions 14 to 18 are supported; PostgreSQL 14 reaches its final release on 12 November 2026 and 13 is already end of life. The project recommends always running the current minor release because minor upgrades are lower risk than staying behind. Major upgrades need pg_upgrade or a dump and reload, so plan them well ahead of end of life. Track the CISA KEV catalogue for PostgreSQL, extensions and poolers. See the Patch Management Guide.

On managed services (Amazon RDS and Aurora, Azure Database for PostgreSQL, Google Cloud SQL), the provider patches the engine and controls the host, and you do not get a true superuser. You still own pg_hba-equivalent network rules, TLS enforcement parameters, roles and grants, RLS, extension choices and log export. Prefer the provider's IAM database authentication where available, because it replaces stored passwords with short-lived tokens tied to cloud identities. See the Cloud Logging and Audit Trail Guide.

Where identity fits: service roles as non-human identities

In most estates the majority of PostgreSQL logins belong to applications, ETL jobs, pollers, backup agents and increasingly AI agents, not people. Each is a non-human identity with a credential that is often long-lived and copied into configuration files (ATT&CK T1552.001 Credentials In Files). Give each workload its own role per environment, store its secret in a vault or replace it with certificate or IAM token authentication, set CONNECTION LIMIT to its expected pool size, and record an owner. The PocketOS database deletion shows what happens when an automated actor holds a credential that can destroy production. See the OWASP NHI Top 10 NHI5 Overprivileged NHI Guide.

Implementation roadmap

1. Inventory (week 1): list every cluster, version, listen address and pg_hba.conf. Export pg_roles attributes, predefined role memberships and database owners. Flag anything below PostgreSQL 15 or on an end-of-life version.
2. Close exposure (weeks 1 to 2): remove internet reachability, replace host with hostssl, delete trust lines and catch-all CIDRs. Test on a staging copy, keep the previous file and roll back with a reload if a client breaks.
3. Turn on logging first (week 2): enable connection logging and pgaudit role, ddl, ship to the SIEM and build a two-week baseline before changing privileges, so you can see which roles are actually used.
4. Fix authentication (weeks 3 to 6): migrate MD5 hashes to SCRAM, require sslmode=verify-full in clients, move service passwords into a vault or IAM authentication.
5. Reduce privilege (weeks 4 to 10): introduce owner, migration, application and read-only roles; remove superuser and ownership from application logins; revoke CREATE on public; review SECURITY DEFINER functions. Roll out per application in pilot rings, with the old grant scripted for quick reinstatement.
6. Protect data (ongoing): enable and force RLS on multi-tenant tables with automated tests, encrypt backups and sensitive columns, and test restores quarterly.
7. Measure and baseline: apply a CIS PostgreSQL Benchmark profile through configuration management and alert on drift.

Choosing your approach: small versus large estates

A small team running a handful of databases gets most of the benefit from a managed service with IAM authentication, enforced TLS, the four-role pattern and pgaudit on role, ddl. Large estates with hundreds of clusters need the controls expressed as code: pg_hba.conf and parameter files templated by configuration management, roles created by a provisioning pipeline, dynamic credentials from a secrets manager, and continuous checks against the CIS benchmark. Choose certificate authentication where you run a PKI, otherwise SCRAM with vaulted rotation.

Metrics that show it is working

• Exposure: number of clusters reachable from the internet or from user networks (target: zero).
• Authentication: percentage of roles with SCRAM verifiers and percentage of sessions in pg_stat_ssl using TLS (target: 100 percent).
• Privilege: count of login roles with superuser, BYPASSRLS or dangerous predefined roles, trending down to named break-glass accounts only.
• Data isolation: exposed tables without RLS enabled (target: zero, enforced in CI).
• Patch latency: days from a PostgreSQL minor release to deployment across the estate.
• Recovery: successful restore tests per quarter and measured restore time.

Framework mapping

Common failure patterns

• Catch-all pg_hba lines: host all all 0.0.0.0/0 md5 added during troubleshooting and never removed, often combined with listen_addresses = '*' in container images.
• The application as superuser: one postgres password shared by the application, the migration tool and humans, which turns any SQL injection into host command execution through COPY ... PROGRAM. See the SQL Injection Prevention Guide.
• RLS written but not enabled: policies exist in migrations but the ENABLE or FORCE statement is missing, or the API connects as the owner, as in the Moltbook exposure above.
• Upgraded public schema: clusters taken from PostgreSQL 13 or earlier with pg_upgrade still grant CREATE on public to all roles.
• Client TLS on prefer: the server has a certificate, but clients use the default sslmode and never verify it, so a network attacker can downgrade or intercept.

Practitioner checklist

• pg_hba.conf has no trust on TCP, no catch-all CIDR, uses hostssl and rejects superuser and replication roles from the network.
• password_encryption = scram-sha-256 and no md5 verifiers remain in pg_authid.
• Every client connection string uses sslmode=verify-full; pg_stat_ssl shows TLS on all sessions.
• Application logins own nothing, hold no superuser, CREATEROLE or BYPASSRLS, and have a per-role CONNECTION LIMIT.
• Nobody routinely holds pg_execute_server_program, pg_read_server_files, pg_write_server_files or pg_write_all_data.
• REVOKE CREATE ON SCHEMA public FROM PUBLIC applied in every upgraded database and in template1.
• All SECURITY DEFINER functions set a fixed search_path and have EXECUTE revoked from PUBLIC.
• RLS enabled and forced on every multi-tenant or API-exposed table, with a CI test.
• pgaudit loaded with at least role, ddl; connection logging on; logs shipped off host with alerts defined.
• Every cluster on a supported major version and the latest minor release; backups encrypted and restore-tested.

Standards and references

• PostgreSQL: Client Authentication (pg_hba.conf)
• PostgreSQL: Predefined Roles
• PostgreSQL: Schemas and Secure Schema Usage Patterns
• PostgreSQL: Row Security Policies
• PostgreSQL: Error Reporting and Logging
• PostgreSQL: Versioning Policy
• pgAudit: PostgreSQL Audit Extension
• NIST: SP 800-53 Rev. 5 Security and Privacy Controls

Related NHI Mgmt Group resources: Database Security Guide · Database Credential and Access Security Guide · MySQL Security Guide · Linux Server Hardening Guide · Secrets Management Guide