PostgreSQL – GRANT

In PostgreSQL, the GRANT statement is a powerful tool used to assign privileges to a role, allowing it to alter database objects like tables, views, functions, and more.

Here we will learn about the syntax and application of the GRANT statement, with examples to illustrate its usage in PostgreSQL.

Syntax

The following shows the syntax of the GRANT statement:

GRANT privilege_list | ALL 
ON  table_name
TO  role_name;

Parameters

Let’s analyze the above syntax:

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

PostgreSQL GRANT Statement Example

Let us take a look at an example of the GRANT Statement in PostgreSQL to better understand the concept.

1. Creating a Role

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

CREATE ROLE anshul
LOGIN 
PASSWORD 'geeks12345';

2. Creating a Table

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)
);

3. Attempting to Select Data

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

4. Granting SELECT Privilege

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;

5. Selecting Data Again

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

SELECT * FROM players;

This time, the query should execute successfully.

6. Attempting to Insert Data

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

7. Granting INSERT, UPDATE, and DELETE Privileges

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

GRANT INSERT, UPDATE, DELETE
ON players
TO anshul;

8. Inserting Data Again

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:

Important Points About PostgreSQL GRANT Statement

• You can allow a role to grant the same privileges to other roles by using the GRANT OPTION.
• The GRANT statement is not limited to tables. You can grant privileges on other object types such as sequences, functions, schemas, and databases.
• Privileges granted can also be revoked using the REVOKE statement.
• You can also grant privileges on schemas to control the creation of objects within the schema.

R

RajuKumar19

Follow

Improve

Article Tags :

• PostgreSQL
• postgreSQL-administration