PostgreSQL Security & Roles: Privileges, pg_hba & TLS

Q: How is this different from Row-Level Security?

Object privileges (GRANT/REVOKE) control which objects a role can access — which tables it can read or write. Row-Level Security controls which rows within a table a role can see or modify. They're complementary layers: grants decide table-level access; RLS narrows it to specific rows (e.g. a tenant's own data). Serious multi-tenant security uses both.

PostgreSQL Security & Roles

Securing PostgreSQL is layered: who can connect (authentication), what they can do (roles and privileges), how the connection is protected (SSL/TLS), and how data is protected at rest (encryption). Postgres gives you fine-grained control over each, and the default posture of a fresh install is not the posture you want in production — securing it is deliberate work.

The foundation is the role — Postgres's unified concept for both users and groups — and the principle of least privilege: every role should have exactly the access it needs and no more. Layered on top are host-based authentication rules (pg_hba.conf) that gate connections, TLS to encrypt traffic, and encryption for data at rest. Get these right and a compromised application credential is a contained incident rather than a total breach. This page covers the model and the hardening practices.

TL;DR

Roles are Postgres's unified users/groups. Grant privileges to roles and apply least privilege — the app role should not be a superuser or table owner.
GRANT/REVOKE control object privileges (SELECT, INSERT, etc.); use group roles and default privileges to manage access at scale.
pg_hba.conf governs who can connect from where, and how they authenticate (scram-sha-256, cert, etc.) — the connection gatekeeper.
Use SCRAM password auth and require SSL/TLS for connections so credentials and data aren't sent in the clear.
Encryption at rest (disk/volume encryption; column encryption with pgcrypto for specific fields) protects data if storage is stolen.
Combine with Row-Level Security for per-row access — object privileges control which tables, RLS controls which rows.

Quick Example

Least-privilege role setup: an application role that can use the data but can't own or alter the schema, plus reusable group roles.

If app_user's credential leaks, the attacker gets data access scoped to the app schema — not the ability to drop tables, read every database, or bypass RLS.

Core Concepts

Roles: Users and Groups Unified

Postgres has no separate "users" and "groups" — there are only roles. A role with LOGIN acts like a user (it can connect); a role without it acts like a group you grant membership into. Roles have attributes (SUPERUSER, CREATEDB, CREATEROLE, LOGIN, REPLICATION) and can be members of other roles, inheriting their privileges. This unification lets you model access cleanly: define group roles for capability sets (app_readonly, app_readwrite), and make login roles members of them.

Privileges and Least Privilege

Object-level privileges (SELECT, INSERT, UPDATE, DELETE, USAGE, EXECUTE, etc.) are granted and revoked with GRANT/REVOKE. The guiding principle is least privilege: each role gets only what it needs.

The application should connect as a role that can read/write the data it uses — but is not a superuser and does not own the tables (owners can drop/alter them and bypass RLS).
Superuser is for administration only, never for app traffic.
Use group roles and ALTER DEFAULT PRIVILEGES so new objects automatically get the right grants, instead of hand-granting each table.

Least privilege is what turns a leaked credential from catastrophe into a bounded problem.

Authentication: pg_hba.conf

Before a role's password even matters, pg_hba.conf (host-based authentication) decides whether a connection is allowed and how it must authenticate. Each line matches a connection by type, database, user, and client address, and specifies an auth method:

Order matters (first match wins), and this file is your front door — a too-permissive rule (like trust from anywhere) is a critical misconfiguration. Prefer scram-sha-256 for passwords and hostssl to force TLS.

Encryption: In Transit and At Rest

In transit — enable and require SSL/TLS so credentials and query data aren't sent in plaintext over the network. Enforce it via hostssl rules and server config; verify certificates for sensitive connections.
At rest — protect stored data if the disk is stolen. The common approach is volume/disk encryption (transparent, whole-cluster). For specific sensitive fields, pgcrypto can encrypt individual column values, though it shifts key management and querying complexity to you.

Best Practices

Never Run the App as Superuser or Table Owner

The application's login role should have data privileges only — not SUPERUSER, and not ownership of the tables (owners can alter/drop them and are exempt from RLS). This single practice contains the damage of a compromised app credential dramatically. Reserve superuser strictly for administration.

Model Access With Group Roles and Default Privileges

Manage privileges through reusable group roles (app_readonly, app_readwrite) and ALTER DEFAULT PRIVILEGES, so access is consistent and new tables are covered automatically. Hand-granting per table per user drifts and leaves gaps.

Lock Down pg_hba.conf

Treat pg_hba.conf as the perimeter. Remove permissive defaults, use scram-sha-256 (not md5 or trust), restrict source addresses to known networks, and require TLS with hostssl. Reject by default and allow specifically. A single loose line can expose the whole database.

Require TLS Everywhere

Force SSL/TLS for connections so passwords and data aren't sniffable on the wire, and verify server certificates for sensitive clients to prevent man-in-the-middle. Unencrypted database connections in production are an avoidable risk.

Manage Secrets and Rotate Credentials

Keep database passwords out of code and config files — use a secrets manager, inject at runtime, and rotate periodically. Combine with connection pooling and per-service roles so credentials are scoped and revocable. See Secrets Management.

Common Mistakes

App Connecting as a Superuser

Permissive pg_hba.conf

Unencrypted Connections

Leaving TLS optional means credentials and data can traverse the network in plaintext, sniffable on a shared or compromised segment. Require hostssl and verify certs for sensitive links.

FAQ

What's the difference between a user and a role in PostgreSQL?

There isn't one at the core — Postgres has only roles. A role with the LOGIN attribute behaves like a user (it can connect); a role without LOGIN behaves like a group you grant other roles membership in. This unification lets you define group roles for capability sets and make login roles members of them, modeling users and groups with one mechanism.

How do I apply least privilege?

Give each role exactly the privileges it needs and no more. Concretely: the application connects as a login role that can read/write its data but is not a superuser and does not own the tables; superuser is reserved for administration; and you manage grants through reusable group roles plus default privileges so new objects are covered. This contains the blast radius when a credential leaks.

What does pg_hba.conf do?

It's PostgreSQL's host-based authentication file — the gatekeeper that decides, for each incoming connection, whether it's allowed (by connection type, database, user, and client address) and how it must authenticate (e.g. scram-sha-256, certificate). Rules are matched top-down, first match wins. It's your front door: a too-permissive line (like trust from any address) is a serious vulnerability, so lock it down and reject by default.

How is this different from Row-Level Security?

Object privileges (GRANT/REVOKE) control which objects a role can access — which tables it can read or write. Row-Level Security controls which rows within a table a role can see or modify. They're complementary layers: grants decide table-level access; RLS narrows it to specific rows (e.g. a tenant's own data). Serious multi-tenant security uses both.

Do I need to encrypt PostgreSQL data?

For sensitive data, yes — on two fronts. In transit: require SSL/TLS so credentials and query data aren't sent in plaintext (enforced via hostssl). At rest: use volume/disk encryption so a stolen disk doesn't expose data, and optionally encrypt specific columns with pgcrypto for extra protection (accepting the key-management and query complexity). Managed providers often handle at-rest encryption for you — confirm it's enabled.