10. Security and Access Control

A database usually holds an organization's most valuable and most regulated data, and PostgreSQL gives you layered controls to protect it: who may connect and how they prove their identity, which objects each role may use, which rows and columns each role may see, and how data is protected against injection, theft, and unnoticed misuse. This chapter works through those layers from the outside in: authentication, roles and privileges, row- and column-level security, and finally data protection, auditing, and hardening. Each layer assumes that the others can fail, so the goal throughout is defense in depth rather than a single perfect wall.

<a id="101-authentication"></a>

10.1 Authentication

Authentication answers one question, "who is this client?", before any SQL runs; this subchapter covers how PostgreSQL decides which method to use for each connection, the methods themselves, encryption of the connection, and the policies that keep credentials healthy.

pg_hba.conf Rules and Order

A server that accepts connections must decide, for every connection attempt, whether to accept it and how the client must prove its identity. A laptop on the office network, a backup job on the database host, and an application server in a private subnet deserve different treatment. PostgreSQL makes this decision with host-based authentication: a list of rules in the file pg_hba.conf ("hba" stands for host-based authentication), checked from top to bottom for every new connection.

Analogy. pg_hba.conf is the guest list at a venue door. The bouncer reads it from the top and stops at the first line that describes you; that line says whether you need an ID, a ticket, or are turned away. The bouncer never reads further down, even if a later line would have been kinder.

Where the file is

SHOW hba_file;
--               hba_file
-- -------------------------------------
--  /etc/postgresql/17/main/pg_hba.conf      -- Debian/Ubuntu packages; $PGDATA/pg_hba.conf elsewhere

Record format. Each non-comment line is one rule with five or six fields:

# TYPE      DATABASE        USER            ADDRESS             METHOD          [OPTIONS]
local       all             postgres                            peer
local       all             all                                 peer
host        all             all             127.0.0.1/32        scram-sha-256
host        all             all             ::1/128             scram-sha-256
hostssl     shop            app_user        10.0.1.0/24         scram-sha-256
hostssl     all             +dba            10.0.9.0/24         cert
host        replication     replicator      10.0.2.15/32        scram-sha-256
host        all             all             0.0.0.0/0           reject      # explicit catch-all

Field	Values	Meaning
TYPE	local	Unix-domain socket connections
	host	TCP/IP, encrypted or not
	hostssl / hostnossl	TCP/IP only with / only without TLS
	hostgssenc / hostnogssenc	TCP/IP only with / only without GSSAPI encryption
DATABASE	all, a name, a comma list	Which database is requested
	sameuser, samerole	Database named like the user / database named like a role the user belongs to
	replication	Physical replication connections (not matched by all)
	@file	Names read from a file
USER	all, a name, a comma list	Which role is requested
	+group	Any member of role group (directly or indirectly)
ADDRESS	CIDR (10.0.1.0/24), host name, samehost, samenet	Client address (absent for local)
METHOD	See the table below	How the client must authenticate
OPTIONS	name=value pairs	Method-specific settings, such as map=, clientcert=, ldapserver=

PostgreSQL 16 added regular expressions in the database and user fields (a value starting with /, such as /^report_) and include, include_if_exists, and include_dir directives for splitting the file.

Authentication methods at a glance

Method	How identity is proven	Use it for
trust	Not at all: any claimed user name is accepted	Throwaway local development only (Peer and Trust Methods)
reject	Connection always refused	Explicit deny rules
scram-sha-256	Salted challenge-response password exchange	The default for password logins (Password Authentication and SCRAM)
md5	Older challenge-response password exchange	Legacy only; deprecated in PostgreSQL 18
password	Password sent in clear text	Never, unless the connection is TLS-encrypted and nothing else is possible
peer	Operating system user of a local socket	Local administration and jobs on the database host
ident	RFC 1413 ident server on the client host	Avoid: the client controls the answer
cert	TLS client certificate	Service accounts and administrators (Certificate and LDAP Authentication)
ldap, radius, pam, bsd	Delegated to an external service	Central directories
gss, sspi	Kerberos / Active Directory tickets	Enterprise single sign-on
oauth (PostgreSQL 18+)	OAuth 2.0 bearer token checked by a validator module	Integration with identity providers

First match wins, with no fallthrough. The server takes the connection's type, requested database, requested user, and client address, and walks the file from top to bottom. The first rule whose first four fields all match decides the outcome. If that rule's method then fails (for example, a wrong password), the connection is rejected; PostgreSQL does not try the next rule. If no rule matches, the connection is rejected as well.

 Connection: TCP from 10.0.1.25, user=app_user, database=shop, TLS on

 line 1  local ...                          type mismatch       -> skip
 line 2  local ...                          type mismatch       -> skip
 line 3  host all all 127.0.0.1/32          address mismatch    -> skip
 line 4  host all all ::1/128               address mismatch    -> skip
 line 5  hostssl shop app_user 10.0.1.0/24  ALL FIELDS MATCH    -> use scram-sha-256, stop
 line 6+ (never consulted)

Order matters. Put specific rules before general ones. A broad rule early in the file silently shadows every narrower rule below it:

# WRONG: line 1 matches every TCP client in 10.0.0.0/8, so the cert rule for DBAs is dead code
host     all  all   10.0.0.0/8   scram-sha-256
hostssl  all  +dba  10.0.9.0/24  cert

# RIGHT: the narrow rule first
hostssl  all  +dba  10.0.9.0/24  cert
host     all  all   10.0.0.0/8   scram-sha-256

Reloading and checking. The file is read at server start and on reload; existing sessions are not affected. Before reloading, look at the pg_hba_file_rules view, which parses the file on disk and reports errors per line without applying anything:

SELECT line_number, type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules
ORDER BY rule_number;
--  line_number |  type   |   database    | user_name  |  address   |  auth_method  | error
-- -------------+---------+---------------+------------+------------+---------------+-------
--           89 | local   | {all}         | {postgres} |            | peer          |
--           94 | hostssl | {shop}        | {app_user} | 10.0.1.0   | scram-sha-256 |
--           97 | host    | {all}         | {all}      | 0.0.0.0    | scrom-sha-256 | invalid authentication method "scrom-sha-256"

SELECT pg_reload_conf();       -- or: pg_ctl reload / systemctl reload postgresql@17-main

If the file contains errors, a reload keeps the previous, working rules and logs a message; a server started with a broken file refuses to start at all, which is why checking first matters.

Diagnosing a rejection. The server log names the cause, while the client sees a generic message:

FATAL:  no pg_hba.conf entry for host "10.0.3.7", user "app_user", database "shop", SSL encryption
FATAL:  password authentication failed for user "app_user"
DETAIL:  Connection matched file "/etc/postgresql/17/main/pg_hba.conf" line 94: "hostssl shop app_user 10.0.1.0/24 scram-sha-256"

The DETAIL line tells you exactly which rule was applied, which settles most "why is it asking for a password?" questions.

Key takeaways

• pg_hba.conf is a top-to-bottom list of rules; the first rule matching type, database, user, and address decides the method.
• A failed authentication does not fall through to later rules, and no matching rule means rejection.
• Order rules from most specific to most general and end with an explicit reject to make the intent visible.
• replication must be named explicitly; all does not cover replication connections.
• Validate with pg_hba_file_rules before pg_reload_conf(); read the DETAIL line of the server log when a connection fails.

🧪 Practice

1. (Beginner) Print your server's hba_file, query pg_hba_file_rules, and explain in one sentence what each rule allows. Which rule does a psql connection over the Unix socket use, and which one does psql -h localhost use?
2. (Intermediate) Add a rule that lets role report_ro connect to database shop only from 10.0.5.0/24 with SCRAM, and only over TLS. Introduce a typo on purpose, check pg_hba_file_rules, fix it, reload, and verify that a connection as report_ro from 127.0.0.1 is rejected and see which log message appears.
3. (Interview) "After adding a new pg_hba.conf line for a contractor, they still cannot log in, but the line looks correct. How do you debug it?" Hint: Think about rule order and shadowing, whether a reload happened, the error column of pg_hba_file_rules, listen_addresses, and the DETAIL line in the server log.

Password Authentication and SCRAM

Passwords are still the most common way applications authenticate, and every password scheme has to solve two problems. First, the server must be able to check a password without storing it in a form that, if stolen, can be used to log in. Second, the password must not be recoverable from what travels over the network. PostgreSQL's older md5 method fails both tests in important ways; SCRAM-SHA-256 (Salted Challenge Response Authentication Mechanism, RFC 7677) solves both, and has been the default since PostgreSQL 14.

Analogy. With md5, the server keeps a copy of a house key; anyone who steals that copy can open the door. With SCRAM, the server keeps only a lock that recognizes the key. Stealing the lock lets you study it, but you still cannot open the door with it.

The three password methods

	password	md5	scram-sha-256
Sent over the wire	Clear-text password	md5(md5(password + user) + salt)	Proof derived from the password and a random nonce; the password never leaves the client
Stored on the server	Hash (if set by SCRAM or MD5)	md5(password + user)	Salted, iterated verifier
Stolen stored value lets an attacker log in	No	Yes: the stored hash is enough to compute a valid response ("pass-the-hash")	No
Brute-force cost per guess	n/a	One MD5: billions per second on a GPU	4096 iterations of HMAC-SHA-256 (tunable)
Server proves its identity to client	No	No	Yes (mutual authentication)
Status	Avoid	Deprecated in PostgreSQL 18	Recommended

How a SCRAM exchange works

 Client                                                    Server
   |  client-first: user name, client nonce Nc               |
   |-------------------------------------------------------->|
   |  server-first: Nc+Ns (combined nonce), salt, iterations  |  stored verifier:
   |<--------------------------------------------------------|    salt, iterations,
   |                                                          |    StoredKey, ServerKey
   |  client derives SaltedPassword = PBKDF2(password, salt,  |
   |  iterations) and sends ClientProof (never the password)  |
   |-------------------------------------------------------->|
   |                                       server verifies proof against StoredKey
   |  server-final: ServerSignature                           |
   |<--------------------------------------------------------|
   |  client checks the signature: proves the server knows    |
   |  the verifier, which defeats a fake server               |

Because each exchange uses fresh nonces, a recorded exchange cannot be replayed. Because the server stores only StoredKey (a hash of a key derived from the password), the stored value cannot be presented as a credential.

What the server stores

SHOW password_encryption;          -- scram-sha-256 (the default since PostgreSQL 14)

CREATE ROLE app_user LOGIN;
\password app_user                 -- psql prompts twice and sends only the computed verifier

SELECT rolname, left(rolpassword, 40) || '...' AS verifier
FROM pg_authid WHERE rolname = 'app_user';      -- pg_authid is readable by superusers only
--  rolname  |                 verifier
-- ----------+--------------------------------------------
--  app_user | SCRAM-SHA-256$4096:mE3c4Yb0bZxXo7aHf1+Q...
--             ^ mechanism   ^ iterations  ^ salt, then $StoredKey:ServerKey

\password computes the verifier in the client, so the clear-text password never reaches the server, its logs, or pg_stat_statements. By contrast, ALTER ROLE app_user PASSWORD 'secret' sends the clear text in the SQL statement, where log_statement = 'ddl' or an error message can record it. The number of iterations for new verifiers is controlled by scram_iterations (PostgreSQL 16+, default 4096); raising it slows down brute-force attacks and every login equally.

Method and stored format interact. The pg_hba.conf method and the stored verifier must be compatible:

pg_hba.conf method	Stored as MD5	Stored as SCRAM
md5	MD5 exchange	SCRAM exchange (automatic upgrade)
scram-sha-256	Login fails	SCRAM exchange

This table gives a safe migration path from MD5:

-- 1. Make new passwords use SCRAM (postgresql.conf, then reload)
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

-- 2. Find roles still stored as MD5
SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';

-- 3. Have each of them set its password again (\password), which stores a SCRAM verifier.
--    pg_hba.conf can stay on "md5" meanwhile: it already uses SCRAM for converted roles.

-- 4. When the query in step 2 returns no rows, change "md5" to "scram-sha-256" in pg_hba.conf and reload.

Protecting the client side. A client that accepts whatever method the server asks for can be tricked by a fake server into sending a clear-text password. Since PostgreSQL 16, libpq can refuse weaker methods with require_auth, and channel_binding=require ties the SCRAM exchange to the TLS session (method SCRAM-SHA-256-PLUS), defeating a man-in-the-middle that terminates TLS:

import psycopg  # psycopg 3, uses libpq

conn = psycopg.connect(
    host="db.internal",
    dbname="shop",
    user="app_user",
    password=load_secret("db/app_user"),   # from a secret store, never hard-coded
    sslmode="verify-full",                 # verify the server certificate and host name
    channel_binding="require",             # SCRAM must be bound to this TLS session
    require_auth="scram-sha-256",          # refuse password/md5 even if the server asks (libpq 16+)
)

Brute-force protection. PostgreSQL has no built-in account lockout. Limit exposure through network rules (pg_hba.conf, firewalls), the auth_delay module (adds a pause after each failed attempt), and tools such as fail2ban reading password authentication failed lines from the log.

Key takeaways

