row level security on source tables

[docteurklein]

Hi! I'm reading the source code and https://github.com/trickle-labs/pg-trickle/blob/main/plans/sql/PLAN_ROW_LEVEL_SECURITY.md#current-state
and I should be able to to DIFFERENTIAL maintenance using cdc_mode => WAL on a source with RLS enabled, since the bgworker is a superuser and the wal contains all diffs, regardless of RLS.

Is it currently possible? It seems like pg-trickle is overly restrictive here, there should be a way to force differential mode, although I understand the caution for correctness.

If I follow what is said in https://github.com/trickle-labs/pg-trickle/blob/main/docs/SQL_REFERENCE.md#row-level-security-rls,
if the stream table is owned by a superuser, then the source's policies should be ignored and I should be able to continue without a forced fallback to FULL mode.

[grove]

Hi, thanks for digging into this. You're right that the current check is too broad for the case you describe.

It is not currently possible. pg_trickle falls back to FULL whenever any source has RLS enabled, without checking whether RLS actually applies to the stream-table owner.

One nuance is that the scheduler's superuser identity is not what executes the defining SQL. pg_trickle switches to the stream-table owner with row_security = on. However, if that owner is a superuser or has BYPASSRLS, PostgreSQL
ignores source RLS, including FORCE ROW LEVEL SECURITY. WAL CDC also requires REPLICA IDENTITY FULL, so pg_trickle has the old and new row images needed for differential maintenance.

I think we can safely narrow the restriction:

• Allow DIFFERENTIAL with WAL when the current stream-table owner is a superuser or has BYPASSRLS.
• Keep the FULL fallback when the owner is subject to RLS.
• Recheck this during refresh so changing the owner or revoking BYPASSRLS fails safely.

WAL alone is not enough for owners subject to RLS because policies can depend on other tables or functions, changing visibility without producing a WAL event for the protected source.

So yes, this should be supported. Until that check is narrowed, FULL is the only supported mode for an RLS-enabled source. Thanks for raising it.

[grove]

I have created GitHub issue #1030.

[docteurklein]

Thank you so much for your time, this project and your reactivity :) I'm gonna test this right away.