Prerequisites

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Core Connector Core connectors are included with all Alation platform tiers (subject to each tier’s connector limits) and are fully supported by Alation.

Before you install the PostgreSQL OCF connector, ensure that you:

Configure Network Connectivity

Open outbound TCP port 5432 to PostgreSQL server.

Create a Service Account

Sample SQL to create an account

CREATE USER alation WITH PASSWORD '[password]';

Grant Required Permissions

The service account you want to use requires a specific set of permissions on PostgreSQL.

Grant Permissions for Connection

GRANT CONNECT ON DATABASE [database_name] TO alation;

Grant Permissions for Metadata Extraction

Grant Access on External Schema

GRANT USAGE ON SCHEMA <external_schema_name> TO <user_name>;

PG_CATALOG Schema Access

To grant access to PG_CATALOG schema, use the following query:

GRANT USAGE ON SCHEMA PG_CATALOG TO alation;

To grant access on all the views in PG_CATALOG schema, use the following query:

GRANT SELECT ON ALL TABLES IN SCHEMA PG_CATALOG TO alation;

To grant access to specific views, refer the following information:

GRANT SELECT ON PG_CATALOG.PG_NAMESPACE TO alation;
GRANT SELECT ON PG_CATALOG.PG_TABLES TO alation;
GRANT SELECT ON PG_CATALOG.PG_DESCRIPTION TO alation;
GRANT SELECT ON PG_CATALOG.PG_STAT_ALL_TABLES TO alation;
GRANT SELECT ON PG_CATALOG.PG_INHERITS TO alation;
GRANT SELECT ON PG_CATALOG.PG_CLASS TO alation;
GRANT SELECT ON PG_CATALOG.PG_VIEWS TO alation;
GRANT SELECT ON PG_CATALOG.PG_MATVIEWS TO alation;
GRANT SELECT ON PG_CATALOG.PG_REWRITE TO alation;
GRANT SELECT ON PG_CATALOG.PG_ATTRIBUTE TO alation;
GRANT SELECT ON PG_CATALOG.PG_TYPE TO alation;
GRANT SELECT ON PG_CATALOG.PG_ATTRDEF TO alation;
GRANT SELECT ON PG_CATALOG.PG_CONSTRAINT TO alation;
GRANT SELECT ON PG_CATALOG.PG_INDEX TO alation;
GRANT SELECT ON PG_CATALOG.PG_AM TO alation;
GRANT SELECT ON PG_CATALOG.PG_PROC TO alation;
GRANT SELECT ON PG_CATALOG.PG_LANGUAGE TO alation;

Use the following information for the list of views used for extracting various metadata types:

Metadata Type

System Views

Schema

PG_NAMESPACE

Table

PG_TABLES, PG_DESCRIPTION, PG_STAT_ALL_TABLES,

PG_INHERITS, PG_CLASS, PG_NAMESPACE

View

PG_VIEWS, PG_MATVIEWS, PG_REWRITE

Column

PG_ATTRIBUTE, PG_CLASS, PG_NAMESPACE, PG_TYPE, PG_DESCRIPTION, PG_STAT_ALL_TABLES, PG_INHERITS, PG_ATTRDEF

Primary Key

PG_CONSTRAINT, PG_ATTRIBUTE, PG_CLASS, PG_NAMESPACE

Foreign Key

PG_CONSTRAINT, PG_ATTRIBUTE, PG_CLASS, PG_NAMESPACE

Index

PG_INDEX, PG_CLASS, PG_ATTRIBUTE, PG_AM, PG_TABLES

Function

PG_PROC, PG_NAMESPACE, PG_LANGUAGE

Function Definition

PG_PROC, PG_NAMESPACE, PG_LANGUAGE

Grant Permissions for Table or View Profiling

GRANT USAGE ON SCHEMA [schema] TO alation;
GRANT SELECT ON ALL TABLES IN SCHEMA [schema] TO alation;

Grant Permissions for Query Log Ingestion

GRANT USAGE ON SCHEMA <qli_schema_name> TO alation;
GRANT SELECT ON <qli_schema_name>.<qli_table_name> TO alation;