PostgresqlHow-ToBeginner · 3 min read

How to Grant Access to Schema in PostgreSQL: Simple Guide

To grant access to a schema in PostgreSQL, use the GRANT command with privileges like USAGE or CREATE on the schema to a user or role. For example, GRANT USAGE ON SCHEMA schema_name TO user_name; allows the user to access objects in that schema.

📐

Syntax

The basic syntax to grant access to a schema in PostgreSQL is:

GRANT privilege_type ON SCHEMA schema_name TO role_name;

Where:

privilege_type can be USAGE (to access objects in the schema) or CREATE (to create new objects in the schema).
schema_name is the name of the schema you want to grant access to.
role_name is the user or role receiving the privileges.

sql

GRANT USAGE ON SCHEMA schema_name TO role_name;
GRANT CREATE ON SCHEMA schema_name TO role_name;

💻

Example

This example shows how to grant a user named alice permission to use and create objects in the schema sales.

sql

CREATE SCHEMA sales;
CREATE USER alice WITH PASSWORD 'password123';
GRANT USAGE ON SCHEMA sales TO alice;
GRANT CREATE ON SCHEMA sales TO alice;

Output

CREATE SCHEMA CREATE ROLE GRANT GRANT

⚠️

Common Pitfalls

Common mistakes when granting schema access include:

Granting USAGE without CREATE if the user needs to add new objects.
Forgetting to grant SELECT or other privileges on individual tables inside the schema, since schema privileges do not cover table-level access.
Trying to grant privileges to a user that does not exist or misspelling the schema or role name.

sql

/* Wrong: grants USAGE but user cannot create objects */
GRANT USAGE ON SCHEMA sales TO alice;

/* Right: grants both USAGE and CREATE */
GRANT USAGE, CREATE ON SCHEMA sales TO alice;

Output

GRANT GRANT

📊

Quick Reference

Command	Description
GRANT USAGE ON SCHEMA schema_name TO role_name;	Allows access to objects in the schema.
GRANT CREATE ON SCHEMA schema_name TO role_name;	Allows creating new objects in the schema.
REVOKE privilege ON SCHEMA schema_name FROM role_name;	Removes granted privileges from a role.
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO role_name;	Grants select access on all tables inside the schema.

✅

Key Takeaways

Use GRANT USAGE to allow access to schema objects.

Use GRANT CREATE to allow creating objects in the schema.

Schema privileges do not grant table-level access; grant table privileges separately.

Always verify role and schema names to avoid errors.

Combine privileges as needed for full access control.