This script provides a standard way to access postgres from a remote server. It will fetch db credentials based on the following inputs:
DB_CREDENTIALS_NAME is the name of the Secrets Manager secret to retrieve the db credentials from. (Required)DB_NAME is the database name to connect to (Optional, Default: postgres)ENV should be set to prod to modify the remote database, else it returns for a local environment. (Optional)| #!/bin/bash | |
| DB_NAME=${DB_NAME:-postgres} | |
| if [ "$ENV" = "prod" ] && [ -z "$DB_CREDENTIALS_NAME" ]; then | |
| echo "Error: DB_CREDENTIALS_NAME must be set in production environment" | |
| exit 1 | |
| fi | |
| if [ "$ENV" = "prod" ]; then | |
| # Get database credentials from AWS Secrets Manager | |
| DB_SECRET=$(aws secretsmanager get-secret-value --secret-id $DB_CREDENTIALS_NAME | jq -r '.SecretString') | |
| # Parse JSON and set environment variables | |
| export POSTGRES_USERNAME=$(echo $DB_SECRET | jq -r '.username') | |
| export POSTGRES_PASSWORD=$(echo $DB_SECRET | jq -r '.password') | |
| export POSTGRES_HOST=$(echo $DB_SECRET | jq -r '.host') | |
| export POSTGRES_PORT=$(echo $DB_SECRET | jq -r '.port') | |
| export POSTGRES_URI="postgresql://${POSTGRES_USERNAME}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:${POSTGRES_PORT}/${DB_NAME}" | |
| echo "Connecting to postgresql://${POSTGRES_USERNAME}:****@${POSTGRES_HOST}:${POSTGRES_PORT}/${DB_NAME}" | |
| # Run migrations | |
| alembic "$@" | |
| else | |
| export POSTGRES_URI="postgresql://postgres:@localhost:5432/${DB_NAME}" | |
| echo "Connecting to postgresql://postgres:@localhost:5432/${DB_NAME}" | |
| alembic "$@" | |
| fi |
| #!/bin/bash | |
| DB_NAME=${DB_NAME:-postgres} | |
| if [ "$ENV" = "prod" ] && [ -z "$DB_CREDENTIALS_NAME" ]; then | |
| echo "Error: DB_CREDENTIALS_NAME must be set in production environment" | |
| exit 1 | |
| fi | |
| if [ "$ENV" = "prod" ]; then | |
| # Get database credentials from AWS Secrets Manager | |
| DB_SECRET=$(aws secretsmanager get-secret-value --secret-id $DB_CREDENTIALS_NAME | jq -r '.SecretString') | |
| # Parse JSON and set environment variables | |
| POSTGRES_USERNAME=$(echo $DB_SECRET | jq -r '.username') | |
| POSTGRES_PASSWORD=$(echo $DB_SECRET | jq -r '.password') | |
| POSTGRES_HOST=$(echo $DB_SECRET | jq -r '.host') | |
| POSTGRES_PORT=$(echo $DB_SECRET | jq -r '.port') | |
| echo "Connecting to postgresql://${POSTGRES_USERNAME}:****@${POSTGRES_HOST}:${POSTGRES_PORT}/${DB_NAME}" | |
| # Connect using psql | |
| PGPASSWORD=$POSTGRES_PASSWORD psql -h "$POSTGRES_HOST" -p "$POSTGRES_PORT" -U "$POSTGRES_USERNAME" -d "$DB_NAME" | |
| else | |
| echo "Connecting to postgresql://postgres:@localhost:5432/${DB_NAME}" | |
| psql -h localhost -p 5432 -U postgres -d "$DB_NAME" | |
| fi |