PostgreSQL Users, Roles and Privileges on AlmaLinux 10 (GRANT Guide)

PostgreSQL on AlmaLinux series, part 2. Previous: part 1, Install PostgreSQL on AlmaLinux 10: PGDG vs AppStream, pg_hba.conf and Remote Access. Next up: backups and restores with pg_dump, pg_dumpall and pg_basebackup.

A fresh PostgreSQL install has exactly one useful account, the postgres superuser, and it is tempting to let applications, reports and humans all connect with it. Don’t. PostgreSQL has a flexible role system that lets you give every application and person exactly the access they need, and nothing more. This guide builds a clean, repeatable permission layout on AlmaLinux 10: an owner role that owns the schema, read-only and read-write group roles, login roles that inherit from those groups, default privileges so new tables are covered automatically, and a safe way to remove users later.

Applies to: PostgreSQL 18 from the PGDG repository on AlmaLinux 10, as installed in part 1. Everything also works on AppStream PostgreSQL 16, and on RHEL, Rocky Linux, Oracle Linux and CentOS Stream 10. Version-specific behavior (PostgreSQL 15, 16, 17 and 18 changes) is called out where it matters.

Prerequisites

A PostgreSQL server running on AlmaLinux 10 (lab host db1, 192.168.1.40) with remote access enabled, as set up in part 1.
Shell access with sudo on db1, and a client host such as app1 (192.168.1.30) with the psql client installed.
password_encryption left at its default of scram-sha-256.
About 20 minutes. All SQL below is run in psql; lines starting with $ are shell commands.

How PostgreSQL roles work

PostgreSQL has no separate “users” and “groups”. There are only roles. A role with the LOGIN attribute can connect and behaves like a user; a role without it behaves like a group. CREATE USER is just CREATE ROLE ... LOGIN. A few rules shape everything else:

Roles are cluster-wide: one role can be granted access in many databases.
Privileges are per object: connecting to a database, using a schema and reading a table are three separate grants.
Every object has an owner, and the owner (plus superusers) can always alter or drop it. Ownership is not something you can grant away piece by piece.
A role that is a member of another role can use that role’s privileges, either automatically (INHERIT) or by running SET ROLE.
The pseudo-role PUBLIC means “every role”. By default PUBLIC can connect to every database and create temporary tables in it.

Role	Type	Purpose
`inv_owner`	group (NOLOGIN)	Owns the `invdb` database, the `inv` schema and all its tables
`inv_read`	group (NOLOGIN)	SELECT on everything in schema `inv`
`inv_write`	group (NOLOGIN)	SELECT, INSERT, UPDATE, DELETE plus sequence use
`deployer`	login	Runs schema migrations by switching to `inv_owner`
`appsvc`	login	The application service account, member of `inv_write`
`reporter`	login	A reporting user, member of `inv_read`

Step 1: Inspect the existing roles and databases

$ sudo -iu postgres psql

\du
\l
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolvaliduntil
FROM pg_roles WHERE rolname !~ '^pg_' ORDER BY 1;

\du lists roles and their attributes, and \l lists databases with their owners and access privileges. Roles starting with pg_ are predefined roles that ship with PostgreSQL; we will use a couple of them later.

Step 2: Create a non-superuser admin role

Day-to-day user management doesn’t need superuser. A role with CREATEROLE and CREATEDB can create databases and manage the roles it creates:

CREATE ROLE dbadmin WITH LOGIN CREATEROLE CREATEDB;
\password dbadmin

Since PostgreSQL 16, CREATEROLE is much safer than it used to be: a CREATEROLE role automatically gets ADMIN OPTION on the roles it creates and can only alter, drop or grant membership in roles it holds ADMIN OPTION on. It can no longer take over arbitrary roles or create superusers. On PostgreSQL 15 and older, treat CREATEROLE as almost-superuser.

Always set passwords with \password (or createuser --pwprompt). Writing PASSWORD 'secret' in SQL can leave the clear-text password in your shell history, ~/.psql_history and possibly the server log.

Step 3: Create the group roles and login roles

Connect as dbadmin from here on so the new roles are managed by it rather than by postgres:

$ psql -h localhost -U dbadmin -d postgres

-- group roles (cannot log in)
CREATE ROLE inv_owner NOLOGIN;
CREATE ROLE inv_read  NOLOGIN;
CREATE ROLE inv_write NOLOGIN;

-- let dbadmin act as the owner role (needed on PostgreSQL 16+)
GRANT inv_owner TO dbadmin WITH INHERIT FALSE, SET TRUE;

