PostgreSQL: Grant/Revoke Privileges

This PostgreSQL tutorial explains how to grant and revoke privileges in PostgreSQL with syntax and examples.

Description

You can GRANT and REVOKE privileges on various database objects in PostgreSQL. We'll look at how to grant and revoke privileges on tables in PostgreSQL.

Grant Privileges on Table

You can grant users various privileges to tables. These permissions can be any combination of SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE, ALTER, DROP, GRANT OPTION or ALL.

Syntax

The syntax for granting privileges on a table in PostgreSQL is:

GRANT privileges ON object TO user;

privileges

The privileges to assign. It can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
TRUNCATE	Ability to perform TRUNCATE statements on the table.
REFERENCES	Ability to create foreign keys (requires privileges on both parent and child tables).
TRIGGER	Ability to create triggers on the table.
CREATE	Ability to perform CREATE TABLE statements.
ALL	Grants all permissions.

object

The name of the database object that you are granting permissions for. In the case of granting privileges on a table, this would be the table name.

user

The name of the user that will be granted these privileges.

Example

Let's look at some examples of how to grant privileges on tables in PostgreSQL.

For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called products to a user name techonthenet, you would run the following GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON products TO techonthenet;

You can also use the ALL keyword to indicate that you wish to grant all permissions to a user named techonthenet. For example:

GRANT ALL ON products TO techonthenet;

If you wanted to grant only SELECT access on the products table to all users, you could grant the privileges to PUBLIC. For example:

GRANT SELECT ON products TO PUBLIC;

Revoke Privileges on Table

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, or ALL.

Syntax

The syntax for revoking privileges on a table in PostgreSQL is:

REVOKE privileges ON object FROM user;

privileges

The privileges to revoke. It can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
TRUNCATE	Ability to perform TRUNCATE statements on the table.
REFERENCES	Ability to create foreign keys (requires privileges on both parent and child tables).
TRIGGER	Ability to create triggers on the table.
CREATE	Ability to perform CREATE TABLE statements.
ALL	Grants all permissions.

object

The name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.

user

The name of the user that will have these privileges revoked.

Example

Let's look at some examples of how to revoke privileges on tables in PostgreSQL.

For example, if you wanted to revoke DELETE and UPDATE privileges on a table called products from a user named techonthenet, you would run the following REVOKE statement:

REVOKE DELETE, UPDATE ON products FROM techonthenet;

If you wanted to revoke all permissions on a table for a user named techonthenet, you could use the ALL keyword as follows:

REVOKE ALL ON products FROM techonthenet;

If you had granted SELECT privileges to * (ie: all users) on the products table and you wanted to revoke these privileges, you could run the following REVOKE statement:

REVOKE SELECT ON products FROM PUBLIC;