PostgreSQL Grant Privileges

In PostgreSQL, a user must have the LOGIN privilege to log into the database. However, explicit permissions must be assigned to perform database operations, including selecting tables, columns, and records.

The PostgreSQL GRANT query allows you to modify privileges for users on database objects such as tables, columns, views, functions, schemas, and more.

This tutorial will walk you through using the PostgreSQL GRANT command to modify privileges for various database objects.

Basic Usage

The basic syntax for the GRANT command is:

GRANT privilege(s) ON object(s) TO user

In the above syntax, you can specify one or more privileges to grant on the specified object to the user.

To set all the privileges on an object, you can use the ALL keyword.

Some of the privileges you can specify to grant on an Object include:

SELECT
INSERT
UPDATE
DELETE
CREATE
CONNECT
TRIGGER
TRUNCATE
REFERENCES
EXECUTE

Next, you specify the database object and the user which to grant the set privileges.

Grant Statement Examples

Let us look at a few examples of how to use the GRANT query on the PostgreSQL database.

First, create a local user with no privileges. To do this, ensure you log in as the admin user.

psql -U postgres
CREATE ROLE mods LOGIN PASSWORD 'hello';

In the next step, create a sample database and table that we will use to illustrate PostgreSQL privileges.

A sample query is below:

DROP DATABASE IF EXISTS privs;
CREATE DATABASE privs;
\c privs;
CREATE TABLE modify (id SERIAL, username VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, amount INT, PRIMARY KEY(id));

Finally, exit the Postgres user and log in as the user we just created above. In the above example, use the command:

psql -U mods -d privs;

Example 1

If you try to select values from the modify table of the privs database, you will get a permission denied error.

For example:

privs=> select * from modify;
ERROR: permission denied for table modify

You can solve this error by granting all permissions to the mods user or by adding select privileges.

To do this, log in as the Postgres user.

psql -U postgres -d privs;
GRANT SELECT ON modify TO mods;

This allows the mods users to select items from the modify table of the privs database.

Once you run the select statement, you should get results as:

privs=# SELECT * FROM modify;
id | username | email | amount
----+----------+-------+--------
(0 rows)

Example 2

You can also assign multiple privileges at once. For example, to specify SELECT, INSERT, DELETE, and TRUNCATE privileges, use the command.

GRANT SELECT, INSERT, DELETE, TRUNCATE ON modify TO mods;

Example 3

To grant all permissions to a user, set the privileges to ALL, as shown in the example command below:

GRANT ALL ON modify TO mods;

Example 4

In all the examples above, we only assign privileges to a user on a specific table. To grant the privileges to all tables of a specified schema, we can use the command:

GRANT SELECT, INSERT, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA "privs" TO mods;

Example 5

You can also GRANT a connect privilege to a specific user to a specific database. The query is:

GRANT CONNECT ON DATABASE privs TO mods;

To revoke all the permissions granted to a user, use the REVOKE query as:

REVOKE SELECT, INSERT, DELETE, TRUNCATE ON modify FROM mods;

Closing

As this guide has shown you, PostgreSQL allows you to modify privileges to a user on database objects using the GRANT and REVOKE commands.