-- login roles
CREATE ROLE deployer LOGIN;
CREATE ROLE appsvc   LOGIN CONNECTION LIMIT 50;
CREATE ROLE reporter LOGIN VALID UNTIL '2027-06-30';

\password deployer
\password appsvc
\password reporter

On PostgreSQL 16 and newer, dbadmin automatically gets ADMIN OPTION on every role it creates, but not the right to SET ROLE to it. Making a role the owner of a database or schema, and setting its default privileges, requires that right, so dbadmin grants itself membership in inv_owner with SET TRUE and INHERIT FALSE (it can do that because it holds ADMIN OPTION).

CONNECTION LIMIT caps how many sessions a role can hold, which stops a runaway application pool from starving everyone else. VALID UNTIL sets a password expiry; after that date password logins for that role fail (use VALID UNTIL 'infinity' to remove it). You can do the same from the shell with the createuser wrapper:

$ createuser -h localhost -U dbadmin --login --pwprompt reporter2

Confirm the passwords are stored as SCRAM hashes. Only a superuser can read pg_authid, so run this as postgres:

$ sudo -iu postgres psql -c "SELECT rolname, left(rolpassword, 14) AS hash FROM pg_authid WHERE rolpassword IS NOT NULL;"

Every hash should start with SCRAM-SHA-256$. PostgreSQL 18 deprecates MD5 passwords and warns when one is set (controlled by md5_password_warnings), so if you see md5 hashes, set password_encryption = scram-sha-256 and reset those passwords.

Step 4: Create the database and lock down PUBLIC

CREATE DATABASE invdb OWNER inv_owner;

-- the owner manages the database's privileges
SET ROLE inv_owner;
-- nobody connects unless we say so
REVOKE CONNECT, TEMPORARY ON DATABASE invdb FROM PUBLIC;
GRANT CONNECT ON DATABASE invdb TO inv_read, inv_write, deployer;
GRANT TEMPORARY ON DATABASE invdb TO inv_write;
RESET ROLE;

The shell equivalent of the first line is createdb -h localhost -U dbadmin -O inv_owner invdb. This works because of the SET TRUE membership granted in Step 3; without it PostgreSQL 16+ answers must be able to SET ROLE "inv_owner". If you followed part 1, the appdb database owned by appuser is untouched; this post builds a separate, cleaner layout.

Step 5: Create a dedicated schema

\c invdb
CREATE SCHEMA inv AUTHORIZATION inv_owner;

-- act as the database owner for the public schema and grants
SET ROLE inv_owner;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA inv TO inv_read, inv_write;
RESET ROLE;

ALTER ROLE appsvc   IN DATABASE invdb SET search_path = inv;
ALTER ROLE reporter IN DATABASE invdb SET search_path = inv;

Since PostgreSQL 15 the public schema is owned by pg_database_owner and PUBLIC only has USAGE on it, not CREATE, so random users can no longer create tables there. Only the owner can revoke that grant, which is why the commands switch to inv_owner (the database owner) first. Revoking the remaining USAGE and putting application objects in their own schema keeps things tidy. ALTER ROLE ... IN DATABASE ... SET gives each login a sensible search_path so queries don’t need the inv. prefix. You can set other per-role defaults the same way, for example statement_timeout for the reporting user.

Step 6: Grant privileges on existing and future objects

GRANT ... ON ALL TABLES IN SCHEMA only covers tables that already exist. To cover tables created later, add default privileges for the role that will create them, which is inv_owner:

SET ROLE inv_owner;

-- existing objects
GRANT SELECT ON ALL TABLES IN SCHEMA inv TO inv_read;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA inv TO inv_write;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA inv TO inv_write;

-- future objects created by inv_owner
ALTER DEFAULT PRIVILEGES FOR ROLE inv_owner IN SCHEMA inv
    GRANT SELECT ON TABLES TO inv_read;
ALTER DEFAULT PRIVILEGES FOR ROLE inv_owner IN SCHEMA inv
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO inv_write;
ALTER DEFAULT PRIVILEGES FOR ROLE inv_owner IN SCHEMA inv
    GRANT USAGE, SELECT ON SEQUENCES TO inv_write;

RESET ROLE;

Running these as inv_owner matters: only an object’s owner (or a role with grant option) can grant on it, and only a role that can act as inv_owner may set its default privileges. dbadmin can switch to it thanks to the membership granted in Step 3. Check the result with \ddp.

Step 7: Add members to the groups

