Design & Architecture

Connect to database through .pgpass file / Secrect Manager .

1. how to create a secret in secret manager with customer managed key encryption.
2. how to create secret policy and attach that policy to user (service account)
3. how to use jq commands
4. how to set access key, id and region to connect to aws account
5. command to connect to RDS instance – psql -h ” -p ” -U ‘username’ -p ‘<password’

Shell script to Connect to database from secret manager

1. how to create user CREATE USER WITH PASSWORD ”
2. how to create role CREATE ROLE name [ WITH ADMIN role_name ]
3. how to store secret values in secret manager
4. worked on shell script

Pass query to psql command during runtime.

!/bin/sh

USAGE=”Usage: $0 [-c sql_query]”

Get the secrets from AWS Secrets Manager

secrets=$(aws secretsmanager get-secret-value –secret-id <secret name> –region <regio> | jq -r ‘.SecretString’)

Extract the secrets

PGHOST=$(echo $secrets | jq -r ‘.host’)
PGPORT=$(echo $secrets | jq -r ‘.port’)
PGDATABASE=$(echo $secrets | jq -r ‘.database’)
PGUSER=$(echo $secrets | jq -r ‘.username’)
PGPASSWORD=$(echo $secrets | jq -r ‘.password’)
SQL_QUERY=””

while getopts “:c:” opt; do
case $opt in
c) SQL_QUERY=”$OPTARG”
;;
\?) echo “Invalid option -$OPTARG” >&2
echo
echo “$USAGE”
exit 1
;;
esac
done

Connect to the PostgreSQL database

psql -h “${PGHOST}” -p “${PGPORT}” -d “${PGDATABASE}” -U “${PGUSER}” -c “${SQL_QUERY}”

———————————————————————————————
sh <file.sh> -c “sql query”
Python Script

import argparse
import subprocess
import json

Define the usage and arguments for the script

parser = argparse.ArgumentParser(description=’Connects to a PostgreSQL database and executes an SQL query’)
parser.add_argument(‘-c’, ‘–sql_command’, type=str, help=’The SQL command to execute’)
parser.add_argument(‘-f’, ‘–sql_file’, type=str, help=’The file containing the SQL query’)
args = parser.parse_args()

Get the secrets from AWS Secrets Manager

cmd = “aws secretsmanager get-secret-value –secret-id /998perftest/postgres –region us-east-2”
secrets = subprocess.check_output(cmd, shell=True).decode(‘utf-8’)
secrets_dict = json.loads(secrets)[‘SecretString’]

Extract the secrets

PGHOST = json.loads(secrets_dict)[‘host’]
PGPORT = json.loads(secrets_dict)[‘port’]
PGDATABASE = json.loads(secrets_dict)[‘database’]
PGUSER = json.loads(secrets_dict)[‘username’]
PGPASSWORD = json.loads(secrets_dict)[‘password’]

Connect to the PostgreSQL database and run the specified command or file

if args.sql_command:
cmd = ‘PGPASSWORD=”{}” psql -h {} -p {} -d {} -U {} -w -c “{}”‘.format(PGPASSWORD, PGHOST, PGPORT, PGDATABASE, PGUSER, args.sql_command)
elif args.sql_file:
with open(args.sql_file, ‘r’) as f:
sql_query = f.read().strip()
cmd = ‘PGPASSWORD=”{}” psql -h {} -p {} -d {} -U {} -w -f “{}”‘.format(PGPASSWORD, PGHOST, PGPORT, PGDATABASE, PGUSER, args.sql_file)
else:
cmd = ‘PGPASSWORD=”{}” psql -h {} -p {} -d {} -U {} -w’.format(PGPASSWORD, PGHOST, PGPORT, PGDATABASE, PGUSER)

subprocess.call(cmd, shell=True)