September 23, 2023
Summary: In this tutorial, you will learn how to use the PostgreSQL GRANT
statement to grant privileges on database objects to a role.
Table of Contents
Introduction to PostgreSQL GRANT statement
After creating a role with the LOGIN
attribute, the role can log in to the PostgreSQL database server. However, it cannot do anything to the database objects like tables, views, functions, etc.
For example, the user role cannot select data from a table or execute a specific function.
To allow the user role to interact with database objects, you need to grant privileges on the database objects to the user role by using the GRANT
statement.
The following shows the simple form of the GRANT
statement that grants one or more privileges on a table to a role:
GRANT privilege_list | ALL
ON table_name
TO role_name;
In this syntax:
- First, specify the
privilege_list
that can beSELECT
,INSERT
,UPDATE
,DELETE
,TRUNCATE
, etc. You use theALL
option to grant all privileges on a table to the role. - Second, specify the name of the table after the
ON
keyword. - Third, specify the name of the role to which you want to grant privileges.
PostgreSQL GRANT statement examples
First, use the postgres
user to connect to the PostgreSQL database server using any client tool of your choice.
Second, create a new user role called joe
that can login to the PostgreSQL database server:
create role joe
login
password 'Abcd1234';
Third, create a new table called candidates
:
create table candidates (
candidate_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(candidate_id)
);
Fourth, use the role joe
to log in to the PostgreSQL database server in a separate session.
Fifth, attempt to select data from the candidates
table from the joe
‘s session:
SELECT * FROM candidates;
PostgreSQL issued an error:
ERROR: permission denied for table candidates
To grant the SELECT
privilege on the candidates
table to the role joe
, you execute the following GRANT
statement in the postgres
‘ session:
GRANT SELECT
ON candidates
TO joe;
Sixth, execute the SELECT
statement from the joe
‘s session:
SELECT * FROM candidates;
PostgreSQL returns an empty result set instead of an error.
Seventh, execute the following INSERT
statement:
INSERT INTO candidates(first_name, last_name, email, phone)
VALUES('Joe','Com','joe.com@example.com','408-111-2222');
PostgreSQL issued the following error because joe
does not have the INSERT
privilege on the candidates
table:
ERROR: permission denied for table candidates
Eighth, grant INSERT
, UPDATE
, and DELETE
privileges on the candidates
table to the role joe
:
GRANT INSERT, UPDATE, DELETE
ON candidates
TO joe;
Ninth, execute the INSERT
statement again from the joe
‘s session:
INSERT INTO candidates(first_name, last_name, email, phone)
VALUES('Joe','Com','joe.com@example.com','408-111-2222');
Now, joe
can insert data into the candidates
table. In addition, it can update or delete data from the table.
More PostgreSQL GRANT examples
Let’s takes some more examples of using the GRANT
statement.
Grant all privileges on a table to a role
The following statement grants all privileges on the candidates
table to the role joe
:
GRANT ALL
ON candidates
TO joe;
Grant all privileges on all tables in a schema to a role
The following statement grants all privileges on all tables in the public
schema of the dvdrental
sample database to the role joe
:
GRANT ALL
ON ALL TABLES
IN SCHEMA "public"
TO joe;
Grant SELECT on all tables
Sometimes, you want to create a readonly role that can only select data from all tables in a specified schema.
In order to do that, you can grant SELECT
privilege on all tables in the public
schema like this:
GRANT SELECT
ON ALL TABLES
IN SCHEMA "public"
TO reader;
So far, you have learned how to grant privileges on tables. To grant privileges on other objects, check it out the GRANT
statement syntax.
Summary
- Use the
GRANT
statement to grant privileges on database objects to a role.