# Postgres

## 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:password@db.host: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.
* `use_adbc` (optional) -> Enable Arrow Database Connectivity (ADBC) driver for high-performance data transfer. See [ADBC](https://docs.slingdata.io/connections/database-connections/adbc) for setup and details. (*v1.5.2+*)
* `adbc_uri` (optional) -> Override the automatically constructed ADBC connection URI when using `use_adbc=true`.

### 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`

{% hint style="warning" %}
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.
{% endhint %}

### Using `sling conns`

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

{% code overflow="wrap" %}

```bash
$ 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:mypass@host.ip: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 \
  user=my-sa@my-project.gserviceaccount.com \
  database=mydatabase \
  gcp_key_file=/path/to/credentials.json
```

{% endcode %}

### Environment Variable

See [here](https://docs.slingdata.io/sling-cli/environment#dot-env-file-.env.sling) to learn more about the `.env.sling` file.

{% code overflow="wrap" %}

```bash
export POSTGRES='postgresql://myuser:mypass@host.ip: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>" }'
```

{% endcode %}

### Sling Env File YAML

See [here](https://docs.slingdata.io/sling-cli/environment#sling-env-file-env.yaml) to learn more about the sling `env.yaml` file.

```yaml
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:mypass@host.ip:5432/mydatabase?sslmode=require"

  POSTGRES_CLOUDSQL:
    type: postgres
    gcp_instance: my-instance
    gcp_project: my-project
    gcp_region: us-central1
    user: my-sa@my-project.gserviceaccount.com
    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: my-sa@my-project.gserviceaccount.com
    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:

   ```sql
   CREATE USER sling WITH PASSWORD '<password>';
   ```
2. If you are planning to load data into this connection, grant the following privileges to that user:

   ```sql
   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.

   ```sql
   -- 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 <support@slingdata.io>, on [discord](https://discord.gg/q5xtaSNDvp) or open a Github Issue [here](https://github.com/slingdata-io/sling-cli/issues).