PostgreSQL DBA Part 7: Roles, Users and Privileges (GRANT)

This is Part 7 of the PostgreSQL DBA series. In PostgreSQL there are no separate users and groups: there are only roles. A role that can log in is what other databases call a user, and a role that cannot log in is usually used as a group. To create a user and grant privileges you run CREATE ROLE ... LOGIN PASSWORD, then grant CONNECT on the database, USAGE on the schema and SELECT (or more) on the tables. By the end of this part you will be able to build a clean read-only and read-write setup for an application, make it work for tables created in the future, check who can do what, and remove a user safely.

In Part 6 we covered pg_hba.conf, which decides who may connect. This part covers what a role may do once it is connected. You will use the psql meta-commands from Part 4, such as \du and \dp, all the way through.

What you will learn

• The difference between role attributes, object privileges and role membership
• How to create login roles (users) and group roles, and add users to groups
• Why a user needs three grants (database, schema, table) before a simple SELECT works
• How ALTER DEFAULT PRIVILEGES makes grants apply to tables created later
• How to use predefined roles such as pg_monitor instead of superuser
• How to drop a role that owns objects with REASSIGN OWNED and DROP OWNED

Prerequisites

• Parts 1 to 6 of this series, especially Part 4 (psql) and Part 6 (pg_hba.conf)
• One Ubuntu server with PostgreSQL 18 from the PGDG repository, cluster 18 main running on port 5432
• Shell access with sudo, so you can run sudo -u postgres psql
• The default Ubuntu pg_hba.conf, which allows password logins from localhost with scram-sha-256 (Part 6)

How it works

A role is a named identity inside the PostgreSQL cluster. Roles are shared by all databases in the cluster: you create a role once and it exists in every database, while tables and schemas belong to one database only.

Think of a role as a staff ID card. Some cards open the front door of the building (the LOGIN attribute). Some cards are never given to a person and only describe a team, such as "accounts team"; when you add a person to that team, their own card opens every door the team's card opens. That team card is a group role.

Three separate ideas control what a role can do:

Every object has an owner, normally the role that created it. The owner holds all privileges on it, and only the owner (or a superuser) can drop or alter the object or grant privileges on it. There is also a special name, PUBLIC, which means "every role, including roles created later".

To read a row from a table, a role must pass three checks, in this order:

connect to database shop   -> needs CONNECT on the database
look inside schema sales   -> needs USAGE on the schema
read table sales.orders    -> needs SELECT on the table

By default PUBLIC already has CONNECT and TEMPORARY on every new database, so the first check usually passes. Tables and schemas get no privileges for PUBLIC by default, which is why a new user who can connect still sees "permission denied". The public schema is an exception: everyone has USAGE on it, but since PostgreSQL 15 ordinary users can no longer create objects in it (in PostgreSQL 14 and earlier, and in databases upgraded from those versions, everyone could).

The plan for this part is the pattern most teams use in production: one owner role that owns the tables, one group role per access level, and personal login roles that are only members of groups. Privileges are granted to groups, never to people, so adding or removing a person is one command.

app_owner (LOGIN)   owns database shop, schema sales, all tables
app_readonly  (NOLOGIN group)  -> SELECT
app_readwrite (NOLOGIN group)  -> SELECT, INSERT, UPDATE, DELETE
ravi (LOGIN) member of app_readonly
asha (LOGIN) member of app_readwrite

Creating login roles and group roles

Step 1: List the roles that exist

On the database server, as your sudo user, open psql as the postgres operating system user and run \du ("describe users").

sudo -u postgres psql -c '\du'

                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

A fresh cluster has one login role, postgres, the superuser created at install time. A superuser bypasses every permission check except the right to log in, so you should not give it to applications or people. The cluster also has predefined roles whose names start with pg_; \du hides them, and \duS shows them.

Step 2: Create the owner and the group roles

On the database server, in psql as the postgres user (run sudo -u postgres psql), create the owner role and two group roles.

CREATE ROLE app_owner LOGIN PASSWORD 'Owner#2026';
CREATE ROLE app_readonly NOLOGIN;
CREATE ROLE app_readwrite NOLOGIN;

CREATE ROLE
CREATE ROLE
CREATE ROLE

