unclear about row-level security USING vs. CHECK

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: unclear about row-level security USING vs. CHECK
Date: 2015-09-23 01:01:29
Message-ID: 5601F9E9.6070108@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I'm testing the new row-level security feature. I'm not clear on the
difference between the USING and CHECK clauses in the CREATE POLICY
statement.

The documentation says:

"""
A policy grants the ability to SELECT, INSERT, UPDATE, or DELETE rows
which match the relevant policy expression. Existing table rows are
checked against the expression specified via USING, while new rows that
would be created via INSERT or UPDATE are checked against the expression
specified via WITH CHECK. When a USING expression returns true for a
given row then that row is visible to the user, while if a false or null
is returned then the row is not visible. When a WITH CHECK expression
returns true for a row then that row is added, while if a false or null
is returned then an error occurs.
"""

So basically, USING filters out what you see, CHECK controls what you
can write.

But then this doesn't work correctly:

CREATE TABLE test1 (content text, entered_by text);
ALTER TABLE test1 ENABLE ROW LEVEL SECURITY;
CREATE POLICY test1_policy ON test1 FOR ALL TO PUBLIC USING (entered_by
= current_user);
GRANT ALL ON TABLE test1 TO PUBLIC;

CREATE USER foo1;
SET SESSION AUTHORIZATION foo1;
INSERT INTO test1 VALUES ('blah', 'foo2'); -- fails

This is a typical you-can-only-see-your-own-rows setup, which works for
the reading case, but it evidently also controls writes. So I'm not
sure what the CHECK clause is supposed to add on top of that.

(Btw., what's the meaning of a policy for DELETE?)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2015-09-23 01:01:52 Re: Less than ideal error reporting in pg_stat_statements
Previous Message Andres Freund 2015-09-23 00:23:27 Re: Rework the way multixact truncations work