Newly Created Partitions Fail With Permission Errors After Granting SELECT on All Tables

Issue

After granting SELECT permissions using GRANT SELECT ON ALL TABLES IN SCHEMA on a PostgreSQL database with partitioned tables, newly created partitions fail with permission errors.

The authorizing user appears to have the required permissions, but the failures continue for any partition added after the initial grant.

Environment

Connector: Amazon RDS for PostgreSQL

Resolution

To resolve this issue, configure default privileges so future partitions automatically grant SELECT permissions to the Fivetran user.

Identify the partition creator role

Determine which database user or role creates your partitions. This is typically an application user, automated script, or cron job. To check the owner of existing partitions, run:

SELECT tablename, tableowner 
FROM pg_tables 
WHERE schemaname = 'your_schema_name' 
AND tablename LIKE 'your_partition_pattern%';

Grant SELECT on future partitions

Run the following command, replacing <partition_creator_role>, <schema_name>, and <fivetran_user> with the actual values:

ALTER DEFAULT PRIVILEGES FOR ROLE <partition_creator_role> 
IN SCHEMA "<schema_name>" 
GRANT SELECT ON TABLES TO <fivetran_user>;

If multiple roles create partitions, repeat this command for each role.

Grant permissions on existing partitions

Grant SELECT permissions on all existing tables and partitions in the schema:

GRANT SELECT ON ALL TABLES IN SCHEMA "schema_name" TO fivetran_user;

After you complete these steps, the fivetran_user has access to existing partitions and automatically receives access to future partitions created by the specified role.

Cause

PostgreSQL creates each partition as a separate child table. When you run GRANT SELECT ON ALL TABLES IN SCHEMA, PostgreSQL grants permissions only on tables that exist at that time. Partitions created later do not inherit those permissions.

For Query-Based sync methods, Fivetran queries each partition directly instead of querying only the parent table. Therefore, the Fivetran user must have explicit SELECT permissions on every partition.