M↓ View as Markdown ↗

PostgreSQL¶

Connect a PostgreSQL database to Actian AI Analyst so you can build Models on its tables and let your AI Analysts query them.

Availability

Who can do this: members with the Edit capability in the active Workspace. If your organization has turned on Restrict connection management, you also need the Manage organization capability.
Plan: Studio lists only the connectors that your organization's plan and your deployment include. If PostgreSQL is missing from the list, ask your administrator.
Deployment: Actian SaaS and self-managed deployments.

To connect PostgreSQL, you:

Create a login role with read-only permissions in your database.
Optionally, grant access to tables created later.
Allow AI Analyst's IP addresses through your firewall or network rules.
Add the connection in Studio.

Queries are read-only

AI Analyst runs the queries behind answers, Models, Metrics and previews inside a read-only transaction, so PostgreSQL refuses data changes made through those queries. Still use a dedicated user that can only read the data you want to expose. See Read-only access.

In PostgreSQL, a role can act as both a login account and a permission holder. The following SQL creates a login role that can read one schema:

-- Replace the placeholder values with your database, schema and a secure password

-- Create the login role
CREATE ROLE analyst_login WITH LOGIN PASSWORD '<your_secure_password>';

-- Allow the role to connect to the database
GRANT CONNECT ON DATABASE your_database_name TO analyst_login;

-- Allow the role to use the schema
GRANT USAGE ON SCHEMA your_schema_name TO analyst_login;

-- Grant read access to existing tables and views
GRANT SELECT ON ALL TABLES IN SCHEMA your_schema_name TO analyst_login;

-- (Optional) Grant read access to sequences
GRANT SELECT ON ALL SEQUENCES IN SCHEMA your_schema_name TO analyst_login;

GRANT SELECT ON ALL TABLES also covers the views that exist in the schema. Repeat the USAGE and SELECT grants for every schema you want AI Analyst to read.

2. Optional: grant access to future tables¶

PostgreSQL default privileges can grant access to tables and views that are created later, so you don't need to repeat the grants. They apply per creating role:

-- Run this as the role that creates the tables, or name that role with FOR ROLE
ALTER DEFAULT PRIVILEGES FOR ROLE <table_creator_role> IN SCHEMA your_schema_name
GRANT SELECT ON TABLES TO analyst_login;

Default privileges affect only tables and views that <table_creator_role> creates after you run the statement. Omit FOR ROLE to apply them to objects created by the role that runs the statement.

3. Allow AI Analyst to reach your server¶

If your PostgreSQL server is behind a firewall or network rules, allow inbound connections on its port (5432 by default) from AI Analyst's outbound IP addresses:

Actian SaaS: when your deployment publishes its outbound addresses, the Setup help panel next to the connection form lists them under Which IPs to allow. If the panel doesn't list any addresses, contact Actian support for them.
Self-managed deployment: allow the outbound addresses of your own AI Analyst installation. If the database has a private network address, your operator must also allow that private range for warehouse connections.

See Network access for details.

4. Add the connection in Studio¶

In Studio, open Connections, click + (Add connection) and choose PostgreSQL under Warehouses.
Under Connection details, enter a Name. Names must be unique among the connections you can see. See Connections and Workspaces. Optionally add a Description.
Optionally, tick Private to this Workspace so that only the active Workspace can see and use the connection. See Who can see the connection.

Under Credentials, fill in the fields:

Field	Value
Host	Your server's hostname, without a protocol (no `https://`)
Port	`5432` by default
Database	The database that `analyst_login` can connect to
Username	`analyst_login`
Password	The password you set in step 1
SSL mode	Require by default. See SSL mode.

Instead of typing each field, you can click Paste a connection string, paste a URI such as postgresql://user:password@host:5432/database?sslmode=require or a keyword string such as host=… port=… dbname=… user=… password=…, and click Fill the form. Nothing is sent until you test the connection.

Click Test connection. When the test succeeds, Schemas appears and lists the schemas in the database. public is selected if it exists. A listed schema that the role has no USAGE on shows no tables.
The preselected schema (public) is exposed by default. To change it, select the schemas to expose for browsing and model authoring. Changing the selection resets the test, so click Test connection again afterwards. Your selection is kept. If nothing is preselected because public doesn't exist, select at least one schema before you save.
Click Save connection. The button stays disabled until a test succeeds and the connection has a name.

Studio opens the new connection. Credentials are encrypted at rest and are never shown again after saving. To browse tables, re-test the connection or update credentials later, see Manage connections.

SSL mode¶

The SSL mode options behave as follows in AI Analyst. They don't follow the PostgreSQL sslmode naming exactly.

SSL mode	Encrypts the connection	Verifies the server certificate
Disable	No	No
Require (default)	Yes	Yes
Require (no verify)	Yes	No
Verify CA	Yes	No. It currently behaves like Require (no verify).
Verify full	Yes	Yes

If Test connection fails with Could not establish a secure connection, your server probably uses a self-signed certificate or one issued by a private certificate authority. Choose Require (no verify) to keep the connection encrypted without verifying the certificate. Uploading a CA certificate or a client certificate is not supported.

Who can see the connection¶

A new connection is attached to the Workspace you are working in.

Organization connection (default): an organization admin can attach it to other Workspaces later.
Private to this Workspace: only this Workspace can see or use it. An organization admin can later share it with the whole organization. A shared connection can't be made private again.

See Connections and Workspaces.

Local and private addresses

If you enter localhost, a private IP address or a hostname without a domain, the form shows This looks like a local address. The form doesn't block you, but you can only save after a successful test, and the test only succeeds if AI Analyst's servers can reach the address and your deployment allows it. AI Analyst refuses private addresses unless the deployment allows that range. On a self-managed deployment, ask your operator to allow it. See Network access. localhost points to the server AI Analyst connects from, not to your computer.

Next steps¶

Before people can ask questions about this data:

Create Models on the tables you want to analyse.
Add the Models to an AI Analyst.

You can then monitor the connection's health.

Troubleshooting¶

Message after Test connection or Save connection	Likely cause and fix
Fill in … before testing the connection	A required field is empty. Fill in the fields it names.
Authentication failed	Check the Username and Password, and that the role has the `LOGIN` attribute.
Could not reach the warehouse	Check the Host and Port, and that your firewall allows AI Analyst's IP addresses (step 3).
Could not establish a secure connection	The SSL settings don't match the server. See SSL mode.
This warehouse user cannot see those objects	Grant `CONNECT`, `USAGE` and `SELECT` as shown in step 1.
The test succeeds but Schemas doesn't appear	The request to list schemas failed after the test (a brief error appears). Click Test connection again. If it keeps failing, check the role's `CONNECT` grant.
Warehouse host is not allowed	The host is a private or blocked address, doesn't resolve, or includes a protocol, path or port. Use a publicly reachable host name, or on a self-managed deployment ask your operator to allow the private range. See Network access.
A connection with this name already exists	Choose a different Name. This message appears after you click Save connection.