Each statement prints CREATE ROLE. The groups have no password because nobody will ever log in as them.

Step 3: Create personal login roles

In the same psql session, create two people. CREATE USER is the same command as CREATE ROLE, except that it adds LOGIN by default.

CREATE ROLE ravi LOGIN PASSWORD 'Ravi#2026';
CREATE USER asha PASSWORD 'Asha#2026';
\du

CREATE ROLE
CREATE ROLE
                               List of roles
   Role name   |                         Attributes
---------------+------------------------------------------------------------
 app_owner     |
 app_readonly  | Cannot login
 app_readwrite | Cannot login
 asha          |
 postgres      | Superuser, Create role, Create DB, Replication, Bypass RLS
 ravi          |

Both forms print CREATE ROLE, which shows they are the same command. An empty Attributes column means an ordinary login role with no special abilities, which is exactly what you want for people and applications.

Step 4: Add the users to the groups

GRANT app_readonly TO ravi;
GRANT app_readwrite TO asha;
\drg

GRANT ROLE
GRANT ROLE
                 List of role grants
 Role name |   Member of   |   Options    | Grantor
-----------+---------------+--------------+----------
 asha      | app_readwrite | INHERIT, SET | postgres
 ravi      | app_readonly  | INHERIT, SET | postgres
(2 rows)

\drg (added in psql 16) lists role grants. The Options column shows the two membership options from the documentation:

To undo a membership later, run REVOKE app_readonly FROM ravi;. Note that the attributes LOGIN, SUPERUSER, CREATEDB and CREATEROLE are never inherited through membership; a member only gets them after SET ROLE to a role that has them.

Granting privileges on the database, schema and tables

Step 5: Create the database and sample table as the owner

As the postgres user, create the database with app_owner as its owner. Then, as app_owner, create the schema and table, so that app_owner owns them too.

sudo -u postgres psql -c "CREATE DATABASE shop OWNER app_owner;"
psql -h localhost -U app_owner -d shop

CREATE SCHEMA sales;
CREATE TABLE sales.orders (
    order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer    text    NOT NULL,
    amount      numeric(10,2) NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);
INSERT INTO sales.orders (customer, amount) VALUES
    ('Meena Traders', 1250.00),
    ('Kumar Stores',   480.50),
    ('Fresh Mart',    2999.00);

CREATE SCHEMA
CREATE TABLE
INSERT 0 3

The -h localhost option makes psql connect over TCP, so the Ubuntu rule host all all 127.0.0.1/32 scram-sha-256 applies and psql prompts for the password. The orders table now has three rows and app_owner owns the database, the schema and the table.

Step 6: See the permission errors a new user gets

On the database server, as your sudo user, connect as ravi and try to read the table.

psql -h localhost -U ravi -d shop -c 'SELECT order_id, customer, amount FROM sales.orders;'

Password for user ravi:
ERROR:  permission denied for schema sales
LINE 1: SELECT order_id, customer, amount FROM sales.orders;
                                               ^

The connection worked, because PUBLIC has CONNECT on the new database. The query failed at the second check: ravi has no USAGE on schema sales. Being a member of app_readonly does not help yet, because the group has no privileges either.

Step 7: Grant USAGE on the schema and SELECT on the tables

As the postgres user, connected to shop (sudo -u postgres psql -d shop), grant the read privileges to the group. A superuser's GRANT is performed as if the owner issued it, so the grantor shown later is app_owner.

GRANT USAGE ON SCHEMA sales TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO app_readonly;

GRANT
GRANT

Verify as ravi. The SELECT now works and an INSERT is still refused:

psql -h localhost -U ravi -d shop -c 'SELECT order_id, customer, amount FROM sales.orders;'
psql -h localhost -U ravi -d shop -c "INSERT INTO sales.orders (customer, amount) VALUES ('Test', 1);"

 order_id |   customer    | amount
----------+---------------+---------
        1 | Meena Traders | 1250.00
        2 | Kumar Stores  |  480.50
        3 | Fresh Mart    | 2999.00
(3 rows)

ERROR:  permission denied for table orders

To see the privileges on a table, run \dp in psql as the postgres user:

\dp sales.orders

                                   Access privileges
 Schema |  Name  | Type  |      Access privileges       | Column privileges | Policies
