Postgres user creation with ansible¶

Prerequisites¶

The following packages should be installed ansible,ansible-playbook and ansible-vault (password encruption),openssl,psycopg2.

For the latest macos version we have to follow the below approach  :As we can't install psycopg2-binary   in latest macos for latest version of ansible with default python 3.9

brew install python, openssl , ansible 
export LDFLAGS="-L/opt/homebrew/opt/openssl@3/lib" 
export CPPFLAGS="-I/opt/homebrew/opt/openssl@3/include" 
export PKG_CONFIG_PATH="/opt/homebrew/opt/openssl@3/lib/pkgconfig"
#### Install  psycopg2-binary 
/opt/homebrew/opt/python@3.13/bin/python3.13 -m pip install --user psycopg2-binary --break-system-packages

Configuration¶

We are maintaining two repo's for the purpose of DB credentials management via ansible.

1. appgrowthplatform (Gitlab)
2. appgrowthplatform (Gitlab)

Here 1st repository is used for all ansible configuration file and 2nd repository is used for assigning values for different database instances  we have.

1. acp-edge-db-prod
2. acp-edge-db-prod-replica
3. dt-growth-db-prod
4. dt-growth-db-stg-dev
5. supply-db-master
6. supply-db-replica
7. unified-demand-prod-db
8. unified-demand-stg-db

How to run play-book to manage DB users.¶

We have to download both repository in a single directory. And then we can run ansible-playbook command for different DB instance we have. In the below example we will run/test ansible for  acp-edge-db.

ansible-playbook -v -i bln-ansible-shared-inventories/inventories/production/hosts bln-ansible/acpedge_gcp_master_users.yml -e 'ansible_python_interpreter=/opt/homebrew/bin/python3' --check --diff

Info

More information on ansible postgres_user module: Postgresql User Module

Password hash generation¶

To generate password hash the following algorithm is used:

'str["md5"] + md5[ password + username ]'

Bash example:

echo "md5$(echo -n 'verysecretpasswordJOE' | md5sum | awk '{print $1}')"

Where:

• username: JOE
• password: verysecretpassword