Configure a catalog integration for Snowflake Postgres¶

Prerequisites¶

• A Snowflake account with the Snowflake Postgres feature enabled.

• A Postgres instance in READY state. To create a Postgres instance, see CREATE POSTGRES INSTANCE.

• The role that creates the catalog integration must have the following privileges:

  • USAGE on the Postgres instance.

  • CREATE INTEGRATION on the account.

Considerations¶

Consider the following when you use a Snowflake Postgres catalog integration:

• Snowflake Postgres catalog integrations always use vended credentials. You must set ACCESS_DELEGATION_MODE = VENDED_CREDENTIALS. The EXTERNAL_VOLUME_CREDENTIALS mode is not supported.

• You don’t need to specify REST_AUTHENTICATION. Snowflake handles authentication with the Postgres instance automatically.

• Auto-refresh uses metadata polling. Change notifications are not supported.

• Tables are read-only. Write operations to Postgres-managed Iceberg tables are not supported.

• This feature is not supported with burstable Postgres instances.

• This feature is supported on AWS only.

Create a catalog integration for Snowflake Postgres¶

To create a catalog integration for Snowflake Postgres, use the CREATE CATALOG INTEGRATION (Snowflake Postgres) command.

For example:

CREATE CATALOG INTEGRATION my_postgres_catalog_int
  CATALOG_SOURCE = SNOWFLAKE_POSTGRES
  TABLE_FORMAT = ICEBERG
  CATALOG_NAMESPACE = 'public'
  REST_CONFIG = (
    POSTGRES_INSTANCE = 'my_pg_instance'
    ACCESS_DELEGATION_MODE = VENDED_CREDENTIALS
    CATALOG_NAME = 'my_database'
  )
  ENABLED = TRUE;

For the full syntax and parameter reference, see CREATE CATALOG INTEGRATION (Snowflake Postgres).

Create an Iceberg table¶

After you create the catalog integration, you can create Iceberg tables that reference tables in your Postgres instance. When you create an Iceberg table with a Snowflake Postgres catalog integration, you specify the catalog integration and the table name as it exists in the Postgres database.

For example:

CREATE ICEBERG TABLE my_iceberg_table
  CATALOG = 'my_postgres_catalog_int'
  CATALOG_TABLE_NAME = 'orders'
  AUTO_REFRESH = TRUE;

Where:

• CATALOG = '<catalog_integration_name>': Specifies the name of the catalog integration. Required.

• CATALOG_TABLE_NAME = '<table_name>': Specifies the name of the table as it exists in the Postgres database. Required.

• CATALOG_NAMESPACE = '<schema_name>': (Optional) Specifies the Postgres schema that contains the table. Defaults to the CATALOG_NAMESPACE value set on the catalog integration.

• CATALOG_NAME = '<database_name>': (Optional) Specifies the Postgres database that contains the table. Defaults to the CATALOG_NAME value set on the catalog integration.

• AUTO_REFRESH = TRUE: (Optional) Enables automatic metadata refresh. When enabled, Snowflake periodically polls the catalog for metadata changes and refreshes the table automatically.

Create a catalog-linked database¶

Instead of creating individual Iceberg tables, you can create a catalog-linked database that automatically discovers and syncs tables from your Postgres database. A catalog-linked database creates corresponding schemas and Iceberg tables in Snowflake that stay in sync with your Postgres database.

For example:

CREATE DATABASE my_postgres_db
  LINKED_CATALOG = (
    CATALOG = my_postgres_catalog_int
    ALLOWED_WRITE_OPERATIONS = NONE
  );

Where:

• CATALOG = <catalog_integration_name>: Specifies the name of the Snowflake Postgres catalog integration. Required. The catalog-linked database syncs the Postgres database specified by the CATALOG_NAME value on the catalog integration.

• ALLOWED_WRITE_OPERATIONS = NONE: Required for Snowflake Postgres catalog-linked databases. Snowflake Postgres tables are read-only.

After the catalog-linked database is created, Snowflake automatically discovers namespaces and tables from the Postgres database and creates corresponding schemas and Iceberg tables. For more information, see Use a catalog-linked database for Apache Iceberg™ tables.

Query the Iceberg table¶

After you create an Iceberg table or a catalog-linked database, you can query the tables using standard SQL:

SELECT * FROM my_iceberg_table;

Access control¶

The catalog integration owner role must have USAGE on the Postgres instance. Snowflake checks this privilege when you create the catalog integration.

Any role that has USAGE granted on the catalog integration can create Iceberg tables that reference tables in the Postgres instance. No additional grants on the Postgres instance are required. The role also needs the standard Snowflake privileges to create tables in the target database and schema.

For example, to grant another role the ability to create Iceberg tables using the catalog integration:

GRANT USAGE ON INTEGRATION my_postgres_catalog_int TO ROLE analyst_role;
GRANT USAGE ON DATABASE my_db TO ROLE analyst_role;
GRANT USAGE ON SCHEMA my_db.public TO ROLE analyst_role;
GRANT CREATE ICEBERG TABLE ON SCHEMA my_db.public TO ROLE analyst_role;