Row level security only on SELECT

Question

I am enabling Row level security on a table. I want the row security only on SELECT. So that other users can insert and update their respective records. I am using postgreSQL and I am pretty much beginner for 'ROW-LEVEL-SECURITY'. I am trying it with following commands. Please update if I am mixing PSQL and MySQL commands. or any better way to achieve it.

ALTER TABLE important_table ENABLE ROW LEVEL SECURITY;

It works fine but then when I specify for select,

ALTER TABLE important_table SET ROW SECURITY FOR SELECT;

I get error with it:

Error: syntax error at or near "ROW"

psql is a command line client for Prostgres and not a synonym for it. I changed the tags for you. — sticky bit, Jul 24 '18 at 12:36
Once you have enabled row level security, use [`CREATE POLICY`](https://www.postgresql.org/docs/current/static/sql-createpolicy.html) to create a policy for `SELECT`. — Laurenz Albe, Jul 24 '18 at 12:47
Thanks. It has worked. I was mistakenly using Policy `FOR ALL`. — khaldi, Jul 24 '18 at 12:54
@LaurenzAlbe could you please provide your comment as answer, so kahldi could accept it? — Thomas Berger, Jul 24 '18 at 16:08
I don't think that's enough for an answer, and I don't feel like writing up some sample code. — Laurenz Albe, Jul 24 '18 at 16:59

score 1 · Accepted Answer · answered Jul 24 '18 at 19:08

1

As mentioned by Laurenz Albe, we have to create Policy for required privileges like For and Update. Once Policy is created, Row level security will work for mentioned previledges.

answered Jul 24 '18 at 19:08

khaldi

472
7
15

Hi. This is a comment, and not an answer. :-) Can you copy the text to your question and delete this "answer"? Regards – kometen Jul 24 '18 at 19:14
@kometen.. than how can I accept the answer and mark the question solved? do you recommend me deleting it anyways? – khaldi Jul 25 '18 at 07:47
Aah, I may have misunderstood your answer to your own question as a comment. If this solves your problem you can leave it as it is. Regards – kometen Jul 25 '18 at 08:31

Row level security only on SELECT

1 Answers1