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

• 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:[e-mail]: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.

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:[e-mail]:5432/mydatabase?sslmode=require&role=<role>"

Environment Variable

export POSTGRES='postgresql://myuser:[e-mail]:5432/mydatabase?sslmode=require&role=<role>'
export POSTGRES='{ type: postgresql, 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:[e-mail]:5432/mydatabase?sslmode=require"

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 [e-mail], on discord or open a Github Issue here.