PostgreSQL Roles & Permissions: Guide to GRANT and REVOKE

Understanding PostgreSQL Roles

A role is an identity that PostgreSQL recognizes for connecting and performing operations. It has a name, an optional password, and a set of permissions (reading, writing, inserting, deleting, and more). Your application uses this role as a key, presenting the name and password when connecting. PostgreSQL checks permissions on every single operation at the database level, not the application.

Think of it like a company with four rooms: your office, the kitchen, the lobby, and the intern's office. When a new intern arrives, you give them a card that only lets them into the lobby. If the intern loses the card, whoever finds it can't access your office or the kitchen. This is the same protection roles provide for your database.

Your application knows who is who (admin, customer, etc.), but PostgreSQL only knows which role is connecting. If your app gets compromised through SQL injection or a vulnerable library, the attacker inherits whatever permissions the database role has. A role with write permissions is manageable, but a superuser role could destroy everything in one command.

Starting the Environment

Click on "Start PostgreSQL" and let the environment start. Open VS Code, then open the integrated terminal by clicking the three lines, then Terminal, New Terminal.

Verify PostgreSQL is running:

sudo systemctl status postgresql

Connect as the superuser:

sudo -u postgres psql

Connect to the shopdb database:

\c shopdb

List the existing tables:

SELECT * FROM products;
SELECT * FROM orders;

Creating Roles

Create a read-only role:

CREATE ROLE app_readonly WITH LOGIN PASSWORD 'readonly123';

Create a writer role:

CREATE ROLE app_writer WITH LOGIN PASSWORD 'writer123';

List all roles:

\du

You'll see three roles: postgres (the superuser) and the two new roles with no permissions yet.

Granting Permissions

Permissions come in three layers. For the read-only role:

Step 1 - Grant connection to the database:

GRANT CONNECT ON DATABASE shopdb TO app_readonly;

Step 2 - Grant usage on the public schema:

GRANT USAGE ON SCHEMA public TO app_readonly;

Step 3 - Grant SELECT on all tables:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

For the writer role, grant connection and schema usage:

GRANT CONNECT ON DATABASE shopdb TO app_writer;
GRANT USAGE ON SCHEMA public TO app_writer;

Grant SELECT, INSERT, and UPDATE on all tables:

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_writer;

Grant usage on all sequences (required for auto-incrementing IDs):

GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_writer;

Without usage on sequences, inserts fail even when INSERT is granted. Serial columns use sequences under the hood, and this is commonly forgotten.

Testing the Roles

Open a second terminal (Terminal, New Terminal) and connect as the read-only role:

sudo -u postgres psql -d shopdb -U app_readonly -h localhost

Enter the password readonly123. The arrow in the prompt indicates you're connected as this role.

Test reading:

SELECT * FROM orders;

Test inserting (should fail):

INSERT INTO products (name, price, category) VALUES ('test item', 9.99, 'Test');

Test dropping a table (should fail):

DROP TABLE products;

Open a third terminal and connect as the writer role:

sudo -u postgres psql -d shopdb -U app_writer -h localhost

Enter the password writer123.

Test inserting:

INSERT INTO products (name, price, category) VALUES ('Margherita pizza', 29.99, 'main');

Test deleting (should fail):

DELETE FROM products WHERE name = 'Margherita pizza';

Test dropping a table (should fail):

DROP TABLE products;

Revoking Permissions

Go back to the first terminal (the superuser session). Revoke INSERT on products from the writer role:

REVOKE INSERT ON products FROM app_writer;

Switch to the writer terminal and try inserting again:

INSERT INTO products (name, price, category) VALUES ('test', 9.99, 'test');

To restore the permission, go back to the superuser terminal:

GRANT INSERT ON products TO app_writer;

Setting Default Privileges

Grants only apply to existing tables. When you create a new table tomorrow, it won't get those grants. Use ALTER DEFAULT PRIVILEGES to make it automatic:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO app_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO app_writer;

This removes the need to manually grant permissions every time you add a table. Set it once and forget it.

You've built a least privilege setup, giving only the minimum required access to each role. This makes your database more secure at the database level with no extra code. Production databases should be configured this way from day one.