# How to Use GRANT Statement in PostgreSQL

> In PostgreSQL, users can access and override the privileges/rights of the database objects through the “GRANT” statement.

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.

---
[View this page online](https://www.commandprompt.com/education/how-to-use-grant-statement-in-postgresql/)