# PostgreSQL

> Connect your app’s PostgreSQL database with a read-only user. Queries run live, your tables are never copied, and the password is encrypted.

Source: https://tini.so/docs/data/postgresql

Connect the Postgres database behind your app, or any other PostgreSQL database. Orcabase queries it live, as a user you create for it, so every answer reflects your data as it is right now. Your tables stay where they are.

## Before you start

- The database’s **host**, **port** (usually `5432`) and **database name**.
- A **user and password** for Orcabase. We recommend creating a read-only user just for Orcabase (below).
- The database has to **accept connections from the internet**. If it only allows known IP addresses, [message us](https://linkedin.com/company/tinilab) for the address to allow.

Any PostgreSQL database you can reach over the internet works, whether you run it yourself or use a managed service. If Orcabase hosts your Postgres server, see [Hosted by Orcabase](https://tini.so/docs/data/hosted) instead: there’s nothing to set up.

## Create a read-only user

Orcabase never needs to change your data. If it connects as a user that can only read, nothing in Orcabase can change it, even by mistake. Run this as an admin user on your database, with your own password:

```sql
CREATE ROLE orcabase_readonly WITH LOGIN PASSWORD 'choose-a-long-random-password';
GRANT pg_read_all_data TO orcabase_readonly;
```

`pg_read_all_data` lets the user read every table, including ones you add later, and nothing else. On older Postgres versions, or to share only some schemas, grant access schema by schema instead:

```sql
CREATE ROLE orcabase_readonly WITH LOGIN PASSWORD 'choose-a-long-random-password';
GRANT CONNECT ON DATABASE your_database TO orcabase_readonly;
GRANT USAGE ON SCHEMA public TO orcabase_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO orcabase_readonly;
-- Tables your app creates later are readable too:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO orcabase_readonly;
```

Run the last line as the user that creates your app’s tables, since it applies to tables that user makes. Repeat the `USAGE`, `SELECT` and `DEFAULT PRIVILEGES` lines for each schema you want Orcabase to see.

> **Tip: A time limit on the database’s side**
>
> Orcabase stops waiting for a query after 30 seconds. To make sure your database stops working on it too, give the user its own limit:
>
> ```sql
> ALTER ROLE orcabase_readonly SET statement_timeout = '30s';
> ```

## Connect it

1. **Start a new data source**

   Go to **Data Sources → New data source** and pick **PostgreSQL**.

2. **Fill in the details**

   | Field | What to enter |
   | --- | --- |
   | Name | What your team sees, like “App database”. |
   | Host | The server’s address, like `db.example.com`. |
   | Port | `5432` unless your provider says otherwise. |
   | Database | The database name, not the server name. |
   | Username | The user you created, like `orcabase_readonly`. |
   | Password | That user’s password. It’s encrypted and never shown again. |

3. **Create it and test it**

   Click **Create data source**, open it, and click **Test connection**. You should see **Connected**.

## How Orcabase uses the connection

- **Live queries.** Explore, the SQL Editor, dashboards and the agent all query the database directly. The latest result of each saved query is kept so dashboards open instantly; your tables aren’t copied.
- **Encrypted in transit.** Orcabase uses an encrypted (TLS) connection whenever your server offers one.
- **Limits.** Each query returns at most 5,000 rows and stops after 30 seconds. See [Limits](https://tini.so/docs/limits).
- **Safe parameters.** Values you type into a query’s [parameters](https://tini.so/docs/queries#parameters) are sent separately from the SQL, never pasted into it.
- **Your password is write-only.** It’s encrypted when you save it and never sent back to the browser. When you edit the data source, leave the password blank to keep it.

## If the test fails

| The error mentions | What to check |
| --- | --- |
| timeout, connection refused, no route to host | The host and port, and that the database accepts connections from the internet (firewall, security group or IP allowlist). |
| password authentication failed | The username and password. Passwords are case-sensitive. |
| database “…” does not exist | The database name. It’s the database inside the server, often not the same as the server’s name. |
| no pg_hba.conf entry | Your server doesn’t allow this user to connect from outside. Allow it in your provider’s settings or pg_hba.conf. |
| permission denied for table | The user can’t read that table. Grant it SELECT, or use pg_read_all_data (above). |

More fixes are on [Troubleshooting](https://tini.so/docs/troubleshooting).

---

Previous: [Connect your data](https://tini.so/docs/data.md) · Next: [BigQuery](https://tini.so/docs/data/bigquery.md)

All docs: https://tini.so/llms.txt