--------+--------+-------+------------------------------+-------------------+----------
 sales  | orders | table | app_owner=arwdDxtm/app_owner+|                   |
        |        |       | app_readonly=r/app_owner     |                   |
(1 row)

Each line reads grantee=privileges/grantor. The letters are: r SELECT, a INSERT, w UPDATE, d DELETE, D TRUNCATE, x REFERENCES, t TRIGGER and m MAINTAIN (new in PostgreSQL 17; on PostgreSQL 16 the owner line ends in arwdDxt). An asterisk after a letter means the privilege was granted WITH GRANT OPTION. An empty Access privileges column means the object still has its default privileges: everything for the owner, nothing for anyone else.

Step 8: Give the read-write group its privileges

As the postgres user, connected to shop:

GRANT USAGE ON SCHEMA sales TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO app_readwrite;

GRANT
GRANT

Verify as asha:

psql -h localhost -U asha -d shop -c "INSERT INTO sales.orders (customer, amount) VALUES ('Sri Balaji Agencies', 760.00) RETURNING order_id, customer;"
psql -h localhost -U asha -d shop -c 'TRUNCATE sales.orders;'

 order_id |      customer
----------+---------------------
        4 | Sri Balaji Agencies
(1 row)

INSERT 0 1
ERROR:  permission denied for table orders

The insert works, and the identity column produced order_id 4 without any extra grant on its sequence. TRUNCATE is refused because it is a separate privilege that we did not grant; this is how you keep an application from emptying a table by mistake. Avoid GRANT ALL for applications for the same reason.

To undo any grant, use REVOKE with the same shape, for example REVOKE INSERT, UPDATE, DELETE ON sales.orders FROM app_readwrite;. After that statement asha's UPDATE fails with ERROR: permission denied for table orders.

Default privileges for tables created later

Step 9: See why ALL TABLES is not enough

GRANT ... ON ALL TABLES IN SCHEMA only touches tables that exist when you run it. As app_owner, create a new table that uses the older serial type, then test it as ravi and asha.

CREATE TABLE sales.refunds (
    refund_id  serial PRIMARY KEY,
    order_id   bigint NOT NULL,
    amount     numeric(10,2) NOT NULL
);

psql -h localhost -U ravi -d shop -c 'SELECT * FROM sales.refunds;'

ERROR:  permission denied for table refunds

The group has SELECT on orders but not on refunds, because refunds did not exist when we ran the grant. Re-running the grant after every deployment is easy to forget, so PostgreSQL has default privileges.

Step 10: Set default privileges for the owner role

ALTER DEFAULT PRIVILEGES stores privileges that PostgreSQL applies automatically to objects created later by a given role. It does not change existing objects. As the postgres user, connected to shop:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA sales
    GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA sales
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA sales
    GRANT USAGE ON SEQUENCES TO app_readwrite;

ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES

USAGE on sequences matters for serial columns: inserting calls nextval() on the sequence, and without USAGE asha gets ERROR: permission denied for sequence refunds_refund_id_seq. Identity columns, like order_id, do not need it.

Default privileges only help future objects, so fix the tables that already exist with the grants from Steps 7 and 8, plus the sequences:

GRANT SELECT ON ALL TABLES IN SCHEMA sales TO app_readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO app_readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA sales TO app_readwrite;
\ddp

GRANT
GRANT
GRANT
                  Default access privileges
   Owner   | Schema |   Type   |      Access privileges
-----------+--------+----------+------------------------------
 app_owner | sales  | sequence | app_readwrite=U/app_owner
 app_owner | sales  | table    | app_readonly=r/app_owner    +
           |        |          | app_readwrite=arwd/app_owner
(2 rows)

\ddp lists the stored defaults. The Owner column is the role whose new objects will receive them: here app_owner, which is what we want, because app_owner creates the tables.

Verify with a brand-new table. As app_owner, create sales.payments; then, without running any GRANT, read it as ravi and insert as asha:

CREATE TABLE sales.payments (
    payment_id  serial PRIMARY KEY,
    order_id    bigint NOT NULL,
    paid        numeric(10,2) NOT NULL
);

