Case:

The question: Why this task was automatized in script? Simply I’m lazy? or crazy? 🙂 I little bit both 🙂

Sometimes DevOps members get monotone tasks. For example check all used user & their passwords on the environment’s PostgreSQL, as the key-holder (KeePass) recently updated and sometimes somebody update exist credentials with wrong password / edit the name of user by accident. The idea that I create a script to this task was born when I checked, how many credential we have stored for PostgreSQL. Now I will explain, what is the fastest mode to extract needed information from KeePass & use it with simply script to test logins in PostgreSQL.

Extract credentials from KeePass:

Export keys from KeePass:

GIF

Another option to export all of you stored keys. Use it when your *.kdbx contain just needed credentials.

Convert CSV data to more comfort format:

GIF

Now you can simply copy and paste column like in gif “B” & “C”.

Script:

Now you have the extracted credentials, so we can start the test.

Pre-requirements / format of your source-file (where you store credentials)

Please delimit your user & password with TAB. This is the only rule, what you must to follow. This rule is needed as script use awk print function to separate data. Simply copy & paste from excel is compatible, you don’t need any other formatting on it!

Source of the script:

#Target: Script to check postgres database user(s) & password(s).
#Created by: Mikáczó Péter (SLS)
#Created at: 2016.12.14
#Tested on: CentOS release 6.8 (Final) & psql (PostgreSQL) 9.3.13
#USAGE
#db_credentials_check.sh <SOURCE>
#	where SOURCE --> path where you store database users and passwords

#!/bin/bash
SOURCE=$1
while read line
	do
	DB_USER=`echo "$line" | awk {'print $1'}`
	DB_PASS=`echo "$line" | awk {'print $2'}`
	echo "========================================================================"
	export PGPASSWORD=$DB_PASS; psql -U $DB_USER -p 5432 -c "\q"
	if [ $? != 0 ];
		then
			export GREP_COLOR='1;31'
			echo "Password for $DB_USER is WRONG!" | grep --color -E "\b(WRONG|$DB_USER|)\b|$"
			echo "Please check
			user: $DB_USER
			associated password: $DB_PASS"
		else
			export GREP_COLOR='1;32'
			echo "Password for $DB_USER is OK!" | grep --color -E "\b(OK|$DB_USER|)\b|$"
	fi
done < $SOURCE
exit

• Please don’t left to add executable-right after you created the script file chmod +x db_credentials_check.sh
• Also don’t left to fill up positional-parameter, when you start the script ./db_credentials_check.sh /home/pmikaczo/postgres_users_and_passwords

Example output of the script:

Everything is okay, the shipped password & username are valid, psql connection was created successfully:

On source-file you left empty-line:

Wrong password / username was shipped to psql:

Notes:

• Please adjust the script with the correct-one port
• Keep in mind, that store credentials as a plain-text is NOT SAFE. Please delete source-file from server as soon as you tested PostgreSQL login!