Roles and Privileges

GRANT what is needed

PostgreSQL uses roles for users and groups. Grant privileges (SELECT, INSERT, UPDATE, DELETE, USAGE on schemas, EXECUTE on functions) to group roles, then make login roles members. Give applications only what they need (no superuser, no ownership of tables they just use), and give reporting users read-only access. Use ALTER DEFAULT PRIVILEGES so future tables get the right grants too.

Least privilege, tenant isolation, recoverability

Roles limit access, row-level security filters rows, and backups make recovery possible.

A read-only reporting role, run

I ran this with psql against PostgreSQL 16.2 (a local server started with the pgserver Python package), on a fresh database loaded with the shop schema from the first section. The analyst role inherits SELECT on products from reporting, so it can count products, but reading customers and updating products are both denied.

CREATE ROLE reporting NOLOGIN;
GRANT SELECT ON products TO reporting;
CREATE ROLE analyst LOGIN PASSWORD 'example-only' IN ROLE reporting;

SET ROLE analyst;
SELECT count(*) AS products_visible FROM products;
SELECT count(*) FROM customers;
UPDATE products SET price = 1;
RESET ROLE;

Output:

 products_visible 
------------------
                3
(1 row)

ERROR:  permission denied for table customers
ERROR:  permission denied for table products

Separate migration and runtime roles

Run schema migrations with an owner role and the application with a role that can only read and write data.

त्वरित जाँच: What should an application's database role usually NOT have?

• SELECT on its tables
• Superuser privileges
• INSERT on its tables
• A password or other authentication