--- title: "How to Manage PostgreSQL Users and Permissions on Ubuntu" description: "Create PostgreSQL roles, grant least-privilege access to databases, schemas and tables, set default privileges and row-level security on Ubuntu 24.04." category: "Database Management" language: en source: https://cubepath.com/docs/database-management/user-and-permission-management-in-postgresql updated: 2026-09-25 --- # How to Manage PostgreSQL Users and Permissions on Ubuntu 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 `sudo` privileges. - 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 `CONNECT` on the database, `USAGE` on the schema and `SELECT` on the table. Missing any one of them fails. - **Object owners have full rights** on what they own. Only the owner (or a superuser) can `ALTER` or `DROP` an object. - **`PUBLIC` is an implicit group** that includes every role. By default it has `CONNECT` and `TEMPORARY` on new databases. Since PostgreSQL 15 it no longer has `CREATE` on the `public` schema. - **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: ```bash sudo -u postgres psql ``` List the existing roles with the `\du` meta-command: ```sql \du ``` On a fresh installation only the superuser exists: ```output 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: ```sql 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`: ```sql 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: ```sql \password migrator \password app_user \password analyst ``` Check the result: ```sql \du ``` ```output 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): ```sql \drg ``` ```output 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: ```sql 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: ```sql 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: ```sql \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`: ```sql \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: ```sql 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: ```sql REVOKE ALL ON SCHEMA public FROM PUBLIC; ``` Set the default search path for the database so unqualified table names resolve to `app`: ```sql 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: ```sql \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: ```sql 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: ```sql 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; ``` > Important: Default 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 run `SET ROLE app_owner;` first, as shown below. If `migrator` creates a table as itself, the table is owned by `migrator` and the defaults do not apply. Check the stored defaults: ```sql \ddp ``` ```output 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: ```sql \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`: ```bash psql -h localhost -U migrator -d appdb ``` Switch to the owner role and create two tables: ```sql 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', 'billing@acme.test', 'B12345678'); INSERT INTO orders (customer_id, tenant, total) VALUES (1, 'acme', 99.90), (1, 'globex', 15.00); ``` Inspect the table privileges: ```sql \dp customers ``` ```output 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: ```bash psql -h localhost -U migrator -d appdb ``` ```sql 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: ```bash psql -h localhost -U analyst -d appdb ``` ```sql SELECT id, name FROM customers; SELECT * FROM customers; ``` The first query works. The second fails because `*` includes `tax_id`: ```output 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: ```sql 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: ```sql CREATE POLICY reporting_read_all ON orders FOR SELECT TO app_readonly USING (true); ``` Test as `app_user`: ```bash psql -h localhost -U app_user -d appdb ``` ```sql SELECT * FROM orders; SET app.tenant = 'acme'; SELECT * FROM orders; ``` ```output 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: ```bash 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: ```text # 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`: ```bash sudo nano /etc/postgresql/16/main/postgresql.conf ``` ```ini 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: ```bash sudo systemctl restart postgresql ``` Allow the port only from the application server: ```bash 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: ```bash 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: ```sql 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: ```sql 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: ```sql SELECT has_table_privilege('app_user', 'app.orders', 'DELETE') AS app_user_delete, has_table_privilege('analyst', 'app.orders', 'INSERT') AS analyst_insert; ``` ```output app_user_delete | analyst_insert -----------------+---------------- t | f ``` Current sessions, to see which roles are actually connected: ```sql 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: ```sql 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: ```sql 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 missing `USAGE` on the schema. Run `GRANT USAGE ON SCHEMA app TO the_role;`. - **`permission denied for table ...` on a new table**: the table was not created by `app_owner`, so default privileges did not apply. Check the owner with `\dt app.*` and fix it with `ALTER TABLE app.table_name OWNER TO app_owner;`, then grant the missing privileges. - **`permission denied for sequence ..._id_seq`**: grant `USAGE, SELECT` on the sequences to the writing role. - **`Peer authentication failed for user "app_user"`**: you connected over the Unix socket. Add `-h localhost` to 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 with `sudo 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.