psql -h localhost -U asha -d shop -c 'INSERT INTO sales.payments (order_id, paid) VALUES (1, 1250.00);'
psql -h localhost -U ravi -d shop -c 'SELECT count(*) FROM sales.payments;'

INSERT 0 1
 count
-------
     1
(1 row)

Both work with no extra grant. To remove a default privilege later, run the same statement with REVOKE, for example ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA sales REVOKE SELECT ON TABLES FROM app_readonly;.

Locking down PUBLIC and checking access

Step 11: Remove the default CONNECT for everyone

Any login role can connect to shop, because PUBLIC has CONNECT. pg_hba.conf can restrict this too (Part 6), but a database privilege keeps the rule inside the database. As the postgres user, connected to shop:

REVOKE CONNECT ON DATABASE shop FROM PUBLIC;
GRANT CONNECT ON DATABASE shop TO app_readonly, app_readwrite;
SELECT unnest(datacl) AS acl FROM pg_database WHERE datname = 'shop';

REVOKE
GRANT
            acl
---------------------------
 =T/app_owner
 app_owner=CTc/app_owner
 app_readonly=c/app_owner
 app_readwrite=c/app_owner
(4 rows)

An empty grantee means PUBLIC, so =T/app_owner says everyone keeps TEMPORARY (c is CONNECT, T is TEMPORARY, C is CREATE). The owner keeps all three. Test with a role that is in no group:

sudo -u postgres psql -c "CREATE ROLE guest LOGIN PASSWORD 'Guest#2026';"
psql -h localhost -U guest -d shop -c 'SELECT 1;'

CREATE ROLE
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL:  permission denied for database "shop"
DETAIL:  User does not have CONNECT privilege.

To undo, run GRANT CONNECT ON DATABASE shop TO PUBLIC;.

Step 12: Check privileges without logging in as the user

You do not need each user's password to test their access. The has_table_privilege function answers for any role, including privileges it gets through groups. As the postgres user, connected to shop:

SELECT r.rolname,
       has_table_privilege(r.rolname, 'sales.orders', 'SELECT') AS can_select,
       has_table_privilege(r.rolname, 'sales.orders', 'INSERT') AS can_insert
  FROM pg_roles r
 WHERE r.rolname IN ('ravi', 'asha', 'guest')
 ORDER BY 1;

 rolname | can_select | can_insert
---------+------------+------------
 asha    | t          | t
 guest   | f          | f
 ravi    | t          | f
(3 rows)

This is the answer to the question auditors ask most. Similar functions exist for other objects: has_schema_privilege, has_database_privilege, has_sequence_privilege and has_function_privilege.

Role attributes, SET ROLE and predefined roles

Step 13: Limit connections and set an expiry date

As the postgres user, change attributes of an existing role with ALTER ROLE:

ALTER ROLE ravi CONNECTION LIMIT 5 VALID UNTIL '2027-03-31';
\du ravi

ALTER ROLE
                      List of roles
 Role name |                 Attributes
-----------+---------------------------------------------
 ravi      | 5 connections                              +
           | Password valid until 2027-03-31 00:00:00+00

The time is shown in the server's time zone, UTC here. To undo, run ALTER ROLE ravi CONNECTION LIMIT -1 VALID UNTIL 'infinity';.

Step 14: Become a group role with SET ROLE

SET ROLE switches the current session to another role the user is a member of (with the SET option). Objects created after it are owned by that role. As ravi:

SELECT current_user, session_user;
SET ROLE app_readonly;
SELECT current_user, session_user;
RESET ROLE;
SET ROLE app_readwrite;

 current_user | session_user
--------------+--------------
 ravi         | ravi
(1 row)

SET
 current_user | session_user
--------------+--------------
 app_readonly | ravi
(1 row)

RESET
ERROR:  permission denied to set role "app_readwrite"

session_user is who logged in and current_user is whose privileges are being checked. ravi can become app_readonly but not app_readwrite, because ravi is not a member of it.

Step 15: Give monitoring access with pg_monitor, not superuser

Predefined roles bundle common administrative abilities. Grant them like any group. The ones a DBA uses most:

As the postgres user, create a role for a monitoring tool:

CREATE ROLE monitor_user LOGIN PASSWORD 'Mon#2026';
GRANT pg_monitor TO monitor_user;

