Prerequisites

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of 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;