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 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 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:

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:

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.

ALTER ROLE orcabase_readonly SET statement_timeout = '30s';

Connect it

1. Start a new data source

   Go to Data Sources→ then 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.
• Safe parameters. Values you type into a query’s 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

More fixes are on Troubleshooting.