PostgreSQL GRANT Statement

Summary: In this tutorial, you’ll learn how to grant privileges on database objects to a role using the PostgreSQL GRANT statement.

PostgreSQL GRANT Statement Overview #

The GRANT statement grants one or more privileges on database objects such as tables, schemas, views, sequences, and functions to roles.

Here’s the basic syntax of the GRANT statement:

GRANT privilege_list | ALL
ON object_type object_name
TO role_name
[WITH GRANT OPTION];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify one or more privileges you want to grant to a role after the GRANT keyword. If you want to grant all privileges on a database object to a role, you can use the ALL keyword instead of listing all the privileges.
Second, provide the object type (table, schema, etc.) and the object name to which the privileges apply. If the object type is a table, you can ignore the TABLE keyword.
Third, specify a role that receives the privileges.
Finally, use the WITH GRANT OPTION to allow the role to grant the granted privileges to other roles.

Object types and privileges #

The following table shows the object type and privileges of each:

Object Type	Privilege	Example
Table	`SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `REFERENCES`, `TRIGGER`	`GRANT SELECT ON table_name TO role;`
Schema	`USAGE`, `CREATE`	`GRANT USAGE, CREATE ON SCHEMA schema_name TO role;`
Sequence	`USAGE`, `SELECT`, `UPDATE`	`GRANT USAGE, SELECT ON SEQUENCE seq_name TO role;`
Function	`EXECUTE`	`GRANT EXECUTE ON FUNCTION fn() TO role;`
View	`SELECT`	`GRANT SELECT ON view_name TO role;`
Role	Role Membership	`GRANT role1 TO role2;`
Public Access	Open access	`GRANT SELECT ON table_name TO PUBLIC;`
Domain	`USAGE`	`GRANT USAGE ON DOMAIN my_domain TO my_role;`
Foreign Data Wrapper	`USAGE`	`GRANT USAGE ON FOREIGN DATA WRAPPER my_fdw TO role1;`
Foreign Server	`USAGE`	`GRANT USAGE ON FOREIGN SERVER my_foreign_server TO role1;`
Language	`USAGE`	`GRANT USAGE ON LANGUAGE plpython3u TO role1;`
Large Object	`SELECT`, `UPDATE`	`GRANT USAGE ON LARGE OBJECT 12345 TO role1;`
Parameter	`SET`, `ALERT SYSTEM`	`GRANT SET ON PARAMETER search_path TO role1; GRANT ALTER SYSTEM ON PARAMETER work_mem TO role1;`
Tablespace	`CREATE`	`GRANT CREATE ON TABLESPACE my_tablespace TO role1;`
Type	`USAGE`	`GRANT USAGE ON TYPE my_custom_type TO role1;`

Creating a new role #

First, connect to the PostgreSQL server using psql:

psql -U postgres -d inventoryCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a new role named calf with the LOGIN privilege:

CREATE ROLE calf
WITH LOGIN PASSWORD 'securepassword';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, create a new table called scanners that stores the scanning devices:

CREATE TABLE scanners(
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    serial_no VARCHAR(25) NOT NULL,
    warehouse_id INT NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Granting privileges on tables #

First, attempt to insert a new row into the scanners table using the role calf:

INSERT INTO scanners(serial_no, warehouse_id) 
VALUES('US-GXZSYU', 1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Error:

ERROR: permission denied for table scannersCode language: plaintext (plaintext)

Second, grant the INSERT privilege on the scanners table to the calf using the role postgres:

GRANT INSERT
ON scanners
TO calf;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, use the role calf to insert data into the scanners table again:

INSERT INTO scanners(serial_no, warehouse_id) 
VALUES('US-GXZSYU', 1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Now, role calf can insert a new row into the scanners table.

However, the role calf cannot do anything else to the scanners table like selecting and updating data.

Fourth, grant all privileges on the scanners table to the role calf:

GRANT ALL
ON scanners
TO calf;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The role calf can have full privileges on the scanners table.

Fifth, retrieve data from the scanners table:

SELECT * FROM scanners;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 id | serial_no | warehouse_id
----+-----------+--------------
  1 | US-GXZSYU |            1Code language: plaintext (plaintext)

Summary #

Use the GRANT statement to grant one or more privileges on a database object to a role.

Was this tutorial helpful ?