Skip to main content

Search

How can we help you?

How do I configure PostgreSQL Credentials?

This guide outlines the process of creating a user, granting the necessary permissions, and adding the credentials to the ThinkData Platform to connect to a PostgreSQL database.

Step-by-Step Guide:

Step 1: Create a User

To create a user in your PostgreSQL instance, follow these steps:

  1. Log in to the PostgreSQL instance using your preferred SQL client or workbench (e.g., DataGrip, Cloud SQL Studio).

  2. Create a new user with a secure password:

    CREATE USER username WITH PASSWORD 'changeme';
    Replace 'username' with the desired username and 'changeme' with a secure password.   

             Password Recommendation:

    • Use a password that is 64 characters long, consisting of alphanumeric characters for maximum security.
    • Note: Avoid using special characters in the password, as they may cause connection issues.

Step 2: Grant Permissions to the User

After creating the user, grant only the minimum necessary permissions listed below to enable secure access for the ThinkData Platform.

Basic Permissions

The following permissions are required to connect, list, and virtualize data assets from PostgreSQL in the ThinkData Platform.

 

  1. Grant schema access to the user to enable access to the schema: 
    GRANT USAGE ON SCHEMA schema_name TO username;
    Replace 'schema_name' with the name of the schema you wish to provide access to.       
  2. Grant SELECT permission on the required tables within the schema: 
    GRANT SELECT ON schema_name.table_name TO username;
    Replace `schema_name.table_name` with the specific table you want the user to access.
  3. (Optional) Grant SELECT permission on all tables in a schema: 
    GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;
    Replace 'schema_name' with the name of the schema you wish to provide access to.       
  4. (Optional) Ensure future tables inherit SELECT privileges: 
    ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO username;
    This ensures that any new tables created in the schema will automatically have the required SELECT permissions for the user.

Additional Permissions for Managing Data Products

If the user needs to create or manage data products from existing data assets in the ThinkData Platform, grant the following additional permissions. Alternatively, you may create a new user and assign these permissions if more precise access control is required.

 

  1. Grant CREATE permission on the schema to allow object creation within the schema:

    GRANT CREATE ON SCHEMA schema_name TO username;

Step 3: Add PostgreSQL Credentials to the ThinkData Platform

After creating the above credentials in the Postgres instance, add them to the ThinkData Platform to allow data access by following the steps below. This step only needs to be completed once as long as you continue to use the same 'user' these credentials are associated with.

Steps to add PostgreSQL credentials to the platform:

  1. Open the platform and navigate to the Credentials page:
    • Data > Credentials > Create Credential
  2. Enter the user name these credentials belong to (or any other identifying name) in the Credential Name field.
  3. In the Type dropdown, select Postgres.
  4. Enter the Username and Password for the PostgreSQL user created earlier.
  5. Click Save to store the credentials.

Step 4: Using the Credentials

Now, you can use these credentials with a:

 

Related to