PostgreSQL

The PostgreSQL connector turns approved relational rows into searchable documents. It supports two indexing modes:

• Automatic discovery reads accessible base tables, lets administrators select schemas, tables, and columns, and maps rows into documents.
• Custom SQL runs a controlled SELECT or WITH query and maps returned columns into document ID, title, searchable content, metadata, and update time.

Use a dedicated read-only account and expose only curated tables or views. Do not connect a broad production account to a database that contains unapproved personal data, credentials, tokens, or operational secrets.

What You Need

Create a Read-Only PostgreSQL Role

Run grants from a database administrator account. Adjust database, schema, table, and view names to match your environment.

1
CREATE ROLE aivis_reader
2
  LOGIN
3
  PASSWORD '<generated-password>'
4
  NOSUPERUSER
5
  NOCREATEDB
6
  NOCREATEROLE
7
  NOREPLICATION
8
  NOBYPASSRLS
9
  CONNECTION LIMIT 3;
10
11
GRANT CONNECT ON DATABASE appdb TO aivis_reader;
12
GRANT USAGE ON SCHEMA knowledge TO aivis_reader;
13
14
GRANT SELECT ON TABLE
15
  knowledge.product_catalog_view,
16
  knowledge.faq_article_view
17
TO aivis_reader;

If you want AIvis to read all current tables in a dedicated schema, PostgreSQL supports GRANT SELECT ON ALL TABLES IN SCHEMA .... Use it only for a schema that is already curated for indexing.

1
GRANT USAGE ON SCHEMA aivis_public TO aivis_reader;
2
GRANT SELECT ON ALL TABLES IN SCHEMA aivis_public TO aivis_reader;

Create views when the source tables contain sensitive columns:

1
CREATE VIEW knowledge.product_catalog_view AS
2
SELECT
3
  id,
4
  name,
5
  status,
6
  public_summary,
7
  category,
8
  updated_at
9
FROM product_catalog
10
WHERE searchable = true;
11
12
GRANT SELECT ON TABLE knowledge.product_catalog_view TO aivis_reader;

Create the Credential

PostgreSQL sslmode follows libpq behavior. verify-full verifies the certificate chain and host name; verify-ca verifies the certificate authority; require requires SSL without full host-name verification.

Choose an Indexing Mode

Automatic Discovery

Automatic discovery reads accessible non-system base tables. It excludes PostgreSQL system schemas such as pg_catalog, information_schema, and pg_toast.

In the schema tree:

• Select only schemas, tables, and fields that are approved for indexing.
• Binary columns are not indexed.
• AIvis uses primary keys first, then non-null unique indexes, then a row hash when no stable key exists.
• Tables without a stable key can create new document IDs when selected values change; prefer adding a primary key or using Custom SQL with an explicit ID.
• The default title field is inferred from columns such as title, name, subject, or label; you can override it in table settings.
• The update field is inferred from datetime columns such as updated_at, updated, modified_at, modified, or last_modified; no update field means the table requires full scans.

Custom SQL Requirements

The connector wraps your query as a subquery and validates returned columns. Keep the SQL read-only and deterministic.

Example:

1
SELECT
2
  p.id::text AS doc_id,
3
  p.name AS title,
4
  concat_ws(
5
    E'\n',
6
    'Status: ' || p.status,
7
    'Category: ' || p.category,
8
    'Summary: ' || p.public_summary
9
  ) AS body,
10
  p.category,
11
  p.updated_at
12
FROM knowledge.product_catalog_view AS p
13
WHERE p.searchable = true

For this query, set:

Production Safety

The connector opens PostgreSQL sessions as read-only and applies a statement timeout during reads. That protects AIvis from writing data, but it does not replace database-side controls.

Before production:

• Use a replica, analytics database, or curated view layer when possible.
• Add indexes that support your Custom SQL filters and update window.
• Avoid SELECT * in Custom SQL; explicitly return only approved columns.
• Avoid long-running joins against hot OLTP tables.
• Keep batch size modest until query plans and sync duration are measured.
• Confirm row-level security behavior with the exact read-only role if your database uses RLS.
• Verify that query plans do not create heavy locks, sequential scans on large tables, or unexpected temp files.

Verification

1. Confirm the credential connects with the read-only role.
2. In Automatic discovery, confirm the expected schemas, tables, and fields appear.
3. In Custom SQL, validate that the query returns every configured column.
4. Run an initial sync and inspect row count, indexed document count, and failures.
5. Search for representative records by title and content.
6. Confirm unselected schemas, tables, fields, and sensitive columns are absent from search.
7. Update one test row and confirm the same document updates when an update field is configured.
8. Test as a user outside the connector audience and confirm restricted documents do not appear.

Troubleshooting

Related Official Documentation

• PostgreSQL CREATE ROLE
• PostgreSQL GRANT
• PostgreSQL libpq connection parameters and sslmode