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 and Apps (STRONGLY_SERVICES)

When a PostgreSQL data source is attached to a workflow or app, its decrypted connection details are injected via STRONGLY_SERVICES under services.datasources.postgres (one entry per configured connection):

{
  "services": {
    "datasources": {
      "postgres": [
        {
          "id": "abc123",
          "name": "prod-postgres",
          "type": "postgres",
          "category": "data-source",
          "status": "connected",
          "connection": {
            "host": "postgres.example.com",
            "port": 5432,
            "database": "myapp",
            "ssl": { "enabled": true }
          },
          "auth": {
            "method": "username_password",
            "credentials": {
              "username": "app_user",
              "password": "<password>"
            }
          }
        }
      ]
    }
  }
}

Python Example

import os, json
import psycopg2

# Parse STRONGLY_SERVICES environment variable
services = json.loads(os.environ['STRONGLY_SERVICES'])

# Pick your PostgreSQL data source by name (its label)
pg = next(
    d for d in services['services']['datasources']['postgres']
    if d['name'] == 'prod-postgres'
)

# Connect using the connection and auth sections
conn = psycopg2.connect(
    host=pg['connection']['host'],
    port=pg['connection']['port'],
    database=pg['connection']['database'],
    user=pg['auth']['credentials']['username'],
    password=pg['auth']['credentials']['password']
)

# 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 pg = services.services.datasources.postgres
  .find(d => d.name === 'prod-postgres');

// Create client using the connection and auth sections
const client = new Client({
  host: pg.connection.host,
  port: pg.connection.port,
  database: pg.connection.database,
  user: pg.auth.credentials.username,
  password: pg.auth.credentials.password,
  ssl: pg.connection.ssl && pg.connection.ssl.enabled
    ? { 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