Database Access

The Odin cluster includes a PostgreSQL database for application data.

Prerequisites

Install the PostgreSQL client:

sudo apt-get update
sudo apt-get install -y postgresql-client

Note: The PostgreSQL client should be included in the custom build image.

Getting Database Credentials

Retrieve credentials from AWS Secrets Manager:

aws secretsmanager get-secret-value \
  --secret-id odin-db-connection \
  --region us-west-2 | jq -r .SecretString | jq

This returns a JSON object with:

host - Database hostname
port - Database port (typically 5432)
database - Database name
username - Database username
password - Database password

Connecting to the Database

Using psql

# Get credentials first
CREDS=$(aws secretsmanager get-secret-value \
  --secret-id odin-db-connection \
  --region us-west-2 --query 'SecretString' --output text)

# Extract values
DB_HOST=$(echo $CREDS | jq -r '.host')
DB_PORT=$(echo $CREDS | jq -r '.port')
DB_NAME=$(echo $CREDS | jq -r '.database')
DB_USER=$(echo $CREDS | jq -r '.username')
DB_PASS=$(echo $CREDS | jq -r '.password')

# Connect
PGPASSWORD=$DB_PASS psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME

Using Connection Script

If available, use the provided connection script:

./odin/scripts/connect-db.sh

Common Commands

Once connected to psql:

-- List databases
\l

-- List tables
\dt

-- Describe table structure
\d table_name

-- Show current database
SELECT current_database();

-- Show current user
SELECT current_user;

-- Exit
\q

Connection String Format

For applications that need a connection string:

postgresql://username:password@host:port/database

Or with SSL:

postgresql://username:password@host:port/database?sslmode=require

Python Example

import psycopg2
import json
import boto3

# Get credentials
secrets = boto3.client('secretsmanager', region_name='us-west-2')
secret = secrets.get_secret_value(SecretId='odin-db-connection')
creds = json.loads(secret['SecretString'])

# Connect
conn = psycopg2.connect(
    host=creds['host'],
    port=creds['port'],
    database=creds['database'],
    user=creds['username'],
    password=creds['password']
)

# Query
cursor = conn.cursor()
cursor.execute("SELECT * FROM my_table LIMIT 10")
rows = cursor.fetchall()

Security Notes

Never hardcode credentials - Always use Secrets Manager
Use IAM authentication when possible for automated systems
Database access is internal only - Must be on VPN or cluster network
Rotate credentials periodically using Secrets Manager rotation