PostgreSQL

opensre integrations setup postgresql

POSTGRESQL_HOST=your-postgresql-host
POSTGRESQL_PORT=5432
POSTGRESQL_DATABASE=your-database
POSTGRESQL_USERNAME=opensre_readonly
POSTGRESQL_PASSWORD=your-password
POSTGRESQL_SSL_MODE=prefer   # prefer, require, or disable

{
  "version": 1,
  "integrations": [
    {
      "id": "postgresql-prod",
      "service": "postgresql",
      "status": "active",
      "credentials": {
        "host": "prod-primary.postgres.example.com",
        "port": 5432,
        "database": "application_db",
        "username": "opensre_readonly",
        "password": "your-password",
        "ssl_mode": "prefer"
      }
    }
  ]
}

CREATE USER opensre_readonly WITH PASSWORD 'secure-password';
GRANT pg_monitor TO opensre_readonly;
GRANT CONNECT ON DATABASE application_db TO opensre_readonly;
\c application_db
GRANT USAGE ON SCHEMA public TO opensre_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO opensre_readonly;

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

docker run -d --name postgres-dev -p 127.0.0.1:5433:5432 \
  -e POSTGRES_PASSWORD=verifypass \
  -e POSTGRES_DB=app_db \
  postgres:16 -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all

i=0
until docker exec postgres-dev pg_isready -U postgres > /dev/null 2>&1; do
  i=$((i + 1))
  [ "$i" -ge 30 ] && { echo "ERROR: PostgreSQL never became ready" >&2; exit 1; }
  sleep 2
done

docker exec postgres-dev psql -U postgres -d app_db -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
docker exec postgres-dev psql -U postgres -d app_db -c "
CREATE TABLE orders (id INT PRIMARY KEY, amount DECIMAL(10,2));
INSERT INTO orders VALUES (1, 42.50), (2, 17.00);
"
docker exec postgres-dev psql -U postgres -d app_db -c "SELECT pg_sleep(2);" > /dev/null

export POSTGRESQL_HOST=127.0.0.1
export POSTGRESQL_PORT=5433
export POSTGRESQL_DATABASE=app_db
export POSTGRESQL_USERNAME=postgres
export POSTGRESQL_PASSWORD=verifypass
export POSTGRESQL_SSL_MODE=disable

opensre integrations verify postgresql

SERVICE    │ SOURCE    │ STATUS   │ DETAIL
postgresql │ local env │ ✓ passed │ Connected to PostgreSQL 16.15
           │           │          │ target database: app_db.

export OPENSRE_DEMO_STORE_DIR="$(mktemp -d /tmp/opensre-postgresql-demo.XXXXXX)"
export OPENSRE_INTEGRATIONS_STORE_PATH="$OPENSRE_DEMO_STORE_DIR/integrations.json"

opensre

docker rm -f postgres-dev
rm -rf "$OPENSRE_DEMO_STORE_DIR"
unset OPENSRE_DEMO_STORE_DIR OPENSRE_INTEGRATIONS_STORE_PATH
unset POSTGRESQL_HOST POSTGRESQL_PORT POSTGRESQL_DATABASE
unset POSTGRESQL_USERNAME POSTGRESQL_PASSWORD POSTGRESQL_SSL_MODE

opensre integrations verify postgresql

Service: postgresql
Status: passed
Detail: Connected to PostgreSQL 16.1; target database: application_db