Skip to main content

​
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

​
Setup

1

Create an Omni database user

  1. In your Postgres database, run the following command to create a database user for Omni:
    Create Omni user
  2. 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
  3. Lastly, ensure any tables created in the future will be accessible:
    Grant permissions to future tables to the Omni user
  4. Optional. Enable the pg_stat_statements extension to use the Modeling Agent’s Suggest model from query history skill:
    Enable pg_stat_statements extension
2

Set up a table upload schema

This step is optional. However, we recommend completing it as part of the initial set up or you won’t be able to use uploaded files like CSVs in joins.
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 Omni user permissions to upload schema
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.
4

Create the connection in Omni

  1. In Omni, click Settings > Connections.
  2. Click the Postgres option.
  3. On the connection setup page, fill in the connection details:
    ​
    required
    A user-friendly name for the connection, which will be used throughout Omni.
    ​
    required
    The address of the database server.
    ​
    default:"5432"
    required
    The port number for the database connection.
    ​
    required
    The database to connect to.
    ​
    required
    The username for database authentication.
    ​
    required
    The password for database authentication.
    ​
    required
    The timezone used by the database.
    ​
    required
    The timezone to use for Omni queries. If a timezone is specified, data will be converted from the Database Timezone to the selected timezone.
    ​
    When enabled, users can override the connection timezone with their own when querying.
    ​
    default:"900"
    Maximum time in seconds before a query times out.
    ​
    default:"Both"
    Controls how Omni infers relationships during schema refreshes. Select from four modes:
    • Both - Infer from foreign key constraints and column name matching
    • Foreign keys only - Foreign key constraints only
    • Column names only - Column name matching only
    • None - Skip auto-generation entirely
    ​
    default:"enabled"
    When enabled, Omni will prefix generated view names with schema and catalog, e.g. my_catalog_public__orders instead of orders. Defaults to enabled. See View name generation for more information.
    ​
    required
    Select the minimum level of access users should have to models in the connection.
  4. When finished, click Create connection.

​
What’s next?

Now that your database is set up, you can: