PostgreSQL

Add PostgreSQL as a decube connection and help your team discover, document and monitor their data assets to drive data-driven insights and decisions.

Supported Capabilities

Capability	Supported
Metadata Extraction	✅
Metadata Types Collected	Schema, Table, Column, View
Data Profiling	✅
Data Preview	✅
Data Quality	✅
Configurable Collection	✅
External Table	❌
View Table	✅
Stored Procedure	❌

Data Quality Support

Capability	Supported
Freshness	✅
Volume	✅
Field Health	✅
Custom SQL	✅
Schema Drift	✅
Job Failure	❌

Lineage Support

Capability	Supported
View Table Lineage	✅
External Table Lineage	❌
SQL Query Lineage	❌
Foreign Key Lineage	✅
Stored Procedure Lineage	❌

Connection Requirements

Connecting to decube is as easy as providing us with credentials to your PostgreSQL database. At a minimum, we require:

• username

• password

• host address

• host port

• database name

PostgreSQL

The source name will be for you to differentiate and recognize particular sources within the decube application.

We strongly encourage you to create a decube read-only user for this credential purpose, which you can follow here.

Security Concerns

If access to your database is protected by security measures, we allow for connecting via SSH Tunneling or you could whitelist our IP. See more here.

Custom User for decube

We highly recommend that you create a Read-Only user for decube. We have prepared a script that you may run on your PostgreSQL database to create this user.

1. Create a New User for decube

CREATE ROLE decube WITH LOGIN ENCRYPTED PASSWORD 'a_new_password';

2. Allow connection to the database

GRANT CONNECT ON DATABASE "your_db_name" TO decube;

3. We need access to information_schema

GRANT USAGE ON SCHEMA information_schema TO decube;
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO decube;

4. You may need to run this per schema that you have based on the default behavior of the schema.

GRANT USAGE ON SCHEMA <schema_name> TO decube; 
GRANT SELECT ON ALL TABLES IN SCHEMA TO decube;

If you want all of the tables in all your schemas to be included in decube, run the following SQL script:

DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT nspname FROM pg_namespace where nspname !~* 'pg|information_schema'
    LOOP
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO decube $$, sch);
    EXECUTE format($$ GRANT SELECT ON ALL TABLES IN SCHEMA %I TO decube $$, sch);
    END LOOP;
END;
$do$;