Select spec version:

PostgreSQL Destination Config Reference

Complete Rudder CLI reference for the PostgreSQL destination config keys, SSL modes, object storage providers, sync scheduling, and secrets.
Available Plans
  • free
  • growth
  • enterprise

  •   13 minute read  

PostgreSQL is a warehouse destination. RudderStack stages events as files in object storage, then loads them into a PostgreSQL database on a schedule.

In a PostgreSQL destination spec:

  • type: postgres
  • definition_version: 1

Sample configuration

yaml
version: rudder/v1
kind: destination
metadata:
  name: postgres-prod
spec:
  id: postgres-prod
  display_name: PostgreSQL Production
  type: postgres
  definition_version: 1
  enabled: true
  config:
    host: db.example.com
    port: "5432"
    database: analytics
    user: "{{ .PG_USER }}"
    password: "{{ .PG_PASSWORD }}"
    namespace: rudder_events

    ssl_mode: require
    use_ssh: false

    use_rudder_storage: false
    bucket_provider: S3
    bucket_name: acme-postgres-staging
    s3:
      role_based_auth: true
      iam_role_arn: "arn:aws:iam::123456789012:role/RudderStackS3"
    cleanup_object_storage_files: false

    sync_frequency: "180"
    sync_start_at: "01:00"
    exclude_window:
      start_time: "02:00"
      end_time: "03:00"

    prefer_append: true
    skip_users_table: true
    skip_tracks_table: false
    json_paths: context.traits,properties.metadata

    underscore_divide_numbers: false
    allow_users_context_traits: false

    connection_mode:
      web: cloud
      cloud: cloud
    consent_management:
      web:
        - provider: oneTrust
          consents:
            - analytics

The above example stages files in your own S3 bucket through an IAM role, so it carries no access keys, and uses require, so it needs no certificates. Which keys apply depends on several switches — see Key dependencies.

Config keys

config accepts only the keys listed below. The shared config key rules cover unknown keys, defaults, and immutability.

PostgreSQL’s string keys don’t accept {{ path || fallback }} templates as a way around their constraints — a template is measured as literal text against the same rule. Use {{ .VAR }} substitution for the secret keys.

Key dependencies

ssl_mode, use_ssh, use_rudder_storage, and bucket_provider decide which other keys apply. Rudder CLI enforces every requirement below; a key outside its branch is accepted and ignored.

KeyRequired when
client_key, client_cert, server_cassl_mode is verify-ca
ssh (all four fields)use_ssh is true
bucket_provideruse_rudder_storage is false
bucket_nameuse_rudder_storage is false and bucket_provider isn’t AZURE_BLOB
s3.iam_role_arnbucket_provider is S3 and s3.role_based_auth is true
access_key_id, s3.access_keybucket_provider is S3 and s3.role_based_auth isn’t true
gcs.credentialsbucket_provider is GCS
azure.account_name, azure.container_namebucket_provider is AZURE_BLOB
azure.account_keybucket_provider is AZURE_BLOB and azure.use_sas_tokens isn’t true
azure.sas_tokenbucket_provider is AZURE_BLOB and azure.use_sas_tokens is true
access_key_id, minio.end_point, minio.secret_access_key, minio.use_sslbucket_provider is MINIO

Every storage requirement also assumes use_rudder_storage is false.

Three provider settings behave differently from the dashboard, which defaults them:

  • An omitted s3.role_based_auth counts as false, so Rudder CLI asks for access keys. Write role_based_auth: true to use s3.iam_role_arn.
  • An omitted azure.use_sas_tokens counts as false, so Rudder CLI asks for azure.account_key.
  • minio.use_ssl must be written out. The dashboard defaults it to true; a spec that omits it fails validation.

Connection

host

Required

Type: string

Description: Hostname of the PostgreSQL server.

Notes:

  • 1 to 200 characters, and must not contain line breaks.
  • An ngrok.io host is rejected.

port

Required

Type: string

Description: Port of the PostgreSQL server, written as a string — "5432", not 5432.

Notes:

  • At most 100 characters, and must not contain line breaks.

database

Required

Type: string

Description: Name of the database RudderStack loads data into.

Notes:

  • At most 100 characters, and must not contain line breaks.

user

RequiredSecret

Type: string

Description: Database user with the permissions RudderStack needs to create schemas and load tables.

Notes:

  • At most 100 characters, and must not contain line breaks.
See Setting user permissions in PostgreSQL.

password

RequiredSecret

Type: string

Description: Password for user.

Supply it as a {{ .VAR }} reference rather than a literal — see Secrets.

namespace

Type: string

Description: Schema RudderStack creates its tables in. Defaults to the source name when omitted.