GRANT inv_write TO appsvc;
GRANT inv_read  TO reporter;

-- deployer may switch to inv_owner but does not inherit its rights silently
GRANT inv_owner TO deployer WITH INHERIT FALSE, SET TRUE;

The WITH INHERIT ... SET ... options are PostgreSQL 16 and newer. They let deployer become the owner only on purpose, with SET ROLE inv_owner, which means every migration creates objects owned by inv_owner and the default privileges from Step 6 apply. On PostgreSQL 15 and older, use CREATE ROLE deployer LOGIN NOINHERIT and a plain GRANT inv_owner TO deployer for the same effect.

Step 8: Allow the roles in pg_hba.conf

Privileges decide what a role can do after it connects; pg_hba.conf decides whether it can connect at all. A + in the user column matches every member of a group role, so you don’t have to edit the file every time you add a user:

$ sudo vi /var/lib/pgsql/18/data/pg_hba.conf

# TYPE  DATABASE  USER                         ADDRESS          METHOD
host    invdb     +inv_read,+inv_write,deployer 192.168.1.0/24   scram-sha-256

$ sudo -iu postgres psql -c "SELECT pg_reload_conf();"
$ sudo -iu postgres psql -c "SELECT line_number, database, user_name, address, auth_method, error FROM pg_hba_file_rules WHERE 'invdb' = ANY(database);"

Rules are matched top to bottom and the first match wins, so put this line above any broader host all all rule. An empty error column means the rule parsed correctly. For AppStream installs the file is /var/lib/pgsql/data/pg_hba.conf.

Step 9: Verify the permissions

First, create a table the way a migration would, as deployer switching to the owner role:

$ psql -h 192.168.1.40 -U deployer -d invdb

SET ROLE inv_owner;
CREATE TABLE inv.items (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku   text NOT NULL UNIQUE,
    name  text NOT NULL,
    qty   integer NOT NULL DEFAULT 0
);
INSERT INTO inv.items (sku, name, qty) VALUES ('A-100', 'Widget', 25);
RESET ROLE;
\dp inv.items

\dp should show inv_read=r/inv_owner and inv_write=arwd/inv_owner, which proves the default privileges kicked in without any extra GRANT. Now test from the client host:

$ psql -h 192.168.1.40 -U reporter -d invdb -c "SELECT * FROM items;"
$ psql -h 192.168.1.40 -U reporter -d invdb -c "DELETE FROM items;"
ERROR:  permission denied for table items

$ psql -h 192.168.1.40 -U appsvc -d invdb -c "INSERT INTO items (sku, name) VALUES ('B-200', 'Gadget');"
INSERT 0 1
$ psql -h 192.168.1.40 -U appsvc -d invdb -c "DROP TABLE items;"
ERROR:  must be owner of table items

You can also check privileges without logging in as each user, which is handy in scripts:

SELECT has_database_privilege('reporter', 'invdb', 'CONNECT'),
       has_schema_privilege('reporter', 'inv', 'USAGE'),
       has_table_privilege('reporter', 'inv.items', 'SELECT'),
       has_table_privilege('reporter', 'inv.items', 'DELETE');

Finer-grained options

Column privileges

Hide sensitive columns from a role by granting on specific columns instead of the whole table. Run this as the owner (SET ROLE inv_owner; first) and remember to revoke any table-level grant first, because a table-level SELECT covers every column:

REVOKE SELECT ON inv.items FROM inv_read;
GRANT SELECT (id, sku, name) ON inv.items TO inv_read;

Row-level security

Row-level security filters which rows a role can see. Like grants, policies are created by the table owner. Policies don’t apply to the table owner or superusers unless you also use FORCE ROW LEVEL SECURITY:

ALTER TABLE inv.items ENABLE ROW LEVEL SECURITY;
CREATE POLICY in_stock_only ON inv.items FOR SELECT TO inv_read USING (qty > 0);

Predefined roles

PostgreSQL ships with predefined roles for common jobs, so you don’t need superuser for them:

Role	What it allows
`pg_read_all_data`	SELECT on every table in every database (PostgreSQL 14+)
`pg_write_all_data`	INSERT, UPDATE and DELETE everywhere (PostgreSQL 14+)
`pg_monitor`	Read monitoring views and statistics, ideal for monitoring agents
`pg_maintain`	VACUUM, ANALYZE, REINDEX, REFRESH MATERIALIZED VIEW and more on all relations (PostgreSQL 17+)
`pg_signal_backend`	Cancel queries or terminate sessions of non-superuser roles

