Synced Tables - Partitioned Tables

[tpde]

I am using synced tables with Lakebase Postgres and am seeing that all Postgres tables created via synced tables have a partition set based on the primary key I select when making the synced table. This is resulting in our downstream users in Postgres not having access to the table we made due to them not having partition access. Is there a way to denote a primary key without making the Postgres table a partitioned table? We are trying to grant select access on the whole table once instead of needing to maintain partition access.

[GabFernandes]

Hi @tpde ,

This is expected behavior — synced tables in Lakebase use hash partitioning on the primary key internally for sync pipeline performance (parallel upserts). There's currently no option to create a synced table without partitioning.

However, you shouldn't need per-partition grants. The fix is in how you're granting access. As databricks_superuser, run:

-- Grant schema-level usage first
GRANT USAGE ON SCHEMA <your_schema> TO <role>;

-- Then grant SELECT on the parent (partitioned) table
GRANT SELECT ON <your_synced_table> TO <role>;

In PostgreSQL 11+ (which Lakebase uses), GRANT SELECT on a partitioned parent table automatically propagates to all existing partitions. If your downstream users still can't access it, the issue is likely one of:

1. Missing USAGE on the schema — without this, the table-level grant is invisible to the role.
2. Grants were issued before some partitions were created (race condition with the sync pipeline creating new partitions). Fix with:

-- Covers all existing tables in the schema, including partition tables
GRANT SELECT ON ALL TABLES IN SCHEMA <your_schema> TO <role>;

-- Ensures future partitions (created by the sync pipeline) inherit the grant
ALTER DEFAULT PRIVILEGES IN SCHEMA <your_schema> GRANT SELECT ON TABLES TO <role>;

Important note: Synced tables are owned by the internal databricks_writer_<dbid> role (not by you), so only databricks_superuser can issue these grants. Regular users with pg_read_all_data (which databricks_superuser has) bypass partition-level checks entirely, which is why the creator can read it but other roles can't.

The ALTER DEFAULT PRIVILEGES approach is the "set it and forget it" solution — any new partitions created during future syncs will automatically inherit the SELECT grant.

If my answer was helpful, please consider marking it as accepted solution!