Skip to content
Related Articles
Open in App
Not now

Related Articles

PostgreSQL – GRANT

Improve Article
Save Article
  • Last Updated : 09 Jan, 2023
Improve Article
Save Article

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:

My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!