How to Grant All Privileges for a Specific Schema in PostgreSQL Database

By Posted on

Grant Privilege for All Tables in Specific Schema in PostgreSQL Database

This is an article for showing to grant privileges for all tables in a specific schema in PostgreSQL database. The process is very simple. It just took one single query to achieve it. The process for executing it exist in the PostgreSQL command console. The following is the actual execution of the process with several steps :

  1. Access the PostgreSQL command console as follows : 

    C:\app>psql -Upostgres
Password for user postgres:
psql (12.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

  2. After that, just list the available schema by executing the following command : 

    postgres=# \dn
   List of schemas
   Name        |  Owner
---------------+----------
 customer      | postgres
 distributor   | postgres
 product       | postgres
 transaction   | postgres
 public        | postgres
 vendor        | postgres
(6 rows)

postgres=#

  3. If the user which is going to be granted the privilege does not exist, create it first :
    postgres=# create user admin with password ‘password’;

    CREATE ROLE
    postgres=#

  4. But before going after, check the privileges of the schema by executing the following command : 

    postgres=# \dn+
                           List of schemas
   Name        |  Owner   |  Access privileges   |      Description
---------------+----------+----------------------+------------------------
 customer      | postgres |                      |
 distributor   | postgres |                      | 
 product       | postgres |                      |
 public        | postgres | postgres=UC/postgres+| standard public schema 
               |          | =UC/postgres         | 
 transaction   | postgres |                      |
 vendor        | postgres |                      |
(6 rows)


postgres=#

  5. So, in order to assign all privileges of one user to a specific schema, the following is the command execution : 

    postgres=# grant all privileges on schema customer to admin;
GRANT 
postgres=#

    The above example is showing how to grant all privileges to a specific schema. That schema name is ‘customer’. Moreover, the target user for the privilege granting process is ‘admin’.

  6. Finally, check the privilege list for the schema to show that the granted privilege has exist. 
    postgres=# \dn+
                           List of schemas
   Name        |  Owner   |  Access privileges   |      Description
---------------+----------+----------------------+------------------------
 customer      | postgres | postgres=UC/postgres+|  
               | postgres | admin=UC/postgres    |
 distributor   | postgres |                      | 
 product       | postgres |                      |
 public        | postgres | postgres=UC/postgres+| standard public schema 
               |          | =UC/postgres         | 
 transaction   | postgres |                      |
 vendor        | postgres |                      |
(6 rows)


postgres=#

 

Leave a Reply