> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://developers.alephant.io/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://developers.alephant.io/_mcp/server.

# PostgreSQL

> Configure the PostgreSQL connector to read structured knowledge from approved databases, schemas, tables, and SQL queries.

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

| Item            | Requirement                                                                                                                                 |
| --------------- | ------------------------------------------------------------------------------------------------------------------------------------------- |
| Network route   | AIvis workers must be able to reach the PostgreSQL host and port. Use private networking, a database proxy, or an allowlisted egress IP.    |
| Credential      | A dedicated login role with `CONNECT`, schema `USAGE`, and `SELECT` only on approved tables or views.                                       |
| Data scope      | Prefer sanitized views for sensitive schemas, joins, aggregates, or field renaming.                                                         |
| Stable identity | Use a primary key, a non-null unique key, or a stable `id_column` in Custom SQL.                                                            |
| Update field    | Prefer a `timestamp` / `timestamptz` column such as `updated_at` for incremental sync.                                                      |
| SSL mode        | Choose the weakest mode only when the network path is already trusted; use certificate verification for external or regulated environments. |

## Create a Read-Only PostgreSQL Role

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

```sql
CREATE ROLE aivis_reader
  LOGIN
  PASSWORD '<generated-password>'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  NOBYPASSRLS
  CONNECTION LIMIT 3;

GRANT CONNECT ON DATABASE appdb TO aivis_reader;
GRANT USAGE ON SCHEMA knowledge TO aivis_reader;

GRANT SELECT ON TABLE
  knowledge.product_catalog_view,
  knowledge.faq_article_view
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.

```sql
GRANT USAGE ON SCHEMA aivis_public TO aivis_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA aivis_public TO aivis_reader;
```

Create views when the source tables contain sensitive columns:

```sql
CREATE VIEW knowledge.product_catalog_view AS
SELECT
  id,
  name,
  status,
  public_summary,
  category,
  updated_at
FROM product_catalog
WHERE searchable = true;

GRANT SELECT ON TABLE knowledge.product_catalog_view TO aivis_reader;
```

## Create the Credential

| AIvis Field | Recommended Value                                                                       | Notes                                                                                          |
| ----------- | --------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------- |
| Host        | PostgreSQL host name or private endpoint.                                               | Do not include protocol prefixes such as `postgresql://`.                                      |
| Port        | `5432` unless your deployment uses a custom port.                                       | The UI defaults to `5432`.                                                                     |
| Database    | The database containing the approved schema or views.                                   | The connector connects to one database per credential.                                         |
| Username    | Dedicated read-only role, such as `aivis_reader`.                                       | Avoid superuser, owner, migration, or application writer accounts.                             |
| Password    | Generated password for the read-only role.                                              | Rotate it through your normal credential process.                                              |
| SSL Mode    | `verify-full` for externally reachable hosts; `require` or `prefer` only when approved. | The UI defaults to `prefer`, which may fall back depending on server and client configuration. |

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

| Mode                | Use When                                                                                               | Boundary                                                                                                                |
| ------------------- | ------------------------------------------------------------------------------------------------------ | ----------------------------------------------------------------------------------------------------------------------- |
| Automatic discovery | You want AIvis to discover accessible base tables and let an admin select schemas, tables, and fields. | It discovers base tables, not arbitrary joins or views. Use Custom SQL for views, joins, aggregates, or renamed fields. |
| Custom SQL          | You need a curated view, join, filter, projection, or stable row shape.                                | The query must be a single `SELECT` or `WITH` statement and return one row per document.                                |

### 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.

| Field             | Requirement                                                                                       |
| ----------------- | ------------------------------------------------------------------------------------------------- |
| SQL Query         | A single `SELECT` or `WITH` query. Do not include a trailing semicolon or multiple statements.    |
| ID Column         | A non-empty unique value per row. Cast compound keys to text when needed.                         |
| Title Column      | A human-readable title used as the document name.                                                 |
| Content Columns   | One or more columns whose values become searchable document text.                                 |
| Metadata Columns  | Optional fields stored as metadata for filtering or debugging.                                    |
| Updated At Column | Optional timestamp column used to poll only rows updated in each sync window.                     |
| Batch Size        | Number of rows fetched per batch. The UI default is `16`; increase only after testing query cost. |

Example:

```sql
SELECT
  p.id::text AS doc_id,
  p.name AS title,
  concat_ws(
    E'\n',
    'Status: ' || p.status,
    'Category: ' || p.category,
    'Summary: ' || p.public_summary
  ) AS body,
  p.category,
  p.updated_at
FROM knowledge.product_catalog_view AS p
WHERE p.searchable = true
```

For this query, set:

| AIvis Field       | Value        |
| ----------------- | ------------ |
| ID Column         | `doc_id`     |
| Title Column      | `title`      |
| Content Columns   | `body`       |
| Metadata Columns  | `category`   |
| Updated At Column | `updated_at` |

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

| Symptom                         | Likely Cause                                                                                                              | Resolution                                                                              |
| ------------------------------- | ------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------- |
| Authentication failed           | Wrong role, password, database, host, or port.                                                                            | Test with the same role from the deployment network and rotate the password if needed.  |
| Permission denied               | Missing `CONNECT`, schema `USAGE`, or table/view `SELECT`.                                                                | Grant the minimum missing privilege to the read-only role.                              |
| No tables discovered            | The role cannot see any base tables, or only views were granted.                                                          | Grant selected base tables for Automatic discovery, or use Custom SQL for views.        |
| Custom SQL rejected             | Query is blank, has multiple statements, ends with a semicolon plus extra text, or does not start with `SELECT` / `WITH`. | Use one read-only query and return explicit aliases for mapped columns.                 |
| Missing column error            | The query does not return the configured ID, title, content, metadata, or update column.                                  | Add aliases or update field mapping.                                                    |
| Invalid update field            | The update column is not a PostgreSQL datetime value.                                                                     | Use `timestamp` or `timestamptz`, or leave update field empty and accept full scans.    |
| Duplicate or changing documents | No stable identity exists, or the configured ID changes over time.                                                        | Use primary keys, non-null unique keys, or a stable `id_column`.                        |
| Sync times out                  | Query cost exceeds the statement timeout or batch size is too high.                                                       | Add indexes, reduce scope, lower batch size, or move indexing to a replica/view.        |
| SSL connection fails            | `sslmode`, certificate trust, or host-name verification does not match server configuration.                              | Use the correct `sslmode` and certificate chain; for public hosts prefer `verify-full`. |

## Related Official Documentation

* [PostgreSQL `CREATE ROLE`](https://www.postgresql.org/docs/current/sql-createrole.html)
* [PostgreSQL `GRANT`](https://www.postgresql.org/docs/current/sql-grant.html)
* [PostgreSQL libpq connection parameters and `sslmode`](https://www.postgresql.org/docs/current/libpq-connect.html)