PostgreSQL Configuration

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

Connection Parameters

Required Fields

Field	Description	Example
Host	Database hostname	postgres.example.com
Port	PostgreSQL port (default: 5432)	5432
Database	Database name	myapp
Username	Database user	app_user
Password	User password (encrypted at rest)

Optional Fields

Field	Description
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:

Field	Example Value	Notes
Data source label	prod-postgres	Kebab-case unique identifier (used as both name and label)
Host	postgres.example.com	Database hostname
Port	5432	Default PostgreSQL port
Database	myapp	Database name
Username	app_user	Database user
Password	password	Encrypted at rest
Use SSL	Enabled	Recommended for production

Test Connection

When you create or test a PostgreSQL data source, the platform connects using the pg (node-postgres) driver and executes SELECT 1 to verify connectivity. The connection uses a 10-second timeout. If SSL is enabled, connections use rejectUnauthorized: false by default.

PostgreSQL-compatible databases (CockroachDB, Greenplum, pgvector, Supabase) use the same test connection logic.

Schema Discovery

PostgreSQL has full native schema discovery support. Clicking Refresh Metadata returns:

• Tables: All user tables (excluding pg_catalog and information_schema schemas)
• Schemas: All schemas (excluding pg_catalog, information_schema, pg_toast)
• Databases: All non-template databases
• Size: Database size in bytes (via pg_database_size)
• Row count: Approximate live row count (via pg_stat_user_tables)

Column-Level Metadata

You can fetch column details for individual tables, which returns:

• Column name, data type, and character maximum length
• Nullability
• Primary key and foreign key constraints
• Default values
• Table row count and total relation size

Usage in Workflows (STRONGLY_SERVICES)

When a PostgreSQL data source is attached to a workflow, credentials are injected via STRONGLY_SERVICES with all fields at the top level (not nested under a credentials key):

{
  "datasources": {
    "prod_postgres": {
      "type": "postgres",
      "name": "prod-postgres",
      "host": "postgres.example.com",
      "port": 5432,
      "database": "myapp",
      "username": "app_user",
      "password": "actual_password",
      "ssl": true,
      "connectionString": "postgresql://app_user:actual_password@postgres.example.com:5432/myapp"
    }
  }
}

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 (key is sanitized name: hyphens become underscores)
pg = datasources['prod_postgres']

# Connect using top-level credential fields
conn = psycopg2.connect(
    host=pg['host'],
    port=pg['port'],
    database=pg['database'],
    user=pg['username'],
    password=pg['password'],
    sslmode='require' if pg.get('ssl') else 'prefer'
)

# Or use the connection string directly
conn = psycopg2.connect(pg['connectionString'])

# 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 (key is sanitized name)
const pg = datasources['prod_postgres'];

// Create client using top-level fields
const client = new Client({
  host: pg.host,
  port: pg.port,
  database: pg.database,
  user: pg.username,
  password: pg.password,
  ssl: pg.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

Related Resources

• Data Sources Overview
• How to Connect Data Sources
• PostgreSQL Official Documentation