PostgreSQL on-prem

Follow these steps to connect your on-prem PostgreSQLto Select Star.

Before you start

To connect PostgreSQL hosted on-prem to Select Star, you will need...

  • access to admin user of PostgreSQL hosted on-prem

1. Create PostgreSQL user

Connect to the PostgreSQL database using an administrative user account and create a new user (service account) for the integration, by executing SQL query:

CREATE USER selectstar WITH encrypted password 's313ctst8r'

Replace s313ctst8r with strong and secure password.

Then, it is necessary to grant permissions to selected databases and schemas. To do this, run the following query individually in the context of the selected database for each schema:

GRANT USAGE ON SCHEMA <schema_name> TO selectstar;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO selectstar;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO selectstar;

Replace <schema_name> with the expected schema name and repeat for each databases & schemas.

2. Ensure network connectivity

To establish a connection between Select Star and your PostgreSQL instance, it is essential that your PostgreSQL instance is accessible from the following IP addresses:

  • 3.23.108.85

  • 3.20.56.105

If your PostgreSQL instance is protected by a firewall, you'll need to add these two IP addresses to your whitelist to allow for the connection. If you encounter any challenges or require further assistance in adapting these configurations to your specific network topology, please don't hesitate to reach out to our technical support team for expert guidance and solutions.

3. Create a new data source

1. Go to the Select Star Settings. Click Data in the sidebar, then + Add to create a new Data Source.

2. Fill form in the required information:

  • Source Type: Select "PostgreSQL"

  • Display Name: This value is PostgreSQL by default, but you can override it if desired.

  • Hostname: The public hostname of your instance.

  • Port: The port used to connect. By default is 5432, but you can adjust it if required.

  • Username: The PostgreSQL user name to connect. In our examples of SQL queries above, we use selectstar.

  • Password: The password of the PostgreSQL user.

  • DB Name: Any database we have access to that is used to initiate a first connection.

3. Click Connect.

2. Fill form in the required information:

  • Source Type: Select "None"

3. Click Connect again.

Having no access to query logs can result in some limitations for the Select Star function. Specifically, information on popularity and lineage, except for those that result from views, will not be available. If none of the available integrations fit your needs, please feel free to contact us so that we can explore alternative solutions for log delivery.

4. Choose databases and schemas

After you fill in the information, you'll be asked to select the databases you'd like to load into Select Star.

Select Star will not read queries or metadata or generate lineage for databases, schemas, or tables that are not loaded. Please load all data for which you expect to see lineage.

You can change the databases and schemas you have loaded if needed.

Select the database and click Next.

For each database you selected, you'll be able to select the schemas.

Your metadata should start loading automatically. Please allow 24-48 hours to completely generate popularity and lineage.

When the sync is complete, you'll be able to explore PostgreSQL in Select Star.

Last updated