What is it? #
PostgreSQL security has two separate layers, and confusing them is the usual source of frustration.
Authentication asks "who are you, and may you connect at all?" That is pg_hba.conf — a file of rules matched by connection type, database, user and source address.
Authorisation asks "now that you are in, what may you do?" That is GRANT and REVOKE on databases, schemas, tables and columns.
A connection must pass both. Being granted every privilege on a table is useless if pg_hba.conf refuses the connection, and a permitted connection still cannot read a table it lacks SELECT on.
The organising principle throughout is least privilege: each role gets exactly what it needs and nothing more.
Think of it like this #
A building with a door policy and a key policy.
The door policy is a list at reception: which people, arriving from where, may come in at all, and what identification they must show. That is pg_hba.conf.
The key policy decides which rooms your key opens once you are inside. That is GRANT.
Someone with a key to every room still cannot get past reception if they are not on the list. And someone who passes reception still cannot open a room their key does not fit. Least privilege means nobody is handed the master key because it was easier than cutting the right one.
Simple example #
A typical application needs three roles: one the application connects as, with read and write on its own tables only; one for analysts, read-only; and one for the nightly backup, which only needs to read everything.
None of them should be a superuser, and none should own the tables they use.
Code #
-- ---------- Roles: users and groups are the same thing ----------
CREATE ROLE app_user LOGIN PASSWORD 'strong-password-here';
-- LOGIN is what makes a role a "user". Without it, it is a group.
CREATE ROLE readonly; -- no LOGIN: this is a group role
CREATE ROLE analyst LOGIN PASSWORD 'another-strong-password';
GRANT readonly TO analyst; -- analyst INHERITS everything readonly has
-- CREATE USER is simply shorthand for CREATE ROLE ... LOGIN.
\du -- list roles and their attributes-- ---------- Role attributes ----------
CREATE ROLE deploy LOGIN PASSWORD '...' CREATEDB; -- may create databases
ALTER ROLE app_user CONNECTION LIMIT 50; -- cap concurrent connections
ALTER ROLE app_user VALID UNTIL '2027-01-01'; -- password expiry
ALTER ROLE app_user PASSWORD 'new-password'; -- rotate a password
-- !! SUPERUSER bypasses ALL permission checks, including row-level security.
-- Applications should NEVER connect as a superuser.
-- Keep the postgres superuser for administration only.-- ---------- The privilege chain: three levels must all allow it ----------
-- 1. Database level: may you connect to this database?
GRANT CONNECT ON DATABASE shop TO app_user;
-- 2. Schema level: may you see inside the schema?
GRANT USAGE ON SCHEMA public TO app_user;
-- Without USAGE, you cannot touch ANY object in the schema,
-- no matter what table privileges you hold. This trips up everybody once.
-- 3. Table level: what may you do to the tables?
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user; -- needed for identity columns-- ---------- Future tables: the step people forget ----------
-- GRANT ... ON ALL TABLES only affects tables that EXIST RIGHT NOW.
-- A table created tomorrow will not be covered. Fix it permanently:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE ON SEQUENCES TO app_user;
-- Note: default privileges apply to objects created by the role that RUNS
-- this statement. Run it as the role that will own the new tables.-- ---------- A read-only role, done properly ----------
GRANT CONNECT ON DATABASE shop TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
-- Then give it to whoever needs it:
GRANT readonly TO analyst;
GRANT readonly TO backup_user;-- ---------- Column-level and REVOKE ----------
GRANT SELECT (id, name, city) ON customers TO analyst; -- not the email column
REVOKE DELETE ON orders FROM app_user; -- take a privilege back
-- PUBLIC is an implicit role meaning "everyone". On older PostgreSQL versions
-- PUBLIC could create objects in the public schema. Lock it down:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE shop FROM PUBLIC;
-- (PostgreSQL 15+ already restricts this by default.)-- ---------- Inspecting who can do what ----------
\dp customers -- privileges on a table (\z is the same)
-- Ask directly:
SELECT has_table_privilege('app_user', 'orders', 'SELECT');
SELECT has_schema_privilege('app_user', 'public', 'USAGE');
-- Which roles is a role a member of?
SELECT r.rolname AS role, m.rolname AS member_of
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.member
JOIN pg_roles m ON m.oid = am.roleid;-- ---------- Row-Level Security: different rows for different users ----------
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- With RLS enabled and NO policy, the table returns NO ROWS to non-owners.
-- Policies then open up specific access.
CREATE POLICY orders_own_rows ON orders
FOR ALL -- SELECT, INSERT, UPDATE, DELETE
TO app_user
USING (tenant_id = current_setting('app.tenant_id')::bigint)
-- ^ USING: which existing rows are VISIBLE
WITH CHECK (tenant_id = current_setting('app.tenant_id')::bigint);
-- ^ WITH CHECK: which NEW rows may be WRITTEN
-- The application sets this per connection, per request:
SET app.tenant_id = '42';
SELECT * FROM orders; -- only tenant 42's rows, enforced by the DATABASE
-- !! Table OWNERS and SUPERUSERS bypass RLS by default.
-- Force it even for the owner:
ALTER TABLE orders FORCE ROW LEVEL SECURITY;---------- pg_hba.conf: who may connect ----------
# File location: SHOW hba_file;
# Rules are read TOP TO BOTTOM. THE FIRST MATCHING LINE WINS.
# After editing: sudo systemctl reload postgresql (reload, not restart)
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
# ^ unix socket ^ OS user must match role name
host shop app_user 10.0.1.0/24 scram-sha-256
# ^ TCP/IP ^ only from this subnet ^ password, securely hashed
hostssl shop analyst 0.0.0.0/0 scram-sha-256
# ^ SSL/TLS REQUIRED for this rule to match
host all all 0.0.0.0/0 reject
# ^ explicit catch-all deny at the END
# METHODS, worst to best:
# trust NO PASSWORD AT ALL. Never use beyond a local sandbox.
# md5 legacy hashing. Superseded — use scram-sha-256.
# scram-sha-256 the modern default. Use this.
# peer local only: the OS username must match the database role.
# cert client TLS certificate authentication.# ---------- SSL/TLS: encrypt traffic over the network ----------
# In postgresql.conf:
# ssl = on
# ssl_cert_file = '/etc/ssl/certs/server.crt'
# ssl_key_file = '/etc/ssl/private/server.key'
# The key must be readable ONLY by the postgres user, or the server refuses to start:
sudo chown postgres:postgres /etc/ssl/private/server.key
sudo chmod 600 /etc/ssl/private/server.key
# Force clients to verify the server, preventing man-in-the-middle:
psql "host=db.example.com dbname=shop user=analyst sslmode=verify-full"
# sslmode=require encrypts, but does NOT verify who you connected to
# sslmode=verify-full encrypts AND verifies the certificate and hostname# ---------- Network level: the first line of defence ----------
# PostgreSQL should not be reachable from the internet at all.
sudo ss -tulpn | grep 5432 # check what it is listening on
# In postgresql.conf, bind only where it is needed:
# listen_addresses = 'localhost' # local only
# listen_addresses = '10.0.1.5' # one private interface
# Firewall: allow only the application servers.
sudo ufw allow from 10.0.1.0/24 to any port 5432
sudo ufw deny 5432How it works #
In PostgreSQL there is one concept — the role — and "user" simply means a role with the LOGIN attribute. A role without LOGIN works as a group: grant privileges to it, then grant the role itself to people. Changing what analysts may see then means altering one role rather than every analyst.
Authorisation is a chain of three levels, and all three must permit the action. CONNECT on the database, USAGE on the schema, then the table privilege itself. The schema step is the one that catches everybody: without USAGE ON SCHEMA, a role holding SELECT on every table still cannot read a single one, and the error message is not always obvious.
GRANT ... ON ALL TABLES applies only to tables existing at that moment. Tables created afterwards are not covered, which is why permissions mysteriously stop working after a deployment adds a table. ALTER DEFAULT PRIVILEGES fixes this properly by stating what should happen for future objects — and it applies to objects created by the role that ran it, so it must be run as the role that will own them.
Row-Level Security filters rows rather than tables. Once enabled, the table returns nothing to non-owners until a policy grants access. A policy's USING clause controls which existing rows are visible; WITH CHECK controls which new rows may be written. Without WITH CHECK, a tenant could insert rows belonging to another tenant even though they cannot see them. Note that owners and superusers bypass RLS unless FORCE ROW LEVEL SECURITY is set — which is precisely why applications must not connect as the table owner.
pg_hba.conf is evaluated top to bottom and the first matching line wins. A permissive rule above a restrictive one makes the restrictive one unreachable, so ordering is the whole design. Rules match on connection type, database, role and source address. hostssl matches only encrypted connections, which is how you require TLS for a particular role. Changes take effect on reload — a restart is not needed.
Use scram-sha-256 for password authentication. md5 is legacy, and trust means no password at all, which has no place outside a disposable sandbox.
Finally, sslmode matters on the client. require encrypts the connection but does not verify who answered, leaving a man-in-the-middle possible. verify-full checks the certificate and the hostname, and is what production clients should use.
Real-world use #
The single highest-value habit is that applications never connect as a superuser, and never as the table owner. A superuser bypasses every check including RLS; an owner bypasses RLS and can drop the tables. Create an application role with exactly the privileges it needs, and keep ownership separate. If the application is ever compromised through SQL injection, this is the difference between reading some data and losing the database.
Defence in depth is the practical model. The firewall and listen_addresses mean most of the world cannot reach the port at all. pg_hba.conf restricts which roles may connect from which networks. GRANT limits what each role may do. RLS limits which rows they see. Each layer is imperfect alone; together they are strong.
Group roles make permissions maintainable. Granting privileges to readonly and then granting readonly to individuals means offboarding someone is one REVOKE, and reviewing access is reading one role rather than auditing dozens.
Row-Level Security is the standard approach for multi-tenant applications, and it is far more reliable than adding WHERE tenant_id = ? in application code — that condition only has to be forgotten once. Set the tenant per connection and let the database enforce it. Do verify the policy with tests, including that a tenant cannot insert rows for another tenant, which is the WITH CHECK half.
Rotate passwords, and store them in a secrets manager or a .pgpass file with 600 permissions rather than in application config committed to a repository. The automated-backup lesson covers .pgpass in detail.
Always reload rather than restart after editing pg_hba.conf, and test the change from an actual client before walking away — an ordering mistake in that file can lock out either your application or yourself.
Common mistakes #
- Letting the application connect as a superuser or as the table owner, bypassing RLS and every check.
- Granting table privileges but forgetting USAGE on the schema, so nothing is accessible.
- Using GRANT ON ALL TABLES without ALTER DEFAULT PRIVILEGES, so new tables are not covered.
- Writing an RLS policy with USING but no WITH CHECK, allowing writes into another tenant’s rows.
- Using sslmode=require instead of verify-full, which encrypts but does not verify the server.
Practice #
Create three roles on a test database: an application role with read and write on one schema, a readonly group role, and an analyst that inherits it. Verify with has_table_privilege that each can do exactly what you intend and nothing more. Then deliberately omit GRANT USAGE ON SCHEMA and observe the failure. Finally, add a tenant_id column to a table, enable row-level security with both USING and WITH CHECK, and confirm from two sessions with different app.tenant_id settings that neither can read or insert the other's rows.