PostgreSQL Privileges are the permission or right to execute SQL statements on someone else objects that can be granted to a user or group of users to perform SQL operations such as SELECT, INSERT, UPDATE, DELETE, TRUNCATE, and so on. In PostgreSQL, there are two types of Privileges Cluster level Privileges and Object level Privileges. The Cluster level Privileges are granted by Super User and the object level privileges are granted by the owner of the object or the Superuser.
Postgresql Privilege Types And Abbreviations
The following is the list of Postgresql Privilege Types and their Abbreviations.
| Privilege Name | Privilege Abbreviation | Applicable Object Types |
|---|---|---|
| SELECT | r (“read”) | LARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table column |
| INSERT | a (“append”) | TABLE, table column |
| UPDATE | w (“write”) | LARGE OBJECT, SEQUENCE, TABLE, table column |
| DELETE | d | TABLE |
| TRUNCATE | D | TABLE |
| REFERENCES | x | TABLE, table column |
| TRIGGER | t | TABLE |
| CREATE | C | DATABASE, SCHEMA, TABLESPACE |
| CONNECT | c | DATABASE |
| TEMPORARY | T | DATABASE |
| EXECUTE | X | FUNCTION, PROCEDURE |
| USAGE | U | DOMAIN, FOREIGN-DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE |
Now let us see how to grant and revoke Privilege in PostgreSQL in the following section.
1. Grant CONNECT to the Database
The Connect to database privilege allows the grantee to make a connection to the particular database.
In the below example, we will grant connect permission to DEVAN user on the POSTGRES database.
Command:
postgresql=# GRANT CONNECT ON DATABASE POSTGRES TO DEVAN;
Output:
2. Grant USAGE on Schema
The Usage privilege is used for the procedural languages that allow the creation of the functions in that language.
In the below example we will grant usage permission schema EMP_RECORDS to the user DEVAN.
Command:
postgresql=# GRANT USAGE ON SCHEMA EMP_RECORDS TO DEVAN;
Output:
3. Grant DML Statements on Schema
We can grant the DML statements on all tables present in the particular schema. In the following example, we have granted DML permission on schema EMP_RECORDS to the user RAM.
Command:
postgresql=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA EMP_RECORDS TO RAM;
Output:
4. Grant All Privileges On All Tables In The Schema
In this section, we will grant all privileges on all tables in the schema named WASI to the user RAM.
Command:
postgresql=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA WASI TO RAM;
Output:
5. Grant Create Database
The Create Database Privilege allows users to create a database. We will grant the same permission to user RAM in the below section.
Command:
postgresql=# ALTER USER RAM CREATEDB;
Output:
6. Grant Superuser Privilege
In this section, we will grant the SUPERUSER permission to user MOHAN.
Command:
postgresql=# ALTER USER MOHAN WITH SUPERUSER;
Output:
7. Remove Superuser Privilege
The Superuser Privilege can be removed using the below Alter User command.
We will remove the Superuser Privilege from the user MOHAN that we granted in the above section.
Command:
postgresql=# ALTER USER MOHAN WITH NOSUPERUSER;
Output:
8. Remove INSERT, UPDATE, DELETE from User
In this section, we will use the revoke command to revoke the INSERT, UPDATE, and DELETE Privilege from user RAM for the table FILMS_DETAIL.
Command:
postgresql=# REVOKE INSERT,UPDATE,DELETE ON FILMS_DETAIL FROM RAM;
Output:
9. Revoke all Privilege From the Table
We can Revoke all Privilege From the Table using the REVOKE ALL command. In the below example, we will revoke all permission on table FILMS_DETAIL from the user RAM.
Command:
postgresql=# REVOKE ALL ON FILMS_DETAIL FROM RAM;
Output:
10. Revoke Select On Table from Public
The REVOKE SELECT ON permission will revoke the select permission. In this example, we will revoke the select permission on FILMS_DETAIL from Public.
Command:
postgresql=# REVOKE SELECT ON FILMS_DETAIL FROM PUBLIC;
Output:
