In PostgreSQL, access privileges for database objects are managed by users or roles. Managing the privileges means granting or revoking the permissions. When it comes to allocating/granting privileges, the GRANT statement can be used. Using the GRANT statement, specific, multiple, or all permissions can be granted to a Postgres User.

This article will explain how to grant access to all tables to a specific Postgres user using the GRANT keyword.

How to Grant Permissions on all Tables to a Postgres User?

Using the GRANT statement, access to all tables can be allotted to a certain Postgres user. Here is the basic syntax that is to fulfill this task:

GRANT permissions_list
ON ALL TABLES IN SCHEMA public 
TO user;

- Replace the “permissions_list” with the permissions to be granted, such as INSERT, SELECT, etc.
- The GRANT statement is utilized along with the “ON ALL TABLES” clause to assign permissions on all tables to a user.
- Specify the name of the selected user in place of “user”.

Example: Granting DML Permissions on All Tables to a Postgres User?

Let’s consider the following steps to grant the permissions of CRUD operations on all tables to a particular user.

Step 1: Review Available Users

Type the “\du” command to see the available users:

\du;

Step 2: Assign Permissions on All Tables/Relations to a Specific User

Suppose we want to grant “INSERT”, “UPDATE”, “DELETE”, and “SELECT” privileges on all tables to a user named “joseph”. For this, type the following command:

GRANT INSERT, UPDATE, SELECT, DELETE
ON ALL TABLES IN SCHEMA public 
TO joseph;

The output proves that the stated permissions on all the tables have been granted to the “joseph”.

Step 3: Grant Permissions on All Tables to Multiple Users

Specify the multiple users’ names using the comma-separated syntax to grant all table privileges to multiple users:

GRANT INSERT, UPDATE, SELECT, DELETE
ON ALL TABLES IN SCHEMA public 
TO sample_user, example_user;

The specified permissions on all tables have been successfully granted to multiple users.

Conclusion

In PostgreSQL, the GRANT statement is utilized along with the “ON ALL TABLES” clause to assign permissions on all tables to single or multiple users. Specify the multiple users’ names using the comma-separated syntax to grant all table privileges to multiple users. Using the GRANT statement, only specific, multiple, or all permissions can be granted to a Postgres User. This article has explained a practical guide on granting permission on all tables to single or multiple users.