PostgreSQL GRANT (original) (raw)

Last Updated : 15 Jul, 2025

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:

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:

PostgreSQL GRANT Statement Example

Important Points About PostgreSQL GRANT Statement