The “GRANT” statement is used for assigning privileges to the objects of the database. The database objects contain tablespace, schemas, functions, tables, sequences, etc. The “GRANT” statement helps us to access or override the particular role in PostgreSQL.

This write-up aims to explain the usage of the Postgres “GRANT” statement using practical examples. So, let’s begin.

Usage of GRANT Privileges in PostgreSQL

PostgreSQL is a relational DBMS that provides a variety of functionalities to its users. The new user has limited rights to access database objects by default. Therefore, the “GRANT” statement is utilized to assign a specific role to the selected users. For instance, the syntax of the “GRANT” statement is mentioned below.

Syntax

GRANT priv_list ON object(s) TO role_name;

In the above syntax, the “GRANT” command specifies the access privileges to the object. The “role_name” identify the name/role of the user. Moreover, users can give access to one or more than one privileges to objects. The “priv_list” represents the list of privileges that can be assigned to an object. Here is the list of privileges:

- INSERT
- SELECT
- UPDATE
- CREATE
- DELETE
- CONNECT
- TRUNCATE
- TRIGGER
- EXECUTE
- REFERENCES

Note: For assigning all privileges to an object, users can utilize the ALL keyword in PostgreSQL.

You need to follow the below-given stepwise instructions to understand the “GRANT” command in PostgreSQL.

Step 1: Create New Database

Firstly, let’s create a new database on which all modifications will be performed:

CREATE DATABASE db_org;

The database named “db_org” has been created successfully.

Step 2: Connect Database

To establish a connection with the “db_org” database, let’s execute the \c command followed by the name of the selected database:

\c db_org;

The output shows the connection with the db_org database has been established successfully.

Step 3: Create a Role

In the existing database, a new role named “hr_role” is created along with the “login” attribute:

CREATE ROLE hr_role login password 'asa123';

A new role “hr_role” has been created successfully.

Step 4: Create a Table

A new table named “emp_tab” is created using the “CREATE TABLE” statement:

CREATE TABLE emp_tab (
f_name varchar(100) not null, 
l_name varchar(100) not null);

In the above figure, a table “emp_tab” with two columns, has been created in the “db_org” database.

Step 5: Insert Records Into emp_tab

Let’s login as “hr_role”:

Now, execute the insert query to place some new records into the emp_tab table:

INSERT INTO emp_tab (f_name, l_name) 
VALUES('peter', 'ben');

Since we are logged in as hr_role, so, we encountered an error “permission denied”. It proves that the hr_role doesn’t have permission to insert the data into a specific table.

Step 6: GRANT Privileges

To access the privileges of object “emp_table” to “hr_role”, the “GRANT” statement is employed as shown in the below statement:

GRANT SELECT ON emp_tab TO hr_role;

The “GRANT” message in the output verifies that privileges of “emp_tab” have been given to “hr_role” in PostgreSQL.

Step 7: Insert Values in Existing Table

In this step, the “INSERT” statement is utilized to insert values “peter” and “ben” in the “f_name” and “l_name” columns of the “emp_tab” table:

INSERT INTO emp_tab(f_name, l_name) 
VALUES('peter', 'ben');

The output “INSERT 0 1” confirms that one row has been successfully stored into the “emp_tab”.

Step 8: Verify Table’s Data

The user can confirm the table’s data through the “SELECT” statement:

SELECT * FROM emp_tab;

Great Work! You have experienced the usage of the “GRANT” statement to acquire the privilege of objects.

Conclusion

In PostgreSQL, users can access and override the privilege of database objects through the “GRANT” statement. This statement is used for assigning privileges to the database objects such as tablespace, schemas, functions, tables, sequences, etc. This post has explained all the essential steps to use the GRANT statement in PostgreSQL.