unclear about row-level security USING vs. CHECK - Mailing list pgsql-hackers

From Peter Eisentraut
Subject unclear about row-level security USING vs. CHECK
Date
Msg-id 5601F9E9.6070108@gmx.net
Whole thread Raw
Responses Re: unclear about row-level security USING vs. CHECK  ("Charles Clavadetscher" <clavadetscher@swisspug.org>)
Re: unclear about row-level security USING vs. CHECK  (Stephen Frost <sfrost@snowman.net>)
List 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?)



pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: Rework the way multixact truncations work
Next
From: Peter Geoghegan
Date:
Subject: Re: Less than ideal error reporting in pg_stat_statements