Schema-level access control in PostgreSQL

Introduction

Schema-level access control helps you decide who can see or change groups of database objects. It keeps your data safe and organized.

You want to let only certain users see or change tables in a specific schema.

You have different teams working on different parts of the database and want to keep their data separate.

You want to prevent accidental changes by limiting who can add or remove tables in a schema.

You want to organize your database objects and control access by grouping them in schemas.

You want to give read-only access to some users for all objects in a schema.

Syntax

PostgreSQL

GRANT { { USAGE | CREATE } [, ...] | ALL [ PRIVILEGES ] } ON SCHEMA schema_name TO role_name;

REVOKE { { USAGE | CREATE } [, ...] | ALL [ PRIVILEGES ] } ON SCHEMA schema_name FROM role_name;

USAGE lets a user access objects in the schema (like tables).

CREATE lets a user add new objects (like tables) to the schema.

Examples

Allow user 'alice' to access objects in the 'sales' schema.

PostgreSQL

GRANT USAGE ON SCHEMA sales TO alice;

Allow user 'bob' to create new tables or objects in the 'marketing' schema.

PostgreSQL

GRANT CREATE ON SCHEMA marketing TO bob;

Remove access for 'alice' to use objects in the 'sales' schema.

PostgreSQL

REVOKE USAGE ON SCHEMA sales FROM alice;

Give all schema-level rights to the role 'hr_team' on the 'hr' schema.

PostgreSQL

GRANT ALL PRIVILEGES ON SCHEMA hr TO hr_team;

Sample Program

This example creates a schema named 'project' and a role named 'developer'. It then gives the developer role permission to use and create objects in the 'project' schema. Finally, it shows the granted privileges.

PostgreSQL

CREATE SCHEMA project;
CREATE ROLE developer;
GRANT USAGE ON SCHEMA project TO developer;
GRANT CREATE ON SCHEMA project TO developer;
-- Check privileges
SELECT grantee, privilege_type FROM information_schema.schema_privileges WHERE schema_name = 'project' AND grantee = 'developer';

Important Notes

Granting USAGE alone does not allow creating or modifying objects, only accessing them.

CREATE privilege is needed to add new tables or other objects inside the schema.

Always check current privileges with information_schema.schema_privileges to confirm access rights.

Summary

Schema-level access control manages who can use or add objects in a schema.

Use GRANT and REVOKE with USAGE and CREATE privileges to control access.

Check privileges anytime to keep your database secure and organized.