Notes:

  • At most 64 characters, and must not start with pg_ in any capitalization.
  • The PostgreSQL setup guide says the namespace can’t be changed later, so treat it as fixed. It isn’t marked immutable in the API schema, so a change isn’t rejected.

TLS

ssl_mode

Required

Type: string

Description: How RudderStack secures its connection to the server.

Notes:

  • disable — no encryption.
  • require — encrypted, without verifying the server.
  • verify-ca — encrypted, and the server’s certificate is checked against server_ca. Needs all three certificate keys below.
See SSL modes.

client_key

RequiredSecret

Type: string

Description: Contents of the client key PEM file.

Notes:

  • Required when ssl_mode is verify-ca. Leave it unset otherwise.

client_cert

RequiredSecret

Type: string

Description: Contents of the client certificate PEM file.

Notes:

  • Required when ssl_mode is verify-ca. Leave it unset otherwise.

server_ca

Required

Type: string

Description: Contents of the server CA PEM file.

Notes:

  • Required when ssl_mode is verify-ca. Leave it unset otherwise.
See Using verify-ca to configure PostgreSQL destination for how to obtain the three files.

SSH tunnel

SSH tunneling is available on the Enterprise plan. See SSH connection.

use_ssh

Type: boolean

Default value: false

Description: Connect to PostgreSQL through an SSH tunnel via a bastion host.

ssh

Required

Type: object

Description: Bastion host connection details.

Notes:

  • Required when use_ssh is true, with all four fields. Leave it unset otherwise.
  • host — IP address or hostname of the bastion host. At most 100 characters.
  • port — SSH port of the bastion host, as a string. At most 100 characters.
  • user — user RudderStack logs in to the bastion host as. At most 100 characters. Secret — see Secrets.
  • public_key — the public key RudderStack generates for this destination. At most 1000 characters.
yaml
use_ssh: true
ssh:
  host: 203.0.113.10
  port: "22"
  user: "{{ .PG_SSH_USER }}"
  public_key: "ssh-rsa AAAA..."

RudderStack holds the private key; add public_key to the bastion host’s authorized_keys. The key comes from RudderStack, so the practical route is to enable SSH on the destination in the dashboard, then import it to pick up the value.

Object storage

use_rudder_storage decides whether RudderStack stages files in its own storage or in yours. With your own, bucket_provider picks the provider, and only that provider’s block applies.

use_rudder_storage

Required

Type: boolean

Description: Stage files in RudderStack-managed object storage instead of your own.

Notes:

  • Available only on RudderStack-hosted data planes. Self-hosted data planes must set false and configure a provider.
  • The dashboard defaults this field to false. Rudder CLI requires it explicitly.

bucket_provider

Required

Type: string

Description: Object storage provider for staging files.

Notes:

  • Required when use_rudder_storage is false. Leave it unset otherwise.
  • One of S3, GCS, AZURE_BLOB, or MINIO.

bucket_name

Required

Type: string

Description: Name of the staging bucket. The bucket must already exist. Azure uses azure.container_name instead.

Notes:

  • Required when use_rudder_storage is false and bucket_provider isn’t AZURE_BLOB. Leave it unset otherwise.
  • 3 to 63 characters, whichever provider you use, and must not contain line breaks.
  • For S3: lowercase letters, digits, dots, and hyphens; not starting with xn--, no consecutive dots, not an IP address.
  • For GCS: lowercase letters, digits, dots, hyphens, and underscores; not starting with goog, not containing google, no consecutive dots, not an IP address.
  • For MINIO: lowercase letters, digits, dots, and hyphens; not an IP address.

access_key_id

RequiredSecret

Type: string

Description: Access key ID for S3 or MinIO. It sits at the top level because both providers use it.

Notes:

  • Required when bucket_provider is MINIO, or S3 with s3.role_based_auth not true. Leave it unset otherwise.
  • At most 100 characters, and must not contain line breaks.

cleanup_object_storage_files

Type: boolean

Default value: false

Description: Delete staged files after a sync completes successfully.

Notes:

  • Applies when use_rudder_storage is false.

s3

Required

Type: object

Description: Amazon S3 settings.

Notes:

  • Required when use_rudder_storage is false and bucket_provider is S3. Leave it unset otherwise.
  • role_based_auth — boolean. true to use iam_role_arn; omitted or false to use access_key_id and access_key.
  • iam_role_arn — ARN of the IAM role RudderStack assumes. Required when role_based_auth is true. At most 100 characters.
  • access_key — AWS secret access key matching access_key_id. Required when role_based_auth isn’t true. At most 100 characters. Secret.
