How to Grant Permissions in PostgreSQL

How to Grant Permissions in PostgreSQL

Use the GRANT statement to give users or roles precise privileges on databases, schemas, tables, sequences, functions, and more. It is the cornerstone of fine-grained access control.

What problem does GRANT solve?

GRANT lets you avoid one superuser for everything. You can hand out only the rights a developer, analyst, or app needs, keeping data safe and audits simple.

What is the basic GRANT syntax?

The format is GRANT privilege_list ON object_type object_name TO role_list [WITH GRANT OPTION]. Replace each placeholder with your objects and roles.

How do I grant read-only access to one table?

Run GRANT SELECT ON public.sales TO analyst;. The analyst role can now query public.sales but cannot modify it.

How can I let a role create tables in a schema?

First make the schema owner execute GRANT CREATE ON SCHEMA reporting TO loader;. Then log in as loader and create tables freely inside reporting.

How do I grant permissions on all current and future tables?

Combine a direct GRANT with ALTER DEFAULT PRIVILEGES:GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

Can I delegate privileges to others?

Add WITH GRANT OPTION. Example: GRANT SELECT ON customers TO team_lead WITH GRANT OPTION;. The team_lead may now pass SELECT to colleagues.

Best practices for GRANT

Grant to roles, not users

Create roles like reader, writer, admin. Then GRANT reader TO alice;. This keeps permissions tidy when staff changes.

Revoke before grant for least privilege

Start with REVOKE ALL if you inherit an environment with broad rights. Then grant back only what is necessary.

How to list current privileges?

Run \dp tablename in psql. It shows who has which rights on the object.

When should I use GRANT … ON DATABASE?

Grant database-level CONNECT and TEMP rights to permit session creation and temp table usage. Everything else should be on schema or table level.