Quickstart Transformation Removes Table Privileges

Issue

My Quickstart transformation removes the privileges I assign to user roles on my PostgreSQL tables. Each run recreates the tables, and my users lose access until I reapply the privileges.

Environment

Destination: PostgreSQL

Resolution

To resolve this issue, configure default privileges for the database user that creates the transformation tables.

1. Log in to PostgreSQL as a superuser or admin.
2. Run this command for each schema where the transformation writes data:

ALTER DEFAULT PRIVILEGES FOR USER <fivetran_user> IN SCHEMA <schema_name>
GRANT SELECT ON TABLES TO <role_name>;

3. Add additional privileges if needed:

ALTER DEFAULT PRIVILEGES FOR USER <fivetran_user> IN SCHEMA <schema_name>
GRANT SELECT, INSERT, UPDATE ON TABLES TO <role_name>;

4. Run the transformation again.
5. Confirm that the privileges persist.

Cause

This issue occurs when a Quickstart transformation recreates its tables during each run. PostgreSQL assigns privileges to specific table objects, so newly created tables don't retain privileges unless default privileges are set.