Skip to content

Connecting to PostgreSQL

8 min read · Updated 4 Nov 2026

This guide connects Astrato to a PostgreSQL database. When you finish, the connection appears in Data & AI → Data Connections and you can build a semantic layer on it. The form is the one described in Data connections; this page adds what is specific to PostgreSQL. It describes the current lobby, although it keeps its Help Center address.

Before you start: have these ready.

  • Astrato permission. You need a workspace role that can create data connections: Owner, Admin or Creator. The account menu, your profile picture at the top right, shows your role under your name. If New connection is missing from Data & AI → Data Connections, ask a workspace administrator (see How access works).
  • The fields below, from whoever runs the database.
  • Network access. The database must accept connections from Astrato. See Allow Astrato through your firewall.
  • A database user that can connect and read the tables you want. A typical read-only grant is GRANT CONNECT ON DATABASE analytics TO astrato_reader;, then GRANT USAGE ON SCHEMA public TO astrato_reader; and GRANT SELECT ON ALL TABLES IN SCHEMA public TO astrato_reader;. Adjust them to your own policy.
Field Section Example Notes
Username Authentication astrato_reader Required
Password Authentication Required. Leading or trailing spaces are rejected
Host Connection details db.example.com or 10.0.4.12 Required. The host name or IP address only, with no postgres:// and no port
Port Connection details 5432 Required. Filled in with PostgreSQL’s default, 5432; change it only if your server differs
SSL mode Connection details require Optional. Defaults to require
SSL root certificate Connection details Optional. Paste the PEM text, for verify-ca or verify-full
SSL certificate Connection details Optional. Only if your server asks for a client certificate
SSL key Connection details Optional. The key for the client certificate

You do not type a database name here. You choose it from a list after validating.

The options are disable, allow, prefer, require, verify-ca and verify-full.

  • Keep require unless you have a reason to change it. The connection is encrypted.
  • Use verify-ca or verify-full when your security policy asks Astrato to check the server’s certificate. Paste your certificate authority’s certificate into SSL root certificate. verify-full also checks that Host matches the name on the certificate.
  • Use disable, allow or prefer only for a server that cannot offer SSL.
  1. Open Data & AI → Data Connections and select New connection.

    The New connection panel beside the Data Connections list, with a Source grid of Snowflake, Google BigQuery, ClickHouse, Databricks, Amazon Redshift, Supabase, DuckDB, Dremio and PostgreSQL tiles, and Google Sheets and Demo data greyed out

  2. Select PostgreSQL. The panel title changes to New PostgreSQL connection.

  3. Under Authentication, enter the Username and Password.

  4. Under Connection details, enter the Host, check the Port, and choose an SSL mode and any certificates. If your workspace shows Deployment Region, you can leave it unset for now. If you select one, validation and database discovery are routed through it, so your database must accept traffic from that region. A Data App on this connection needs one. See Choose a Deployment Region.

  5. Select Validate Connection. It stays unavailable while a required field is empty; the form lists those fields after “Still needed:”.

    The New PostgreSQL connection panel filled in: Username and Password under Authentication, then Host db.example.com, Port 5432, SSL mode require and three optional SSL certificate boxes under Connection details, with the Validate Connection button below

  6. Under Data to use, choose the Database. Schema stays unavailable until the connection is saved; you can choose one in step 9.

  7. Under Name, the suggested Connection name is PSQL. Change it to something people will recognise, such as the database’s name.

    A New PostgreSQL connection panel after validating: a Validation result reading Connected to PostgreSQL in 140 ms, Data to use with Database sales and Schema left at (Default), and Connection name Revenue Analysis above the Save connection button

  8. Select Save connection.

  9. The panel shows Connection created with “Choose a schema now, or finish to use this connection.” Choose a schema or leave it as Choose later, then select Finish.

    A New PostgreSQL connection panel after saving: Connection created with “Choose a schema now, or finish to use this connection.”, a Schema list set to Choose later and a Finish button

Who else can use the connection is set afterwards, on its Manage Access tab; see Manage resource access.

  • After step 5, a Validation result section says “Connected to PostgreSQL in n ms.”, and the Database list fills in. Before that, it reads “Available once the connection validates.”
  • The Database list shows the databases this user can reach.
  • After Finish, the connection is in the Data Connections list with its name, source, connection details and Deployment Region.

To confirm that the connection can read your tables before you model them, start an empty semantic layer on it and open Add → Tables & Views; see Check that it works.

If your database sits behind a firewall, a cloud security group or pg_hba.conf rules, it must accept connections from the addresses Astrato connects from. Otherwise validation fails or times out.

  • With a Deployment Region selected, the form names that region’s addresses under the region, as “Allow traffic from … at your database.” Allow those addresses.
  • Without a Deployment Region (Legacy / not selected), allow Astrato’s addresses 34.142.11.11 and 34.142.68.232.

Add them on the database port (5432 unless you changed it).

Where you add them depends on where the database runs:

Where the database runs Where to allow the addresses
Your own server The server’s firewall and a pg_hba.conf entry for each address
A managed service on AWS, Google Cloud or Azure, with a public address The service’s own network settings: its security group, authorized networks or firewall rules
A private address only, such as 10.0.4.12, with no route from the internet Astrato cannot reach it from the addresses above. See Databases with a private address

A database with only a private address, such as Cloud SQL on Google Cloud or Azure Database for PostgreSQL with a private IP, cannot be reached from Astrato’s public addresses listed above. Validate Connection then fails or times out, whatever you allow. There are three ways to connect it:

  • Add a public address, allowed only to Astrato. Turn on the service’s public address alongside the private one, and allow only Astrato’s addresses on it: Cloud SQL calls these authorized networks, and Azure Database for PostgreSQL uses firewall rules. Keep SSL mode at require or stricter. Enter the public address as Host.
  • Use a private Deployment Region. A private Deployment Region set up for your workspace appears in the Deployment Region list under its own name. Choose it in step 4 and allow the addresses the form names under it. If none is listed, ask Astrato support whether one can be set up for your Google Cloud or Azure network.
  • On AWS, use AWS PrivateLink or AWS private connections.

When validation fails, an error notice at the bottom of the panel usually starts “Connection validation failed” and lists details below it. A line starting “Source message:” carries PostgreSQL’s own error, with your password replaced by [credential removed]; read it first. If it does not make the cause clear, work down this table:

A red notice at the bottom of the New PostgreSQL connection panel reading Connection validation failed: the source refused the credentials or sign-in, listing HTTP status 401, Error code 28P01, the source message password authentication failed for user astrato_reader, Step: Testing the connection, the request and a correlation ID, with a Copy details button

Check What goes wrong What to do
Host It includes postgres://, a port, a path, or a name only reachable inside your network Enter the host name or IP address on its own, one that is reachable from the internet or through your Deployment Region
Port The server listens on a port other than 5432, or a pooler uses its own port Enter the port your server or pooler actually uses
Firewall Astrato’s addresses are not allowed Allow the addresses in Allow Astrato through your firewall
Username and Password A typo, an expired password, or a user without CONNECT on any database Sign in with the same values from another tool, such as psql, to confirm them
SSL mode The server does not offer SSL, or verify-ca / verify-full has no matching SSL root certificate Try require; for verification, paste the right certificate authority certificate
No databases listed “This credential reported no databases. Check its grants, or validate again.” Grant the user CONNECT on the database, then validate again

A password with leading or trailing spaces shows “Remove leading or trailing spaces before validating.” Select Trim it, then validate again.

The same checks, for every source, are in When a data connection will not validate.