In PostgreSQL, the database administrators or superusers are capable of assigning privileges for database objects to users or roles. For instance, the GRANT statement assists us in assigning/granting privileges to a particular user. Using the GRANT ALL statement, all permissions on a schema can be granted to a Postgres User.

This article will explain how to grant all privileges on the schema to a specific Postgres user.

How to Grant All Privileges on Schema to User in PostgreSQL?

In PostgreSQL, the GRANT ALL statement is used to allocate all privileges on the schema to certain Postgres users. Here is the basic syntax:

GRANT ALL ON tab_name TO user;

The above syntax is used to grant all privileges on a specific table to a certain user. However, if you want to grant all privileges on all tables of a schema, then the following syntax can be used:

GRANT ALL ON ALL TABLES 
IN SCHEMA schema TO user;

Specify the schema name and user name of your choice in place of the “schema” and “user” options.

Example: Granting All Privileges on Schema to User in Postgres

Let’s consider the following steps to grant all permissions on the schema to a particular user.

Step 1: Review Available Users

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

\du;

Step 2: Review Available Schemas

Utilize the “\dn” command to see the available schemas:

\dn;

Step 3: Granting All Permissions on Schema to a Single User

Suppose we want to grant all privileges on the “public” schema to a user named “joseph”. For this, we will use the following command:

GRANT ALL 
ON ALL TABLES 
IN SCHEMA public TO joseph;

The output proves that all permissions on all the tables of the “public” schema have been granted to the user “joseph”.

Step 4: Granting All Permissions on Schema to Multiple Users

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

GRANT ALL
ON ALL TABLES 
IN SCHEMA public 
TO sample_user, example_user;

All permissions on the “public” schema have been successfully granted to multiple users.

Conclusion

In PostgreSQL, the “GRANT ALL” statement is utilized along with the “ON ALL TABLES IN SCHEMA” clause to assign permissions on the schema to single or multiple users. Specify the multiple users’ names using the comma-separated syntax to grant all schema privileges to multiple users. This article has explained a practical guide on granting permission on the schema to single or multiple users.