How Do I Ensure Fivetran Has Access to Required System Columns For the XMIN Sync Method?

Question

How do I ensure that Fivetran has access to required system columns used by the XMIN sync method?

Environment

• Connector: PostgreSQL
• Sync method: XMIN system column

Answer

Verify that the xmin, xmax and ctid columns are accessible to Fivetran.

Fivetran must be able to access the xmin, xmax and ctid columns in every table that needs to be synced. If table level read-only access was granted, then we will have access to these columns. If column level permissions were granted, then manual access likely needs to be granted. This is at the table-level, which means we must identify which table is missing the permission first and then add the permission to those tables accordingly.

Find which tables are missing access to the required columns

Run the following query to find which tables are missing access to the required columns:

SELECT tables.relname AS table_name, pn.nspname AS schema_name
FROM pg_catalog.pg_class tables
JOIN pg_catalog.pg_attribute columns ON columns.attrelid = tables.oid
JOIN pg_catalog.pg_namespace pn ON pn.oid = tables.relnamespace
WHERE tables.relname IS NOT NULL
AND (tables.oid IS NULL OR (tables.relname != 'spatial_ref_sys' AND tables.relkind IN ('r', 'p')))
AND has_column_privilege(tables.oid,columns.attname,'SELECT') = false 
AND columns.attname in ('xmin','xmax','ctid');

Grant access to tables that need it

Compare the returned list to the connector's schema tab. Any tables that are enabled to sync and appear in the result of the above query must have access granted to the required columns. Here is an example of how to enable access on a table named example_table:

GRANT SELECT(xmin, xmax, ctid) ON example_table TO FIVETRAN_CONNECTOR_DATABASE_USERNAME_HERE;