Skip to main content

PostgreSQL Configuration

Connect to PostgreSQL databases for transactional data, structured queries, and OLTP systems.

Connection Parameters​

Required Fields​

  • Host: Database hostname (e.g., postgres.example.com)
  • Port: 5432 (default)
  • Database: Database name
  • Username: Database user
  • Password: User password (encrypted at rest)

Optional Fields​

  • Use SSL: Enable for encrypted connections (recommended for production)

Connection String Format​

postgresql://user:***@host:5432/database

Configuration Example​

When creating a PostgreSQL data source, provide the following information:

FieldExample ValueNotes
Nameprod-postgresUnique identifier
LabelProduction PostgreSQLDisplay name
Hostpostgres.example.comDatabase hostname
Port5432Default PostgreSQL port
DatabasemyappDatabase name
Usernameapp_userDatabase user
Password••••••••Encrypted at rest
Use SSL✓ EnabledRecommended

Usage in Applications​

Python Example​

import os, json
import psycopg2

# Parse STRONGLY_SERVICES environment variable
services = json.loads(os.getenv('STRONGLY_SERVICES', '{}'))
datasources = services.get('datasources', {})

# Get PostgreSQL data source
pg_config = datasources['prod-postgres']

# Connect using credentials
conn = psycopg2.connect(
    host=pg_config['credentials']['host'],
    port=pg_config['credentials']['port'],
    database=pg_config['credentials']['database'],
    user=pg_config['credentials']['username'],
    password=pg_config['credentials']['password'],
    sslmode='require' if pg_config['credentials'].get('ssl') else 'prefer'
)

# Execute queries
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE active = true")
users = cursor.fetchall()

Node.js Example​

const { Client } = require('pg');

// Parse STRONGLY_SERVICES environment variable
const services = JSON.parse(process.env.STRONGLY_SERVICES || '{}');
const datasources = services.datasources || {};

// Get PostgreSQL data source
const pgConfig = datasources['prod-postgres'];

// Create client
const client = new Client({
  host: pgConfig.credentials.host,
  port: pgConfig.credentials.port,
  database: pgConfig.credentials.database,
  user: pgConfig.credentials.username,
  password: pgConfig.credentials.password,
  ssl: pgConfig.credentials.ssl ? { rejectUnauthorized: false } : false
});

// Connect and query
await client.connect();
const result = await client.query('SELECT * FROM users WHERE active = true');
const users = result.rows;

SSL/TLS Configuration​

Enabling SSL​

To enable SSL for encrypted connections:

  1. Check the Use SSL checkbox when configuring the data source
  2. Ensure your PostgreSQL server is configured to accept SSL connections
  3. Verify certificate validity if using self-signed certificates

SSL Modes​

PostgreSQL supports multiple SSL modes:

  • prefer (default): Use SSL if available, otherwise use unencrypted
  • require: Always use SSL (recommended for production)
  • verify-ca: Verify server certificate
  • verify-full: Verify server certificate and hostname

Common Issues​

Connection Refused​

  • Verify the host and port are correct
  • Check firewall rules allow connections
  • Ensure PostgreSQL is listening on the correct interface

Authentication Failed​

  • Verify username and password
  • Check pg_hba.conf allows connections from the platform's IP
  • Ensure user has necessary database permissions

Database Not Found​

  • Verify the database name is correct
  • Check if the database exists: \l in psql
  • Ensure user has CONNECT privilege on the database

Best Practices​

  1. Use SSL in Production: Always enable SSL for production databases
  2. Least Privilege: Create dedicated users with minimal required permissions
  3. Connection Pooling: Use connection pooling in your application for better performance
  4. Monitor Connections: Track connection usage and monitor for connection leaks
  5. Regular Testing: Periodically test connections to ensure they remain valid