PostgreSQL Connections

PostgreSQL connections enable agents to query relational databases.

Recommended: Create and manage connections via the web UI at https://ui.{deployment}.firetigerapi.com/settings/connections

Network Configuration

Firetiger’s query servers must be able to reach your database. Choose the appropriate networking setup based on your database configuration:

1. Private Network Database

Scenario: Database runs in a private VPC/network (AWS, GCP, Azure)

Solution: Set up a private network connection:

  • AWS: Configure AWS PrivateLink
  • GCP: Configure Private Service Connect
  • Azure: Configure Private Link

Contact Firetiger support to coordinate private network setup.

2. Public Database with IP Allowlist

Scenario: Database accepts public connections but restricts access by IP address

Solution: Add Firetiger’s static IP addresses to your database’s allowlist

  • Contact Firetiger support to obtain the static IP addresses for your deployment’s query servers
  • Add these IPs to your database firewall rules or security groups

3. Public Database with Standard Authentication

Scenario: Database accepts public connections with username/password authentication

Solution: No special networking configuration needed

  • Ensure your database accepts connections on the standard PostgreSQL port (5432)
  • Verify firewall allows inbound connections from the internet
  • Use ssl_mode: "verify-full" for secure connections

Connection Parameters

A PostgreSQL connection requires the following configuration:

Required Parameters

Parameter Type Description
host string Database hostname or IP address (e.g., db.example.com)
port int32 Database port number (typically 5432)
database string Name of the database to connect to
username string Username for authentication
password string Password for authentication (stored securely as a secret)

Optional Parameters

Parameter Type Description Default
ssl_mode string SSL/TLS connection mode (see below) require
role string PostgreSQL role to assume after connecting None
read_only bool Enable database-level read-only enforcement false

SSL Modes

The ssl_mode parameter controls how the connection encrypts data in transit. Choose the appropriate mode based on your security requirements:

Mode Description Use When
disable No SSL encryption Not recommended - Only for local development or testing
require Require SSL but don’t verify server certificate You want encryption but certificate verification isn’t critical
verify-ca Require SSL and verify the server certificate is signed by a trusted CA You have a proper CA-signed certificate
verify-full Require SSL, verify certificate, and check hostname matches Most secure - Recommended for production

For production environments, use verify-full when possible to prevent man-in-the-middle attacks.

Read-Only Mode

The read_only parameter enables database-level read-only enforcement for the connection.

How it works: After connection, SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY is executed automatically.

Effect: PostgreSQL enforces read-only at the database level - any attempt to INSERT, UPDATE, DELETE, TRUNCATE, or perform other write operations will fail with an error.

When to use:

  • Production agent connections - Recommended for all AI agent access to prevent accidental data modification
  • Analytics/reporting connections - Ensure reporting tools cannot modify data
  • Compliance requirements - Meet regulatory requirements for read-only access

Example:

{
  "read_only": true
}

This provides strong database-level protection against data modification. Unlike role-based permissions, this setting prevents writes regardless of the user’s privileges.

Note: Default is false for backward compatibility. We recommend setting read_only: true for all new production connections.

Role-Based Access Control

The role parameter enables privilege separation - connect with admin credentials but execute queries with restricted permissions.

How it works: After connection, SET ROLE <role> is executed automatically.

Common pattern - Use built-in read-only role:

{
  "role": "pg_read_all_data"
}

This prevents data modification while allowing full read access.

Description Field

The description should document your schema to help agents write effective queries.

Example:

Production customer database.

Tables:
- users: user_id (uuid), email (text), status (text: 'active'/'suspended')
- subscriptions: subscription_id (uuid), user_id (uuid), plan_name (text), status (text)
- billing_events: event_id (uuid), subscription_id (uuid), event_type (text), amount_cents (int)

Common patterns:
- Find user by email: WHERE email = '<email>'
- Active subscriptions: WHERE status = 'active'

Example Connection 

{
  "display_name": "Production Customer Database",
  "description": "Production customer database...",
  "connection_details": {
    "postgres": {
      "host": "prod-db.example.com",
      "port": 5432,
      "database": "customers",
      "username": "admin",
      "password": "password",
      "ssl_mode": "verify-full",
      "role": "pg_read_all_data",
      "read_only": true
    }
  }
}

Query Support

Firetiger supports standard PostgreSQL query syntax including:

  • SELECT statements with all standard clauses (WHERE, JOIN, GROUP BY, ORDER BY, LIMIT, etc.)
  • Common Table Expressions (CTEs) with WITH
  • SQL comments (both -- single-line and /* */ multi-line styles)
  • All PostgreSQL functions (aggregate, string, date/time, etc.)

Security Model

Firetiger provides multiple layers of security for PostgreSQL connections:

  1. Database-level read-only enforcement - Use read_only: true to prevent writes at the PostgreSQL level
  2. Role-based access control - Use read-only roles (pg_read_all_data) to limit permissions
  3. SSL encryption - Use ssl_mode: "verify-full" for secure connections

Recommended approach: Enable read_only: true for all production agent connections. This provides strong database-level protection regardless of the user’s actual permissions.

Defense in depth: Combine read_only: true with a read-only role for maximum protection:

  • read_only: true - Prevents writes even if the role has write permissions
  • role: "pg_read_all_data" - Limits permissions at the database role level

Best Practices

  • Enable read-only mode - Set read_only: true for all production agent connections
  • Use read-only roles - Set role: "pg_read_all_data" or custom read-only roles for defense in depth
  • Document your schema - Include table/column information in the description field
  • Enable SSL in production - Use ssl_mode: "verify-full"
  • Limit data exposure - Only grant access to necessary tables through PostgreSQL permissions
This site uses Just the Docs, a documentation theme for Jekyll.