PostgreSQL – GRANT

Last Updated : 17 Aug, 2021

In PostgreSQL, the GRANT statement is used to grant privileges to a role to alter on database objects like tables, views, functions, etc.

The following shows the syntax of the GRANT statement:

Syntax:
GRANT privilege_list | ALL 
ON  table_name
TO  role_name;

Let’s analyze the above syntax:

Some elements of the privilege_list are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, etc. The ALL option to grant all available privileges to a role.
It is required to mention the name of the table after the ON keyword.
The next step is to mention the name of the role to be granted the privileges.

Example:

Create a role called ‘Anshul’ that can login to the PostgreSQL database server as shown below:

create role anshul
login 
password 'geeks12345';

Now create a table called candidate as below:

create table Players (
    player_id int generated always as identity,
    first_name varchar(100) not null,
    last_name varchar(100) not null,
    email varchar(255) not null unique,
    phone varchar(25) not null,
    primary key(player_id)
);

Now, use the role ‘Anshul’ to log in to the PostgreSQL database server separately and try to select data from the players table from the Anshul’s session:

SELECT * FROM players;

Here PostgreSQL will raise an error as shown below:

ERROR:  permission denied for table players

To grant the SELECT privilege on the players table to the role anshul, the GRANT statement can be executed in the postgres‘ session as shown below:

GRANT SELECT 
ON players
TO anshul;

Now, execute the SELECT statement from the anshul’s session:

SELECT * FROM players;

Seventh, execute the following INSERT statement;

INSERT INTO players(first_name, last_name, email, phone)
VALUES('raju', 'kumar', 'raju.kumar@geeforgeeks.org', '408-111-2222');

PostgreSQL issued the following error because anshul does not have the INSERT privilege on the players table:

ERROR:  permission denied for table players

Now, grant INSERT, UPDATE, and DELETE privileges on the candidates table to the role anshul:

GRANT INSERT, UPDATE, DELETE
ON players
TO anshul;

Ninth, execute the INSERT statement again from the anshul’s session:

INSERT INTO players(first_name, last_name, email, phone)
VALUES('raju', 'kumar', 'raju.kumar@gmail.com', '408-111-2222');

Now, anshul can insert data into the players table. In addition to that the Anshul role can update or delete data from the table.

Output:

My Personal Notes

Related Articles

Table of Contents

PostgreSQL – GRANT