PostgreSQL Destination

Connect PostgreSQL as a destination to load data from your Supaflow sources. Supaflow creates and manages its own schema and tables in the target database, writes rows, and adds columns as your source schema evolves -- so the destination user needs privileges to create and write objects, not the read-only access a PostgreSQL source uses. Works with self-managed PostgreSQL and managed services such as Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL, and Supabase.

Prerequisites

Before you begin, ensure you have:

• A PostgreSQL database (version 9.6 or higher)
• Network connectivity between Supaflow and your PostgreSQL server
• A database user that can create a schema and tables and write data (see below) -- a read-only user is not sufficient for a destination
• The pgcrypto extension available in the target database
• Firewall configured to allow connections from Supaflow IP

If your database has IP restrictions, add this IP to your pg_hba.conf (or your provider's allowed networks):

18.214.240.61

Create Destination User

Create a dedicated user for Supaflow and let it create and own the objects it loads into. Because the user owns the tables it creates, it can evolve and replace them without extra grants.

CREATE USER supaflow_writer PASSWORD 'your_secure_password';

-- Allow Supaflow to create its target schema in the database
GRANT CREATE ON DATABASE your_database TO supaflow_writer;

-- pgcrypto functions are looked up in the public schema
GRANT USAGE ON SCHEMA public TO supaflow_writer;

If you prefer to create the target schema yourself instead of granting CREATE ON DATABASE, grant the user rights on that schema:

GRANT USAGE, CREATE ON SCHEMA your_schema TO supaflow_writer;

pgcrypto: Supaflow uses the pgcrypto extension. Install it once as an administrator if your platform restricts extension creation:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Replace your_database, your_schema, and the password with your actual values.

Configuration

Step 1: Connection

Database Host*

Database server IP or hostname
Example: db.example.com or 192.168.1.100

Database Port*

Port on which PostgreSQL is listening
Default: 5432

Database Name*

Name of the target database to load into
Example: analytics_db

Database Username*

Username for authentication
Example: supaflow_writer

Database Password*

Password for the database user
Stored encrypted

---

Step 2: Advanced Settings (Optional)

Noop Query

Query to test connectivity without returning data
Default: SELECT 1

SSL Mode

SSL connection security level
Options: disable, allow, prefer (default), require, verify-ca, verify-full
Recommended: require or higher for production

JDBC Connection Properties

Additional JDBC connection parameters
Format: key=value pairs separated by semicolons
Example: connectTimeout=30;socketTimeout=30

---

Step 3: Test & Save

After configuring all required properties, click Test & Save to verify the connection and save the destination.

Schema Evolution

Supaflow creates the target schema and tables on the first load and keeps them in step with your source:

• New columns in the source are added to the destination table
• New tables are created when you add objects to the pipeline
• Type widening is applied when a source column's type grows
• Removed columns are kept in the destination; existing data is not dropped

Troubleshooting

Common issues and their solutions:

Insufficient permissions

Problem:

• "permission denied for database" or "permission denied for schema"
• The connection test passes but loads fail with a permission error

Solutions:

1. Grant the user rights to create its objects. A read-only user (only SELECT) cannot create a schema or tables:

   GRANT CREATE ON DATABASE your_database TO supaflow_writer;
   GRANT USAGE ON SCHEMA public TO supaflow_writer;

   Or, if you pre-create the schema:

   GRANT USAGE, CREATE ON SCHEMA your_schema TO supaflow_writer;

2. Check the grant:

   SELECT has_database_privilege('supaflow_writer', 'your_database', 'CREATE');

---

pgcrypto not available

Problem:

• An error mentioning pgcrypto or a missing function such as digest
• Loads fail right after connecting

Solutions:

1. Install the extension as an administrator:

   CREATE EXTENSION IF NOT EXISTS pgcrypto;

2. Managed services: on RDS, Cloud SQL, Azure, or Supabase you may need to enable pgcrypto from the provider's extensions list.

---

Connection refused

Problem:

• Cannot connect to PostgreSQL server
• "Connection refused" error or network timeout

Solutions:

1. Verify firewall rules:
   • Add Supaflow IP (18.214.240.61) to allowed IPs
   • Check pg_hba.conf, e.g. host all supaflow_writer 18.214.240.61/32 md5
2. Check listen_addresses in postgresql.conf allows external connections, then reload PostgreSQL.

---

Authentication failed

Problem:

• "password authentication failed"
• "no pg_hba.conf entry"

Solutions:

1. Verify the username and password.
2. Add a pg_hba.conf entry for the Supaflow IP and reload PostgreSQL.

---

SSL connection errors

Problem:

• "SSL connection failed" or certificate verification errors

Solutions:

1. Adjust SSL Mode -- try require; use prefer if the server's SSL setup is incomplete.
2. Self-signed certificates: use require (skips verification); avoid verify-ca / verify-full unless you have a trusted CA chain.

Support

Need help? Contact us at support@supa-flow.io