yaml
bucket_provider: S3
bucket_name: acme-postgres-staging
s3:
  role_based_auth: true
  iam_role_arn: "arn:aws:iam::123456789012:role/RudderStackS3"

gcs

Required

Type: object

Description: Google Cloud Storage settings.

Notes:

  • Required when use_rudder_storage is false and bucket_provider is GCS. Leave it unset otherwise.
  • credentials — contents of the JSON key file for a service account that can create objects in the bucket. Required. Secret.
yaml
bucket_provider: GCS
bucket_name: acme-postgres-staging
gcs:
  credentials: '{{ .PG_GCS_CREDENTIALS }}'

azure

Required

Type: object

Description: Azure Blob Storage settings.

Notes:

  • Required when use_rudder_storage is false and bucket_provider is AZURE_BLOB. Leave it unset otherwise.
  • account_name — storage account name. Required. At most 100 characters.
  • container_name — staging container, which must already exist. Required. 3 to 63 characters of lowercase letters, digits, and single hyphens.
  • use_sas_tokens — boolean. true to authenticate with sas_token; omitted or false to use account_key.
  • account_key — storage account key. Required when use_sas_tokens isn’t true. At most 100 characters. Secret.
  • sas_token — shared access signature token. Required when use_sas_tokens is true. Secret.
yaml
bucket_provider: AZURE_BLOB
azure:
  account_name: acmestorage
  container_name: rudder-staging
  use_sas_tokens: true
  sas_token: "{{ .PG_AZURE_SAS_TOKEN }}"

minio

Required

Type: object

Description: MinIO settings. The access key ID goes in the top-level access_key_id.

Notes:

  • Required when use_rudder_storage is false and bucket_provider is MINIO. Leave it unset otherwise.
  • end_point — MinIO server endpoint. Required. 1 to 100 characters; an ngrok.io endpoint is rejected.
  • secret_access_key — MinIO secret access key. Required. At most 100 characters. Secret.
  • use_ssl — boolean. Connect to MinIO over TLS. Required, even when true.
yaml
bucket_provider: MINIO
bucket_name: rudder-staging
access_key_id: "{{ .MINIO_ACCESS_KEY_ID }}"
minio:
  end_point: minio.example.com:9000
  secret_access_key: "{{ .MINIO_SECRET_ACCESS_KEY }}"
  use_ssl: true

Sync scheduling

sync_frequency

Required

Type: string

Description: How often RudderStack syncs staged events into PostgreSQL, in minutes. Written as a string, not a number.

Notes:

  • One of 5, 10, 15, 30, 60, 180, 360, 720, or 1440.
  • The dashboard defaults this field to 180. Rudder CLI requires it explicitly.
  • A spec that omits this key fails validation.

sync_start_at

Type: string

Description: Time of day, in UTC, that anchors the sync schedule. Subsequent syncs are computed from it at sync_frequency intervals. Written as HH:MM.

Notes:

  • Not validated locally: any string is accepted, and a value the scheduler can’t parse silently yields no scheduled times.

exclude_window

Type: object

Description: Daily window, in UTC, during which RudderStack doesn’t sync. Omit the block entirely to sync around the clock.

Notes:

  • When present, both fields are required: start_time and end_time, each HH:MM.
  • Neither field’s format is validated locally.

Table behavior

prefer_append

Type: boolean

Default value: true

Description: Append incoming events to existing tables. Set it to false to merge instead, which guarantees no duplicates at the cost of noticeably longer syncs. This is what the dashboard calls Warehouse Append.

skip_users_table

Type: boolean

Default value: true

Description: Send identify events only to the identifies table, skipping the users table. The users table holds one row per unique user and is maintained with a merge, which can add significant time to each sync.

skip_tracks_table

Type: boolean

Default value: false

Description: Skip sending events to the tracks table. Per-event tables are unaffected.

json_paths

Type: string

Description: Comma-separated dot-notation paths whose values are stored as JSON columns instead of being flattened. Applies to every track event sent to this destination.

Notes:

  • Not validated locally.
See JSON Column Support.

Legacy column naming

Both keys below preserve the column naming of destinations created before the behavior changed. Leave them at their defaults on a new destination. Neither can be changed once the destination exists — the API rejects the update.

underscore_divide_numbers

ImmutableInternal

Type: boolean

Default value: false

Description: When false, numeric suffixes in column names are preserved: v3 stays v3 rather than being split into v_3.

allow_users_context_traits

ImmutableInternal

Type: boolean

Default value: false

Description: When false, context.traits.* fields aren’t promoted to top-level traits and are stored only as context_traits_* columns.

Per-source keys

