Connect PostgreSQL

Give Halo a read-only login and your database's address, test it, and run discovery.

When you're done, Halo can read the schemas you choose from your PostgreSQL database, and its tables are ready to build datasets on.

Before you start

• In Halo: you need a role that can add connections: Owner, Admin or Builder.

• In your database: someone who can create a role and grant access. If that's your IT team, send them Allowlists, SSH tunnels and read-only logins.

• On your network: Halo connects from its servers to your database's host and port, so that path has to be open. Read the network section first.

Create a read-only login

Halo only reads, and its login should only be able to read. This example lets a role called halo_reader read the sales schema. Change the database and schema names to yours, and repeat the schema lines for each schema Halo should see.

create role halo_reader login password 'use-a-long-random-password';
alter role halo_reader set default_transaction_read_only = on;

grant connect on database analytics to halo_reader;
grant usage on schema sales to halo_reader;
grant select on all tables in schema sales to halo_reader;

-- Also cover tables this role creates in the schema later.
alter default privileges in schema sales grant select on tables to halo_reader;

The last line only covers tables made later by the role that runs it. If another role creates tables in that schema, run it as that role too, or add for role with its name.

To show Halo less than a whole schema, make a schema of views and grant only that. Discovery reads views' names and columns, but doesn't profile them, since that would run their queries.

Network

Halo connects to your database's host and port, either directly or through an SSH bastion, which you set up under SSH tunnel in the connection's settings.

Add the connection

1. Open Data, then New connection, and choose PostgreSQL.

2. Under Details, give it a Name people will recognize, such as "Sales warehouse", and choose a Workspace. Who can use the connection follows the workspace's sharing.

3. Fill in Connection settings:

   Setting	What to enter
   Host	The hostname or IP address
   Port	5432 unless yours differs
   Database	The database's name
   User	halo_reader, or the login you made
   Password	Its password. It's encrypted and never shown again
   TLS	require by default. See below
   Schemas	Optional. Schemas to include, each its own (press Enter or type a comma after each). All readable schemas otherwise

Choosing a TLS mode

verify-ca and verify-full check against public certificate authorities, unless you add your own under TLS certificates, which also takes a client certificate and key. See TLS.

Test it

Choose Test connection. You'll see Connected, the time it took and the server's version, or Couldn't connect with the database's own error.

Two warnings can appear even when it connects:

• "This user is a superuser. Connect with a read-only role instead."

• "This user can change data. Halo only reads, but a read-only role is safer."

Halo still runs read-only either way, but fix these before you go on.

Save and discover

Choose Save and discover. Halo saves the connection, opens its page and starts discovery, which reads your tables and profiles them. You can leave the page while it runs, and Halo tells you when it's done. Save only skips discovery, for when you want to run it later.

If it finishes with No tables found, the login can't see any schemas. Grant it select on the ones you want, and choose Run discovery again.

Change the password later

1. Change the password in your database. To avoid a gap, make a second login, or do this at a quiet time.

2. In Halo, open the connection's Settings tab, type the new password, and save.

3. Choose Test connection on the connection's page.

Next

Your tables are ready. Build a dataset names them for people, or follow Your first report from step 3.