Create User for dumping database PostgreSQL

It is very important to dump PostgreSQL Database Server using the right and appropriate tool or utility so that it can be used for dumping specific database located in. By default installation of PostgreSQL Database Server, the tool provided for dumping a specific database is ‘pg_dump’.

This tool, the ‘pg_dump’ tool can be executed in the command line or terminal. Since this tool is actually trying to access and to connect to PostgreSQL Database Server, before being able to dump the actual file from a specific database, commonly it needs user and password granted with the right privilege on the database which is becoming the dump target.

So, in order to begin the actual dumping process, create a user which has a specific privilege only for dumping the specific database by connecting to PostgreSQL Database Server. Checking the actual service of PostgreSQL Database Server first whether it is active or not. The information regarding on checking PostgreSQL Database Server’s service can be viewed in an article titled ‘Check PostgreSQL Service Status’.

Whether the service itself is active or not, it will determine if PostgreSQL Database Server itself can be used for further operation such as dumping selected database. But the dumping process itself need a specific user which can be acquired by performing the following steps :

1. Connect to PostgreSQL Command Console. This is a step which is done in a CLI environment. If there is an editor or the working environment is in GUI, it is best to connect using tools which is provided in that environment. Use the admin level account which has the adequate privilege to create user and grant the user with the specified right for dumping database. Below is the command syntax :

psql -Upostgres
Description : 
psql : It is the command used to connect to PostgreSQL Command Console. 
-U : It is an additional parameter used to specify user which is used for connecting to PostgreSQL Database Server
postgres : It is the value of the -U parameter. It is defining the user which is used for connecting to PostgreSQL Database Server. 

Below is the execution of the command in order to connect to PostgreSQL Command Console :

user@hostname:~$ psql -Upostgres
Password for user postgres:
psql (9.5.3, server 9.3.10)
Type "help" for help.
postgres=# 

2. After successfully connected to PostgreSQL Database Server, perform the following query to create a user intended specifically for PostgreSQL Database backup process with the specific query command as follows :

create user username with encrypted password 'password';
Description : 
create user : It is the query used to create a new user
username : It is the value or the name of user which is going to be created 
with encrypted password : It is part of the query for creating username specifically intended to provide the new added user to create an encrypted password
'password' : It is the plain text value of the password which is associated for the new added user

The execution example of the above query in PostgreSQL Command Console :

postgres=# create user dbdump_user with encrypted password 'password';
CREATE ROLE
postgres=#

3. Grant privilege specifically for dumping or backup database which is shown as follows :

grant connect on database databasename to user;
Description : 
grant connect on database : It is the query used to grant the right to connect to a certain database with a certain user
databasename : It is the name of the database which is specified to be selected for granting the right to connect to it
to : It is part of the query for granting right 
user : It is the value or the name of the user which is granted with the right specified for a certain database.

The execution process can be shown below in PostgreSQL Command Console :

postgres=# grant connect on database dbcontact to dbdump_user;
GRANT
postgres=#

4. Connect to the database intended to be configured or selected for dumping or backup process by just typing ‘\c\ which can be assumed that the character ‘c’ is for connect for an example it is shown below :

postgres=# \c dbcontact;
psql (9.5.3, server 9.3.10)
You are now connected to database "dbcontact" as user "postgres".
dbcontact=#

Since from the first step the connection to PostgreSQL Command Console has been done with ‘postgres’ account, it can be seen in the above output, the connection to the database which is meant to be selected for further grant operation is obvious. After successfully connected to the selected database, the next will specifically show how to grant the right for specific right to operate on the database.

5. Grant the suitable right to the newly created user so that it can be used for database backup :

grant usage on schema public to user;

First granting usage to the public schema by executing the following query :

Second is granting select on all sequnces in public schema by executing the following query :

grant select on all sequences in schema public to user;

The third step or the last one is granting select on all tables in the public schema by executing the following query :

grant select on all tables in schema public to user;

Below are the output execution of all queries described above in order :

dbcontact=# grant usage on schema public to dbdump_user;
GRANT
dbcontact=# grant select on all sequences in schema public to dbdump_user;
GRANT
dbcontact=# grant select on all tables in schema public to dbdump_user;
GRANT
dbcontact=# \q

6. After successfully granting the sufficient right on the selected database, try to execute a backup process using the newly created user on the database selected using the following pattern :

pg_dump -Uuser --format=c --file=dump-db-file-name.sqlc database_name
Description : 
pg_dump : It is tool or utility used to dump a certain database
-U : It is an additional parameter used to provide user which is going to be utilized for connecting and dumping database in PostgreSQL Database Server
--format=c : It is the additional parameter used to specify the format of dump database file which in this case, the value is 'c' and it stands for 'custom' format. 
--file=dump-db-file-name.sqlc : It is the additional parameter used to specify the name of the file which is going to be used as the dump database file and in this case the name is dump-db-file-name.sqlc
database_name : It is the name of the database which is going to be selected for dumping and backup purpose.

as the pattern shown above, below is the example of the execution :

user@hostname:~$ pg_dump -Udbdump_user --format=c --file=mydb.sqlc mydb
Password:
user@hostname:~$