Postgres

Steps to prepare and connect to Postgres

Lightup account setup

Lightup needs a user account that can query data and metadata for all tables you want to monitor. You might already have a user set up that will work, or you might need to create one. Grant the user sufficient privileges using security roles.

You can run the following DDL to create a user account named lightup and grant it privileges; just substitute a password for <strong_password_here>:

CREATE USER lightup WITH LOGIN BYPASSRLS ENCRYPTED PASSWORD '<strong_password_here>' IN ROLE pg_read_all_data;

Postgres 11-13

Run the following DDL to set up a role and the lightup user account, with the following adjustments:

  • You can use whatever role name you want (readaccess is just an example).
  • GRANT CONNECT on all databases you want Lightup to see (cdb is just an example).
  • GRANT USAGE ON and GRANT SELECT ON ALL TABLES IN SCHEMA for all schemas you want Lightup to see (testdata is just an example).
  • Substitute your own password for **redacted**.
CREATE ROLE readaccess;
GRANT CONNECT ON DATABASE cdb TO readaccess;
GRANT USAGE ON SCHEMA testdata TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA testdata TO readaccess;
CREATE USER lightup WITH LOGIN BYPASSRLS ENCRYPTED PASSWORD '**redacted**';
GRANT ROLE readaccess TO lightup;

Connector setting

  • Host Name - The hostname for the datasource (check the browser address bar; you want the string between // and /). Example: https://host.na.me/
  • Database Name - The name of the database you're connecting to.
  • Username - Enter lightup.
  • Password - The username account's password.

Schema scan frequency

You can adjust how often scans run for a datasource.

  • In section 3 - Advanced, select a value for Schema scan frequency: Hourly, Daily, or Weekly.

Query governance

Postgres datasources support the Query history, Scheduling, Enable data storage, and Maximum backfill duration settings. For steps, see Set query governance settings for a datasource.