Skip to main content

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": "",
"ssh_tunnel_enabled": false,
"ssh_host": "",
"ssh_port": 22,
"ssh_username": ""
}

Configuration Schema​

ParameterTypeRequiredDescription
hostStringNoPostgreSQL server hostname or IP address (default: "127.0.0.1")
portNumberNoPostgreSQL server port (default: 5432)
userStringNoUsername for authentication
passwordStringNoPassword for authentication
dbnameStringYesDatabase name to connect to
sslmodeStringNoSSL mode (options: "disable", "allow", "prefer", "require", "verify-ca", "verify-full")
sslrootcertFileStringNoSSL Root Certificate (base64-encoded)
sslcertFileStringNoSSL Client Certificate (base64-encoded)
sslkeyFileStringNoSSL Client Key (base64-encoded)
ssh_tunnel_enabledBooleanNoWhether to use SSH tunneling (default: false)
ssh_hostStringNoSSH bastion host address
ssh_portNumberNoSSH port (default: 22)
ssh_usernameStringNoSSH username

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 IDLegion Type
20, 21, 23INTEGER
700, 701, 1700FLOAT
16BOOLEAN
1082, 1182DATE
1114, 1184, 1115, 1185DATETIME
1043, 1002, 1003, 1014, 1015, 1008, 1009, 2951STRING

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​

ParameterTypeRequiredDescription
hostStringYesRedshift cluster endpoint
portNumberYesRedshift port (typically 5439)
userStringYesUsername for authentication
passwordStringYesPassword for authentication
dbnameStringYesDatabase name
sslmodeStringNoSSL mode (default: "prefer")
adhoc_query_groupStringNoQuery group for ad-hoc queries (default: "default")
scheduled_query_groupStringNoQuery 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​

ParameterTypeRequiredDescription
hostStringYesRedshift cluster endpoint
portNumberYesRedshift port
userStringYesUsername for authentication
dbnameStringYesDatabase name
aws_regionStringYesAWS region of the Redshift cluster
clusteridStringNoRedshift cluster identifier
rolenameStringNoIAM role ARN to assume
aws_access_key_idStringNoAWS access key ID
aws_secret_access_keyStringNoAWS secret access key
adhoc_query_groupStringNoQuery group for ad-hoc queries
scheduled_query_groupStringNoQuery 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

SSH Tunneling​

To connect to PostgreSQL databases behind a firewall through a bastion host:

from legion_query_runner.query_runner import QueryRunner

# Configuration with SSH tunnel
pg_config = {
# Database connection
"host": "internal-postgres.example.com", # Internal database host
"port": 5432,
"user": "postgres",
"password": "password",
"dbname": "mydatabase",

# SSH tunnel configuration
"ssh_tunnel_enabled": True,
"ssh_host": "bastion.example.com", # Public bastion host
"ssh_port": 22,
"ssh_username": "ssh_user"
}

# Create runner - tunnel is established automatically when needed
runner = QueryRunner('pg', pg_config)

# Run queries as normal
results = runner.run_query("SELECT * FROM users")

The SSH tunnel is automatically established when running queries and cleaned up afterward. SSH key authentication is configured through the application settings.