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?)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
