PostgreSQL

The PostgreSQL query runner allows you to connect to and query PostgreSQL databases, including variants like Redshift, RedshiftIAM, and CockroachDB.

Configuration

The PostgreSQL query runner requires the following configuration:

{
  "host": "127.0.0.1",
  "port": 5432,
  "user": "postgres",
  "password": "password",
  "dbname": "database_name",
  "sslmode": "prefer",
  "sslrootcertFile": "",
  "sslcertFile": "",
  "sslkeyFile": ""
}

Configuration Schema

Parameter	Type	Required	Description
host	String	No	PostgreSQL server hostname or IP address (default: "127.0.0.1")
port	Number	No	PostgreSQL server port (default: 5432)
user	String	No	Username for authentication
password	String	No	Password for authentication
dbname	String	Yes	Database name to connect to
sslmode	String	No	SSL mode (options: "disable", "allow", "prefer", "require", "verify-ca", "verify-full")
sslrootcertFile	String	No	SSL Root Certificate (base64-encoded)
sslcertFile	String	No	SSL Client Certificate (base64-encoded)
sslkeyFile	String	No	SSL Client Key (base64-encoded)

Example Usage

from legion_query_runner.query_runner import QueryRunner

# Initialize with PostgreSQL configuration
pg_config = {
    "host": "postgres.example.com",
    "port": 5432,
    "user": "postgres",
    "password": "password",
    "dbname": "mydatabase"
}

# Create a QueryRunner instance
runner = QueryRunner('pg', pg_config)

# Test connection
runner.test_connection()

# Execute a query
results = runner.run_query("SELECT * FROM users LIMIT 10")

Type Mapping

The PostgreSQL query runner maps PostgreSQL data types to standardized types:

PostgreSQL Type ID	Legion Type
20, 21, 23	INTEGER
700, 701, 1700	FLOAT
16	BOOLEAN
1082, 1182	DATE
1114, 1184, 1115, 1185	DATETIME
1043, 1002, 1003, 1014, 1015, 1008, 1009, 2951	STRING

Schema Retrieval

The PostgreSQL query runner extracts schema information from both information_schema and pg_catalog:

# Get schema for all tables
schema = runner.get_schema()

# Get columns for a specific table
columns = runner.get_table_columns("users")

# Get column types for a specific table
column_types = runner.get_table_types("users")

Amazon Redshift

For Amazon Redshift, you can use the redshift type, which has a specific configuration:

{
  "host": "redshift-cluster.example.region.redshift.amazonaws.com",
  "port": 5439,
  "user": "username",
  "password": "password",
  "dbname": "database_name",
  "sslmode": "prefer",
  "adhoc_query_group": "default",
  "scheduled_query_group": "default"
}

Redshift Configuration Schema

Parameter	Type	Required	Description
host	String	Yes	Redshift cluster endpoint
port	Number	Yes	Redshift port (typically 5439)
user	String	Yes	Username for authentication
password	String	Yes	Password for authentication
dbname	String	Yes	Database name
sslmode	String	No	SSL mode (default: "prefer")
adhoc_query_group	String	No	Query group for ad-hoc queries (default: "default")
scheduled_query_group	String	No	Query group for scheduled queries (default: "default")

Amazon Redshift with IAM Authentication

For Redshift with IAM authentication, you can use the redshift_iam type:

{
  "host": "redshift-cluster.example.region.redshift.amazonaws.com",
  "port": 5439,
  "user": "username",
  "dbname": "database_name",
  "aws_region": "us-west-2",
  "clusterid": "redshift-cluster-1",
  "rolename": "arn:aws:iam::account-id:role/rolename",
  "aws_access_key_id": "your-access-key",
  "aws_secret_access_key": "your-secret-key"
}

RedshiftIAM Configuration Schema

Parameter	Type	Required	Description
host	String	Yes	Redshift cluster endpoint
port	Number	Yes	Redshift port
user	String	Yes	Username for authentication
dbname	String	Yes	Database name
aws_region	String	Yes	AWS region of the Redshift cluster
clusterid	String	No	Redshift cluster identifier
rolename	String	No	IAM role ARN to assume
aws_access_key_id	String	No	AWS access key ID
aws_secret_access_key	String	No	AWS secret access key
adhoc_query_group	String	No	Query group for ad-hoc queries
scheduled_query_group	String	No	Query group for scheduled queries

CockroachDB

CockroachDB uses the same configuration as PostgreSQL but with the type cockroach.

Implementation Details

The PostgreSQL query runner uses the psycopg2 library to connect to PostgreSQL databases. This library must be installed for the query runner to be enabled.

To install the required dependency:

pip install psycopg2-binary

For Redshift with IAM authentication, the boto3 library is also required:

pip install boto3

The query runner also includes support for:

• SSL connections with certificate verification
• Custom JSON encoding for PostgreSQL-specific types like Range
• Query cancellation
• Schema retrieval that respects user permissions