> For the complete documentation index, see [llms.txt](https://docs.getdot.ai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.getdot.ai/integrations/databases/postgres.md).

# Postgres

Using Neon? Follow the [Neon](/integrations/databases/neon.md) guide.

## Create a Group and a User

As a super user, execute the following SQL commands to create a group, a user assigned to that group, and permissions to access a system table.

```sql
CREATE USER dot_user PASSWORD '<something secret>';

CREATE GROUP dot_group;

ALTER GROUP dot_group ADD USER dot_user;

-- Grant Postgres' monitor role for meta data
GRANT pg_monitor TO dot_group;
```

## Grants Read Access to Data

Then for each schema `schema`, execute the following three commands to grant read-only access.

```sql
-- Grant usage on schema and select on current and future child tables
GRANT USAGE ON SCHEMA "schema" TO GROUP dot_group;
GRANT SELECT ON ALL TABLES IN SCHEMA "schema" TO GROUP dot_group;
ALTER DEFAULT PRIVILEGES IN SCHEMA "schema" GRANT SELECT ON TABLES TO GROUP dot_group;
```

**Note**: to programmatically generate these three queries for all schemas, you can use the following command. These commands still need to be executed.

```sql
SELECT 
    'GRANT USAGE ON SCHEMA "' || schema_name || '" TO GROUP dot_group;' || chr(10) ||
    'GRANT SELECT ON ALL TABLES IN SCHEMA "' || schema_name || '" TO GROUP dot_group;' || chr(10) ||
    'ALTER DEFAULT PRIVILEGES IN SCHEMA "' || schema_name || '" GRANT SELECT ON TABLES TO GROUP dot_group;' AS single_schema_statement
FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema', 'pg_catalog', 'pg_toast') AND schema_name NOT LIKE 'pg_temp%' AND schema_name NOT LIKE 'pg_toast_temp%';
```

## Allow Dot IPs

If your organization uses a firewall to manage Postgres access, Dot will only access your Postgres through the following IPs:

* `5.78.211.110`
* `178.105.217.177`

## SSL Mode

The connection dialog has an **SSL Mode** setting. New connections start on **Require**, which encrypts the connection but does not check the server's certificate. Connections created before this setting existed stay on **Prefer**, which encrypts when the server allows it and falls back to an unencrypted connection when it does not.

* **Verify Full**: encrypts, checks that a public certificate authority signed the server certificate, and checks that the certificate matches the database host name.
* **Verify CA Only**: encrypts and checks the certificate authority, but not the host name.
* **Require**: encrypts without checking the certificate. Use this for servers with a private or self-signed certificate.
* **Prefer**: encrypts when possible, otherwise connects without encryption.
* **Disable SSL**: never encrypts.

## Connect via SSH Tunnel

If your database is in a private network or behind a firewall, Dot can connect through an SSH bastion host instead of exposing it directly. In the connection dialog, enable **Connect via SSH Tunnel** and provide:

* **SSH Host**: your bastion / jump server
* **SSH Port**: `22` (default) or a custom port
* **SSH Username**: the SSH user
* **SSH Authentication**: SSH password or private key

Dot tunnels the database connection through the bastion, so the database itself never needs a public endpoint.