PostgreSQL Connection | DataKnits Knowledge Hub

PostgreSQL

On-premises PostgreSQL and cloud-managed instances (Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL). JDBC-based connectivity with metadata browsing, data preview, and read/write pipeline support.

On this page

Connection Fields
Authentication Methods
SSL / TLS
SSH Tunneling
RDS IAM Authentication
Write Modes
Known Limitations

Connection Fields

Field	Required	Default	Description
Display Name	Yes	—	A human-readable label for this connection, shown across the platform.
Host	Yes	—	Hostname or IP address of the PostgreSQL server. For RDS, use the RDS endpoint.
Port	Yes	5432	PostgreSQL server port. Change if your server uses a non-default port.
Database	Yes	—	The default database to connect to. Used as the initial catalog for metadata browsing.
Username	Yes*	—	Database username. Not required when using RDS IAM authentication.
Password	Yes*	—	Database password. Stored encrypted. Not required for RDS IAM auth.
SSL Mode	No	`prefer`	See SSL / TLS section below. Recommended: `require` for cloud-hosted instances.
Connection Timeout (s)	No	30	Number of seconds before a connection attempt times out.
Additional JDBC Parameters	No	—	Optional key=value pairs appended to the JDBC URL (e.g., `currentSchema=myschema`). Separate multiple parameters with `&`.

Authentication Methods

DataKnits supports three authentication methods for PostgreSQL connections:

Username / Password — Standard PostgreSQL authentication. The most common method for on-premises and self-managed instances.
SSH Tunnel + Username/Password — Routes the JDBC connection through an SSH tunnel. Required for databases in private subnets with no direct inbound connectivity. See SSH Tunneling.
Amazon RDS IAM Authentication — For PostgreSQL on Amazon RDS or Aurora PostgreSQL. Uses a short-lived IAM token instead of a static password. See RDS IAM Authentication.

SSL / TLS

The SSL Mode field controls whether and how SSL is enforced on the JDBC connection.

Mode	Description	When to Use
`disable`	No SSL. All traffic is unencrypted.	Internal development environments only. Not recommended for production.
`allow`	Use SSL if the server supports it; fall back to unencrypted if not.	Legacy environments where SSL availability is uncertain.
`prefer`	Prefer SSL but allow unencrypted fallback.	Default. Provides encryption where available.
`require`	SSL is required. Fails if the server does not support SSL. Server certificate is not validated.	Cloud-hosted PostgreSQL instances where SSL is always available.
`verify-ca`	SSL required. Server certificate is validated against a trusted CA. Does not verify hostname.	Private cloud or on-premises with internal CA.
`verify-full`	SSL required. Server certificate validated and hostname must match the certificate CN/SAN.	Highest security. Required for PCI-DSS and HIPAA compliant deployments.

SSH Tunneling

When a PostgreSQL instance is in a private subnet with no direct inbound access, DataKnits can route the JDBC connection through an SSH bastion host.

Field	Required	Description
SSH Host	Yes	Hostname or IP of the SSH bastion server.
SSH Port	Yes	SSH port. Default: 22.
SSH Username	Yes	Linux username on the bastion host.
SSH Private Key (PEM)	Yes*	PEM-encoded RSA or Ed25519 private key. Required if not using password auth for SSH.
SSH Password	Yes*	Bastion host password. Use only if private key is not available.

When SSH tunnel is enabled, DataKnits opens an SSH connection to the bastion host and forwards the JDBC port over the tunnel. The PostgreSQL Host field should be the address of the database as reachable from the bastion host (e.g., 10.0.1.45 or an internal DNS name), not the public address.

RDS IAM Authentication

Amazon RDS PostgreSQL and Aurora PostgreSQL support IAM-based authentication. Instead of a static password, the platform generates a short-lived authentication token using AWS STS.

To enable RDS IAM authentication:

Enable IAM database authentication on the RDS instance in the AWS Console.
Create a database user with the rds_iam role: GRANT rds_iam TO your_user;
Attach an IAM policy to the role or user that DataKnits assumes, granting rds-db:connect on the target instance and username.
In DataKnits, select Auth Method: IAM Role, enter the Role ARN, and leave the Password field empty.

RDS IAM tokens expire after 15 minutes. DataKnits re-generates the token automatically before each pipeline execution — no manual rotation is needed.

Write Modes

When PostgreSQL is configured as a pipeline target, the following write modes are available:

Mode	Behaviour
Overwrite	Truncates the target table and writes all incoming rows. Equivalent to `TRUNCATE + INSERT`.
Append	Inserts all incoming rows without modifying existing data.
Merge (Upsert)	Matches rows by a user-defined key. Updates matching rows; inserts new rows. Generates `INSERT ... ON CONFLICT (...) DO UPDATE SET ...`.
Create if Not Exists	Creates the table using the DataFrame schema if it does not exist, then appends data.

Known Limitations

Schema changes on the target table (adding or removing columns) are not applied automatically. The target table schema must match the incoming DataFrame schema, or the pipeline will fail at runtime.
Stored procedures and user-defined functions are not callable from DataKnits pipeline nodes. Use Pre/Post SQL fields to call procedures before or after the main load.
The currentSchema JDBC parameter sets the search path for the connection but does not prevent DataKnits from browsing tables in the public schema unless the PostgreSQL user's search path is configured accordingly.
For very large tables, the metadata browser loads column information from information_schema. Tables with a very high column count (1000+) may be slow to load in the metadata panel.