GRANT and REVOKE permissions in PostgreSQL

Introduction

GRANT and REVOKE let you control who can do what in your database. They help keep your data safe by giving or taking away permissions.

You want to let a colleague read data but not change it.

You need to allow a user to add new records to a table.

You want to stop someone from deleting data they used to access.

You are setting up a new user and want to give them specific rights.

You want to remove permissions from a user who no longer needs them.

Syntax

PostgreSQL

GRANT privilege_type ON object TO user;
REVOKE privilege_type ON object FROM user;

privilege_type can be SELECT, INSERT, UPDATE, DELETE, etc.

object is usually a table, view, or database.

Examples

This lets user alice read data from the employees table.

PostgreSQL

GRANT SELECT ON employees TO alice;

This stops user bob from adding new rows to the employees table.

PostgreSQL

REVOKE INSERT ON employees FROM bob;

This gives carol all rights on the company_db database.

PostgreSQL

GRANT ALL PRIVILEGES ON DATABASE company_db TO carol;

Sample Program

This creates a products table, gives user john permission to read and add data, then shows what permissions john has on that table.

PostgreSQL

CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price NUMERIC);

GRANT SELECT, INSERT ON products TO john;

-- Check permissions for john
-- (In PostgreSQL, you can query information_schema.role_table_grants)

SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name = 'products' AND grantee = 'john';

Important Notes

Only a database superuser or the object owner can grant or revoke permissions.

Be careful granting ALL PRIVILEGES; it gives full control.

Permissions can be given on different levels: tables, schemas, or entire databases.

Summary

GRANT lets you give specific rights to users.

REVOKE takes those rights away.

Use them to keep your data safe and control access.