RLS error due to role updating table lacking permisions

[Zurnaz]

PGSync version: Master Postgres version: 14 Elasticsearch version: OpenSearch_1.0 Redis version: ElastiCache redis6.x Python version: 3.8

Problem Description: The trigger causes a permission issue when row level security is enabled due to the role updating a table not having sufficient permissions.

The fix is the use security definer against the trigger.

The set search_path is required due to CVE-2018-1058

Here is the change I made and tested it fixes the issue and doesn't break any tests but I'm unsure if there are other considerations to be made.

https://github.com/Zurnaz/pgsync/commit/926e2ef4c92cf05d5b0ff685c6826812a2a2248c

[toluaina]

• I think we only want to do this only if Row level security is enabled. is that a correct assumption?
• Also is does the search_path need to include other schemas besides public?

[toluaina]

• I guess we would only need to apply these changes to the trigger on bootstrap.
• We would also need to include all Postgres schemas in the schema.json - including public
• What is it recommended way for determining if RLS is enabled/configured?

[Zurnaz]

• Yep I had to manually update the trigger manually via db because I noticed it wasn't swapping to the newer version
• Not sure about this but I made sure to include the schema property on the tables I wanted to access
• It's applied at the table level, if a table doesn't have it enabled it defacto becomes open to the roles that have broad access to the specific schema. It doesn't apply to views so they currently bypass rls but I think v15 of postgresql has an update for it.

Not sure the best way to check. Found a stackoverflow post on checking the rls and tested it on a table with/without rls enabled. relrowsecurity returned a boolean if the table had the security enabled correctly.

[Zurnaz]

• I think we only want to do this only if Row level security is enabled. is that a correct assumption?
• Also is does the search_path need to include other schemas besides public?

Sorry I just realised I didn't see your first post and only replied to the second one.

• I think that's up to you but I don't think it would affect a non rls enabled tables if was included and as the role running the trigger would always be the owner of the function, unless the desired result is for the trigger to fail when the role updating the table doesn't have the required permissions for the trigger
• I don't think so, I've seen examples with just pg_catalog, pg_temp options and I use similar functions to call multiple different schemas with different tables that are not listed in the search path.

I'm happy to try/test stuff out but I think I'm at the limit of knowledge in this area to directly advise.