Skip to main content

PostgreSQL Connector

Connect to PostgreSQL databases to enable natural language queries over your structured data.

Overview​

The PostgreSQL connector allows you to:

  • Discover database schema
  • Execute natural language queries
  • Run read-only SQL
  • Index table metadata

Prerequisites​

  • PostgreSQL database access
  • Read-only user credentials
  • Network connectivity to database

Authentication​

Username/Password​

  1. Enter host and port
  2. Provide username and password
  3. Specify database name

SSL Configuration​

ModeDescription
disableNo SSL
requireSSL required
verify-caVerify CA certificate
verify-fullVerify CA and hostname

Configuration​

SettingDescription
HostDatabase hostname
PortDatabase port (5432)
DatabaseDatabase name
UsernameDB user
PasswordUser password
SSL ModeSSL configuration
SchemaSchema to index

Setup Steps​

  1. Add Connector: Knowledge → Add Data Source → PostgreSQL
  2. Enter Connection: Host, port, database
  3. Provide Credentials: Username and password
  4. Configure SSL: If required
  5. Test & Create: Verify and save

Usage with Agents​

Once connected, AI agents can:

User: "What were our total sales last quarter?"

Agent:
├── Tool: search_database_schema
│   Finding relevant tables...
├── Tool: query_database
│   SELECT SUM(amount) FROM orders
│   WHERE created_at >= '2024-10-01'
└── Answer: Total sales were $1.2M

Schema Discovery​

ZenSearch indexes:

  • Table names and descriptions
  • Column names and types
  • Primary and foreign keys
  • Indexes and constraints

Query Validation​

For security, queries are validated:

  • Only SELECT statements allowed
  • No data modification (INSERT, UPDATE, DELETE)
  • No schema changes (DROP, ALTER)
  • Timeout limits enforced

Best Practices​

  1. Create a read-only database user
  2. Limit schema access if needed
  3. Add table/column comments for better NL understanding
  4. Index frequently queried columns

Troubleshooting​

Connection refused: Check host/port and firewall

Authentication failed: Verify username and password

SSL required: Enable appropriate SSL mode