CREATE ROLE monitor LOGIN;
\password monitor
GRANT pg_monitor TO monitor;

Granting predefined roles requires superuser or ADMIN OPTION on them, so run that as postgres.

Step 10: Change and remove roles safely

ALTER ROLE reporter VALID UNTIL 'infinity';
ALTER ROLE appsvc CONNECTION LIMIT 100;
REVOKE inv_read FROM reporter;
ALTER ROLE reporter NOLOGIN;   -- disable without deleting

DROP ROLE fails while the role owns objects or holds privileges anywhere. REASSIGN OWNED and DROP OWNED work on the current database only, so run them in every database where the role has objects or grants, then drop the role. Run this part as postgres, because REASSIGN OWNED needs rights on both the old and the new owner:

\c invdb
REASSIGN OWNED BY reporter TO inv_owner;
DROP OWNED BY reporter;
\c postgres
REASSIGN OWNED BY reporter TO inv_owner;
DROP OWNED BY reporter;
DROP ROLE reporter;

Be careful with DROP OWNED: it drops every object the role still owns in that database, which is why REASSIGN OWNED comes first. Renaming a role with ALTER ROLE ... RENAME TO is fine with SCRAM passwords, but it clears an MD5 password, because MD5 uses the role name as its salt.

Troubleshooting

FATAL: no pg_hba.conf entry for host ... user ... database ...: no rule matches. Check the order of rules, the client address and that you ran SELECT pg_reload_conf();. Query pg_hba_file_rules for parse errors.
FATAL: permission denied for database "invdb": the role lacks CONNECT. After revoking from PUBLIC, grant CONNECT to the group the user belongs to.
FATAL: password authentication failed: wrong password, an expired VALID UNTIL date, or a role without LOGIN. Check \du and the server log in /var/lib/pgsql/18/data/log/.
ERROR: permission denied for schema inv: the role has table grants but no USAGE on the schema. Run GRANT USAGE ON SCHEMA inv TO ....
New tables are not readable: default privileges only apply to objects created by the role named in FOR ROLE. If a migration ran as deployer without SET ROLE inv_owner, the table is owned by deployer. Fix it with ALTER TABLE ... OWNER TO inv_owner plus a one-off GRANT, and compare \ddp with \dp.
ERROR: permission denied for sequence ..._id_seq: tables that use serial columns need USAGE on the sequence for inserts. Grant it as in Step 6. Identity columns like the one above don’t need a separate grant.
ERROR: role "reporter" cannot be dropped because some objects depend on it: the role still owns objects or holds privileges in some database. The error detail names them; run REASSIGN OWNED and DROP OWNED in each listed database.
ERROR: must be able to SET ROLE "inv_owner": PostgreSQL 16+ requires the current role to be able to switch to a role before making it an object owner or changing its default privileges. Grant it with GRANT inv_owner TO dbadmin WITH SET TRUE;.
ERROR: permission denied to grant role or permission denied to alter role: on PostgreSQL 16+, a CREATEROLE role can only manage roles it holds ADMIN OPTION on. Have a superuser run GRANT inv_read TO dbadmin WITH ADMIN OPTION; for roles dbadmin did not create.

Quick reference

Task	Command
List roles	`\du`
List databases and their privileges	`\l`
Show table and default privileges	`\dp` and `\ddp`
Create a login role	`CREATE ROLE name LOGIN;` then `\password name`
Create a database with an owner	`CREATE DATABASE db OWNER role;`
Add a user to a group	`GRANT group TO user;`
Cover future tables	`ALTER DEFAULT PRIVILEGES FOR ROLE owner IN SCHEMA s GRANT ...`
Remove a role	`REASSIGN OWNED`, `DROP OWNED` in each database, then `DROP ROLE`

What’s next in the PostgreSQL series

Part 3 covers backups and restores with pg_dump, pg_dumpall and pg_basebackup, including why pg_dumpall --globals-only matters for the roles you just created, since roles live outside any single database. If you haven’t set up the server yet, start with part 1, Install PostgreSQL on AlmaLinux 10: PGDG vs AppStream, pg_hba.conf and Remote Access. For an older cross-distribution walkthrough, see Install PostgreSQL on RHEL/CentOS/Oracle/Rocky/Alma.

Official references: PostgreSQL documentation: Database Roles, Privileges, ALTER DEFAULT PRIVILEGES and Predefined Roles.

PostgreSQL Users, Roles and Privileges on AlmaLinux 10: GRANT, Default Privileges and Group Roles