PostgreSQL

This guide provides step-by-step instructions for connecting PostgreSQL to Unistream.

Prerequisites

You will need:

• Access to your PostgreSQL instance

• Superuser privileges to create a new user (or existing user credentials)

• Host address and port number

• Unistream platform credentials

---

Step 1: Configure Access in PostgreSQL

Before connecting to Unistream, you need to create a dedicated user in PostgreSQL and grant appropriate permissions.

1.1 Create User

Connect to your PostgreSQL instance (using psql, pgAdmin, or another tool) and run the following commands:

-- Create a new user with password
CREATE USER <username> WITH PASSWORD '<password>';

Replace:

• <username> with your desired username

• <password> with a secure password

1.2 Grant Permissions

Choose the appropriate permission level based on your needs:

Option A: Grant Access to Entire Database

-- Grant connection privilege to the database
GRANT CONNECT ON DATABASE <database> TO <username>;

-- Grant usage on schema
GRANT USAGE ON SCHEMA <schema> TO <username>;

-- Grant select on all existing tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO <username>;

-- Grant select on future tables (optional, but recommended)
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO <username>;

Replace:

• <database> with your database name

• <schema> with your schema name (typically public)

Option B: Grant Access to Specific Tables

-- Grant connection privilege to the database
GRANT CONNECT ON DATABASE <database> TO <username>;

-- Grant usage on schema
GRANT USAGE ON SCHEMA <schema> TO <username>;

-- Grant select on specific tables
GRANT SELECT ON <schema>.<table1> TO <username>;
GRANT SELECT ON <schema>.<table2> TO <username>;

Replace:

• <database> with your database name

• <schema> with your schema name

• <table1>, <table2>, etc. with your table names

Option C: Grant Access to Specific Columns

-- Grant connection privilege to the database
GRANT CONNECT ON DATABASE <database> TO <username>;

-- Grant usage on schema
GRANT USAGE ON SCHEMA <schema> TO <username>;

-- Grant select on specific columns
GRANT SELECT (<column1>, <column2>, ...) ON <schema>.<table> TO <username>;

Replace:

• <database> with your database name

• <schema> with your schema name

• <table> with your table name

• <column1>, <column2>, etc. with your column names

1.3 Example: Complete Setup

Here's a complete example for a database named salesdb:

-- Create user
CREATE USER unistream_user WITH PASSWORD 'SecureP@ssw0rd123!';

-- Grant database connection
GRANT CONNECT ON DATABASE salesdb TO unistream_user;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO unistream_user;

-- Grant select on all current tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO unistream_user;

-- Grant select on all future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO unistream_user;

---

Step 2: Connect to Unistream

2.1 Add PostgreSQL Source

1. In Unistream, navigate to Lake

2. Click Add Source

3. Select PostgreSQL

2.2 Configure Connection

Fill in the General Fields:

• Name: Enter a descriptive name for your connection (e.g., "Production PostgreSQL")

• Code: Enter a unique code identifier (e.g., "prod_postgres")

Fill in the Connection Details:

• Host: Your PostgreSQL server address

• Port: PostgreSQL port (default: 5432)

• Username: The username you created in Step 1 (e.g., unistream_user)

• Password: The password you set in Step 1

• (Optional) Database: The name of the database you want to connect to (e.g., salesdb)

2.3 Test and Save

1. Click Test Connection to verify the connection is working

2. If the connection is successful, you'll see a success message

3. Click Add to save your connection

Need Help?

If you encounter any issues not covered in this guide, please contact Unistream support