CREATE ROLE
GRANT ROLE

While asha runs a long query, compare what guest and monitor_user see in pg_stat_activity (both connected to the postgres database):

SELECT usename, state, query FROM pg_stat_activity WHERE usename = 'asha';

 usename | state |          query
---------+-------+--------------------------
 asha    |       | <insufficient privilege>
(1 row)

 usename | state  |        query
---------+--------+----------------------
 asha    | active | SELECT pg_sleep(20);
(1 row)

guest sees that asha is connected but not what the session is running; monitor_user sees the query text. monitor_user can also run SHOW data_directory;, which an ordinary role cannot, and still cannot connect to shop or read its tables. That is the point: monitoring without data access and without superuser.

Dropping a role safely

Step 16: Reassign or drop what the role owns

A role cannot be dropped while it owns objects or holds privileges. Suppose kiran, a developer who is leaving, created a table. Here is what happens if you try DROP ROLE directly, as the postgres user connected to shop:

DROP ROLE kiran;

ERROR:  role "kiran" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema sales
owner of table sales.import_jan

For this test, kiran was created as a member of app_readwrite and given CREATE on schema sales (GRANT CREATE ON SCHEMA sales TO kiran;), then created the table sales.import_jan.

The DETAIL lists what is in the way, but only for the current database; other databases can hold more. The documented procedure is: reassign ownership of what you want to keep, drop the rest, and repeat in every database of the cluster before DROP ROLE.

REASSIGN OWNED BY kiran TO app_owner;
DROP OWNED BY kiran;
-- repeat both commands in every other database: \c postgres, \c shop2, ...
DROP ROLE kiran;

REASSIGN OWNED
DROP OWNED
DROP ROLE

Verify with \dt sales.*: sales.import_jan is still there, now owned by app_owner. If DROP ROLE still fails, the DETAIL names the database or object that remains.

Common mistakes and how to fix them

ERROR: permission denied for schema sales

The role has table privileges but no USAGE on the schema. Fix: GRANT USAGE ON SCHEMA sales TO the_group;

ERROR: permission denied for table orders, right after a deployment

The table was created after your GRANT ... ON ALL TABLES. Fix the existing table with the grant again, and add ALTER DEFAULT PRIVILEGES FOR ROLE set to the role that really creates the tables.

Default privileges are set but new tables still have none

The most common cause is a missing FOR ROLE. If you run ALTER DEFAULT PRIVILEGES as postgres without it, the defaults apply only to tables created by postgres. \ddp shows this clearly:

                  Default access privileges
   Owner   | Schema |   Type   |      Access privileges
-----------+--------+----------+------------------------------
 app_owner | sales  | sequence | app_readwrite=U/app_owner
 app_owner | sales  | table    | app_readonly=r/app_owner    +
           |        |          | app_readwrite=arwd/app_owner
 postgres  | sales  | table    | app_readonly=r/postgres
(3 rows)

Remove the wrong row with the same statement and REVOKE (ALTER DEFAULT PRIVILEGES IN SCHEMA sales REVOKE SELECT ON TABLES FROM app_readonly;) and run it again with FOR ROLE. Default privileges are also not inherited: they apply to the role that actually runs CREATE TABLE, not to the groups it belongs to.

ERROR: permission denied for sequence refunds_refund_id_seq

The table uses serial and the inserting role has no USAGE on its sequence. Fix: GRANT USAGE ON ALL SEQUENCES IN SCHEMA sales TO app_readwrite; plus a default privilege ON SEQUENCES. Identity columns avoid this.

FATAL: role "app_readonly" is not permitted to log in

Someone used a group role in a connection string. Connect as a member login role instead. If the role really should log in, ALTER ROLE name LOGIN;.

FATAL: permission denied for database "shop" (User does not have CONNECT privilege)

CONNECT was revoked from PUBLIC and the role is in no group that has it. Add the user to the right group. Removing a user from their only group (REVOKE app_readwrite FROM asha) produces this same error, which is a quick way to cut off access.

Revoking from a user changes nothing

The user still has the privilege through a group or through PUBLIC. A REVOKE only removes a grant that was made to that exact grantee. Check with has_table_privilege and remove the user from the group instead.

