How to Grant All Privileges in PostgreSQL Database

This is just a simple article for showing on about how to grant all privileges available. Those privileges are the privileges available to a PostgreSQL database. The following are steps for achieving it :

1. First of all, just access the PostgreSQL database server as follows :

   C:\>psql -Upostgres
   Password for user postgres:
   psql (14.0)
   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. Continue on the first part, just execute a command to list the database available in the PostgreSQL database server :

   postgres=#\l
   List of databases
   Name        | Owner    | Encoding | Collate                    | Ctype                      | Access privileges 
   ------------+----------+----------+----------------------------+----------------------------+--------------------------- 
   postgres    | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
   sysapp      | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =Tc/postgres             +
               |          |          |                            |                            | postgres=CTc/postgres    + 
   template0   | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres              + 
               |          |          |                            |                            | postgres=CTc/postgres
   template1   | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres              + 
               |          |          |                            |                            | postgres=CTc/postgres
   (5 rows)
   
   postgres=#
   

3. So, according to the output above, there are several default database available with another database which is specifically not. It is the database with the name of ‘sysapp’. The main goal is to assign another user beside ‘postgres’ to have or to possess all privileges appointed for that database. Basically, the syntax is very simple. The following is the pattern of the syntax :

   grant all privileges on database db_name

   The following is the execution of the command for granting all privileges on certain real database :

   grant all privileges on database sysapp to user db_user
   GRANT
   postgres=#
   

4. After granting, try to execute the command for listing the query. It is for checking whether the user has already granted privileges to a specific database in the previous command. In the above command or query execution, the database name is ‘sysapp’. Furthermore, the user name is ‘db_user’. So, the output of the command or query for listing the database after granting privileges process above exist as follows :

   postgres=# \l
   List of databases
   Name        | Owner    | Encoding | Collate                    | Ctype                      | Access privileges
   ------------+----------+----------+----------------------------+----------------------------+---------------------------
   postgres    | postgres | UTF8     | English_United States.1252 | English_United States.1252 |
   sysapp      | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =Tc/postgres             +
               |          |          |                            |                            | postgres=CTc/postgres    +
               |          |          |                            |                            | db_user=CTc/postgres
   template0   | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres              +
               |          |          |                            |                            | postgres=CTc/postgres
   template1   | postgres | UTF8     | English_United States.1252 | English_United States.1252 | =c/postgres +
               |          |          |                            |                            | postgres=CTc/postgres
   (5 rows)
   
   postgres=#
   

   As in the above output, there is an additional information in the ‘Access privileges’ column. In that column there are several changes appear. In this context, there is a new line appear with the name of ‘db_user’. So, the process for granting privileges of a database for a certain user is a success.