Skip to main content

Connecting Postgres to Omni

In this guide, you'll learn how to connect your Postgres database to Omni.

Requirements​

To follow the steps in this guide, you'll need:

  • Organization Admin permissions in Omni
  • Permissions in Postgres that allow you to create database users

Step 1: Create an Omni database user​

In your Postgres database, run the following command to create a database user for Omni:

Create Omni user
CREATE USER omni WITH ENCRYPTED PASSWORD '<password>';
GRANT CONNECT ON DATABASE database_name to omni;
\c database_name
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO omni;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO omni;

Next, you'll grant permissions to the Omni user. If you're using a schema other than public, run the following to grant usage to Omni:

Grant usage to the Omni user
GRANT USAGE ON SCHEMA schema_name TO omni

Lastly, run the following to make any tables created in the future will be accessible:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON tables TO omni;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON sequences TO omni;

Step 2: Set up a table upload schema​

note

This step is optional, but Omni recommends completing it as part of the initial connection setup.

In this step, you'll create a dedicated schema to use for table uploads. This schema can't be used for other modeled tables.

  1. Create the schema in Postgres.

  2. Run the following commands to grant the Omni user the required privileges:

    GRANT USAGE, CREATE ON SCHEMA upload_schema_name TO omni;

Step 3: Allowlist Omni's IP addresses​

If access to the Postgres database is limited by IP address, you'll need to add Omni's IPs to the allowlist before you create the database connection.

Omni's IP addresses can be found on an individual connection's page, accessed by navigating to Settings > Connections and clicking a connection.

Step 4: Create the connection in Omni​

  1. In Omni, click Settings > Connections.

  2. Click Add connection.

  3. Click Postgres.

  1. Fill in the fields as follows:

    • Display name - Required. Enter a name for the connection, which will display in the connections list in Omni.

    • Host - Required. Enter the host address for the database.

    • Post - Required. Enter the port used by the database.

      The default for Postgres is 5432.

    • Database - Required. Enter the name of the database Omni should connect to.

    • Default schema - Required. Enter the name of the default schema for the database.

    • Include schemas - To only include specific schemas, enter the names of the schemas as a comma-separated list. Leaving this field blank will allow Omni to access all schemas in the database.

    • Username - Required. Enter the username of the database user you want Omni to use.

      This is the Omni user you created in Step 1.

    • Password - Required. Enter the password for the database user.

      This is the password you assigned to the Omni user in Step 1.

    • Schema for Table Uploads - Enter the name of the where table uploads will be stored. If left blank, you'll still be able to upload tables but they won't be pushed to the database.

    • Database Timezone - Required. Select the timezone used by the database.

    • Query timezone - Required. If specified, data will be converted from the Database timezone to this timezone when querying. Refer to the Converting timezone data guide for more information.

    • Allow User-Specific Timezones - Check this option to allow individual users' Query timezone settings to be used as the query timezone for the connection.

    • Query timeout - Enter the number of seconds a query has to execute before the database cancels it. The default is 900 seconds.

  2. When finished, click Create connection.

What's next?​

Now that Postgres is set up, you can: