PostgreSQL Grant and Revoke privileges

Preamble

You can grant GRANT and REVOKE rights for different database objects in PostgreSQL. We will see how to grant and revoke table privileges in PostgreSQL.

Grant privileges for a table

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

Syntax to provide table privileges in PostgreSQL

GRANT privileges ON object TO user;
privileges

Privileges to appoint. This can be any of the following values:

• object – The name of the database object for which you grant privileges. In case you grant privileges for a table, this will be the name of the table.
• user – The name of the user to whom these privileges will be granted.

Some examples to understand how to grant table privileges in PostgreSQL

For example, if you want to grant SELECT, INSERT, UPDATE, and DELETE privileges to a table named products for a user named trizor, you must run the following GRANT statement:

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

You can also use the keyword ALL to specify that you want to grant all privileges to a user named trizor. For example:

GRANT ALL ON products TO trizor;

If you want to grant all users only SELECT access to the products table, you can grant PUBLIC privileges. For example:

GRANT SELECT ON products TO PUBLIC;

Recall the privileges for the table

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

The syntax for revoking table privileges in PostgreSQL

REVOKE privileges ON object FROM user;
privileges

Privileges for review. This can be any of the following values:

• object – The name of the database object for which you are revoking the privileges. In the case of revocation of privileges for a table, this will be the name of the table.
• user – The name of the user for whom the privileges are to be revoked.

Examples of how to revoke privileges for a table in PostgreSQL

For example, if you want to revoke the DELETE and UPDATE privileges for a table named products for a user named trizor, you must run the following REVOKE statement:

REVOKE DELETE, UPDATE ON products FROM trizor;

If you want to revoke all table privileges for a user named trizor, you can use the ALL keyword as follows:

REVOKE ALL ON products FROM trizor;

If you granted SELECT * (i.e. all users) privileges in the products table and wanted to revoke those privileges, you can use the following REVOKE statement:

REVOKE SELECT ON products FROM PUBLIC;

PostgreSQL DBA: Grant and Revoke Privileges in PostgreSQL

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.