PostgreSQL

Connecting to PostgreSQL

To connect Semaphor to your PostgreSQL database, you need to use a connection string in the following format:

Connection String Format

postgresql+psycopg2://username:password@server:port/db

• username – Your database username.
• password – The password for the specified user.
• server – The hostname or IP address of your PostgreSQL server.
• port – The port on which PostgreSQL is running (default: 5432).
• db – The name of the database you want to connect to.

Example Connection String

If your database username is admin, password is mypassword, hosted on db.example.com using the default PostgreSQL port (5432), and the database name is analytics_db, your connection string would be:

postgresql+psycopg2://admin:mypassword@db.example.com:5432/analytics_db

Prerequisites

To successfully connect Semaphor to your PostgreSQL database, ensure the following requirements are met:

Database Permissions

• The user account must have read-only access to the required database.
• The user must have the ability to create session-specific temp tables.
• The user must have access to query INFORMATION_SCHEMA tables for metadata population.

Network Access

• If your database is private, whitelist the Semaphor IP: 44.199.53.142 to allow connections.
• Ensure that firewall rules and security group settings permit network access for Semaphor.

Verifying Information Schema Access

Semaphor requires access to INFORMATION_SCHEMA tables to populate database metadata. To verify that your PostgreSQL user has the necessary permissions, run the following diagnostic queries:

For Tables and Views:

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE table_catalog = 'your-db-name'
AND table_schema = 'your-schema-name';

For Schemas:

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
WHERE catalog_name = 'your-db-name';

Replace the placeholders:

• your-db-name – The name of your database (e.g., analytics_db)
• your-schema-name – The schema you want to query (e.g., reporting)

Expected Results:

• The tables query should return a list of tables and views in your specified database and schema
• The schemas query should return a list of available schemas in your database
• If either query fails or returns no results when you expect data to exist, the Semaphor user likely lacks the required INFORMATION_SCHEMA permissions

Troubleshooting

If you encounter issues connecting to PostgreSQL, consider the following:

• Invalid Credentials: Verify your username and password.
• Network Restrictions: Ensure your firewall or security group allows connections to the PostgreSQL server.
• Incorrect Connection String: Double-check the format of your connection string.
• Database Permissions: Ensure your user has the required privileges to access the database.
• Metadata Population Issues: If Semaphor cannot display database tables or schemas, verify that your user can query INFORMATION_SCHEMA tables using the diagnostic queries above.