Table-level permissions in PostgreSQL

Introduction

Table-level permissions control who can see or change data in a whole table. This keeps data safe and organized.

You want only certain users to read data from a table.

You want to allow some users to add or change data but not delete it.

You want to stop users from accessing sensitive tables.

You want to give a user full control over a table.

You want to share data with a team but limit what they can do.

Syntax

PostgreSQL

GRANT privilege_type ON table_name TO user_or_role;
REVOKE privilege_type ON table_name FROM user_or_role;

privilege_type can be SELECT, INSERT, UPDATE, DELETE, or ALL PRIVILEGES.

You can grant permissions to a user or a group (role).

Examples

Allow user 'alice' to read data from the 'employees' table.

PostgreSQL

GRANT SELECT ON employees TO alice;

Allow the 'sales_team' role to add and change data in the 'sales' table.

PostgreSQL

GRANT INSERT, UPDATE ON sales TO sales_team;

Remove permission for user 'bob' to delete data from the 'customers' table.

PostgreSQL

REVOKE DELETE ON customers FROM bob;

Give the 'manager' role full control over the 'orders' table.

PostgreSQL

GRANT ALL PRIVILEGES ON orders TO manager;

Sample Program

This creates a 'products' table and a user 'john'. Then it gives 'john' permission to read (SELECT) from the 'products' table only. The \dp command shows the permissions on the table.

PostgreSQL

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

CREATE USER john;

GRANT SELECT ON products TO john;

-- Check permissions for john
\dp products

Important Notes

Use GRANT ALL PRIVILEGES carefully; it gives full control.

Revoking permissions removes access immediately.

Permissions can be given to roles to manage groups of users easily.

Summary

Table-level permissions control who can read or change data in a table.

Use GRANT to give permissions and REVOKE to remove them.

Permissions help keep data safe and organized.