Both keys are objects keyed by the local source type — the tokens listed under Source types. A key naming a source type this destination doesn’t support fails validation.

connection_mode

Type: object

Description: Maps each source type you connect to the mode its events reach PostgreSQL in, using the modes in Source types.

Notes:

yaml
connection_mode:
  web: cloud
  cloud: cloud

Source types

PostgreSQL accepts events from these source types in the mentioned connection modes:

Source typeConnection mode
androidcloud
android_kotlincloud
ioscloud
ios_swiftcloud
webcloud
unitycloud
cloudcloud
react_nativecloud
fluttercloud
cordovacloud

Every source type is cloud only — events reach the warehouse from RudderStack’s servers, never in device mode.

The dashboard additionally offers PostgreSQL to AMP, Shopify, and cloud app sources. Rudder CLI doesn’t manage those connections, so amp, shopify, and cloud_source are invalid here.

Connect a source

An event stream connection to this destination is checked against two rules at validate time.

The source’s type must be supported. A source’s type resolves to one of the tokens above before the check — a JavaScript source resolves to web, and webhook and server-side SDK sources resolve to cloud. Every type an event stream source can resolve to is supported here, so this check always passes.

The destination config must carry a connection_mode entry for that source type. This lives on the destination spec, not on the connection spec. Without it:

destination 'postgres-prod' config has no 'connection_mode' entry for source type 'web'

PostgreSQL needs no additional config keys to connect a source of any type.

Secrets

Rudder CLI treats eleven keys as secrets: user, password, client_key, client_cert, access_key_id, s3.access_key, gcs.credentials, azure.account_key, azure.sas_token, minio.secret_access_key, and ssh.user. Write each one you use as a {{ .VAR }} reference and supply the value at apply time:

yaml
config:
  user: "{{ .PG_USER }}"
  password: "{{ .PG_PASSWORD }}"
bash
export RUDDER_PG_USER="rudder"
export RUDDER_PG_PASSWORD="..."
rudder-cli apply

# or
rudder-cli apply --var-file secrets.vars.yaml

Note that:

  • gcs.credentials is JSON, so write its reference in single quotes: credentials: '{{ .PG_GCS_CREDENTIALS }}'.
  • client_key and client_cert span several lines. In a var file, write each as a YAML literal block. A reference that is the whole quoted value, such as '{{ .PG_CLIENT_KEY }}', keeps the line breaks in either quote style, including the single-quoted references rudder-cli import writes. An environment variable can carry a file’s contents directly: export RUDDER_PG_CLIENT_KEY="$(cat client-key.pem)".
  • server_ca isn’t a secret — a CA certificate is public by design.
  • rudder-cli import writes only the secrets the API returns, as {{ .VAR }} references, and leaves the others out. Add any secret key it leaves out, then supply the variables before you apply — see Secrets.

See How to Use Variable Substitution in Rudder CLI.

Changes by release

VersionChange
v0.29.0Breaking: A null config value, written as key: null, key: ~, or key: with no value, fails validate with '<key>' must not be null; set a value or remove the key. Earlier releases accepted it: a top-level null planned an update on every apply, and a null inside a list item failed at the API.
What to do: Remove the key to leave it unset, or give it a value.
v0.29.0Breaking: An empty string, "", on a key that takes one of a fixed set of values fails validate with '<key>' must be one of [...], unless "" is one of the key’s values. Earlier releases accepted it, and the API rejected it at apply.
What to do: Remove the key to leave it unset, or set one of the listed values.
v0.29.0Changed: validate warns when a secret key holds anything but one {{ .VAR }} reference with no default, and when one variable fills two secret keys. It also masks secret values in the lines it prints.
v0.29.0Changed: rudder-cli import writes each secret reference single-quoted, as '{{ .VAR }}', so a value containing double quotes, like a JSON key file, substitutes as a string. A single quote in a value that fills such a reference is written as ''.
What to do: In a spec imported by an earlier release, quote each reference to a JSON value by hand: '{{ .VAR }}'.
v0.29.0Changed: A multi-line value, like a PEM key, keeps its line breaks when it fills a quoted {{ .VAR }} reference, in either quote style. Earlier releases folded the line breaks into spaces, so the destination stored a broken key unless the value was written with \n in a double-quoted reference. That form still works.
v0.29.0Changed: validate reports a value of the wrong type once per key, such as 'email' must be a string, instead of one raw decoding error.

For changes across every destination type, see Destination Type Changes by Release.

See more

Questions? Let's figure it out together.

Join the RudderStack Slack community to connect with other users, customers, and the RudderStack team — or reach out for direct support.

Slack community Join now Email support Reach out