PostgreSQL – GRANT
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:
Example:
Create a role called ‘Anshul’ that can login to the PostgreSQL database server as shown below:
create role anshul login password 'Beginner12345';
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@w3wiki.net', '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:
Contact Us