PostgreSQL controls access with roles: a role can be a user that logs in, a group that holds privileges, or both. Privileges are granted separately at the database, schema, table, column and row level, so an application account can be limited to exactly what it needs. In this tutorial you will build a typical setup on Ubuntu 24.04 with PostgreSQL 16: an owner role that runs migrations, a read-write role for the application, a read-only role for reporting, and login users that inherit from them.
Prerequisites
To follow this tutorial you need:
- A server running Ubuntu 24.04 LTS, for example a CubePath VPS.
- A non-root user with
sudoprivileges. - PostgreSQL 16 installed from the Ubuntu repositories (
sudo apt install postgresql). The paths below use/etc/postgresql/16/main/; adjust the version number if you installed another release.
How PostgreSQL privileges work
A few rules explain almost every "permission denied" error you will see:
- Roles are cluster-wide, but privileges are granted per object. Creating a role gives it no access to any table.
- Access is layered. To read a table, a role needs
CONNECTon the database,USAGEon the schema andSELECTon the table. Missing any one of them fails. - Object owners have full rights on what they own. Only the owner (or a superuser) can
ALTERorDROPan object. PUBLICis an implicit group that includes every role. By default it hasCONNECTandTEMPORARYon new databases. Since PostgreSQL 15 it no longer hasCREATEon thepublicschema.- Grants apply to existing objects only. Tables created later need
ALTER DEFAULT PRIVILEGES, covered in Step 5.
The plan for this guide:
| Role | Type | Purpose |
|---|---|---|
app_owner | group (no login) | Owns the database and schema, used for migrations |
app_readwrite | group (no login) | Read and write data in the app schema |
app_readonly | group (no login) | Read data only |
migrator | login | Member of app_owner |
app_user | login | Member of app_readwrite, used by the application |
analyst | login | Member of app_readonly |
Separating group roles from login roles means you grant privileges once to the group and then add or remove people and services by changing membership.
Step 1 - Connecting to PostgreSQL and listing roles
On Ubuntu, the postgres operating system user maps to the postgres superuser through peer authentication. Open a psql session as that user:
sudo -u postgres psql
List the existing roles with the \du meta-command:
\du
On a fresh installation only the superuser exists:
List of roles
Role name | Attributes
-----------+------------------------------------------------------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
Keep this session open. All SQL in Steps 2 to 7 runs here unless stated otherwise.
Step 2 - Creating group roles and login roles
Create the three group roles. NOLOGIN means nobody can connect as them directly:
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;
CREATE ROLE app_readonly NOLOGIN;
Create the login roles and make each one a member of its group. CREATE ROLE ... LOGIN is the same as CREATE USER:
CREATE ROLE migrator LOGIN IN ROLE app_owner;
CREATE ROLE app_user LOGIN CONNECTION LIMIT 20 IN ROLE app_readwrite;
CREATE ROLE analyst LOGIN IN ROLE app_readonly;
CONNECTION LIMIT 20 caps how many sessions the application account can open, which protects the server if a connection pool misbehaves.
Set passwords with \password. It prompts for the password and sends only a SCRAM-SHA-256 hash to the server, so the plain text never ends up in the server log or in your psql history:
\password migrator
\password app_user
\password analyst
Check the result:
\du
List of roles
Role name | Attributes
---------------+------------------------------------------------------------
analyst |
app_owner | Cannot login
app_readonly | Cannot login
app_readwrite | Cannot login
app_user | 20 connections
migrator |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS
To see group membership, run \drg (PostgreSQL 16 and later):
\drg
List of role grants
Role name | Member of | Options | Grantor
-----------+---------------+--------------+----------
analyst | app_readonly | INHERIT, SET | postgres
app_user | app_readwrite | INHERIT, SET | postgres
migrator | app_owner | INHERIT, SET | postgres
INHERIT means the member automatically uses the group's privileges. SET means it can also switch to the group with SET ROLE, which migrator needs so the objects it creates are owned by app_owner.
Step 3 - Creating the database and restricting who can connect
Create the database owned by the group role, not by an individual user:
CREATE DATABASE appdb OWNER app_owner;
By default every role can connect to a new database through PUBLIC. Remove that and grant CONNECT only to the groups that need it:
REVOKE CONNECT, TEMPORARY ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app_readwrite, app_readonly;
app_owner does not need an explicit grant because it owns the database.
Verify the database access list:
\l appdb
The Access privileges column should list app_owner=CTc/app_owner, app_readwrite=c/app_owner and app_readonly=c/app_owner, and no entry that starts with = (which would mean PUBLIC).
Step 4 - Creating a schema and granting schema access
Connect to the new database. The rest of the SQL in this tutorial runs inside appdb:
\c appdb
Create a dedicated schema owned by app_owner. Using your own schema instead of public keeps application objects separate and makes grants easier to reason about:
CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_readwrite, app_readonly;
USAGE lets a role look up objects in the schema. It does not allow creating objects there; only app_owner can do that.
Since PostgreSQL 15 the public schema is owned by the database owner and ordinary roles cannot create objects in it. Lock it down completely so nobody uses it by accident:
REVOKE ALL ON SCHEMA public FROM PUBLIC;
Set the default search path for the database so unqualified table names resolve to app:
ALTER DATABASE appdb SET search_path = app;
The setting applies to new sessions, so reconnect with \c appdb before continuing. Check the schema privileges with:
\dn+ app
Step 5 - Granting table privileges and default privileges
Table grants have two halves: privileges on the tables that exist now, and default privileges for tables that will be created later by migrations.
Grant privileges on existing objects. Right now the schema is empty, but running these keeps the setup correct if you apply it to a database that already has tables:
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_readwrite;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readwrite;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readonly;
The sequence grant is what lets app_user insert rows into tables that use serial or identity columns. Without it, inserts fail with permission denied for sequence.
Now set default privileges. They apply to objects that app_owner creates in the app schema from now on:
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT USAGE, SELECT ON SEQUENCES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT ON TABLES TO app_readonly;
ImportantDefault privileges are tied to the role that creates the object. They only fire when the table is created by
app_owner. That is why migrations should runSET ROLE app_owner;first, as shown below. Ifmigratorcreates a table as itself, the table is owned bymigratorand the defaults do not apply.
Check the stored defaults:
\ddp
Default access privileges
Owner | Schema | Type | Access privileges
-----------+--------+----------+-----------------------------
app_owner | app | sequence | app_readwrite=rU/app_owner
app_owner | app | table | app_readonly=r/app_owner +
| | | app_readwrite=arwd/app_owner
The letters are the standard abbreviations: r SELECT, a INSERT, w UPDATE, d DELETE, U USAGE.
Creating tables the way a migration would
Exit the postgres session:
\q
Connect as migrator over TCP. Ubuntu's default pg_hba.conf uses peer authentication on the local socket, which only works when the OS user and the database role have the same name, and password (SCRAM) authentication on localhost:
psql -h localhost -U migrator -d appdb
Switch to the owner role and create two tables:
SET ROLE app_owner;
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
email text NOT NULL,
tax_id text,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id),
tenant text NOT NULL,
total numeric(10,2) NOT NULL
);
INSERT INTO customers (name, email, tax_id) VALUES ('Acme', '[email protected]', 'B12345678');
INSERT INTO orders (customer_id, tenant, total) VALUES (1, 'acme', 99.90), (1, 'globex', 15.00);
Inspect the table privileges:
\dp customers
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+-----------+-------+--------------------------------+-------------------+----------
app | customers | table | app_owner=arwdDxt/app_owner +| |
| | | app_readonly=r/app_owner +| |
| | | app_readwrite=arwd/app_owner | |
The default privileges were applied automatically. Leave this session with \q.
Step 6 - Restricting access to specific columns
Column-level grants let you hide sensitive fields from a role. Suppose analyst must not see the tax_id column. Because app_readonly has SELECT on the whole table, first revoke that and then grant SELECT only on the allowed columns.
Connect as the owner again:
psql -h localhost -U migrator -d appdb
SET ROLE app_owner;
REVOKE SELECT ON customers FROM app_readonly;
GRANT SELECT (id, name, email, created_at) ON customers TO app_readonly;
Test it as analyst in a new terminal:
psql -h localhost -U analyst -d appdb
SELECT id, name FROM customers;
SELECT * FROM customers;
The first query works. The second fails because * includes tax_id:
ERROR: permission denied for table customers
An alternative that is easier to maintain for many columns is a view that selects only the safe columns, with SELECT granted on the view instead of the table.
Step 7 - Adding row-level security
Row-level security (RLS) filters which rows a role can see or change. A common use is multi-tenant data where each application connection must only touch its own tenant.
As migrator with SET ROLE app_owner, enable RLS on orders and add a policy that compares the tenant column with a per-session setting:
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
TO app_readwrite
USING (tenant = current_setting('app.tenant', true))
WITH CHECK (tenant = current_setting('app.tenant', true));
USING filters the rows that can be read, updated or deleted. WITH CHECK validates new or modified rows. The second argument true makes current_setting return NULL instead of an error when the setting is missing, so a session that forgets to set a tenant sees no rows.
Table owners bypass RLS, so app_owner still sees everything. app_readonly has no policy, and once RLS is enabled a role without a matching policy sees zero rows. Give reporting a read-only policy that shows all rows:
CREATE POLICY reporting_read_all ON orders
FOR SELECT
TO app_readonly
USING (true);
Test as app_user:
psql -h localhost -U app_user -d appdb
SELECT * FROM orders;
SET app.tenant = 'acme';
SELECT * FROM orders;
id | customer_id | tenant | total
----+-------------+--------+-------
(0 rows)
id | customer_id | tenant | total
----+-------------+--------+-------
1 | 1 | acme | 99.90
(1 row)
Your application sets app.tenant at the start of each transaction (for example with SET LOCAL app.tenant = '...') before running queries.
Step 8 - Requiring password authentication in pg_hba.conf
pg_hba.conf decides which roles can connect from where and how they authenticate. Open it:
sudo nano /etc/postgresql/16/main/pg_hba.conf
Ubuntu's defaults allow peer authentication on the local socket and scram-sha-256 on localhost. If the application runs on another server, add a line that allows only its role, only to its database, only from its address. Replace 10.0.0.5 with the application server's private IP:
# TYPE DATABASE USER ADDRESS METHOD
hostssl appdb app_user 10.0.0.5/32 scram-sha-256
hostssl rejects unencrypted connections. PostgreSQL on Ubuntu ships with SSL enabled using a self-signed certificate, which is enough for encryption on a private network.
For remote access PostgreSQL must also listen on a network interface. Edit postgresql.conf:
sudo nano /etc/postgresql/16/main/postgresql.conf
listen_addresses = 'localhost,10.0.0.4'
Here 10.0.0.4 is the database server's own private IP. Changing listen_addresses requires a restart; pg_hba.conf changes only need a reload:
sudo systemctl restart postgresql
Allow the port only from the application server:
sudo ufw allow from 10.0.0.5 to any port 5432 proto tcp
Confirm that PostgreSQL loaded your rules without errors. The error column must be empty:
sudo -u postgres psql -c "SELECT line_number, database, user_name, address, auth_method, error FROM pg_hba_file_rules;"
Step 9 - Auditing roles and privileges
Run these queries as postgres in appdb (sudo -u postgres psql -d appdb) whenever you want to review access.
Roles that can log in, with their dangerous attributes:
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolbypassrls, rolconnlimit
FROM pg_roles
WHERE rolcanlogin
ORDER BY rolname;
Effective privileges of a specific role on the tables of a schema:
SELECT table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'app_readwrite' AND table_schema = 'app'
ORDER BY table_name, privilege_type;
Quick yes/no checks that take inheritance into account:
SELECT has_table_privilege('app_user', 'app.orders', 'DELETE') AS app_user_delete,
has_table_privilege('analyst', 'app.orders', 'INSERT') AS analyst_insert;
app_user_delete | analyst_insert
-----------------+----------------
t | f
Current sessions, to see which roles are actually connected:
SELECT usename, datname, client_addr, state, backend_start
FROM pg_stat_activity
WHERE backend_type = 'client backend';
Step 10 - Revoking access and removing a user
To take access away from a person, remove them from the group or disable their login:
REVOKE app_readonly FROM analyst;
ALTER ROLE analyst NOLOGIN;
To delete a role, first move or drop what it owns and the privileges granted to it, in each database where it has objects:
REASSIGN OWNED BY analyst TO app_owner;
DROP OWNED BY analyst;
DROP ROLE analyst;
DROP ROLE fails with role "analyst" cannot be dropped because some objects depend on it if you skip the first two commands in any database.
Troubleshooting
permission denied for schema app: the role is missingUSAGEon the schema. RunGRANT USAGE ON SCHEMA app TO the_role;.permission denied for table ...on a new table: the table was not created byapp_owner, so default privileges did not apply. Check the owner with\dt app.*and fix it withALTER TABLE app.table_name OWNER TO app_owner;, then grant the missing privileges.permission denied for sequence ..._id_seq: grantUSAGE, SELECTon the sequences to the writing role.Peer authentication failed for user "app_user": you connected over the Unix socket. Add-h localhostto use password authentication.no pg_hba.conf entry for host ...: no line matches that client address, user, database and SSL state. Add a specific rule and reload withsudo systemctl reload postgresql.- A query returns no rows but no error: RLS is enabled and no policy matches the role or the session setting. Check
\d+ table_name, which lists the policies at the bottom.
Conclusion
You now have a PostgreSQL database where ownership, writes and reads are held by separate group roles, login users inherit only what they need, new tables get the right grants automatically, and sensitive columns and tenant rows are filtered at the database level. From here, consider enabling log_connections and log_disconnections in postgresql.conf for an access trail, setting up automated backups with pg_dump, and putting a connection pooler such as PgBouncer in front of the application role.