Practice exercise

1. Create a login role report_user for a reporting tool that must read every current and future table in schema sales but must not change data. Use the existing groups.
2. Check, without logging in as report_user, that it can SELECT but not DELETE from sales.payments.

Quick reference

# List roles and memberships
sudo -u postgres psql -c '\du'
sudo -u postgres psql -c '\drg'

# Create an owner, two groups and two login roles
sudo -u postgres psql -c "CREATE ROLE app_owner LOGIN PASSWORD 'Owner#2026';"
sudo -u postgres psql -c "CREATE ROLE app_readonly NOLOGIN;"
sudo -u postgres psql -c "CREATE ROLE app_readwrite NOLOGIN;"
sudo -u postgres psql -c "CREATE ROLE ravi LOGIN PASSWORD 'Ravi#2026';"
sudo -u postgres psql -c "CREATE USER asha PASSWORD 'Asha#2026';"

# Add users to groups
sudo -u postgres psql -c "GRANT app_readonly TO ravi;"
sudo -u postgres psql -c "GRANT app_readwrite TO asha;"

# Database owned by app_owner
sudo -u postgres psql -c "CREATE DATABASE shop OWNER app_owner;"

# As app_owner: create schema sales and the tables (Step 5)
psql -h localhost -U app_owner -d shop

# Schema and table privileges for existing objects
sudo -u postgres psql -d shop -c "GRANT USAGE ON SCHEMA sales TO app_readonly, app_readwrite;"
sudo -u postgres psql -d shop -c "GRANT SELECT ON ALL TABLES IN SCHEMA sales TO app_readonly;"
sudo -u postgres psql -d shop -c "GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO app_readwrite;"
sudo -u postgres psql -d shop -c "GRANT USAGE ON ALL SEQUENCES IN SCHEMA sales TO app_readwrite;"

# Default privileges for objects app_owner creates later
sudo -u postgres psql -d shop -c "ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA sales GRANT SELECT ON TABLES TO app_readonly;"
sudo -u postgres psql -d shop -c "ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA sales GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;"
sudo -u postgres psql -d shop -c "ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA sales GRANT USAGE ON SEQUENCES TO app_readwrite;"
sudo -u postgres psql -d shop -c '\ddp'

# Only the groups may connect
sudo -u postgres psql -d shop -c "REVOKE CONNECT ON DATABASE shop FROM PUBLIC;"
sudo -u postgres psql -d shop -c "GRANT CONNECT ON DATABASE shop TO app_readonly, app_readwrite;"

# Check privileges and table ACLs
sudo -u postgres psql -d shop -c "SELECT has_table_privilege('ravi', 'sales.orders', 'SELECT');"
sudo -u postgres psql -d shop -c '\dp sales.*'

# Attributes and monitoring role
sudo -u postgres psql -c "ALTER ROLE ravi CONNECTION LIMIT 5 VALID UNTIL '2027-03-31';"
sudo -u postgres psql -c "CREATE ROLE monitor_user LOGIN PASSWORD 'Mon#2026';"
sudo -u postgres psql -c "GRANT pg_monitor TO monitor_user;"

# Remove a role (repeat the first two in every database)
sudo -u postgres psql -d shop -c "REASSIGN OWNED BY kiran TO app_owner;"
sudo -u postgres psql -d shop -c "DROP OWNED BY kiran;"
sudo -u postgres psql -c "DROP ROLE kiran;"

Summary and what's next

• A role with LOGIN is a user; a role without LOGIN used for grants is a group
• Grant privileges to group roles and make people members, never grant to individuals
• A SELECT needs CONNECT on the database, USAGE on the schema and SELECT on the table
• GRANT ... ON ALL TABLES covers only existing tables; ALTER DEFAULT PRIVILEGES FOR ROLE the_creator covers future ones
• Grant USAGE on sequences for serial columns; identity columns do not need it
• Use predefined roles such as pg_monitor and pg_signal_backend instead of superuser
• Check access with has_table_privilege, \dp, \ddp and \drg
• Drop a role with REASSIGN OWNED, then DROP OWNED in every database, then DROP ROLE

In Part 8 we look at databases, schemas, search_path and template databases.