PostgreSQL

Connecting PostgreSQL databases to Conexor

Prerequisites

PostgreSQL 12 or later
Network access from the agent machine to PostgreSQL
A database user with SELECT permissions

Connection String Format

text

Host=hostname;Port=5432;Database=your_database;Username=conexor_user;Password=your_password;SSL Mode=Prefer;

INFOUse SSL Mode=Require for production to enforce encrypted connections.

Connection Pooling

text

Host=hostname;Port=5432;Database=your_database;Username=conexor_user;Password=your_password;Pooling=true;Minimum Pool Size=5;Maximum Pool Size=100;

Schema Discovery

Conexor uses INFORMATION_SCHEMA and pg_catalog to discover:

All tables and views
Column names, types, and nullability
Primary and foreign key relationships
Estimated row counts (from pg_stat_user_tables)

Query Syntax

Use standard PostgreSQL SQL with named parameters:

sql

SELECT 
    order_id, customer_name, order_date, total
FROM orders 
WHERE customer_id = @customerId
    AND order_date >= @startDate
ORDER BY order_date DESC
LIMIT 100

TIPPostgreSQL uses $1, $2 parameter syntax internally, but Conexor converts @param names automatically.

Required Permissions

sql

-- Create a dedicated user for Conexor
CREATE USER conexor_user WITH PASSWORD 'strong_password';

-- Grant CONNECT on the database
GRANT CONNECT ON DATABASE your_database TO conexor_user;

-- Grant USAGE on schema
GRANT USAGE ON SCHEMA public TO conexor_user;

-- Grant SELECT on all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO conexor_user;

-- Grant SELECT on future tables (optional)
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON TABLES TO conexor_user;

NOTENever grant INSERT, UPDATE, DELETE, CREATE, or DROP permissions.

JSONB Support

PostgreSQL JSONB columns are fully supported. Query nested JSON with the -> and ->> operators:

sql

SELECT 
    id,
    data->>'name' as name,
    data->'metadata'->>'version' as version
FROM items
WHERE data->>'status' = 'active'

Troubleshooting

Connection refused

Check pg_hba.conf allows connections from the agent IP. Verify postgresql.conf has listen_addresses = '*'.

SSL connection required

Add SSL Mode=Require or SSL Mode=Prefer to your connection string.

Database does not exist

Verify the database name is correct and the user has CONNECT permission.

SQL Server MySQL & MariaDB