• Use scram-sha-256 in pg_hba.conf and password_encryption = scram-sha-256; MD5 is deprecated and its stored hash works as a password.
• SCRAM never sends the password, stores only a salted, iterated verifier, and authenticates the server to the client.
• Set passwords with \password (or a driver's equivalent) so the clear text never appears in SQL or logs.
• Migrate from MD5 by re-setting passwords while pg_hba.conf still says md5, then switch the method.
• Harden clients with sslmode=verify-full, channel_binding=require, and require_auth.

🧪 Practice

1. (Beginner) Create a role, set its password with \password, and inspect pg_authid.rolpassword. Identify the iteration count, the salt, and the two keys in the stored value.
2. (Intermediate) Set password_encryption = 'md5' for a session, create a role with a password, and try to log in through a scram-sha-256 rule and an md5 rule. Then re-set the password with SCRAM and repeat. Explain each outcome using the compatibility table.
3. (Interview) "An attacker obtained a copy of pg_authid from an old backup. What can they do with it, and does the answer depend on the password method?" Hint: Compare what an MD5 hash and a SCRAM verifier let an attacker do directly, versus offline guessing, and consider which roles to rotate first.

Certificate and LDAP Authentication

Passwords scattered across many databases are hard to manage: each one must be created, rotated, and revoked separately, and when an employee leaves, someone must remember every database where they had a login. Organizations solve this by delegating identity to a system that already manages it: a certificate authority that issues and revokes certificates, or a directory (LDAP, Active Directory) where accounts are enabled and disabled centrally. PostgreSQL then only maps the externally proven identity to a database role.

Analogy. Certificate authentication is like a passport: an authority you trust issued it, and you check the photo and expiry date without phoning anyone. LDAP authentication is like the door staff calling the company's front desk: "is this person employed here, and is that their password?"

Certificate authentication. With the cert method, the client presents a TLS client certificate signed by a CA that the server trusts (ssl_ca_file). The server checks the signature and validity, then compares the certificate's Common Name (CN) with the requested role name. No password is involved.

# pg_hba.conf
hostssl  all  all  10.0.9.0/24  cert  map=certmap   # cert implies verification of the client certificate

# pg_ident.conf: map certificate names to role names
# MAPNAME   SYSTEM-USERNAME          PG-USERNAME
certmap     /^(.*)@example\.com$     \1          # "andrea@example.com" -> role andrea
certmap     backup-agent             backup      # a service certificate -> role backup

# Issue a client certificate signed by the internal CA (CN = identity)
openssl req -new -newkey rsa:3072 -nodes -keyout client.key \
            -subj "/CN=andrea@example.com" -out client.csr
openssl x509 -req -in client.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
             -days 90 -out client.crt              # short lifetime limits damage from a leaked key

# Connect: libpq reads ~/.postgresql/postgresql.crt and .key by default, or pass them explicitly
psql "host=db.internal dbname=shop user=andrea sslmode=verify-full \
      sslcert=client.crt sslkey=client.key sslrootcert=ca.crt"

Two related options are worth knowing:

Option	Effect
clientcert=verify-full on any method, e.g. scram-sha-256 clientcert=verify-full	Requires a valid certificate whose CN matches the user and a password: two factors
clientcert=verify-ca	Requires a certificate signed by the trusted CA, without checking the name
clientname=DN (PostgreSQL 14+)	Match against the full Distinguished Name instead of the CN
ssl_crl_file / ssl_crl_dir (server)	Certificate revocation lists, so revoked certificates are refused

LDAP authentication. With ldap, the client sends a user name and password (in clear text inside the PostgreSQL protocol, so TLS is mandatory), and the server checks them against an LDAP directory. The role must still exist in PostgreSQL; LDAP only verifies the password. Two modes exist:

# Simple bind: the DN is built from prefix + user name + suffix
hostssl all all 10.0.0.0/16 ldap ldapserver=ldap.example.com ldapscheme=ldaps
        ldapprefix="uid=" ldapsuffix=",ou=people,dc=example,dc=com"

# Search+bind: the server first searches for the user's DN, then binds as that DN
hostssl all all 10.0.0.0/16 ldap ldapserver=ldap.example.com ldapscheme=ldaps
        ldapbasedn="ou=people,dc=example,dc=com" ldapsearchattribute=uid
        ldapbinddn="cn=pg-lookup,dc=example,dc=com" ldapbindpasswd="..."

(Each rule must be written on a single line in the real file; it is wrapped here for readability.)

Mode	When to use
Simple bind	All users live under one predictable DN pattern
Search+bind	Users are spread across organizational units, or you log in with an attribute such as mail or sAMAccountName

Use ldapscheme=ldaps or ldaptls=1 so the password is also encrypted between PostgreSQL and the directory. LDAP does not create roles or manage their privileges; tools such as pg-ldap-sync synchronize directory groups into PostgreSQL roles if you need that.

Choosing a method

	cert	ldap	gss (Kerberos)	oauth (PostgreSQL 18+)
Secret sent to PostgreSQL	None (proof of key possession)	Password	None (ticket)	Bearer token
Central revocation	CRL or short-lived certificates	Disable the account	Disable the principal	Revoke at the identity provider
Best for	Services, automation, administrators	Human users in a directory-centric company	Windows / AD single sign-on	Cloud identity providers
Main operational cost	Running a CA and distributing certificates	Directory availability on every login	Keytabs and realm configuration	Validator module and client support

Key takeaways

• External methods move identity management out of the database; PostgreSQL maps the proven identity to an existing role.
• cert authenticates by a TLS client certificate; map names with pg_ident.conf and revoke with CRLs or short lifetimes.
• clientcert=verify-full combined with a password method gives two-factor authentication.
• ldap transmits the password, so require hostssl and LDAPS; search+bind handles complex directory layouts.
• LDAP verifies passwords only; roles and grants must still exist in PostgreSQL.

🧪 Practice

1. (Beginner) Using openssl, create a CA, a server certificate, and a client certificate with CN=report_ro. Configure a hostssl ... cert rule and connect without a password.
2. (Intermediate) Change the rule to scram-sha-256 clientcert=verify-full and show that a connection needs both the certificate and the password. Then add a pg_ident.conf map so that a certificate with CN=reports@example.com logs in as report_ro.
3. (Interview) "An employee leaves the company. With password, LDAP, and certificate authentication respectively, what must happen so they can no longer connect, and how quickly?" Hint: Consider where the credential lives, existing open sessions, certificate lifetimes and CRLs, and roles that still exist in PostgreSQL after the directory account is disabled.

Peer and Trust Methods

Not every connection comes across a network. Administrators run psql on the database host, cron jobs run backups locally, and developers run PostgreSQL on their laptops. For connections over a Unix-domain socket, the operating system kernel can tell the server exactly which OS user is on the other end, so a password adds little. That is what peer uses. The trust method goes further and checks nothing at all, which is convenient and very dangerous.

Analogy. peer is a door inside an office building that only opens for the badge the building's security system says you are wearing. trust is a door with no lock: whoever claims to be the CEO is the CEO.

Peer authentication. On local connections, the server asks the kernel for the client's OS user (via SO_PEERCRED or getpeereid) and accepts the login if that name equals the requested role name, or if pg_ident.conf maps it:

# pg_hba.conf
local   all   postgres                peer                  # OS user postgres -> role postgres
local   all   all                     peer map=localmap

# pg_ident.conf
# MAPNAME    SYSTEM-USERNAME   PG-USERNAME
localmap     andrea            andrea
localmap     andrea            app_owner     # andrea may also log in as app_owner
localmap     backup            backup

whoami                         # andrea
psql -d shop                   # role andrea: OS user matches the role name
psql -d shop -U app_owner      # allowed through localmap
psql -d shop -U postgres       # FATAL:  Peer authentication failed for user "postgres"
sudo -u postgres psql          # the usual way to reach the superuser on Debian/Ubuntu

peer is not available for TCP connections, because a remote kernel cannot vouch for a user. The similar-looking ident method asks an ident daemon on the client machine who the user is; whoever controls that machine controls the answer, so ident over TCP should be considered unauthenticated.

Trust authentication. trust accepts any connection matching the rule as any role it names, without a password:

# DANGEROUS: any local OS user can connect as any role, including postgres
local   all   all   trust

Where trust appears	Risk
local on a single-user laptop	Low, if nobody else has an account on the machine
local on a shared server	Any OS user becomes superuser with psql -U postgres
host ... 127.0.0.1/32	Any local process, including a compromised web application, becomes superuser
host ... 0.0.0.0/0	Anyone who can reach the port owns the server; automated scanners find these within hours

initdb without the --auth option writes trust rules and prints a warning. Always pass explicit methods when creating a cluster:

initdb -D /srv/pgdata --auth-local=peer --auth-host=scram-sha-256

Official container images are a common trap: setting POSTGRES_HOST_AUTH_METHOD=trust to "make it work" publishes an unauthenticated superuser on whatever port the container exposes.

Recovering a lost superuser password. The legitimate use of trust is emergency access: temporarily put local all postgres trust at the top, reload, set a new password with \password, then remove the line and reload again. With peer configured for postgres, sudo -u postgres psql achieves the same without editing the file, which is one reason peer is the better local default.

Key takeaways

• peer trusts the kernel's report of the OS user on a Unix socket; map OS users to roles with pg_ident.conf.
• ident over TCP relies on the client machine and offers no real assurance.
• trust performs no authentication; anyone matching the rule can act as any named role, including superusers.
• Create clusters with explicit --auth-local and --auth-host; never use trust on shared hosts, network rules, or containers with exposed ports.

🧪 Practice

1. (Beginner) Find the local rules in your pg_hba.conf. Which OS user can connect as postgres without a password, and why?
2. (Intermediate) Write a pg_ident.conf map that lets OS users deploy and ci log in locally as role migrator, but not as any other role. Prove both the allowed and the refused case.
3. (Interview) "A developer's Docker Compose file sets POSTGRES_HOST_AUTH_METHOD=trust and maps port 5432 to the host. What is the risk, and how would you fix it without slowing down local development?" Hint: Think about who can reach the port (other machines on the network, other containers), Docker's port publishing and firewall interaction, binding to 127.0.0.1, and setting a password via POSTGRES_PASSWORD.

Connection Encryption with TLS

Without encryption, everything between client and server crosses the network in clear text: queries, result rows full of customer data, and (with weaker methods) passwords. Anyone on the path, such as a compromised router, a misconfigured cloud network, or a coworker on the same Wi-Fi, can read or alter it. TLS (Transport Layer Security, the protocol behind HTTPS) encrypts the connection and lets the client verify that it is talking to the real server, not an impostor.

Analogy. Without TLS, queries travel as postcards that every mail handler can read. TLS puts them in a sealed envelope, and certificate verification checks that the address on the envelope really belongs to the recipient rather than to someone who set up a fake mailbox.

Server configuration

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'           # certificate presented to clients (paths relative to the data directory)
ssl_key_file  = 'server.key'           # private key: owned by postgres, mode 0600
ssl_ca_file   = 'ca.crt'               # CAs trusted for *client* certificates (needed for cert auth)
ssl_min_protocol_version = 'TLSv1.2'   # default since PostgreSQL 14; 'TLSv1.3' if all clients support it

# Server certificate for host db.internal, signed by an internal CA
openssl req -new -newkey rsa:3072 -nodes -keyout server.key \
            -subj "/CN=db.internal" -addext "subjectAltName=DNS:db.internal" -out server.csr
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
             -days 365 -copy_extensions copy -out server.crt   # -copy_extensions needs OpenSSL 3.0+
chmod 600 server.key && chown postgres:postgres server.key

Changes to SSL settings and files take effect on reload (SELECT pg_reload_conf()), so certificates can be renewed without a restart.

Requiring TLS. ssl = on only offers encryption. To require it, use hostssl rules for network clients and reject everything else:

hostssl    all  all  10.0.0.0/8  scram-sha-256
hostnossl  all  all  0.0.0.0/0   reject           # explicit: no unencrypted network logins

Client modes: encryption is not the same as verification. The sslmode connection parameter decides what the client insists on:

sslmode	Encrypted?	Server certificate checked?	Protects against eavesdropping	Protects against an impostor (MITM)
disable	No	No	No	No
allow	Only if server insists	No	No	No
prefer (libpq default)	If server supports it	No	Only against passive listeners	No
require	Yes	No (unless a root cert file is present)	Yes	No
verify-ca	Yes	Signed by a trusted CA	Yes	Partly: any server with a cert from that CA is accepted
verify-full	Yes	Trusted CA and host name matches	Yes	Yes

The libpq default, prefer, silently falls back to clear text if an attacker strips the TLS offer. Production clients should use verify-full with the CA certificate in sslrootcert (or sslrootcert=system in PostgreSQL 16+ to use the operating system's CA store, which also implies verify-full):

// Node.js with node-postgres
import fs from "node:fs";
import pg from "pg";

const pool = new pg.Pool({
  host: "db.internal",
  database: "shop",
  user: "app_user",
  password: process.env.PGPASSWORD,                 // injected by the platform's secret store
  ssl: {
    ca: fs.readFileSync("/etc/ssl/internal-ca.crt"), // trust only the internal CA
    rejectUnauthorized: true,                       // fail on invalid certificate (verify-full behavior)
  },
});

Checking what is encrypted. The pg_stat_ssl view shows the TLS state of every session:

SELECT a.usename, a.client_addr, s.ssl, s.version, s.cipher
FROM pg_stat_ssl AS s
JOIN pg_stat_activity AS a USING (pid)
WHERE a.backend_type = 'client backend';
--  usename  | client_addr | ssl | version |         cipher
-- ----------+-------------+-----+---------+------------------------
--  app_user | 10.0.1.25   | t   | TLSv1.3 | TLS_AES_256_GCM_SHA384
--  postgres |             | f   |         |                         <- Unix socket: no TLS needed

Other notes. Connection poolers and proxies terminate and re-establish connections, so TLS must be configured on both hops (Connection Pooling with PgBouncer). Replication connections are ordinary connections and should use TLS too. PostgreSQL 17 added sslnegotiation=direct, which starts TLS immediately instead of after a plain-text request, saving a round trip and working with standard TLS proxies. GSSAPI encryption (hostgssenc) is an alternative in Kerberos environments.

Key takeaways

• ssl = on offers TLS; hostssl rules plus a hostnossl ... reject rule make it mandatory.
• Encryption without certificate verification stops eavesdroppers but not impostors; use sslmode=verify-full.
• libpq's default prefer can be silently downgraded; never rely on it in production.
• Keep the private key at mode 0600; certificates can be rotated with a reload.
• Audit live sessions with pg_stat_ssl, and secure every hop, including poolers and replicas.

🧪 Practice

1. (Beginner) Enable TLS with a self-signed certificate, connect with sslmode=require, and confirm the session in pg_stat_ssl. Then connect with sslmode=verify-full and explain the error.
2. (Intermediate) Create an internal CA, issue a server certificate with the correct subjectAltName, and make verify-full work. Then add rules so that TCP connections without TLS are rejected, and show the client-side and server-side error messages.
3. (Interview) "Our application connects with sslmode=require. Security says that is not enough. Why?" Hint: Describe what an attacker on the network path can do when the client does not check the server's identity, and what verify-full, channel_binding, and require_auth add.

Password Policies and Rotation

Even strong authentication methods are undermined by weak or stale secrets: a password that is short, reused across systems, shared by a whole team, or unchanged since the company was founded. Password policy covers how strong passwords must be, how long they live, and how to change them without downtime. PostgreSQL provides only a few building blocks here, so most of the policy is enforced around the database rather than in it.

Analogy. Rotating database passwords is like changing the locks of a building that never closes: you cannot lock everyone out while the new keys are cut, so you hand out the new key first and retire the old one only when nobody uses it any more.

What PostgreSQL provides

Feature	What it does	Limitation
VALID UNTIL 'timestamp'	Password stops working after this time	Expiry only; existing sessions continue; no warning to the user
passwordcheck module	Rejects weak passwords at CREATE/ALTER ROLE	Fully checks only clear-text passwords; a verifier pre-hashed by the client (\password) can only be checked against the user name
CONNECTION LIMIT n	Maximum concurrent sessions per role	Not a lockout
auth_delay module	Pauses after failed attempts	Slows brute force, does not lock accounts
scram_iterations	Cost of each guess against a stolen verifier	Applies to newly set passwords

ALTER ROLE contractor_jane VALID UNTIL '2026-12-31 23:59:59+00';   -- access ends with the contract
ALTER ROLE report_ro CONNECTION LIMIT 5;

SELECT rolname, rolvaliduntil, rolconnlimit
FROM pg_roles WHERE rolcanlogin ORDER BY rolvaliduntil NULLS FIRST;  -- logins with no expiry first

# postgresql.conf
shared_preload_libraries = 'passwordcheck,auth_delay'
passwordcheck.min_password_length = 16     # PostgreSQL 18+; earlier versions hard-code 8
auth_delay.milliseconds = 500

Extensions such as credcheck add richer rules (password history, reuse limits, lockout after failed attempts) if your policy demands them inside the database.

Human users vs. service accounts. Policies that suit people (complexity, expiry every 90 days) are a poor fit for applications, and modern guidance (for example NIST SP 800-63B) discourages forced periodic changes for humans in favor of long passphrases and changing on suspicion of compromise. A practical split:

Account type	Recommended approach
Human users	Prefer central identity (LDAP, Kerberos, OAuth, certificates) so policy lives in one place; personal roles, never shared
Applications	Long random secrets generated by a machine, stored in a secret manager, rotated automatically (Secrets Management)
Superuser postgres	No password at all where possible: peer locally, no network login

Zero-downtime rotation. A role has exactly one password, and changing it does not disconnect existing sessions, but any new connection with the old password fails. With a fleet of application servers and a connection pool, there is a window where some instances still have the old password. Two common patterns avoid it:

 Pattern A: two login roles behind one group role ("blue/green credentials")

   app_rw (NOLOGIN, owns no data, has all grants)
     ^                 ^
     | member          | member
   app_blue (LOGIN)   app_green (LOGIN)

   1. App runs as app_blue.
   2. Set a new password on app_green; deploy config pointing to app_green.
   3. When no sessions of app_blue remain (pg_stat_activity), change or disable app_blue's password.
   4. Next rotation goes the other way.

CREATE ROLE app_rw NOLOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;

CREATE ROLE app_blue  LOGIN IN ROLE app_rw;     -- inherits app_rw's privileges
CREATE ROLE app_green LOGIN IN ROLE app_rw;

-- Step 3: verify the old credential is no longer in use before retiring it
SELECT usename, count(*) FROM pg_stat_activity
WHERE usename IN ('app_blue', 'app_green') GROUP BY usename;
ALTER ROLE app_blue PASSWORD NULL;              -- no password: password logins now fail

Pattern B is dynamic credentials: a secret manager such as HashiCorp Vault creates a short-lived role for each application instance (CREATE ROLE "v-app-abc123" LOGIN PASSWORD '...' VALID UNTIL ... IN ROLE app_rw) and drops it when the lease expires, so there is no long-lived password to rotate at all.

Rotation hygiene

• Never put passwords into ALTER ROLE statements that are logged; use \password or have the tool send a pre-hashed SCRAM verifier.
• Rotate immediately when someone with knowledge of a shared secret leaves, or when a secret appears in a log, ticket, or repository.
• Remember the copies: .pgpass files, pooler configuration (PgBouncer userlist.txt or auth_query), replication primary_conninfo, and CI variables.

Key takeaways

• PostgreSQL offers VALID UNTIL, connection limits, passwordcheck, and auth_delay; richer policies need extensions or external identity systems.
• Treat humans and services differently: central identity for people, machine-generated secrets for applications.
• Changing a password does not end existing sessions; plan rotation so new and old credentials overlap.
• Two login roles under a shared group role, or short-lived dynamic credentials, give rotation without downtime.
• Inventory every place a password is copied before rotating it.

🧪 Practice

1. (Beginner) Create contractor_jane with a password that expires in five minutes. Log in before and after the expiry, and check what happens to a session that was opened before it.
2. (Intermediate) Implement the blue/green pattern for an application role. Write the sequence of SQL statements and the pg_stat_activity check that a deployment script would run for one rotation.
3. (Interview) "How would you rotate the database password of a service with 40 instances and a PgBouncer in front, without any failed requests?" Hint: Consider where PgBouncer stores or looks up credentials, overlapping validity of two credentials, draining old server connections, and how to verify the old credential is unused before revoking it.

<a id="102-roles-and-privileges"></a>

10.2 Roles and Privileges

Once a client is authenticated, PostgreSQL decides what it may do by looking at its role and the privileges granted to that role; this subchapter covers how roles are modeled, how privileges are granted and inherited, who owns what, and how to combine these pieces into a least-privilege design.

Roles vs. Users vs. Groups

Most systems distinguish "users" (who log in) from "groups" (collections of users that share permissions). PostgreSQL unifies both into a single concept, the role. A role can own objects, hold privileges, contain other roles as members, and, if it has the LOGIN attribute, be used to connect. A "user" is just a role that can log in; a "group" is just a role that other roles are members of. Understanding that there is only one kind of thing removes much of the confusion around PostgreSQL permissions.

Analogy. A role is like a badge profile in an access-control system. Some profiles are assigned to a person and open the front door (login roles); others, like "Finance floor", are never carried by anyone directly but are attached to many personal profiles (group roles).

Sample data for this chapter. Examples reuse the accounts and transfers tables from Chapter 8 and add a customers table with some sensitive columns. Run this once as the database owner in a database named shop:

DROP TABLE IF EXISTS customers CASCADE;

CREATE TABLE customers (
  id       int  PRIMARY KEY,
  name     text NOT NULL,
  email    text NOT NULL,
  phone    text,
  tax_id   text,                 -- sensitive: national tax identifier
  region   text NOT NULL,
  manager  text NOT NULL         -- role name of the responsible sales rep
);

INSERT INTO customers VALUES
  (1, 'Acme Corp', 'ops@acme.example',      '+39 02 1234 5678', 'IT01234567890', 'EU', 'alice'),
  (2, 'Globex',    'it@globex.example',     '+1 415 555 0100',  '94-1234567',    'US', 'bob'),
  (3, 'Initech',   'admin@initech.example', NULL,               '95-7654321',    'US', 'bob');

One command, three spellings

CREATE ROLE  analysts;                      -- NOLOGIN by default: a "group"
CREATE USER  alice PASSWORD 'change-me';    -- CREATE USER = CREATE ROLE ... LOGIN
CREATE ROLE  bob   LOGIN;                   -- identical in effect to CREATE USER bob
CREATE GROUP auditors;                      -- obsolete alias for CREATE ROLE

GRANT analysts TO alice, bob;               -- alice and bob become members of analysts

Role attributes. Attributes are capabilities of the role itself, set with CREATE ROLE or ALTER ROLE, as opposed to privileges on objects:

Attribute	Default	Grants
LOGIN	NOLOGIN (LOGIN for CREATE USER)	May be used to connect
SUPERUSER	NOSUPERUSER	Bypasses every permission check; can read files and run programs as the server's OS user
CREATEDB	NOCREATEDB	May create databases
CREATEROLE	NOCREATEROLE	May create roles, and alter or drop roles it has ADMIN OPTION on (PostgreSQL 16+)
REPLICATION	NOREPLICATION	May open replication connections and create replication slots
BYPASSRLS	NOBYPASSRLS	Ignores row-level security policies (Row-Level Security Policies)
INHERIT	INHERIT	Default for whether new memberships pass privileges on (Role Inheritance and SET ROLE)
CONNECTION LIMIT n	-1 (unlimited)	Concurrent connection cap
PASSWORD, VALID UNTIL	None	Credentials and expiry (Password Policies and Rotation)

Roles are cluster-wide. Unlike tables, roles do not belong to a database; they are stored in the shared catalog pg_authid and are visible from every database in the cluster. A role named alice is the same role in shop and in analytics; what differs is the privileges granted in each database.

 Cluster
 ├── roles (shared): postgres, alice, bob, analysts, auditors
 ├── database shop       -- alice: SELECT on customers
 └── database analytics  -- alice: nothing yet; she can connect only if CONNECT is granted

Inspecting roles

\du                                -- psql: roles and attributes
\drg                               -- psql (16+): role memberships with their options

SELECT r.rolname, r.rolcanlogin, r.rolsuper, r.rolcreaterole, r.rolbypassrls,
       array(SELECT g.rolname
             FROM pg_auth_members AS m
             JOIN pg_roles AS g ON g.oid = m.roleid
             WHERE m.member = r.oid) AS member_of
FROM pg_roles AS r
WHERE r.rolname !~ '^pg_'          -- hide predefined roles
ORDER BY 1;
--  rolname  | rolcanlogin | rolsuper | rolcreaterole | rolbypassrls | member_of
-- ----------+-------------+----------+---------------+--------------+------------
--  alice    | t           | f        | f             | f            | {analysts}
--  analysts | f           | f        | f             | f            | {}
--  bob      | t           | f        | f             | f            | {analysts}
--  postgres | t           | t        | t             | t            | {}

Conventions that help. Name group roles after what they allow (shop_read, shop_write) or the job (analysts, support), and grant object privileges only to group roles. Login roles then receive memberships, never direct grants, so adding or removing a person is a single GRANT or REVOKE.

Key takeaways

• PostgreSQL has only roles; a user is a role with LOGIN, a group is a role with members.
• Attributes (LOGIN, SUPERUSER, CREATEDB, CREATEROLE, REPLICATION, BYPASSRLS) are capabilities of the role, distinct from object privileges.
• Roles are cluster-wide; privileges are granted per database object.
• Grant object privileges to group roles and make login roles members of them.
• Use \du, \drg, pg_roles, and pg_auth_members to inspect roles and memberships.

🧪 Practice

1. (Beginner) Create a group role support and two login roles sam and kim, make them members, and show the memberships with \drg and with a query on pg_auth_members.
2. (Intermediate) Create a second database, connect to it as sam, and explain why this works (or does not) even though you granted nothing in it. Then make sure only members of support can connect to it.
3. (Interview) "Why does PostgreSQL not separate users from groups, and what is the practical consequence for designing permissions?" Hint: Think about nested membership, ownership by group roles, and granting privileges to roles rather than individuals.

GRANT and REVOKE

A newly created table can be used only by its owner (and superusers). Everyone else needs explicit permission for each kind of operation: reading, inserting, updating, and so on. GRANT gives a privilege on an object to a role, and REVOKE takes it away. Privileges are deliberately fine-grained: a reporting role can read without writing, and an application can insert without being able to truncate.

Analogy. Privileges are the individual permissions on a keycard: "open the archive room", "use the copier", "enter after hours". GRANT adds a permission to the card; REVOKE removes it.

Privileges by object type

Object	Privileges	ACL letter
Table, view	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, MAINTAIN (17+)	r a w d D x t m
Column	SELECT, INSERT, UPDATE, REFERENCES	r a w x
Sequence	USAGE (nextval, currval), SELECT (currval), UPDATE (nextval, setval)	U r w
Database	CONNECT, CREATE (schemas), TEMPORARY	c C T
Schema	USAGE (look up objects), CREATE (create objects)	U C
Function, procedure	EXECUTE	X
Language, type, domain, foreign data wrapper, server	USAGE	U
Configuration parameter (15+)	SET, ALTER SYSTEM	s A

Basic usage

GRANT SELECT ON customers TO analysts;                         -- read-only
GRANT SELECT, INSERT, UPDATE ON accounts, transfers TO bob;    -- several privileges and tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO auditors;       -- every *existing* table in the schema
GRANT ALL PRIVILEGES ON transfers TO alice;                    -- every privilege that applies to tables

REVOKE INSERT ON transfers FROM alice;                         -- remove one privilege
REVOKE ALL ON customers FROM PUBLIC;                           -- PUBLIC = every role, now and in the future

ON ALL TABLES IN SCHEMA is a one-time expansion: tables created later are not included. Use default privileges for those (Default Privileges).

Privileges work together. Many statements need more than one privilege:

Statement	Privileges needed
SELECT * FROM t	USAGE on the schema, SELECT on t
UPDATE t SET x = 1 WHERE id = 5	UPDATE on t and SELECT on id (it is read in the WHERE clause)
DELETE FROM t WHERE id = 5	DELETE and SELECT on t
INSERT INTO t ... RETURNING *	INSERT and SELECT on t
INSERT into a serial column	INSERT on t and USAGE on the sequence (identity columns need no sequence grant)
CREATE TABLE child (... REFERENCES t)	REFERENCES on t

Reading the access control list. Privileges are stored as an ACL array on each object. \dp (or \z) shows it in a compact notation, grantee=privileges/grantor:

\dp customers
--                                   Access privileges
--  Schema |   Name    | Type  |     Access privileges      | Column privileges | Policies
-- --------+-----------+-------+----------------------------+-------------------+----------
--  public | customers | table | postgres=arwdDxtm/postgres+|                   |
--         |           |       | analysts=r/postgres        |                   |

postgres=arwdDxtm/postgres means the owner has every table privilege; analysts=r/postgres means analysts has SELECT, granted by postgres. An empty ACL column means "default privileges", which for a table is "owner only". To query privileges programmatically:

SELECT has_table_privilege('alice', 'customers', 'SELECT');           -- true (via analysts)

SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'customers';

Grant options. WITH GRANT OPTION lets the recipient pass the privilege on. Revoking it later requires CASCADE if the recipient has already granted it to others, which also removes those dependent grants:

GRANT SELECT ON customers TO alice WITH GRANT OPTION;
-- as alice:
GRANT SELECT ON customers TO kim;                         -- recorded as kim=r/alice
-- as owner:
REVOKE SELECT ON customers FROM alice;
-- ERROR:  dependent privileges exist
REVOKE SELECT ON customers FROM alice CASCADE;            -- also removes kim's grant

PUBLIC and built-in defaults. PUBLIC is a pseudo-role that includes every role. Some privileges are granted to PUBLIC automatically when objects are created, and they are often more than you want:

Object	Granted to PUBLIC by default
Database	CONNECT, TEMPORARY
Function, procedure	EXECUTE
Language, type, domain	USAGE
Schema public (PostgreSQL 15+)	USAGE only (before 15: also CREATE)
Table, view, sequence, schema (other)	Nothing

A REVOKE from an individual role does not remove access that the role receives through PUBLIC or through a group. REVOKE SELECT ON customers FROM kim changes nothing if kim is a member of analysts, which holds SELECT.

Key takeaways

• GRANT and REVOKE manage privileges per object type; many statements need several privileges at once (for example UPDATE ... WHERE also needs SELECT).
• ON ALL TABLES IN SCHEMA affects only existing objects.
• Read ACLs with \dp (grantee=letters/grantor) or check with has_*_privilege functions.
• PUBLIC receives CONNECT/TEMPORARY on databases and EXECUTE on functions by default; revoke these where they are not wanted.
• Revoking from one role does not remove privileges it gets from PUBLIC or its groups.

🧪 Practice

1. (Beginner) Grant analysts read access to customers and accounts. As alice, run a SELECT on each and an UPDATE on accounts, and read the error message. Then decode the ACL shown by \dp accounts.
2. (Intermediate) Grant bob only UPDATE on accounts, and run UPDATE accounts SET balance = 0 and UPDATE accounts SET balance = 0 WHERE id = 1 as bob. Explain why one fails, and find the smallest additional grant that makes it work.
3. (Interview) "We revoked a former employee's access to a sensitive table, but they could still read it. What could explain that?" Hint: Check membership in group roles, grants to PUBLIC, ownership, grant options given to others, BYPASSRLS/superuser attributes, and views or definer functions that expose the same data.

Object Ownership

Every database object (table, view, function, schema, sequence, database) has exactly one owner: by default, the role that created it. Ownership is more than one more privilege. The owner can alter and drop the object, grant and revoke privileges on it, and, for tables, bypasses row-level security by default. These rights cannot be granted to anyone else; they come only with ownership or membership in the owning role. Who owns what is therefore one of the most important security decisions in a schema.

Analogy. Privileges are like keys that the owner hands out; ownership is the title deed to the building. A key holder can open doors, but only the deed holder can renovate, demolish, or change the locks.

What ownership allows

Action	Owner	Role with all privileges but not owner
SELECT, INSERT, UPDATE, DELETE, ...	Yes (implicitly)	Yes
ALTER TABLE, CREATE INDEX, DROP TABLE	Yes	No
GRANT / REVOKE on the object	Yes	Only with grant option, and only for granted privileges
Create policies, enable row-level security	Yes	No
Subject to row-level security	No, unless FORCE ROW LEVEL SECURITY	Yes

Ownership by a group role. If individual developers own tables, the schema becomes a patchwork that nobody else can change, and dropping a departed developer's role becomes a project. The standard pattern is a dedicated, non-login owner role; members of that role can act as owner:

CREATE ROLE shop_owner NOLOGIN;                -- owns the schema objects, never logs in
GRANT shop_owner TO migrator;                  -- the migration tool's login role

-- Migrations run as the owner role, so every new object is owned by it
SET ROLE shop_owner;
CREATE TABLE invoices (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, amount numeric NOT NULL);
RESET ROLE;

SELECT tablename, tableowner FROM pg_tables WHERE schemaname = 'public';
--  tablename | tableowner
-- -----------+------------
--  invoices  | shop_owner

The application connects as a different role that has only the privileges it needs; it cannot DROP or ALTER anything, which limits the damage from a SQL injection or a buggy deployment.

Changing owners

ALTER TABLE customers OWNER TO shop_owner;                 -- one object
REASSIGN OWNED BY alice TO shop_owner;                     -- everything alice owns in the *current* database

To change an owner, you must own the object (or be a superuser), be able to SET ROLE to the new owner, and the new owner must have CREATE on the schema. These rules stop a role from "gifting" a malicious object to a more privileged role.

Dropping a role. A role cannot be dropped while it owns objects or holds privileges in any database:

DROP ROLE alice;
-- ERROR:  role "alice" cannot be dropped because some objects depend on it
-- DETAIL:  owner of table invoices_backup
--          privileges for table customers

The standard procedure, run in every database of the cluster, then once at the end:

-- in each database:
REASSIGN OWNED BY alice TO shop_owner;   -- keep her objects, change their owner
DROP OWNED BY alice;                     -- drop what is left and revoke all her privileges
-- finally, from any database:
DROP ROLE alice;

Ownership and security definer functions. A SECURITY DEFINER function runs with the privileges of its owner (Security Definer vs. Invoker), and a view accesses its tables with the privileges of its owner unless security_invoker is set. Owning these by a superuser turns every flaw into full server compromise; own them by a role that has exactly the privileges the function or view needs.

Key takeaways

• Every object has one owner, which can alter, drop, and grant on it; these rights are not grantable.
• Table owners bypass row-level security unless FORCE ROW LEVEL SECURITY is set.
• Let a NOLOGIN owner role own schema objects; run migrations with SET ROLE to it and run the application as a separate, less privileged role.
• Drop roles with REASSIGN OWNED and DROP OWNED in every database, then DROP ROLE.
• Views and definer functions act with their owner's rights, so choose their owners carefully.

🧪 Practice

1. (Beginner) As alice, create a table (granting her CREATE on a schema if needed). Try to DROP ROLE alice as a superuser, read the error, and remove the role cleanly.
2. (Intermediate) Set up shop_owner, migrator, and app_user. Write a migration that creates a table as shop_owner, and show that app_user, even with ALL PRIVILEGES on the table, cannot ALTER or DROP it.
3. (Interview) "Our tables are owned by the same role the application logs in with. What risks does that create, and how would you migrate away from it safely?" Hint: Consider DDL through SQL injection, RLS bypass by owners, ALTER TABLE ... OWNER TO, REASSIGN OWNED, and the order of steps so the application keeps working throughout.

Default Privileges

GRANT ... ON ALL TABLES IN SCHEMA fixes permissions for the tables that exist today, but tomorrow a migration adds a new table, and the reporting role suddenly gets permission denied. Granting by hand after every migration is easy to forget. Default privileges solve this: they are rules that say "whenever role X creates an object of type Y (in schema Z), automatically grant these privileges".

Analogy. Default privileges are like a company policy that every new meeting room is automatically added to the "All staff" booking group, instead of the facilities team adding each room manually.

Syntax

ALTER DEFAULT PRIVILEGES
  [ FOR ROLE creator_role ]            -- whose newly created objects (default: the current role)
  [ IN SCHEMA schema_name ]            -- limit to one schema (default: all schemas)
  GRANT privileges ON { TABLES | SEQUENCES | FUNCTIONS | ROUTINES | TYPES | SCHEMAS }
  TO role;

A complete setup for the owner-role pattern

-- Objects are created by shop_owner (via SET ROLE during migrations)
ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO shop_read;

ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO shop_write;

ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA public
  GRANT USAGE ON SEQUENCES TO shop_write;          -- needed for serial columns

ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;         -- new functions are no longer executable by everyone

-- Existing objects are not affected: grant them once explicitly
GRANT SELECT ON ALL TABLES IN SCHEMA public TO shop_read;

The classic mistake: the wrong creator. Default privileges apply to objects created by the role named in FOR ROLE, not to every object in the schema. If an administrator runs ALTER DEFAULT PRIVILEGES without FOR ROLE as postgres, the rule applies only to objects postgres creates. When the migration tool then creates tables as migrator, nothing is granted.

 ALTER DEFAULT PRIVILEGES ... GRANT SELECT ON TABLES TO shop_read;   -- run as postgres
         |
         v
 rule stored for creator = postgres
         |
 migrator creates table "invoices"  ->  creator = migrator  ->  no rule matches  ->  shop_read gets nothing

The cure is to create objects consistently as one role (the owner role), and to name that role in FOR ROLE.

Inspecting default privileges

\ddp
--                Default access privileges
--     Owner    | Schema |   Type   |     Access privileges
-- -------------+--------+----------+---------------------------
--  shop_owner  | public | table    | shop_read=r/shop_owner   +
--              |        |          | shop_write=arwd/shop_owner
--  shop_owner  | public | sequence | shop_write=U/shop_owner
--  shop_owner  |        | function | shop_owner=X/shop_owner

The last line shows that functions created by shop_owner are executable only by the owner, because the default PUBLIC grant was revoked.

Rules and limits

Rule	Consequence
Only future objects are affected	Pair with a one-time GRANT ... ON ALL ... IN SCHEMA for existing objects
FOR ROLE must be yourself or a role you are a member of	Owners or their members configure the defaults
Per-schema defaults are added to global ones	A per-schema rule cannot remove something a global rule grants
Defaults are per database	Repeat the setup in each database
DROP OWNED BY removes default privilege entries too	Part of the role-dropping procedure

Key takeaways

• Default privileges grant permissions automatically on objects created in the future.
• They are keyed to the creating role (FOR ROLE), which is the most common source of "it worked for old tables but not new ones".
• Create all schema objects as one owner role and define defaults for that role.
• Combine with a one-time GRANT ... ON ALL ... IN SCHEMA for existing objects; inspect with \ddp.
• Use defaults to remove unwanted built-ins, such as EXECUTE for PUBLIC on new functions.

🧪 Practice

1. (Beginner) Define default privileges so shop_read can read every new table that shop_owner creates in public. Create a table as shop_owner and verify the grant with \dp.
2. (Intermediate) Reproduce the "wrong creator" mistake: define the default as postgres without FOR ROLE, create a table as migrator, and show that shop_read cannot read it. Fix the setup so that it works regardless of which login role runs migrations.
3. (Interview) "Every time we deploy, someone has to re-run a grants script. How would you make permissions self-maintaining?" Hint: Combine a single owner role used via SET ROLE in migrations, ALTER DEFAULT PRIVILEGES FOR ROLE, group roles for read and write, and a CI check that compares \dp output with expectations.

Schema-Level Access Control

Schemas are namespaces inside a database, and they are also a security boundary. To use any object, a role needs USAGE on the schema that contains it, in addition to privileges on the object itself; to create objects, it needs CREATE on the schema. Schemas are also involved in one of PostgreSQL's most subtle attack paths: name resolution through search_path, where a malicious object in a writable schema can shadow a trusted one.

Analogy. A schema is a locked filing cabinet. USAGE is the key to open it; the per-folder privileges decide which folders you may read. CREATE lets you put new folders inside. If strangers can put folders in a cabinet you search first, they can slip in a fake folder with the same label as the real one.

Two-level check

CREATE SCHEMA finance AUTHORIZATION shop_owner;
CREATE TABLE finance.ledger (id int, amount numeric);       -- as shop_owner
GRANT SELECT ON finance.ledger TO analysts;

-- as alice (member of analysts):
SELECT * FROM finance.ledger;
-- ERROR:  permission denied for schema finance             -- table privilege alone is not enough

-- as shop_owner:
GRANT USAGE ON SCHEMA finance TO analysts;
-- as alice:
SELECT * FROM finance.ledger;                               -- works

Revoking USAGE on a schema is a quick way to cut access to everything inside it without touching individual grants.

The public schema before and after PostgreSQL 15

	PostgreSQL 14 and earlier	PostgreSQL 15+
Owner of public	Bootstrap superuser (postgres)	pg_database_owner (the database's owner)
PUBLIC privileges on public	USAGE and CREATE	USAGE only
Consequence	Any user can create objects that others' queries may resolve to	Only the database owner (and grantees) can create objects there

Clusters upgraded with pg_upgrade or restored from old dumps may keep the old, permissive setting. Check and fix it explicitly:

SELECT nspname, nspacl FROM pg_namespace WHERE nspname = 'public';
--  nspname |                  nspacl
-- ---------+------------------------------------------
--  public  | {postgres=UC/postgres,=UC/postgres}        -- "=UC" means PUBLIC has USAGE and CREATE

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

The search_path attack. Unqualified names (customers, lower(), =) are resolved by searching the schemas in search_path in order. If an attacker can create objects in a schema that appears earlier in another user's path than the intended object, the attacker's object is used instead, and it runs with the victim's privileges:

 victim's search_path:  "$user", public
 attacker (has CREATE on public) creates:  public.lower(varchar)  -- a better match for a varchar argument than pg_catalog.lower(text)

 victim runs:  SELECT lower(email) FROM customers;
               -> resolves to public.lower(varchar), attacker code runs as the victim

This class of issue (CVE-2018-1058) is why PostgreSQL 15 removed CREATE from PUBLIC. The defenses:

Defense	How
No untrusted writes to shared schemas	REVOKE CREATE ON SCHEMA public FROM PUBLIC (default in 15+)
Private schemas per user if users need to create objects	CREATE SCHEMA alice AUTHORIZATION alice; the default path "$user", public finds it first
Fixed path for privileged code	SET search_path = pg_catalog, pg_temp on definer functions; schema-qualify names
Fixed path for applications	ALTER ROLE app_user SET search_path = shop, pg_catalog

Database-level gates. The database itself has privileges that sit one level above schemas. Because PUBLIC has CONNECT and TEMPORARY by default, every role in the cluster can connect to every database that pg_hba.conf lets it reach:

REVOKE CONNECT, TEMPORARY ON DATABASE shop FROM PUBLIC;
GRANT CONNECT ON DATABASE shop TO shop_read, shop_write;
GRANT TEMPORARY ON DATABASE shop TO shop_write;         -- only if the application uses temp tables

 Access check layers for: SELECT * FROM finance.ledger
   pg_hba.conf rule      -> may this client connect as this role to this database?
   CONNECT on database   -> may the role open a session here?
   USAGE on schema       -> may the role look inside finance?
   SELECT on table       -> may the role read ledger (or these columns)?
   RLS policies          -> which rows?

Key takeaways

• Accessing an object needs USAGE on its schema plus the object privilege; creating needs CREATE on the schema.
• PostgreSQL 15 removed CREATE on public from PUBLIC; check upgraded clusters, which may still allow it.
• Writable schemas earlier in search_path let attackers shadow functions, operators, and tables; fix paths and schema-qualify privileged code.
• Revoke CONNECT and TEMPORARY on databases from PUBLIC and grant them to the roles that need them.

🧪 Practice

1. (Beginner) Create schema finance with one table, grant SELECT on the table to analysts, and show the error before and after granting USAGE on the schema.
2. (Intermediate) In a scratch database, grant CREATE ON SCHEMA public TO PUBLIC (simulating an old cluster). As an unprivileged role, create a function public.lower(varchar) that logs current_user with RAISE NOTICE, then run a query as another role that calls lower() on a varchar column. Explain what happened and apply two independent fixes.
3. (Interview) "What changed about the public schema in PostgreSQL 15, and why should you still check it on a database that runs version 17?" Hint: Think about how upgrades and restores carry over ACLs, and what nspacl shows.

Role Inheritance and SET ROLE

When alice is a member of analysts, can she use the privileges of analysts automatically, or must she explicitly "become" analysts first? PostgreSQL supports both modes. Inheritance makes a member's privileges include those of its groups automatically; SET ROLE switches the session's current role to a group role, so that permission checks (and ownership of new objects) use that role. Distinguishing the two lets you build both convenient everyday access and deliberate, sudo-style elevation.

Analogy. Inherited membership is like a staff badge that already includes the "Finance floor" permission: it works at every door without thinking. SET ROLE is like checking out the master key from the security desk: you must ask for it explicitly, and while you hold it, everything you do is done as the key holder.

Membership options (PostgreSQL 16+). Each membership grant has three independent options:

GRANT analysts   TO alice;                              -- defaults: INHERIT TRUE, SET TRUE, ADMIN FALSE
GRANT shop_owner TO alice WITH INHERIT FALSE;           -- must SET ROLE to act as owner
GRANT support    TO kim   WITH SET FALSE;               -- inherits support's privileges, cannot become support
GRANT analysts   TO lead  WITH ADMIN TRUE;              -- lead may grant/revoke analysts membership to others

Option	Meaning when TRUE
INHERIT	The member automatically has the privileges of the granted role
SET	The member may SET ROLE to the granted role (and create objects owned by it)
ADMIN	The member may grant and revoke membership in the granted role

Before PostgreSQL 16 inheritance was a property of the member (INHERIT/NOINHERIT role attribute) rather than of each grant; in 16+ that attribute only sets the default for new grants.

Inheritance is transitive; attributes are not inherited. If alice is in analysts and analysts is in shop_read, alice has the privileges of both. But role attributes such as SUPERUSER, CREATEDB, CREATEROLE, and BYPASSRLS are never inherited; a member must SET ROLE to a role that has them.

          shop_read  (SELECT on all tables)
              ^
              | INHERIT TRUE
          analysts   (SELECT on finance.ledger)
              ^
              | INHERIT TRUE
            alice    -> effective privileges: alice's + analysts' + shop_read's

          shop_owner (owns tables)
              ^
              | INHERIT FALSE, SET TRUE
            alice    -> owner powers only after SET ROLE shop_owner

Using SET ROLE

-- connected as alice
SELECT current_user, session_user;
--  current_user | session_user
-- --------------+--------------
--  alice        | alice

SET ROLE shop_owner;                         -- elevate deliberately
ALTER TABLE invoices ADD COLUMN due date;    -- allowed: acting as the owner
SELECT current_user, session_user;
--  shop_owner   | alice                     -- session_user still records who logged in

RESET ROLE;                                  -- back to alice (also: SET ROLE NONE)

BEGIN;
SET LOCAL ROLE shop_owner;                   -- reverts automatically at COMMIT or ROLLBACK
-- ... migration statements ...
COMMIT;

Function	Returns
session_user	The role that authenticated; changes only with SET SESSION AUTHORIZATION (superusers)
current_user	The role whose privileges are checked now (changed by SET ROLE and definer functions)
pg_has_role('alice', 'shop_owner', 'USAGE')	Whether alice has the role's privileges now (inherited)
pg_has_role('alice', 'shop_owner', 'SET')	Whether alice may SET ROLE to it (16+)
pg_has_role('alice', 'shop_owner', 'MEMBER')	Whether alice is a member at all

Using NOINHERIT for safety. Administrators should not carry powerful privileges in every session, where a mistyped DROP or a malicious script can use them. Grant the powerful role with INHERIT FALSE, so daily work runs with the personal role's privileges and elevation is visible in logs (SET ROLE is logged with log_statement = 'all' or pgaudit).

Pitfall: SET ROLE is not a security boundary for untrusted code. A session that ran SET ROLE can always RESET ROLE back. Connection poolers that switch roles per request with SET ROLE must ensure that application code cannot send its own RESET ROLE; otherwise every request can reach the pool's login role's privileges.

Key takeaways

• Memberships can be inherited (automatic privileges) or used via SET ROLE (explicit switch); PostgreSQL 16+ sets INHERIT, SET, and ADMIN per grant.
• Inheritance is transitive; role attributes such as SUPERUSER and BYPASSRLS are never inherited.
• current_user changes with SET ROLE, while session_user keeps the authenticated identity.
• Use INHERIT FALSE for powerful roles to require deliberate elevation, and SET LOCAL ROLE to scope it to a transaction.
• SET ROLE can be undone by the session itself, so it does not isolate untrusted code.

🧪 Practice

1. (Beginner) Make alice a member of analysts and shop_owner with INHERIT FALSE for the latter. Show which of SELECT FROM customers and ALTER TABLE customers work before and after SET ROLE shop_owner.
2. (Intermediate) Build a three-level role chain and write a recursive query over pg_auth_members that lists, for each login role, every role whose privileges it inherits. Compare your result with pg_has_role(..., 'USAGE').
3. (Interview) "A pooler connects as app_pool and runs SET ROLE tenant_42 before each request. Is that secure?" Hint: Consider whether request code can run arbitrary SQL, RESET ROLE, what app_pool itself is allowed to do, and alternatives such as RLS with a session variable or separate pools.

Predefined Roles

Many operational tasks need a slice of superuser power: a monitoring agent must read statistics about every session, a backup operator must run checkpoints, a support engineer must cancel runaway queries. Making these accounts superusers grants far more than they need. Predefined roles are built-in group roles (all named pg_*) that bundle such capabilities, so you can grant exactly one capability by granting membership.

Analogy. Predefined roles are like standard job profiles in an HR system ("Payroll viewer", "IT helpdesk") that come preconfigured with the right permissions, so you assign the profile instead of crafting permissions from scratch.

The most useful predefined roles

Role	Since	Grants
pg_read_all_data	14	SELECT on all tables, views, sequences, plus USAGE on all schemas, in every database (RLS still applies)
pg_write_all_data	14	INSERT, UPDATE, DELETE on all tables, plus schema USAGE
pg_monitor	10	Union of pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables: everything a monitoring agent needs
pg_read_all_stats	10	Full pg_stat_activity (including other users' queries) and other statistics views
pg_read_all_settings	10	Read all configuration parameters, including superuser-only ones
pg_signal_backend	9.6	pg_cancel_backend / pg_terminate_backend on non-superuser sessions
pg_checkpoint	15	Run CHECKPOINT
pg_maintain	17	VACUUM, ANALYZE, CLUSTER, REINDEX, REFRESH MATERIALIZED VIEW, LOCK TABLE on all relations
pg_use_reserved_connections	16	Use the slots reserved by reserved_connections, so admin tools can connect when the server is full
pg_create_subscription	16	Create logical replication subscriptions (with CREATE on the database)
pg_signal_autovacuum_worker	18	Cancel or terminate autovacuum workers
pg_database_owner	14	Implicit membership of the current database's owner; used to own the public schema
pg_read_server_files, pg_write_server_files, pg_execute_server_program	11	Read/write server files with COPY, run shell commands with COPY ... PROGRAM

Examples

-- Monitoring agent (Prometheus postgres_exporter, Datadog, ...)
CREATE ROLE monitor LOGIN CONNECTION LIMIT 3;
GRANT pg_monitor TO monitor;

-- On-call engineers can cancel runaway queries but cannot read data
GRANT pg_signal_backend TO oncall;

-- A reporting role that can read everything, in every database, including tables created later
GRANT pg_read_all_data TO bi_reader;

-- Maintenance jobs without ownership or superuser (PostgreSQL 17+)
GRANT pg_maintain TO maint_job;

pg_read_all_data replaces the old pattern of granting SELECT on each table plus default privileges, at the cost of being all-or-nothing across the cluster. It does not bypass row-level security, so RLS-protected tables still filter rows unless the role also has BYPASSRLS.

Superuser-equivalent roles. pg_read_server_files, pg_write_server_files, and pg_execute_server_program operate with the server's OS user. Writing files into the data directory or running programs can be turned into full control of the cluster, so treat membership in them like superuser access.

-- Who holds which predefined role?
SELECT r.rolname AS predefined, m.rolname AS member
FROM pg_auth_members AS am
JOIN pg_roles AS r ON r.oid = am.roleid
JOIN pg_roles AS m ON m.oid = am.member
WHERE r.rolname LIKE 'pg\_%'
ORDER BY 1, 2;

Key takeaways

• Predefined pg_* roles grant specific administrative capabilities without superuser.
• pg_monitor for monitoring, pg_signal_backend for cancelling queries, pg_maintain (17+) for maintenance, pg_read_all_data/pg_write_all_data for cluster-wide data access.
• pg_read_all_data covers future tables in all databases but still respects RLS.
• The server-file and server-program roles are effectively superuser; grant them as rarely as superuser itself.
• Audit predefined role memberships just like superuser accounts.

🧪 Practice

1. (Beginner) Create a monitor role, grant pg_monitor, and compare what SELECT query FROM pg_stat_activity returns for it and for a plain role while another session runs a query.
2. (Intermediate) Give an oncall role the ability to cancel non-superuser queries. As oncall, cancel a SELECT pg_sleep(600) from app_user and attempt to cancel one from postgres. Explain the result.
3. (Interview) "Your BI vendor asks for superuser to 'read all the data'. What do you give them instead?" Hint: Compare pg_read_all_data, a dedicated read replica, a curated reporting schema with views, and how RLS and column grants interact with each.

Principle of Least Privilege

Every privilege a role holds is a privilege an attacker gets if that role's credentials leak or its application is compromised. The principle of least privilege says each role should have exactly the permissions its job requires and nothing more. Applied consistently, it turns a SQL injection in a reporting endpoint from "attacker drops the database" into "attacker reads a few views they could already see".

Analogy. A hotel gives each guest a key card that opens only their room and the gym, not a master key, even though a master key would be more convenient. When a card is lost, only one room is at risk.

A layered role design

 superuser (postgres)  -- local peer login only; emergencies and cluster administration
       |
 shop_owner (NOLOGIN)  -- owns all objects in shop; never used by applications
       ^ SET ROLE (INHERIT FALSE)
 migrator (LOGIN)      -- CI/CD migrations: SET ROLE shop_owner, then DDL

 shop_write (NOLOGIN)  -- SELECT, INSERT, UPDATE, DELETE on application tables; EXECUTE on API functions
       ^
 app_blue / app_green (LOGIN)   -- the application, rotating credentials

 shop_read (NOLOGIN)   -- SELECT on reporting views only
       ^
 bi_tool (LOGIN), analysts' personal roles

 monitor (LOGIN)       -- pg_monitor only

Implementation

-- 1. Lock down defaults
REVOKE ALL ON DATABASE shop FROM PUBLIC;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;              -- already the default in 15+

-- 2. Owner and group roles
CREATE ROLE shop_owner NOLOGIN;
CREATE ROLE shop_write NOLOGIN;
CREATE ROLE shop_read  NOLOGIN;
ALTER SCHEMA public OWNER TO shop_owner;

GRANT CONNECT ON DATABASE shop TO shop_write, shop_read;
GRANT USAGE ON SCHEMA public TO shop_write, shop_read;

-- 3. Future objects
ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO shop_write;
ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA public
  GRANT USAGE ON SEQUENCES TO shop_write;
ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner
  REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

-- 4. Login roles
CREATE ROLE migrator LOGIN;
GRANT shop_owner TO migrator WITH INHERIT FALSE;         -- must SET ROLE explicitly
CREATE ROLE app_blue LOGIN IN ROLE shop_write;
ALTER ROLE app_blue SET statement_timeout = '5s';        -- limits the cost of runaway or abusive queries
ALTER ROLE app_blue SET search_path = public, pg_catalog;

What to restrict, and how

Risk	Least-privilege control
Application runs DDL after an injection	Application role does not own objects and has no CREATE on schemas
Application deletes all rows	Grant DELETE only where needed; expose critical writes only through definer functions
One service reads another's data	One login role per service, separate schemas, USAGE only where needed
Analyst reads PII	Reporting views and column grants (Column-Level Grants)
Tenant reads another tenant's rows	Row-level security (Multi-Tenant Isolation with RLS)
Leaked credential used from elsewhere	pg_hba.conf restricts each role to its source network
Expensive queries as denial of service	Per-role statement_timeout, CONNECTION LIMIT
Daily admin work with superuser	Personal roles plus predefined roles; superuser only via peer and SET ROLE

Reviewing privileges regularly. Privileges accumulate over time ("temporarily" granted and never revoked). A few queries make a useful periodic review:

-- Roles with powerful attributes
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolreplication, rolbypassrls
FROM pg_roles
WHERE (rolsuper OR rolcreaterole OR rolcreatedb OR rolreplication OR rolbypassrls)
  AND rolname !~ '^pg_';

-- Everything granted on tables, flattened (one row per grantee and privilege)
SELECT c.relname, a.grantee::regrole AS grantee, a.privilege_type
FROM pg_class AS c
CROSS JOIN LATERAL aclexplode(c.relacl) AS a
WHERE c.relnamespace = 'public'::regnamespace
ORDER BY 1, 2, 3;                         -- grantee 0 (shown as "-") means PUBLIC

-- Login roles that have not logged in recently need log analysis or pgaudit; PostgreSQL does not store last login

Key takeaways

• Give each role exactly the privileges its job needs; the payoff comes when something is compromised.
• Separate owner, migration, application, reporting, and monitoring roles, and grant privileges to group roles only.
• Remove broad defaults (PUBLIC on databases, schemas, functions) and use per-role settings such as statement_timeout.
• Combine object grants with pg_hba.conf restrictions, RLS, and column grants for defense in depth.
• Review powerful attributes and grants periodically with catalog queries such as aclexplode.

🧪 Practice

1. (Beginner) Implement the role design above in a fresh database. As app_blue, try CREATE TABLE, DROP TABLE customers, and TRUNCATE accounts, and record the errors.
2. (Intermediate) Write a single query that lists every role that can modify the accounts table, whether through direct grants, group membership, ownership, or superuser status. Test it after adding and removing memberships.
3. (Interview) "Walk me through how you would design database roles for a new service with an API, a nightly ETL job, a BI dashboard, and on-call engineers." Hint: Identify each actor's minimal operations, ownership and migrations, credential handling, network restrictions, timeouts, and how you would verify the design with automated tests.

<a id="103-row-and-column-security"></a>

10.3 Row and Column Security

Table privileges are all-or-nothing: a role that may SELECT from a table sees every row and every column. This subchapter covers the finer-grained tools that restrict which rows (row-level security) and which columns (column grants, views, masking) a role can reach, and the planner behavior that keeps those restrictions from leaking.

Row-Level Security Policies

Consider an accounts table where each customer may see only their own accounts, or a CRM where each sales rep may edit only the customers they manage. The traditional solution is to add WHERE owner = ? to every query in the application, and the traditional failure is the one query where someone forgot. Row-level security (RLS) moves the filter into the database: once enabled on a table, every query by an affected role automatically gets the policy's condition added, no matter how the query was written.

Analogy. RLS is like a filing clerk who, whatever you ask for ("all the folders", "folders from March"), quietly removes every folder you are not cleared to see before handing over the pile. You cannot ask the question in a way that skips the clerk.

Enabling RLS. RLS is off by default. Once enabled, a table with no policies denies everything to roles that are subject to RLS (default deny):

GRANT SELECT, UPDATE ON accounts TO alice, bob;
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;

-- as alice
SELECT * FROM accounts;
--  id | owner | balance
-- ----+-------+---------
-- (0 rows)                         -- privileges allow the query, but no policy allows any row

Creating a policy

CREATE POLICY own_accounts ON accounts
  FOR SELECT                                    -- which command the policy covers (default: ALL)
  TO alice, bob                                 -- which roles (default: PUBLIC)
  USING (owner = current_user);                 -- rows for which this is true are visible

-- as alice
SELECT * FROM accounts;
--  id | owner | balance
-- ----+-------+---------
--   1 | alice |  100.00                        -- the filter was added automatically

SELECT sum(balance) FROM accounts;              -- 100.00: aggregates see only visible rows too

Syntax overview

CREATE POLICY name ON table_name
  [ AS { PERMISSIVE | RESTRICTIVE } ]           -- how it combines with other policies (default PERMISSIVE)
  [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
  [ TO { role_name | PUBLIC | CURRENT_USER } [, ...] ]
  [ USING ( condition ) ]                       -- filters existing rows
  [ WITH CHECK ( condition ) ];                 -- validates new rows ([USING and WITH CHECK Expressions](#using-and-with-check-expressions))

Who is subject to RLS?

Role	Subject to policies?
Superuser	No, never
Role with BYPASSRLS	No
Table owner	No, unless ALTER TABLE ... FORCE ROW LEVEL SECURITY
Any other role	Yes

Owner bypass is a frequent source of "RLS does not work": testing as the role that created the table shows every row. Either test as the application role, or force RLS on owners too:

ALTER TABLE accounts FORCE ROW LEVEL SECURITY;  -- the owner is now filtered as well

Fail loudly instead of silently. Setting row_security = off in a session makes any query that would be filtered by RLS raise an error instead of returning fewer rows. pg_dump uses this so that a backup taken by an RLS-restricted role fails rather than silently omitting rows.

Inspecting policies

SELECT tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies WHERE tablename = 'accounts';
--  tablename |  policyname  | permissive |    roles    |  cmd   |          qual           | with_check
-- -----------+--------------+------------+-------------+--------+-------------------------+------------
--  accounts  | own_accounts | PERMISSIVE | {alice,bob} | SELECT | (owner = CURRENT_USER)  |

SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class WHERE relname = 'accounts';       -- is RLS enabled / forced?

How the filter is applied. The policy condition is added to the query as a security qualifier that is evaluated before any user-supplied condition that is not LEAKPROOF, so a crafted WHERE clause cannot observe hidden rows (Security Barriers in Views). The condition takes part in planning like any other predicate, so an index on the policy column (here owner) matters for performance:

EXPLAIN (COSTS OFF) SELECT * FROM accounts WHERE balance > 10;   -- as alice
--  Seq Scan on accounts
--    Filter: ((owner = CURRENT_USER) AND (balance > '10'::numeric))   -- policy first

Limits to know

• RLS does not apply to constraint checks. A unique or foreign key violation can reveal that a hidden row exists ("duplicate key value violates unique constraint"), which is a side channel for guessable keys.
• Policies that query other tables are subject to those tables' privileges and policies; recursive references between policies cause errors.
• TRUNCATE and DDL are not filtered by policies; control them with privileges.

Key takeaways

• RLS adds policy conditions to every query on a table, so a forgotten WHERE clause cannot leak rows.
• Enabling RLS without policies denies all rows to affected roles.
• Superusers, BYPASSRLS roles, and (unless FORCE is set) owners are not filtered; test as the application role.
• Policies are listed in pg_policies; index the columns they use.
• Constraint violations can reveal hidden rows; RLS does not cover TRUNCATE or DDL.

🧪 Practice

1. (Beginner) Enable RLS on accounts with the own_accounts policy. Query the table as alice, bob, and the owner, and explain each result.
2. (Intermediate) Add a policy that lets a role auditor see all accounts with a balance over 50, without changing what alice and bob see. Verify with EXPLAIN that the right conditions are applied for each role.
3. (Interview) "We enabled RLS, but our integration tests still see every tenant's data. What do you check first?" Hint: Who the tests connect as: owner, superuser, BYPASSRLS; whether FORCE ROW LEVEL SECURITY is set; and whether policies use TO PUBLIC or a specific role.

USING and WITH CHECK Expressions

Reading and writing need different checks. When bob updates a customer, two questions arise: is he allowed to touch the existing row, and is the row allowed to look the way he wants to leave it? Without the second check, bob could update one of his customers and set manager = 'alice', moving the row out of his own visibility and into someone else's. RLS answers the two questions with two expressions: USING filters existing rows, and WITH CHECK validates new or modified rows.

Analogy. USING is the guard at the warehouse exit who decides which boxes you may take out and handle. WITH CHECK is the guard at the entrance who inspects every box you bring back in, making sure it is labeled for a shelf you are allowed to use.

Which expression applies to which command

Command	USING checks	WITH CHECK checks	Row that fails
SELECT	Existing rows	Not used	Silently invisible
INSERT	Not used	The new row	Error
UPDATE	Existing rows (which ones may be updated)	The new version of the row	Existing: silently skipped; new: error
DELETE	Existing rows	Not used	Silently skipped
ALL	All of the above	All of the above (defaults to the USING expression if omitted)	As above

Two details complete the picture. For an UPDATE policy without WITH CHECK, the USING expression is used for both checks. And when an UPDATE or DELETE reads columns (in WHERE or RETURNING), the rows must also pass the SELECT policies; similarly, INSERT ... RETURNING requires the new row to be visible under SELECT policies.

Example: sales reps manage their own customers

GRANT SELECT, INSERT, UPDATE ON customers TO alice, bob;
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

CREATE POLICY reps_read ON customers
  FOR SELECT
  USING (manager = current_user);

CREATE POLICY reps_insert ON customers
  FOR INSERT
  WITH CHECK (manager = current_user);          -- can only create customers assigned to yourself

CREATE POLICY reps_update ON customers
  FOR UPDATE
  USING (manager = current_user)                -- may update only rows you manage now...
  WITH CHECK (manager = current_user);          -- ...and they must still be yours afterwards

-- as bob
UPDATE customers SET phone = '+1 415 555 0199' WHERE id = 2;   -- UPDATE 1
UPDATE customers SET phone = 'x' WHERE id = 1;                 -- UPDATE 0: alice's row is invisible, silently skipped
UPDATE customers SET manager = 'alice' WHERE id = 3;
-- ERROR:  new row violates row-level security policy for table "customers"
INSERT INTO customers VALUES (4, 'Hooli', 'it@hooli.example', NULL, NULL, 'US', 'alice');
-- ERROR:  new row violates row-level security policy for table "customers"

Different conditions for reading and writing. USING and WITH CHECK need not match. A common pattern lets everyone in a region read customers but only the manager write them:

CREATE POLICY region_read ON customers
  FOR SELECT
  USING (region = current_setting('app.region', true));   -- true: missing setting returns NULL instead of an error

Because reps_read and region_read are both permissive SELECT policies, a row is visible if either condition holds.

Permissive and restrictive policies. Policies of the same command combine as follows:

 visible / allowed  =  (permissive_1 OR permissive_2 OR ...)  AND  restrictive_1 AND restrictive_2 ...

At least one permissive policy must exist for any row to be accessible; restrictive policies can only narrow access further. Restrictive policies are ideal for rules that must hold no matter what other policies say:

-- Nobody but finance may touch rows outside their own region, whatever other policies allow
CREATE POLICY same_region_only ON customers
  AS RESTRICTIVE
  FOR ALL
  TO PUBLIC
  USING (region = current_setting('app.region', true))
  WITH CHECK (region = current_setting('app.region', true));

 bob, app.region = 'US', reading customer 1 (manager alice, region EU):
   permissive:  reps_read (manager = bob)? no   OR  region_read (EU = US)? no   -> false
   restrictive: same_region_only (EU = US)? no
   result: invisible

Writing expressions safely

• Keep expressions simple and indexable (column = value); they run for every row considered.
• current_user, current_setting(...), and comparisons on built-in types are cheap and work as index conditions.
• Subqueries in policies (manager IN (SELECT member FROM team WHERE lead = current_user)) are allowed but run under the querying role's privileges and policies on the other table; wrap complex lookups in a STABLE function, often SECURITY DEFINER with a pinned search_path.
• Use the two-argument form current_setting('name', true) so that a missing setting yields NULL and therefore no rows, rather than an error.

Key takeaways

• USING filters existing rows (read, update, delete); WITH CHECK validates new row versions (insert, update).
• Rows failing USING disappear silently; rows failing WITH CHECK raise an error.
• WITH CHECK prevents "moving" rows out of your own visibility, such as reassigning ownership.
• Permissive policies are OR'ed, restrictive policies are AND'ed on top; at least one permissive policy is needed.
• Keep policy expressions simple, indexable, and fail-closed when context is missing.

🧪 Practice

1. (Beginner) Create the three reps_* policies on customers. As bob, insert a customer for himself, try to insert one for alice, and try to update one of alice's customers. Explain each outcome using the table above.
2. (Intermediate) Replace the three policies with a single FOR ALL policy without a WITH CHECK clause, and determine whether bob can still reassign a customer to alice. Then add a restrictive policy that prevents any role from changing tax_id to NULL.
3. (Interview) "Explain the difference between USING and WITH CHECK with an example where omitting WITH CHECK on an UPDATE policy creates a security hole." Hint: Consider a policy with different read and write conditions, and what the default for WITH CHECK is in FOR UPDATE versus FOR ALL policies.

Multi-Tenant Isolation with RLS

SaaS applications often store all customers ("tenants") in shared tables with a tenant_id column (Multi-Tenant Schema Strategies). A single missing tenant filter in one endpoint shows one company's data to another, which is one of the most damaging bugs a SaaS business can have. RLS makes tenant isolation a property of the database rather than of every query. The application tells the database which tenant the current request belongs to, and the policies do the rest.

Analogy. A shared office building with one lobby: RLS is the elevator that, once your badge says "Company 7", only stops at Company 7's floors. Visitors cannot press the button for another company's floor even if they know it exists.

Design

 HTTP request (JWT: tenant 7)
        |
        v
 Application authenticates the user, resolves tenant_id = 7
        |
        v  BEGIN; SELECT set_config('app.tenant_id', '7', true); ...queries...; COMMIT;
 PostgreSQL, connected as app_user (not owner, no BYPASSRLS)
        |
        v
 Policy on every tenant table: tenant_id = app.tenant_id

Implementation, building on the projects and tasks tables from Multi-Tenant Schema Strategies:

-- A helper that reads the tenant from the session, failing closed
CREATE FUNCTION current_tenant() RETURNS bigint
LANGUAGE sql STABLE PARALLEL SAFE
RETURN nullif(current_setting('app.tenant_id', true), '')::bigint;
-- missing setting -> NULL; after SET LOCAL ends, the setting becomes '' -> NULL (a plain cast of '' would error)

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE  ROW LEVEL SECURITY;      -- the owner is filtered too: with no policy for it, it sees no rows
ALTER TABLE tasks    ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks    FORCE  ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
  FOR ALL TO app_user
  USING (tenant_id = current_tenant())
  WITH CHECK (tenant_id = current_tenant());         -- inserts and updates must stay in the tenant

CREATE POLICY tenant_isolation ON tasks
  FOR ALL TO app_user
  USING (tenant_id = current_tenant())
  WITH CHECK (tenant_id = current_tenant());

Setting the tenant per request. SET cannot take a bind parameter, so use set_config(name, value, is_local) with is_local = true, which behaves like SET LOCAL and is reset at the end of the transaction:

import psycopg

def list_tasks(pool, tenant_id: int):
    with pool.connection() as conn:          # psycopg_pool; the block runs in one transaction
        conn.execute(
            "SELECT set_config('app.tenant_id', %s, true)",   # transaction-scoped
            (str(tenant_id),),                                # value comes from the verified token
        )
        return conn.execute(
            "SELECT id, title FROM tasks ORDER BY id"         # no tenant filter needed
        ).fetchall()

Transaction scope is essential with connection pools: a session-level SET would remain on the pooled connection and apply to the next request, possibly for another tenant. With PgBouncer in transaction mode, only transaction-scoped settings are safe.

What can still go wrong

Risk	Mitigation
Application connects as table owner or a BYPASSRLS role	Dedicated app_user; FORCE ROW LEVEL SECURITY; CI check of pg_roles
A new table is created without RLS	Migration check: every table with a tenant_id column must have relrowsecurity and a policy
Tenant context leaks between pooled requests	Only set_config(..., true) inside a transaction
SQL injection sets app.tenant_id to another value	Parameterized queries everywhere; for stronger isolation, verify a signed token in a definer function or use one role per tenant
Cross-tenant references through foreign keys	Composite keys (tenant_id, id) as in Multi-Tenant Schema Strategies
Slow queries	Lead every index with tenant_id; keep the policy function STABLE and simple
Admin, analytics, and data migrations need all tenants	A separate role with BYPASSRLS (or a dedicated policy), used only by back-office tools and migration jobs

Automated guard: tables missing RLS

SELECT c.oid::regclass AS table_without_rls
FROM pg_class AS c
JOIN pg_attribute AS a ON a.attrelid = c.oid AND a.attname = 'tenant_id' AND NOT a.attisdropped
WHERE c.relkind IN ('r', 'p')                              -- ordinary and partitioned tables
  AND c.relnamespace = 'public'::regnamespace
  AND (NOT c.relrowsecurity
       OR NOT EXISTS (SELECT 1 FROM pg_policy AS p WHERE p.polrelid = c.oid));
-- Run in CI after migrations; any row is a build failure

Testing isolation. Tests must run as app_user, never as the owner, and should include adversarial cases: a SELECT without a filter, an INSERT with another tenant's tenant_id, an UPDATE that changes tenant_id, and a request with no tenant set (expecting zero rows, not an error or all rows).

Partitioned tables. Policies are checked on the table named in the query. For a partitioned table, define policies on the parent; if roles can query partitions directly, enable RLS and policies on those too.

Key takeaways

• RLS turns tenant isolation into a database guarantee: every query on a tenant table is filtered by the current tenant.
• Pass the tenant with set_config('app.tenant_id', $1, true) inside each transaction; never with session-level SET on pooled connections.
• Make the policy fail closed (nullif(current_setting(..., true), '')), and use WITH CHECK so writes cannot cross tenants.
• Connect as a non-owner role without BYPASSRLS, and add FORCE ROW LEVEL SECURITY as a safety net.
• Automate checks: tables with tenant_id but no policy, and tests that run as the application role.

🧪 Practice

1. (Beginner) Implement the current_tenant() function and policies on projects and tasks. As app_user, show the results for tenant 1, tenant 2, and no tenant set.
2. (Intermediate) Write a short script that simulates a connection pool: one connection, two consecutive "requests" for different tenants, the first using SET and the second forgetting to set the tenant. Show the leak, then fix it with set_config(..., true) and demonstrate the fail-closed result.
3. (Interview) "How would you guarantee tenant isolation in a shared-schema SaaS database, and how would you prove to an auditor that it works?" Hint: Cover RLS with WITH CHECK, the connecting role's attributes, transaction-scoped context, composite foreign keys, CI checks over the catalogs, and adversarial integration tests.

Column-Level Grants

Some columns are more sensitive than the rest of their table: a customer's tax identifier, a salary, a password hash. A support engineer needs names and email addresses to help customers, but has no reason to see tax IDs. Splitting such data into separate tables is one answer; a simpler one is to grant privileges on individual columns rather than on the whole table.

Analogy. A column grant is like a document with some paragraphs blacked out for certain readers: the same document, but each reader can only read the lines their clearance covers.

Granting column privileges. The examples assume RLS is disabled on customers (ALTER TABLE customers DISABLE ROW LEVEL SECURITY if you followed the previous topics), so that only column privileges are in play.

CREATE ROLE support NOLOGIN;
GRANT support TO kim;

GRANT SELECT (id, name, email, phone, region) ON customers TO support;   -- no tax_id, no manager
GRANT UPDATE (email, phone) ON customers TO support;                      -- may correct contact details only

-- as kim
SELECT id, name, email FROM customers;                -- works
SELECT * FROM customers;
-- ERROR:  permission denied for table customers      -- * includes tax_id
SELECT count(*) FROM customers;                       -- works: needs SELECT on at least one column
UPDATE customers SET phone = '+39 02 0000 0000' WHERE id = 1;   -- works (id is readable)
UPDATE customers SET region = 'US' WHERE id = 1;
-- ERROR:  permission denied for table customers

Table grants override column revokes. A table-level privilege covers every column, and column-level REVOKE cannot carve a hole in it:

GRANT SELECT ON customers TO support;                 -- table-level: all columns
REVOKE SELECT (tax_id) ON customers FROM support;     -- has no effect on the table-level grant!

-- Correct approach: revoke the table-level grant, then grant columns
REVOKE SELECT ON customers FROM support;
GRANT SELECT (id, name, email, phone, region) ON customers TO support;

Grant style	New column added later	Effect on sensitive data
Table-level GRANT SELECT	Automatically readable	Fails open: a new salary column is exposed immediately
Column-level GRANT SELECT (cols)	Not readable until granted	Fails closed

Column privileges for INSERT. With INSERT (cols), a role may supply only those columns; the others receive their defaults. This is useful for columns that must be set by the database, such as created_by DEFAULT current_user or a status that only staff may change.

Inspecting column privileges

\dp customers
--  ... | Column privileges         |
--  ... | name:                    +|
--  ... |   support=r/postgres     +|
--  ... | email:                   +|
--  ... |   support=rw/postgres    +|
--  ...

SELECT grantee, column_name, privilege_type
FROM information_schema.column_privileges
WHERE table_name = 'customers' AND grantee = 'support'
ORDER BY column_name, privilege_type;

Column grants vs. views

	Column grants	Restricted view
SELECT * from clients	Fails	Works (the view lists allowed columns)
Derived or masked values (`'***'		right(tax_id, 4)`)
Row filtering	No (combine with RLS)	Yes, with security_barrier
Extra objects to maintain	None	One view per audience
ORMs and tools that introspect tables	May break on SELECT *	Work naturally

Many teams use column grants as the hard guarantee on the base table and views as the convenient interface on top.

Key takeaways

• GRANT SELECT (cols), UPDATE (cols), INSERT (cols), and REFERENCES (cols) restrict access to specific columns.
• SELECT * fails if any column is not readable; list columns explicitly.
• A table-level grant covers all columns and cannot be narrowed by a column-level revoke.
• Column grants fail closed for new columns, while table grants expose them automatically.
• Combine column grants with views for usability and with RLS for row filtering.

🧪 Practice

1. (Beginner) Give support read access to every column of customers except tax_id, and update access to email and phone. Verify each allowed and forbidden statement.
2. (Intermediate) Start from a table-level GRANT SELECT to support, try to hide tax_id with a column revoke, and show that it fails. Fix it, then add a new column credit_limit and demonstrate the difference between the two grant styles.
3. (Interview) "How would you prevent analysts from reading a salary column while still letting them aggregate salaries by department?" Hint: Column grants alone block both; consider a view or definer function that returns only aggregates, minimum group sizes to prevent singling out individuals, and security_barrier.

Security Barriers in Views

Views have long been used for security: grant access to a view that shows only some rows, not to the underlying table. But the planner's job is to make queries fast, and it freely reorders conditions, including pushing a user's WHERE condition below the view's own filter. If the user's condition is a function with side effects, such as raising a notice or an error that contains its argument, it can run on rows the view was supposed to hide. A security barrier view forbids that reordering for untrusted conditions.

Analogy. A view without a barrier is like a receptionist who filters your mail, but lets you hand the mail carrier a note: "read me every envelope's sender before you sort them". The barrier ensures sorting happens before anyone else's instructions are followed.

The leak

-- as owner: each rep sees only their customers through a view
CREATE VIEW my_customers AS
  SELECT * FROM customers WHERE manager = current_user;
GRANT SELECT ON my_customers TO alice, bob;          -- no access to the customers table itself

-- as bob, in a schema where he may create functions:
CREATE FUNCTION peek(text, text) RETURNS boolean
LANGUAGE plpgsql
COST 0.0000001                                       -- "very cheap": the planner runs it first
AS $$
BEGIN
  RAISE NOTICE 'saw: % %', $1, $2;                   -- side channel: prints its arguments
  RETURN true;
END;
$$;

SELECT name FROM my_customers WHERE peek(name, tax_id);
-- NOTICE:  saw: Acme Corp IT01234567890            <- alice's customer, hidden by the view!
-- NOTICE:  saw: Globex 94-1234567
-- NOTICE:  saw: Initech 95-7654321
--   name
-- ---------
--  Globex
--  Initech

The planner flattened the view and evaluated peek() before manager = current_user, because it was declared cheaper. The result rows are correct, but the notices leaked every hidden row.

The fix

CREATE OR REPLACE VIEW my_customers WITH (security_barrier = true) AS
  SELECT * FROM customers WHERE manager = current_user;

-- as bob
SELECT name FROM my_customers WHERE peek(name, tax_id);
-- NOTICE:  saw: Globex 94-1234567
-- NOTICE:  saw: Initech 95-7654321                  <- only rows that passed the view's filter

EXPLAIN (COSTS OFF) SELECT name FROM my_customers WHERE peek(name, tax_id);
--  Subquery Scan on my_customers
--    Filter: peek(my_customers.name, my_customers.tax_id)      -- user condition applied on top
--    ->  Seq Scan on customers
--          Filter: (manager = CURRENT_USER)                    -- view condition applied first

LEAKPROOF functions. A strict barrier would also stop harmless conditions such as WHERE id = 5 from using an index. PostgreSQL therefore still pushes down conditions built only from leakproof functions and operators: those guaranteed not to reveal information about their arguments except through their result (no errors that echo values, no side effects). Most comparison operators on built-in types are leakproof; only a superuser can mark a function LEAKPROOF.

SELECT count(*) FILTER (WHERE proleakproof) AS leakproof, count(*) AS total
FROM pg_proc;                                        -- most built-in comparison functions are leakproof

-- int4eq (the = operator for integers) is leakproof, so this can still use the primary key index:
EXPLAIN (COSTS OFF) SELECT * FROM my_customers WHERE id = 2;

Barrier, invoker, and check option: three different things

View option	Protects against
security_barrier = true	User conditions observing rows the view filters out
security_invoker = true (15+)	The view granting more than the caller's own privileges and RLS allow (Creating and Using Views)
WITH CHECK OPTION	Inserting or updating rows through the view that the view would not show

A view used as a security boundary that also accepts writes typically needs security_barrier and WITH CHECK OPTION. RLS policies always behave as security barriers, which is one reason to prefer RLS for row filtering.

Performance cost. Barrier views may not be flattened into the outer query, so non-leakproof conditions (for example lower(name) = 'x' or a custom function) cannot use indexes on the base table. Keep the view's own filter selective and indexable, and measure with EXPLAIN.

Key takeaways

• Without a barrier, the planner can evaluate user-supplied functions on rows a view is meant to hide, leaking them through notices or errors.
• WITH (security_barrier = true) applies the view's conditions first; only leakproof conditions are pushed below.
• Leakproof built-in operators keep index usage possible; only superusers can declare functions LEAKPROOF.
• security_barrier, security_invoker, and WITH CHECK OPTION solve different problems; security views often need more than one.
• RLS policies are barriers automatically.

🧪 Practice

1. (Beginner) Reproduce the peek() leak with a non-barrier view, then recreate the view with security_barrier and compare the notices and EXPLAIN output.
2. (Intermediate) Compare the plans of SELECT * FROM my_customers WHERE id = 2 and SELECT * FROM my_customers WHERE lower(email) = 'it@globex.example' on the barrier view, with an expression index on lower(email). Explain which conditions are pushed down and why.
3. (Interview) "Why isn't granting SELECT on a filtered view enough to protect the hidden rows, and what does PostgreSQL do about it?" Hint: Explain predicate pushdown, function cost, side channels through errors and notices, and how leakproof functions balance security with performance.

Data Masking Approaches

Sometimes a role needs the shape of sensitive data but not its real value: developers need realistic test data, support staff need to confirm "the card ending in 4242", analysts need to count customers per email domain. Hiding the column completely blocks these legitimate needs, while showing it exposes personal data. Data masking replaces sensitive values with redacted, partial, fake, or pseudonymous ones, either on the fly for specific roles (dynamic masking) or permanently in a copy of the data (static masking).

Analogy. Masking is like a bank statement that shows **** **** **** 4242: enough to recognize the card, not enough to use it.

Masking techniques

Technique	Example output for ops@acme.example / IT01234567890	Preserves	Reversible?
Redaction	NULL or '[redacted]'	Nothing	No
Partial masking	o***@acme.example, *********7890	Format, domain, last digits	No
Substitution (fake data)	jane.doe@example.org	Realism, format	No
Pseudonymization (keyed hash)	3f9a1c... (same input, same output)	Equality and joins	Only with the key or a lookup table
Generalization	Birth date 1987-04-12 becomes 1987	Coarse analysis	No
Shuffling	Real values swapped between rows	Distributions	No
Noise	Salary 52,300 becomes 51,870	Aggregates (approximately)	No

Dynamic masking with a view. The simplest dynamic approach is a view that applies masking functions, granted to roles that should see masked data, while the base table is not granted to them:

CREATE EXTENSION IF NOT EXISTS pgcrypto;              -- for hmac(), used below

-- The pseudonym key lives in a table that only the view owner can read
CREATE SCHEMA private;
REVOKE ALL ON SCHEMA private FROM PUBLIC;
CREATE TABLE private.mask_keys (name text PRIMARY KEY, key text NOT NULL);
INSERT INTO private.mask_keys VALUES ('email', encode(gen_random_bytes(32), 'hex'));

CREATE VIEW customers_masked WITH (security_barrier = true) AS
SELECT id,
       name,
       left(email, 1) || '***@' || split_part(email, '@', 2)          AS email,     -- partial
       CASE WHEN phone IS NULL THEN NULL
            ELSE '*** ' || right(phone, 4) END                          AS phone,     -- last 4 digits
       repeat('*', greatest(length(tax_id) - 4, 0)) || right(tax_id, 4) AS tax_id,   -- last 4 characters
       encode(hmac(email, (SELECT key FROM private.mask_keys WHERE name = 'email'), 'sha256'), 'hex')
                                                                        AS email_token, -- pseudonym for joins
       region
FROM customers;

REVOKE ALL ON customers FROM analysts;
GRANT SELECT ON customers_masked TO analysts;

-- as alice (an analyst)
SELECT id, email, phone, tax_id FROM customers_masked;
--  id |        email         |  phone   |    tax_id
-- ----+----------------------+----------+---------------
--   1 | o***@acme.example    | *** 5678 | *********7890
--   2 | i***@globex.example  | *** 0100 | ******4567
--   3 | a***@initech.example |          | ******4321

The view reads the key with its owner's privileges, so analysts get consistent pseudonyms without being able to read the key itself. A pseudonym computed with a plain, unkeyed hash can be reversed by hashing a list of known emails, which is why a secret key (HMAC) is used.

The PostgreSQL Anonymizer extension. For larger estates, the anon extension declares masking rules as security labels on columns and roles, and applies them dynamically for masked roles or statically to a whole database or dump:

-- Declarative rules (PostgreSQL Anonymizer; see its documentation for activation in your version)
SECURITY LABEL FOR anon ON COLUMN customers.email
  IS 'MASKED WITH FUNCTION anon.fake_email()';
SECURITY LABEL FOR anon ON COLUMN customers.tax_id
  IS 'MASKED WITH FUNCTION anon.partial(tax_id, 0, $$*********$$, 4)';
SECURITY LABEL FOR anon ON ROLE analyst_jane IS 'MASKED';   -- this role sees masked values

Static masking for non-production copies. Copying production into development or staging environments is one of the most common sources of data leaks. Mask the copy before it leaves the production security boundary:

 production --(pg_dump)--> masking step (in a secured environment) --(masked dump)--> staging / dev / laptops
                                 |
                                 +-- replace emails, names, phone numbers, tax IDs
                                 +-- keep keys and foreign keys consistent
                                 +-- verify: no real values remain (sample checks, regex scans)

Caveats

• Masked data can often be re-identified by combining fields (postcode, birth year, gender). Mask quasi-identifiers too, not only obvious PII.
• Pseudonymized data is still personal data under laws such as the GDPR; only truly anonymized data falls outside their scope.
• A masking view is a security boundary only if the masked role cannot read the base table, cannot run definer functions that expose it, and the view uses security_barrier when it filters rows.
• Masking is not encryption: superusers and roles with base-table access see real values (Column Encryption with pgcrypto).

Key takeaways

• Masking keeps data useful while hiding real values: redaction, partial masking, substitution, pseudonymization, generalization, shuffling, noise.
• Dynamic masking serves different roles different views of the same data; static masking produces safe copies for non-production use.
• Views with masking expressions are the built-in approach; the PostgreSQL Anonymizer extension adds declarative rules and static masking.
• Pseudonyms need a secret key (HMAC), and combinations of masked fields can still identify people.
• Protect the base table with privileges, or the masking can simply be bypassed.

🧪 Practice

1. (Beginner) Create the customers_masked view without the email_token column, grant it to analysts, and verify that alice sees masked values but cannot read customers directly.
2. (Intermediate) Using the email_token column, show that analysts can count distinct customers and join a second masked table on the pseudonym without learning email addresses. Verify that alice cannot read private.mask_keys, then show why an unkeyed sha256(email) would be reversible.
3. (Interview) "Developers want a copy of production for debugging. How do you provide realistic data without exposing customer information?" Hint: Discuss static masking inside the production boundary, referential consistency, quasi-identifiers, verification of the masked output, and access controls on the resulting copies.

<a id="104-data-protection-and-threats"></a>

10.4 Data Protection and Threats

Access control assumes that everyone follows the rules; this subchapter covers what happens when they do not. It deals with the most common attack against database-backed applications (SQL injection), protecting data on disk and in sensitive columns, recording who did what, handling credentials, meeting regulatory requirements, and pulling everything together into a hardening checklist.

SQL Injection and Parameterized Queries

Applications build SQL from user input all the time: a search box, a login form, a filter in a URL. If that input is pasted into the SQL text, a user who types SQL syntax instead of data changes the meaning of the query. This is SQL injection, consistently among the most exploited vulnerabilities in web applications. The fix is to never mix code and data: send the SQL text and the values separately, as a parameterized query.

Analogy. String concatenation is like a form letter where the customer writes their "name" directly into the template, and writes "John. Also, transfer 10,000 dollars to account 42". A parameterized query is a form with a fixed text and a separate box for the name: whatever is written in the box is only ever treated as a name.

The vulnerability

# VULNERABLE: user input becomes part of the SQL text
def find_customer(conn, email: str):
    sql = f"SELECT id, name FROM customers WHERE email = '{email}'"
    return conn.execute(sql).fetchall()

find_customer(conn, "ops@acme.example")          # intended use
find_customer(conn, "x' OR '1'='1")              # WHERE email = 'x' OR '1'='1'  -> every customer
find_customer(conn, "x' UNION SELECT id, tax_id FROM customers --")   # reads another column

 Template:   SELECT id, name FROM customers WHERE email = '{email}'
 Input:      x' OR '1'='1
 Result:     SELECT id, name FROM customers WHERE email = 'x' OR '1'='1'
                                                           ^ quote closed by the attacker
                                                              ^^^^^^^^^^^^ attacker's SQL

Depending on the role's privileges and the driver, injection can read any table the role can read, modify or delete data, call functions such as pg_read_file (if the role is privileged), or, when multiple statements are allowed in one call, run ; DROP TABLE ....

The fix: parameters

# SAFE: the SQL text is constant; the value travels separately
def find_customer(conn, email: str):
    return conn.execute(
        "SELECT id, name FROM customers WHERE email = %s",   # placeholder, not string formatting
        (email,),
    ).fetchall()

// SAFE: JDBC PreparedStatement
String sql = "SELECT id, name FROM customers WHERE email = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, email);                  // bound as a value, never parsed as SQL
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) { /* ... */ }
    }
}

With psycopg 3 and most modern drivers, parameters are sent through the extended query protocol: the server parses the SQL text first and receives the values afterwards in a separate message, so there is no point at which input can be interpreted as SQL (Prepared Statements and Plan Caching). The extended protocol also accepts only one statement per call, which blocks stacked ; DROP TABLE payloads.

What cannot be a parameter. Parameters replace values only. Table names, column names, sort directions, and SQL keywords must be handled differently:

Dynamic part	Safe technique
Value (WHERE email = ?)	Bind parameter
List of values (IN (...))	One array parameter: WHERE id = ANY(%s) with a Python list
LIKE pattern from user text	Bind parameter, and escape %, _, \ in the input if it should match literally
Identifier (table, column)	Allowlist, then quote: psycopg.sql.Identifier, format('%I', ...), quote_ident()
Keyword (ASC/DESC, operators)	Allowlist mapping from user choice to fixed SQL text
Inside PL/pgSQL dynamic SQL	EXECUTE format('... %I ...', name) USING value (Dynamic SQL with EXECUTE)

from psycopg import sql

SORTABLE = {"name": "name", "region": "region", "newest": "id"}   # user choice -> column
DIRECTIONS = {"asc": sql.SQL("ASC"), "desc": sql.SQL("DESC")}

def list_customers(conn, sort: str, direction: str, region: str):
    column = SORTABLE.get(sort, "id")                 # unknown input falls back to a safe default
    query = sql.SQL("SELECT id, name FROM customers WHERE region = %s ORDER BY {} {}").format(
        sql.Identifier(column),                       # quoted identifier: "name"
        DIRECTIONS.get(direction, sql.SQL("ASC")),    # fixed keyword, never user text
    )
    return conn.execute(query, (region,)).fetchall()  # the value is still a parameter

Subtler variants

• Second-order injection. Data that was stored safely (with parameters) is later read and concatenated into another query, for example a saved report filter. Every query must be parameterized, including those built from "trusted" database content.
• ORM escape hatches. ORMs parameterize their own queries, but raw-SQL helpers (.raw(), text(), extra()) accept concatenated strings just as willingly.
• Database-side code. A SECURITY DEFINER function that builds dynamic SQL from its arguments with || gives an injection that runs with the owner's privileges.

Defense in depth. Parameterization is the fix; the rest limits damage when a mistake slips through:

Layer	Effect on a successful injection
Least-privilege application role	Cannot DROP, ALTER, TRUNCATE, or read unrelated schemas
Row-level security	Cannot cross tenant boundaries
statement_timeout per role	Limits expensive exfiltration or pg_sleep-based probing
Generic error messages to users	Hides schema details that help attackers craft payloads
pgaudit and log monitoring	Unusual statements (UNION SELECT, catalog queries) become visible

Key takeaways

• SQL injection happens when input is spliced into SQL text; it can read, modify, or destroy anything the role can.
• Always send values as bind parameters; the SQL text should be a constant.
• Identifiers and keywords cannot be parameters: use allowlists plus identifier quoting (sql.Identifier, format('%I')).
• Watch for second-order injection, ORM raw-SQL helpers, and dynamic SQL in database functions.
• Least privilege, RLS, timeouts, and auditing limit the damage of the injection you missed.

🧪 Practice

1. (Beginner) Run the vulnerable find_customer against the sample customers table with the OR '1'='1' and UNION payloads, then rewrite it with a parameter and show that both payloads return no rows.
2. (Intermediate) Implement a search endpoint with a user-selected sort column, a direction, an optional list of regions, and a "name contains" filter, without any string concatenation of user input. Include a test that passes name"; DROP TABLE customers; -- as the sort column.
3. (Interview) "We use an ORM everywhere, so we are safe from SQL injection, right?" Hint: Think about raw-query helpers, dynamic ORDER BY and column selection, stored procedures with dynamic SQL, second-order injection, and how you would find these in a large codebase.

Encryption at Rest

TLS protects data while it travels; encryption at rest protects it while it sits on storage. The threats are physical and operational: a stolen laptop with a development database, a disk replaced under warranty, a cloud snapshot accidentally shared, a backup tape that goes missing. In all of these, the attacker has the bytes but not a running, authenticated database. If those bytes are encrypted and the key is elsewhere, they are useless.

Analogy. Encryption at rest is a safe in the building. It protects against burglars carrying the safe away, but not against a dishonest employee who already knows the combination and opens it during business hours.

What it does and does not protect

Threat	Protected by encryption at rest?
Stolen or decommissioned disk, lost backup media	Yes
Copied cloud snapshot or misconfigured storage bucket	Yes, if the key is not accessible along with it
Attacker with SQL access (stolen credentials, injection)	No: the database decrypts transparently for them
Attacker with root on the running database host	No: keys and decrypted pages are in memory
Malicious superuser or DBA	No (consider application-level encryption)

Layers where encryption can happen

 Application         encrypts before sending; database stores ciphertext   (strongest separation, hardest to query)
 Column (pgcrypto)   database encrypts selected columns with a supplied key
 Database (TDE)      database encrypts data files and WAL transparently    (not in community PostgreSQL)
 File system         ZFS native encryption, fscrypt
 Block device        LUKS/dm-crypt, cloud volume encryption (EBS, Persistent Disk, Azure Disk)
 Hardware            self-encrypting drives                                 (most transparent, weakest separation)

Options for PostgreSQL. Community PostgreSQL (through version 18) has no built-in transparent data encryption (TDE). The common choices are:

Option	Covers	Notes
Cloud volume encryption with a KMS key	Data directory, WAL, temp files on that volume	Default on most managed services; one checkbox or policy
LUKS/dm-crypt on self-managed servers	Everything on the device	Key must be supplied at boot (network-bound keys, TPM, or manual unlock)
Vendor or extension TDE (for example Percona pg_tde, EDB, Cybertec)	Data files and WAL, per table or cluster	Requires a specific distribution or patched server; check backup and replication support
Column encryption (Column Encryption with pgcrypto) or application encryption	Selected sensitive fields everywhere they go, including backups and replicas	Protects against more threats, but affects querying and indexing

# Self-managed example: encrypted block device for the data directory (LUKS)
cryptsetup luksFormat /dev/nvme1n1                     # set up encryption (destroys existing data)
cryptsetup open /dev/nvme1n1 pgdata                    # unlock: key from a passphrase, keyfile, or TPM
mkfs.xfs /dev/mapper/pgdata
mount /dev/mapper/pgdata /var/lib/postgresql

Do not forget the copies. Data does not live only in the data directory:

Location	How to protect it
WAL archive and base backups	Backup tool encryption (pgBackRest repo1-cipher-type=aes-256-cbc, Barman with encrypted storage) and encrypted buckets (11.3)
pg_dump files	Encrypt the output (`pg_dump ...
Replicas	Same encryption as the primary; replication traffic over TLS
Server logs	May contain query text and parameters; store on encrypted volumes, limit retention
Temporary files, swap	Same encrypted volume (temp_tablespaces on encrypted storage), encrypted or disabled swap
Developer copies	Masked before leaving production (Data Masking Approaches)

Key management. Encryption moves the problem from protecting data to protecting keys. Keep keys in a key management service (AWS KMS, Google Cloud KMS, Azure Key Vault, HashiCorp Vault) or a hardware security module, separate from the data and its backups; restrict and audit who can use them; and plan rotation. Envelope encryption (data keys encrypted by a master key in the KMS) makes rotation cheap because only the small data keys are re-encrypted.

Key takeaways

• Encryption at rest protects against stolen media, lost backups, and leaked snapshots, not against SQL-level or root-level attackers.
• Community PostgreSQL has no built-in TDE; use encrypted volumes, vendor TDE, or column and application encryption.
• Encrypt every copy: WAL archives, backups, dumps, replicas, logs, temp files, and developer copies.
• Keep keys in a KMS or HSM, separate from the data, with access control, auditing, and rotation.

🧪 Practice

1. (Beginner) List every place where data from your PostgreSQL cluster ends up (data directory, WAL archive, backups, logs, replicas, exports), and note for each whether it is encrypted today.
2. (Intermediate) On a test VM, put the data directory on a LUKS volume, start PostgreSQL, and verify that strings on the raw block device finds no table content, while it does on an unencrypted control setup.
3. (Interview) "Our cloud provider encrypts all disks by default. Is our database data therefore protected?" Hint: Map threats to layers: who holds the key, what an attacker with SQL access or a stolen backup can do, snapshot sharing, and when column or application-level encryption is needed.

Column Encryption with pgcrypto

Volume encryption protects files, but anyone who can query the table, including a DBA or an attacker with the application's credentials, sees plaintext. For the most sensitive fields, such as tax identifiers, health data, or bank details, you may want the value to stay encrypted inside the database, so that reading the column without the key yields only ciphertext, in backups, replicas, and query results alike. The pgcrypto extension provides encryption, hashing, and random-number functions in SQL.

Analogy. Column encryption is like putting individual documents in sealed envelopes inside the filing cabinet. Someone with the cabinet key can see that the envelopes exist, but needs a second key to read each one.

Main pgcrypto functions

Function	Purpose
pgp_sym_encrypt(data, key [, options]) / pgp_sym_decrypt(bytea, key)	Symmetric encryption (OpenPGP format, random salt and IV, integrity check)
pgp_pub_encrypt(data, pubkey) / pgp_pub_decrypt(bytea, seckey [, psw])	Public-key encryption: anyone can encrypt, only the private-key holder decrypts
crypt(password, gen_salt('bf', 12))	Password hashing (bcrypt)
digest(data, 'sha256'), hmac(data, key, 'sha256')	Hashes and keyed hashes
gen_random_bytes(n)	Cryptographically secure random bytes

Encrypting a column

CREATE EXTENSION IF NOT EXISTS pgcrypto;

ALTER TABLE customers ADD COLUMN tax_id_enc bytea;           -- ciphertext column

-- Encrypt existing values; the key is passed as a parameter by the client (shown here as :'key' in psql)
UPDATE customers
SET tax_id_enc = pgp_sym_encrypt(tax_id, :'key', 'cipher-algo=aes256'),
    tax_id     = NULL;                                         -- remove the plaintext

SELECT id, left(encode(tax_id_enc, 'hex'), 24) || '...' AS stored FROM customers;
--  id |           stored
-- ----+-----------------------------
--   1 | c30d0409030208c9f1d2a0b3...                          -- different every time, even for equal values

SELECT id, pgp_sym_decrypt(tax_id_enc, :'key') AS tax_id FROM customers WHERE id = 1;
--  id |    tax_id
-- ----+---------------
--   1 | IT01234567890

(In psql, run \set key 'long-random-secret' first. In an application, send the key as a bind parameter fetched from a secret manager.)

Searching encrypted data. Because encryption is randomized, equal plaintexts produce different ciphertexts, so WHERE tax_id_enc = ... and indexes on the ciphertext are useless. For equality lookups, store a blind index: a keyed hash computed with a separate key:

ALTER TABLE customers ADD COLUMN tax_id_bidx bytea;
UPDATE customers
SET tax_id_bidx = hmac(pgp_sym_decrypt(tax_id_enc, :'key'), :'index_key', 'sha256');
CREATE INDEX ON customers (tax_id_bidx);

-- Look up by exact value without decrypting every row
SELECT id FROM customers
WHERE tax_id_bidx = hmac('94-1234567', :'index_key', 'sha256');

Range queries, LIKE, and sorting on encrypted values are not possible without decrypting; that is the price of this protection.

Where the key lives is the whole story. With pgcrypto, the key reaches the database server in the SQL statement or its parameters. It can then appear in places you do not expect:

Exposure	Mitigation
Server log (log_statement, slow-query log with parameters, error messages)	Pass the key as a bind parameter; set log_parameter_max_length = 0 and log_parameter_max_length_on_error = 0 for the roles involved
pg_stat_activity / pg_stat_statements	Bind parameters are not shown; literals are
Superuser on the running server	Cannot be prevented; use application-side encryption if DBAs must not see plaintext
Keys stored in a table next to the data	Never: a stolen backup then contains both

Server-side vs. application-side encryption

	pgcrypto (server-side)	Application-side
Key seen by the database server	Yes, during each call	Never
Protects against a malicious DBA or compromised DB host	No	Yes
Usable from SQL tools and reports	Yes, with the key	Only through the application
Implementation effort	Low	Higher (libraries, key management, migrations)

Password hashing. For storing user passwords of your application (not database roles), never use reversible encryption. crypt() with bcrypt is acceptable inside SQL; dedicated application libraries (Argon2id, bcrypt) are more common:

CREATE TABLE app_users (email text PRIMARY KEY, pw_hash text NOT NULL);
INSERT INTO app_users VALUES ('ana@example.com', crypt('correct horse battery', gen_salt('bf', 12)));

-- Verify: re-hash the candidate with the stored salt and compare
SELECT pw_hash = crypt('correct horse battery', pw_hash) AS ok
FROM app_users WHERE email = 'ana@example.com';           -- true

Key rotation. Rotating the key means decrypting with the old key and re-encrypting with the new one, row by row (in batches, as in Stored Procedures and Transaction Control). Envelope encryption simplifies this: each row or tenant has a data key, and only the data keys are re-encrypted when the master key changes. Destroying a per-customer key also makes that customer's data unreadable in every backup, a technique called crypto-shredding (Compliance Considerations).

Key takeaways

• pgcrypto encrypts individual columns so the data stays ciphertext in queries, backups, and replicas for anyone without the key.
• Use pgp_sym_encrypt (or pgp_pub_encrypt) rather than raw cipher functions; they handle IVs and integrity.
• Encrypted columns cannot be searched or indexed directly; add a keyed blind index for equality lookups.
• Keys sent to the server can leak into logs and are visible to superusers; pass them as parameters, or encrypt in the application.
• Hash passwords with crypt()/bcrypt or Argon2, never with reversible encryption; plan key rotation from the start.

🧪 Practice

1. (Beginner) Encrypt the tax_id column of customers with pgp_sym_encrypt, verify that decryption with the right key works, and observe the error with a wrong key.
2. (Intermediate) Add a blind index and measure lookup performance by exact tax_id on 1 million generated rows, with and without the index. Then enable log_min_duration_statement = 0 and check whether your key appears in the log when passed as a literal versus as a bind parameter from Python.
3. (Interview) "Should we encrypt sensitive columns with pgcrypto or in the application?" Hint: Identify the threat each defends against (stolen backups, curious DBAs, compromised DB host), how keys reach each component, and the impact on querying, indexing, and reporting.

Auditing with pgaudit

When something goes wrong, such as a deleted table, leaked customer records, or a suspicious permission change, the first question is "who did what, and when?". Regulations also require that you be able to answer it. PostgreSQL's built-in logging (log_statement) records statement text, but is coarse, hard to parse, and misses what a statement touched indirectly. Audit triggers (Audit Logging with Triggers) record data changes but not reads or DDL. The pgaudit extension produces detailed, structured audit records of sessions and object access through the standard server log.

Analogy. Normal logging is a security camera pointed at the front door; pgaudit is a sign-out sheet attached to every sensitive cabinet, filled in automatically with who opened it, which folder they took, and when.

Installation

# postgresql.conf (restart required)
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'ddl, role, write'          # session audit classes, see below
pgaudit.log_relation = on                 # one entry per table touched, not just per statement
pgaudit.log_parameter = off               # parameters may contain sensitive data; enable consciously
log_line_prefix = '%m [%p] %q%u@%d %r %a '  # time, pid, user@db, client address, application
log_connections = on
log_disconnections = on

CREATE EXTENSION pgaudit;                  -- in each database where you want object auditing

Session auditing: by statement class

Class	Statements logged
READ	SELECT, COPY ... TO
WRITE	INSERT, UPDATE, DELETE, TRUNCATE, COPY ... FROM
FUNCTION	Function calls and DO blocks
ROLE	GRANT, REVOKE, CREATE/ALTER/DROP ROLE
DDL	All DDL not in ROLE
MISC	DISCARD, FETCH, CHECKPOINT, VACUUM, SET
ALL	Everything

Classes can be set per role or database, so you can audit administrators more heavily than the application:

ALTER ROLE migrator SET pgaudit.log = 'all';              -- everything a privileged role does
ALTER DATABASE shop SET pgaudit.log = 'ddl, role';        -- baseline for everyone in shop

Object auditing: by table and column. Logging every SELECT of a busy application would drown the log. Object auditing logs only statements that touch chosen objects, chosen by granting privileges to an "auditor" role that never logs in:

CREATE ROLE auditor NOLOGIN;
ALTER SYSTEM SET pgaudit.role = 'auditor';
SELECT pg_reload_conf();

GRANT SELECT (tax_id) ON customers TO auditor;            -- log every read of the tax_id column
GRANT UPDATE, DELETE ON accounts TO auditor;              -- log every change to accounts

What an entry looks like

2026-10-06 09:14:02.113 UTC [48211] kim@shop 10.0.1.25(52144) psql LOG:  AUDIT: OBJECT,3,1,READ,SELECT,TABLE,public.customers,"SELECT name, tax_id FROM customers WHERE region = 'US'",<not logged>

Field	Value in the example	Meaning
Audit type	OBJECT	SESSION (class-based) or OBJECT (via pgaudit.role)
Statement ID, substatement ID	3, 1	Statement number in the session; position within it (functions, CTEs)
Class	READ	Statement class from the table above
Command	SELECT	The SQL command
Object type, object name	TABLE, public.customers	What was accessed
Statement	"SELECT name, tax_id ..."	Full statement text
Parameter	<not logged>	Bind parameters, if pgaudit.log_parameter = on

The prefix before AUDIT: comes from log_line_prefix: timestamp, process ID, user@database, client address, and application name.

Choosing an audit mechanism

	log_statement	Audit triggers	pgaudit	Logical decoding / CDC
Reads	all only	No	Yes, selectively	No
DDL and role changes	Yes	Event triggers only	Yes, classified	DDL not decoded
Old and new row values	No	Yes	No (statement level)	Yes
Structured output	No	Yes (tables)	Semi-structured CSV-like log lines	Yes
Overhead	Low to high	Per-row trigger cost	Depends on classes	Separate process

pgaudit and audit triggers are often used together: pgaudit for who ran what, triggers or CDC for what the data looked like before and after.

Keeping audit logs trustworthy. A superuser can turn pgaudit off or delete local log files, so audit logs must leave the server quickly: ship them with syslog, a log agent, or the cloud provider's log service to a separate system (a SIEM) where database administrators cannot modify them. Alert on high-risk events such as ROLE class entries, ALTER SYSTEM, or pgaudit settings changes.

Key takeaways

• pgaudit adds structured audit records (AUDIT: SESSION|OBJECT,...) to the server log, covering reads, writes, DDL, and role changes.
• Session auditing selects statement classes; set heavier auditing per role or database with ALTER ROLE/DATABASE ... SET.
• Object auditing (pgaudit.role plus grants to an auditor role) logs only access to chosen tables or columns.
• Include user, database, and client in log_line_prefix, and be deliberate about logging parameters that may contain sensitive data.
• Ship audit logs off the server to a system DBAs cannot alter, and alert on high-risk events.

🧪 Practice

1. (Beginner) Install pgaudit, set pgaudit.log = 'ddl, role', create a table and grant a privilege, and find both entries in the server log.
2. (Intermediate) Configure object auditing so that every read of customers.tax_id is logged, but reads of other columns are not. Verify with three queries, including one that reads tax_id through a view.
3. (Interview) "An auditor asks you to prove who accessed patient records last quarter. What would you need to have set up in advance?" Hint: Consider object-level auditing on the right tables, identifying the end user behind a shared application role (application_name, SET context), log retention, tamper resistance, and clock synchronization.

Secrets Management

Every component that talks to the database needs a credential: application servers, migration tools, backup jobs, monitoring agents, replicas. Those secrets have a habit of spreading to source code, configuration files, CI logs, chat messages, and laptops. Each copy is a place it can leak from and a place that must be updated during rotation. Secrets management is the discipline of storing credentials in one controlled place, delivering them only to what needs them, and changing them without pain.

Analogy. Secrets management is a key cabinet with a sign-out log, instead of spare keys taped under every desk. Each key is handed to the person who needs it, its use is recorded, and the locks can be changed without hunting down every copy.

Anti-patterns and their fixes

Anti-pattern	Why it is a problem	Better
Password in source code or committed config	Visible to everyone with repository access, forever in git history	Secret manager, injected at deploy time
Password in a Dockerfile or image layer	Anyone who pulls the image can extract it	Runtime injection (mounted file or secret store)
Password in PGPASSWORD or command-line arguments	Visible to other processes (ps, /proc), and in shell history	.pgpass file or service file with passfile
One shared credential for all services	No accountability, rotation breaks everything	One role per service
Secret pasted in chat or tickets	Copies outside any control	Share a link to the secret manager entry
ALTER ROLE ... PASSWORD 'x' in logged sessions	Clear text in server logs	\password or pre-hashed SCRAM verifiers

libpq's built-in mechanisms

# ~/.pgpass  (must be mode 0600, or libpq ignores it)
# hostname:port:database:username:password
db.internal:5432:shop:report_ro:Xk3...long-random...

chmod 600 ~/.pgpass
psql "host=db.internal dbname=shop user=report_ro"        # password read from .pgpass

# A service file keeps connection details and points to a password file
# ~/.pg_service.conf
# [shop-report]
# host=db.internal
# dbname=shop
# user=report_ro
# passfile=/run/secrets/report_ro.pgpass
# sslmode=verify-full
psql "service=shop-report"

Secret managers and dynamic credentials. A secret manager (HashiCorp Vault, AWS Secrets Manager, Google Secret Manager, Azure Key Vault) stores secrets encrypted, controls access by identity, logs every read, and can rotate secrets automatically. The strongest pattern is dynamic credentials: the secret manager creates a short-lived database role on demand and drops it when the lease expires, so no long-lived password exists to leak.

# HashiCorp Vault database secrets engine (sketch)
vault write database/config/shop \
    plugin_name=postgresql-database-plugin \
    connection_url="postgresql://{{username}}:{{password}}@db.internal:5432/shop?sslmode=verify-full" \
    username="vault_admin" password="..." \
    allowed_roles="app"

vault write database/roles/app \
    db_name=shop \
    creation_statements="CREATE ROLE \"{{name}}\" LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}' IN ROLE shop_write;" \
    default_ttl=1h max_ttl=24h                               # credentials expire automatically

vault read database/creds/app                                # returns a fresh user name and password

The vault_admin role needs CREATEROLE and ADMIN OPTION on shop_write (PostgreSQL 16+), but no data access of its own. Objects should be owned by shop_owner, never by the temporary roles, or they cannot be dropped cleanly when leases expire.

Passwordless options. Managed services can replace passwords with identity tokens: AWS RDS IAM authentication issues 15-minute tokens, Google Cloud SQL and Azure Database for PostgreSQL integrate with their identity platforms, and PostgreSQL 18 adds the oauth method for identity providers. Certificate authentication with short-lived certificates (Certificate and LDAP Authentication) is another passwordless option.

Reading secrets in an application

from pathlib import Path
import psycopg

# The platform (Kubernetes, systemd credentials, a Vault agent) mounts the secret as a file
password = Path("/run/secrets/db_password").read_text().strip()

conn = psycopg.connect(
    host="db.internal", dbname="shop", user="app_blue",
    password=password,
    sslmode="verify-full", sslrootcert="/etc/ssl/internal-ca.crt",
)
# Never log the connection string or the password; log the user name and host only

Kubernetes Secrets are only base64-encoded by default; enable encryption at rest for etcd, restrict RBAC on secrets, or use an external secrets operator that syncs from a real secret manager.

Other secrets to manage. Database passwords are not the only secrets: TLS private keys (server.key), pgcrypto and backup encryption keys, replication credentials in primary_conninfo, PgBouncer's credential files (prefer auth_query so passwords are not duplicated in userlist.txt), and cloud credentials of backup tools.

Key takeaways

• Keep secrets in one controlled store, deliver them at runtime, and give each service its own credential.
• Avoid passwords in code, images, command lines, environment variables visible to other processes, and logs.
• Use .pgpass (mode 0600) and service files for libpq clients; mounted secret files for applications.
• Prefer dynamic or token-based credentials (Vault, cloud IAM, OAuth, short-lived certificates) over long-lived passwords.
• Inventory all secrets around the database, including TLS keys, encryption keys, replication, and pooler credentials.

🧪 Practice

1. (Beginner) Move a password from a shell script that uses PGPASSWORD into a .pgpass file and a service file, and verify the script still works. What happens if the file mode is 0644?
2. (Intermediate) Search a repository (for example with git log -p | grep -i -E "password|postgres://" or a tool such as gitleaks) for leaked database credentials. For one finding, describe the full remediation, including rotation and history cleanup.
3. (Interview) "Design credential handling for 30 microservices that each use the same PostgreSQL cluster." Hint: Consider per-service roles, a secret manager with dynamic or rotated credentials, how services authenticate to the secret manager, poolers, auditing of secret access, and emergency (break-glass) access.

Compliance Considerations

Organizations that store personal, health, or payment data are subject to laws and standards that dictate how that data is protected: the GDPR in the European Union, HIPAA for US health data, PCI DSS for payment cards, and frameworks such as SOC 2 and ISO 27001 that customers demand as evidence of good practice. These documents rarely mention PostgreSQL, but many of their requirements land on the database: access control, encryption, audit trails, retention, and the ability to delete a person's data. Knowing how to map requirements to database features, and how to produce evidence, is part of a database engineer's job. (This is technical guidance, not legal advice; compliance decisions involve your legal and security teams.)

Analogy. A compliance framework is like a building code: it does not tell you which bricks to buy, but it requires fire exits, alarms, and inspections, and an inspector will ask you to show them.

The main frameworks

Framework	Scope	Database-relevant themes
GDPR (EU)	Personal data of people in the EU	Data minimization, purpose limitation, right to erasure and access, security of processing, breach notification within 72 hours, data residency for transfers
HIPAA (US)	Protected health information	Access control, unique user identification, audit controls, integrity, encryption (addressable), transmission security
PCI DSS v4.0	Payment card data	Do not store sensitive authentication data (CVV), render card numbers unreadable, MFA for administrative access, audit logs kept 12 months, quarterly access reviews
SOC 2 / ISO 27001	Organizational security controls	Access management, change management, logging and monitoring, backups, incident response, with evidence

Mapping requirements to PostgreSQL features

Requirement	PostgreSQL controls	Evidence an auditor may ask for
Unique, individual accounts	Personal login roles, central identity (LDAP, certificates, OAuth)	Role list, pg_hba.conf
Least privilege and periodic access review	Group roles, column grants, RLS, predefined roles	Exported grants and memberships, review sign-offs
Strong authentication	SCRAM, certificates, clientcert=verify-full for MFA-like admin access	pg_hba_file_rules, configuration
Encryption in transit	hostssl rules, verify-full clients	pg_stat_ssl, configuration
Encryption at rest	Encrypted volumes, TDE, pgcrypto	Cloud/KMS configuration, key policies
Audit trail	pgaudit, connection logging, shipped to SIEM	Sample logs, retention settings
Change management	Migrations in version control, owner role, DDL auditing	Migration history, pgaudit DDL entries
Backup and recovery	Encrypted backups, restore tests (11.3)	Restore test reports
Data minimization and retention	Partitioning by time, scheduled deletion	Retention jobs and their logs

Retention: dropping old data cheaply. Retention policies are easiest to implement when tables are partitioned by time, so expired data is removed by dropping a partition instead of deleting millions of rows:

-- transfers_log: a table partitioned by month (illustrative). Keep 24 months; run monthly, e.g. with pg_cron
ALTER TABLE transfers_log DETACH PARTITION transfers_log_2024_09;
DROP TABLE transfers_log_2024_09;           -- instant, no bloat; backups expire on their own schedule

Right to erasure. Deleting one person's data touches many tables, and copies remain in backups, replicas, logs, and analytics systems. A deletion routine should be explicit, tested, and audited:

CREATE PROCEDURE erase_customer(p_id int)
LANGUAGE plpgsql
AS $$
BEGIN
  DELETE FROM transfers WHERE from_id IN (SELECT id FROM accounts WHERE owner_customer = p_id)
                           OR to_id   IN (SELECT id FROM accounts WHERE owner_customer = p_id);
  DELETE FROM accounts  WHERE owner_customer = p_id;
  -- Where records must be kept for legal reasons (e.g. invoices), anonymize instead of deleting
  UPDATE customers
  SET name = 'erased', email = 'erased-' || id || '@invalid', phone = NULL, tax_id = NULL
  WHERE id = p_id;
  INSERT INTO erasure_log (customer_id, erased_at) VALUES (p_id, now());   -- proof without personal data
END;
$$;

(The owner_customer column and erasure_log table are illustrative; adapt the routine to your schema.)

For backups, the usual approaches are to let backups expire within a documented retention period, and to re-apply erasures after any restore, or to use crypto-shredding: encrypt each person's sensitive data with a per-person key, and delete the key to make every copy unreadable at once.

Data residency. Some regulations restrict where data may be stored or processed. Replicas, backups, log shipping destinations, and managed-service regions all count; check them, not only the primary.

Making compliance continuous. Audits go smoothly when evidence is produced automatically: scheduled exports of roles and grants, configuration snapshots under version control, pgaudit logs retained in a SIEM, and restore tests recorded as they happen. The Security Hardening Checklist gives a baseline to measure against.

Key takeaways

• GDPR, HIPAA, PCI DSS, and SOC 2/ISO 27001 translate into concrete database controls: access control, authentication, encryption, auditing, retention, and erasure.
• Map each requirement to a PostgreSQL feature and to the evidence that proves it is in place.
• Partition by time to make retention cheap; write explicit, audited erasure routines, and plan for copies in backups.
• Crypto-shredding makes data in all copies unreadable by destroying per-subject keys.
• Check data residency for replicas, backups, and logs, and automate evidence collection.

🧪 Practice

1. (Beginner) For a table of customer data you know, classify each column (public, internal, personal, sensitive) and propose a control for each class.
2. (Intermediate) Write and test an erase_customer procedure for the sample schema that anonymizes rows that must be kept and deletes the rest, and a query that proves no personal data for that customer remains in any table.
3. (Interview) "A customer exercises their right to erasure. Their data is in the primary, two replicas, nightly backups kept for 35 days, and an analytics warehouse. Walk me through your response." Hint: Distinguish what replicates automatically from what does not, backup retention versus restore procedures, crypto-shredding, downstream systems, and how you document completion.

Security Hardening Checklist

Each topic in this chapter addresses one layer of defense. Real incidents usually exploit the gap between layers: a forgotten trust rule, an application role that owns its tables, a backup bucket without encryption, a minor release that was never installed. A hardening checklist brings the layers together so they can be reviewed systematically, at setup time and again periodically, because configurations drift.

Analogy. A pilot's pre-flight checklist exists not because pilots do not know their aircraft, but because skipping one item out of fifty is easy and fatal. A hardening checklist does the same for a database server.

The checklist

Area	Check	Reference
Network	listen_addresses limited to required interfaces; port not exposed to the internet; firewall or security groups restrict sources	Chapter 1
	pg_hba.conf: no trust for network rules; specific rules first; final reject	pg_hba.conf Rules and Order
Encryption in transit	ssl = on, hostssl rules, hostnossl ... reject, ssl_min_protocol_version at least TLS 1.2; clients use verify-full	Connection Encryption with TLS
Authentication	scram-sha-256 everywhere; no MD5 verifiers left; superuser only via local peer	Password Authentication and SCRAM
	Expiry for temporary accounts; one role per person and per service	Password Policies and Rotation
Roles	Few superusers; no application role with SUPERUSER, CREATEROLE, BYPASSRLS, or ownership of objects	Principle of Least Privilege
	Server-file and server-program predefined roles granted to nobody, or audited like superuser	Predefined Roles
Privileges	CONNECT/TEMPORARY revoked from PUBLIC on databases; CREATE revoked on public schema; EXECUTE revoked from PUBLIC on sensitive functions	Schema-Level Access Control
	Default privileges defined for the owner role	Default Privileges
	SECURITY DEFINER functions pin search_path and are not owned by superusers	Security Definer vs. Invoker
Data access	RLS on multi-tenant tables, forced, with WITH CHECK; column grants or masking for sensitive columns; security-barrier views	10.3
Data protection	Encrypted volumes, backups, and WAL archive; sensitive columns encrypted where required	Encryption at Rest
Extensions and languages	Only needed extensions installed; untrusted languages restricted to reviewed code	Chapter 9
Logging and auditing	log_connections, log_disconnections, informative log_line_prefix, pgaudit for sensitive objects, logs shipped off-host	Auditing with pgaudit
Resource limits	statement_timeout and idle_in_transaction_session_timeout per role; connection limits	Principle of Least Privilege
Operating system	PostgreSQL runs as an unprivileged OS user; data directory mode 0700; key files 0600; minimal other software on the host	Chapter 1
Patching	Latest minor release of a supported major version; subscribe to security announcements	11.4
Secrets	No credentials in code, images, or logs; rotation procedure tested	Secrets Management
Recovery	Encrypted backups restored and verified regularly	11.3

Automating part of the review. Many items can be checked with SQL and run on a schedule, so drift is detected without waiting for an audit:

-- 1. Risky settings
SELECT name, setting
FROM pg_settings
WHERE (name = 'ssl'                     AND setting <> 'on')
   OR (name = 'password_encryption'     AND setting <> 'scram-sha-256')
   OR (name = 'listen_addresses'        AND setting = '*')
   OR (name IN ('log_connections', 'log_disconnections') AND setting IN ('off', ''));

-- 2. Weak pg_hba.conf rules
SELECT line_number, type, database, user_name, address, auth_method
FROM pg_hba_file_rules
WHERE auth_method IN ('trust', 'password', 'md5', 'ident')
   OR (type = 'host' AND address = '0.0.0.0' AND auth_method <> 'reject');

-- 3. Roles with powerful attributes, and MD5 verifiers (requires superuser for pg_authid)
SELECT rolname,
       rolsuper, rolcreaterole, rolbypassrls, rolreplication,
       rolpassword LIKE 'md5%' AS md5_password
FROM pg_authid
WHERE rolname !~ '^pg_'
  AND (rolsuper OR rolcreaterole OR rolbypassrls OR rolreplication OR rolpassword LIKE 'md5%');

-- 4. Definer functions without a pinned search_path
SELECT p.oid::regprocedure, pg_get_userbyid(p.proowner) AS owner
FROM pg_proc AS p
WHERE p.prosecdef
  AND p.pronamespace <> 'pg_catalog'::regnamespace
  AND NOT coalesce(array_to_string(p.proconfig, ',') LIKE '%search_path=%', false);

-- 5. PUBLIC can create in the public schema (old clusters)
SELECT has_schema_privilege('public', 'public', 'CREATE') AS public_can_create;

-- 6. Tables with RLS enabled but not forced (review whether owners should be filtered)
SELECT oid::regclass FROM pg_class WHERE relrowsecurity AND NOT relforcerowsecurity;

Each query should return no rows (or false) on a hardened server; any result is a finding to fix or to document as an accepted exception.

External references. The CIS PostgreSQL Benchmark provides a detailed, versioned checklist with audit and remediation steps, and is a common baseline for compliance programs. Managed services handle some items (patching, disk encryption, OS hardening) for you, but not your roles, grants, pg_hba equivalents, or application credentials.

Key takeaways

• Security failures usually come from a single gap between layers; a checklist reviews all layers systematically.
• Cover network, TLS, authentication, roles, privileges, row and column controls, encryption, extensions, logging, resource limits, OS, patching, secrets, and recovery.
• Automate checks with catalog queries (pg_settings, pg_hba_file_rules, pg_authid, pg_proc, pg_class) and run them on a schedule to catch drift.
• Treat every finding as either a fix or a documented exception.
• Use the CIS PostgreSQL Benchmark as an external baseline; managed services cover only part of the list.

🧪 Practice

1. (Beginner) Run the six audit queries on your development cluster and explain each finding. Which ones would you accept on a laptop but not in production?
2. (Intermediate) Turn the audit queries into a script that exits with a non-zero status if any check fails, and run it in CI against a freshly provisioned database built from your infrastructure code.
3. (Interview) "You inherit a production PostgreSQL server with no documentation. What are the first ten things you check for security, and in what order?" Hint: Prioritize by exposure and impact: network reachability and pg_hba.conf, superusers and application role privileges, TLS, patch level, backups and their encryption, then logging and auditing.

<a id="11-administration-and-operations"></a>