Postgres

Connect & Ingest data from / to a Postgres database

Setup

The following credentials keys are accepted:

• host (required) -> The hostname / ip of the instance

• user (required) -> The username to access the instance

• database (required) -> The database name of the instance

• schema (optional) -> The default schema to use

• password (optional) -> The password to access the instance

• port (optional) -> The port of the instance. Default is 5432.

• role (optional) -> The role to access the instance

• statement_timeout (optional in v1.4.21+) -> The timeout to use for a postgres query, input as millisecond (10000 for 10 seconds).

• sslmode (optional) -> The sslmode of the instance (disable, allow, prefer, require, verify-ca or verify-full). Default is disable.

• ssh_tunnel (optional) -> The URL of the SSH server you would like to use as a tunnel (example ssh://user:[email protected]:22)

• ssh_private_key (optional) -> The private key to use to access a SSH server (raw string or path to file).

• ssh_passphrase (optional) -> The passphrase to use to access a SSH server.

Google Cloud SQL IAM Authentication (v1.4.25+)

For Google Cloud SQL PostgreSQL instances with IAM authentication enabled, you can use the following properties:

• gcp_instance (required) -> The Cloud SQL instance name

• gcp_project (required) -> The GCP project ID

• gcp_region (required) -> The GCP region (e.g., us-central1)

• user (required) -> The IAM user or service account email (will be converted to lowercase and trimmed)

• gcp_key_file (optional) -> Path to the GCP service account credentials JSON file

• gcp_key_body (optional) -> The GCP service account credentials JSON content as a string

• gcp_lazy_refresh (optional) -> Enable lazy refresh for serverless environments (true or false). Default is false.

• gcp_use_private_ip (optional) -> Use private IP for the connection (true or false). Default is false.

Authentication Methods (in priority order):

1. If gcp_key_body is provided, uses the JSON credentials directly

2. If gcp_key_file is provided, uses the credentials file

3. Otherwise, uses Application Default Credentials (ADC)

Important Notes:

• When using IAM authentication, do NOT provide a password - authentication is handled via IAM tokens

• The user field should match the user identifier in the Users panel of the Cloud SQL instance (e.g., my-sa@my-project).

• The service account must have the Cloud SQL Instance User role

• IAM authentication must be enabled on the Cloud SQL instance: gcloud sql instances patch INSTANCE --database-flags=cloudsql.iam_authentication=on

If you're getting error: FATAL: password authentication failed, this error returned from CloudSQL is misleading. It usually means the user is not found, or the user identifier provided is wrong. Make sure it matches what shows in the Cloud SQL Users panel (on GCP's website). Also ensure the IAM user is actually created. You must create a user even if providing a service account.

Using sling conns

Here are examples of setting a connection named POSTGRES. We must provide the type=postgres property:

$ sling conns set POSTGRES type=postgres host=<host> user=<user> database=<database> password=<password> port=<port> 

# OR use url
$ sling conns set POSTGRES url="postgresql://myuser:[email protected]:5432/mydatabase?sslmode=require&role=<role>"

# For Google Cloud SQL with IAM authentication
$ sling conns set POSTGRES type=postgres \
  gcp_instance=my-instance \
  gcp_project=my-project \
  gcp_region=us-central1 \
  [email protected] \
  database=mydatabase \
  gcp_key_file=/path/to/credentials.json

Environment Variable

export POSTGRES='postgresql://myuser:[email protected]:5432/mydatabase?sslmode=require&role=<role>'

export POSTGRES='{ type: postgres, user: "myuser", password: "mypass", host: "host.ip", port: 5432, database: "mydatabase", sslmode: "require", role: "<role>" }'

Sling Env File YAML

See here to learn more about the sling env.yaml file.

connections:
  POSTGRES:
    type: postgres
    host: <host>
    user: <user>
    password: <password>
    port: <port>
    database: <database>
    sslmode: <sslmode>
    schema: <schema>
    role: <role>

  POSTGRES_URL:
    url: "postgresql://myuser:[email protected]:5432/mydatabase?sslmode=require"

  POSTGRES_CLOUDSQL:
    type: postgres
    gcp_instance: my-instance
    gcp_project: my-project
    gcp_region: us-central1
    user: [email protected]
    database: mydatabase
    gcp_key_file: /path/to/credentials.json
    # gcp_lazy_refresh: true  # Enable for serverless environments
    # gcp_use_private_ip: true  # Enable for private IP connections

  POSTGRES_CLOUDSQL:
    type: postgres
    gcp_instance: my-instance
    gcp_project: my-project
    gcp_region: us-central1
    user: [email protected]
    database: mydatabase
    gcp_key_body: |
      { "type": "service_account", ... }

Database user creation

To allow Sling to access your database, we need to create a user with the proper privileges. Please follow the steps below:

1. Create a user sling (or whatever you prefer) by running:

   CREATE USER sling WITH PASSWORD '<password>';

2. If you are planning to load data into this connection, grant the following privileges to that user:

   GRANT CREATE ON DATABASE <database_name> TO sling;

3. If you are planning to extract data from this connection, you need to give permission to read the tables you'd like Sling to extract.

   -- Need this to read table & column names 
   GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO sling;
   GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO sling;
   
   -- run this to grant SELECT permission to all tables in schema `marketing` to user sling
   GRANT SELECT ON ALL TABLES IN SCHEMA marketing TO sling;

If you are facing issues connecting, please reach out to us at [email protected], on discord or open a Github Issue here.