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.

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

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.