How do I provide access to certain rows that are common for all users when implementing PostgreSQL Row Level Security?

All we need is an easy explanation of the problem, so here it is.

We have a multi-tenant database and this is a table in which we have implemented row-level security based on tenant_id:

tenant_id product_id
0 A
0 B
1 1A
1 1B
2 2A
2 2B

Our requirement is that the products associated with tenant_id = 0 should be available to all tenants.
Products associated with tenant_id != 0 should only be available to the relevant tenants.

Is there a way to implement this using security definers?
Or do we need to have separate tables?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

you can always write multiple conditions while writing RLS policies.
for example: in your case, create policy *policy_name* on *table_name* for all to public using (tenant_id=current_user or tenant_id = 0)

Reference: enter link description here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply