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@geeksforgeeks.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:

Whether you're preparing for your first job interview or aiming to upskill in this ever-evolving tech landscape,
GeeksforGeeks Courses are your key to success. We provide top-quality content at affordable prices, all geared towards accelerating your growth in a time-bound manner. Join the millions we've already empowered, and we're here to do the same for you. Don't miss out -
check it out now!
Last Updated :
09 Jan, 2023
Like Article
Save Article