How to Grant All Privileges to All Tables in a Certain Schema in PostgreSQL

Grant All Privileges to All Tables in a Specific Schema in PostgreSQL

This article will show how to grant all privileges into a specific schema in PostgreSQL database server. There is another article before where the main focus is to grant all privileges to the schema itself. It is to the schema but not the tables in it. It is an article exist in this link with the title of ‘How to Grant All Privileges for a Specific Schema in PostgreSQL Database’. So, the following are the steps to achieve it :

1. First of all, just login to the PostgreSQL command console as follows :

   [root@host ~]# psql -Upostgres -d db_app
   psql (11.10)
   Type "help" for help.
   db_app=# 
   

2. Following after, just list all the available schemas in the database by typing the following command :

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

3. So, the next step is to list the available user in the PostgreSQL database server by executing the following command :

   db_app=# \du
                                      List of roles
    Role name |                         Attributes                         | Member of
   -----------+------------------------------------------------------------+-----------
    admin     |                                                            | {}
    centos    | Cannot login                                               | {}
    postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
   db_app=#
   

4. The command pattern for granting all privileges to all tables available in a specific schema exist as follows :

   grant all privileges on all tables in schema schema_name to user_name

   Using the command pattern above, just execute it as follows :

   db_app=# grant all privileges on all tables in schema customer to admin;
   GRANT
   db_app=#