Secure login to PostgreSQL via bash script

Introduction

In this blog we will generate a PostgreSQL db backup of kong db which is running inside a docker container. We have a bash script which will be running on a host machine but it will execute the pg_dump command inside the docker container. All we need to ensure is we do not expose the password for the user we will be using to generate the db backup.

Steps

The high level steps are documented below:

• Create a backup_user for the db dump backup
• Create a .pgpass file for the backup_user
• Generate a db_dump using the pgpass file

Create a backup_user

Use below commands for the same

Connect to kong docker container 

Use below command to connect to kong container, assuming the container name is 'kong'

docker exec -it kong /bin/bash

Login to PostgreSQL db as admin user

Use below command to connect to postgresql db as admin user for creating a new user for db export. 

psql -d kong -U admin

Create new user

Use the sql query below for the same. Define some strong password as mentioned below.

CREATE USER backup_user WITH PASSWORD '<define some strong password>';

Grant necessary privileges

Provide only limited and necessary permissions for this user. We will be using role based access for this user.

Note : These minimum permissions worked for me. Please see what other permissions are required for your use case.

CREATE ROLE dba;

CREATE USER app_dba WITH PASSWORD '<define some strong password>';
GRANT CONNECT ON DATABASE kong TO dba;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO dba;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dba;
GRANT dba TO app_dba;
ALTER ROLE app_dba LOGIN;

Check current privileges for the new user

Below query will output the data in table format having below meaning for columns

• table_catalog : database name
• table_name : name of the table for which the access is granted
• privilege_type : what is the permission on the table

The only permission this user should have is SELECT i.e. READ permission so as to generate a db backup.

SELECT table_catalog, table_name, privilege_type FROM information_schema.role_table_grants WHERE grantee = 'backup_user';

Create a .pgpass file for the backup_user

We will store the new user password inside the .pgpass file and this file would be added inside the docker container. Which means the db_dump can only be taken by executing the command inside the docker container of kong i.e. postgres db. 

Create a .pgpass file

vi ~/.pgpass

Add the user permission in the file

Below are the contents of the .pgpass file for the backup_user to connect to pgsql without password. The password below is the password you assigned to the user during creation in the sql script mentioned in the first step.

Format

<hostname>:<port>:<database name>:<username>:<password>

Example

localhost:5432:kong:backup_user:password

Change permission of the .pgpass file

Use below command for the same

chmod 600 ~/.pgpass

Generate db_dump using backup_user

Use below command to generate db_dump from the host machine. The command will execute the db dump command inside the docker container.

docker exec kong-db sh -c "pg_dump --username=backup_user --dbname=kong" > /opt/kong.sql

Summary

In this blog we achieved db backup using .pgpass without exposing the user password in a script. There are other ways to authenticate the db user for exporting the data based on your use case you can choose one. Refer official PostgreSQL documentation for the authentication options.