How to Grant Privileges in PostgreSQL: Syntax and Examples

In PostgreSQL, you use the GRANT command to give privileges to users or roles on database objects like tables or schemas. The syntax is GRANT privilege_type ON object TO user;, where you specify what permission to give, on which object, and to whom.

Syntax

The basic syntax of the GRANT command in PostgreSQL is:

• GRANT privilege_type: The permission you want to give, such as SELECT, INSERT, UPDATE, DELETE, or ALL PRIVILEGES.
• ON object: The database object like a table, schema, or database where the privilege applies.
• TO user: The user or role receiving the privilege.

You can grant multiple privileges separated by commas and multiple users as well.

sql

GRANT privilege_type ON object TO user;

Example

This example shows how to grant SELECT and INSERT privileges on a table named employees to a user named john. It allows john to read and add data to the table.

sql

GRANT SELECT, INSERT ON employees TO john;

Output

GRANT

Common Pitfalls

Common mistakes when granting privileges include:

• Forgetting to specify the correct object name or schema, which causes errors.
• Granting privileges to the wrong user or role by typo.
• Not using ALL PRIVILEGES when you want to give full access.
• Assuming privileges are recursive; some privileges must be granted on each object separately.

Always double-check the user and object names and the privileges you want to grant.

sql

/* Wrong: missing object name */
GRANT SELECT TO john;

/* Right: specify object */
GRANT SELECT ON employees TO john;

Quick Reference

Privilege	Description
SELECT	Allows reading data from a table or view
INSERT	Allows adding new rows to a table
UPDATE	Allows modifying existing rows in a table
DELETE	Allows removing rows from a table
ALL PRIVILEGES	Grants all available privileges on the object
USAGE	Allows using a schema or sequence

Key Takeaways

Use the GRANT command to give specific privileges on database objects to users or roles.

Always specify the object and the user correctly to avoid errors.

You can grant multiple privileges at once by separating them with commas.

Common privileges include SELECT, INSERT, UPDATE, DELETE, and ALL PRIVILEGES.

Check privileges carefully to ensure users have only the access they need.