CODEWITHSUNDEEP
sqlpostgresqlselectpostgresql-10row-level-security
Denis Yakovenko
Denis Yakovenko

Reputation: 3535

Set rows visibility using Policy and RLS

I need to hide the rows with deleted = true values for all select statements to the table.

I thought of using row level security with a select policy, but it does not seem to work.

Here's what I tried:

alter table my_table force row level security;

create policy my_selection_policy on my_table
for select to public using (deleted is not true);

The expected result is that now all deleted rows in my_table should be automatically filtered out from any select query on my_table.

So, basically, the query select * from my_table where deleted = true; would return me an empty result.

How do I implement such functionality correctly? Is RLS + policy the correct way to implement that? Or should I just use a before select trigger?

Upvotes: 0

Views: 418

Answers (1)

Abdisamad Khalif
Abdisamad Khalif

Reputation: 825

So, basically, the query select * from my_table where deleted = true; would return me an empty result.

Because you don't need to use where clause in this case to remove soft-deleted records from your result set.

You just need select * from my_table; soft deleted records are already filtered out using using (deleted is not true);

Upvotes: 1

Related Questions