Thread: Incorrect description of the WITH CHECK in the row security can lead to the security issue
Incorrect description of the WITH CHECK in the row security can lead to the security issue
From
Олег Самойлов
Date:
Hi all. This is a documentation issue, I already sent to pgsql-docs, but there is not reply. https://www.postgresql.org/message-id/152637961531.27212.188002690528452126@wrigleys.postgresql.org I'll try here. PostgreSQL 10 (in 11 the same https://www.postgresql.org/docs/10/static/ddl-rowsecurity.html cite To use a different policy for rows that are being added to the table compared to those rows that are visible, the WITH CHECK clause can be used. This policy would allow all users to view all rows in the users table, but only modify their own: CREATE POLICY user_policy ON users USING (true) WITH CHECK (user_name = current_user); end cite This is is wrong description. Every one can steal other row with such policy. Lets demonstrate. You are now connected to database "olleg" as user "olleg". olleg(at)[local]:9700/olleg => create table users (user_name text primary key, description text); CREATE TABLE olleg(at)[local]:9700/olleg => ALTER TABLE users ENABLE ROW LEVEL SECURITY; ALTER TABLE olleg(at)[local]:9700/olleg => grant all on users to public; GRANT => CREATE POLICY user_policy ON users -> USING (true) -> WITH CHECK (user_name = current_user); CREATE POLICY olleg(at)[local]:9700/olleg => insert into users (user_name) values ('olleg'); INSERT 0 1 olleg(at)[local]:9700/olleg => set role postgres; SET olleg(at)[local]:9700/olleg =# create user test with password 'test' login; CREATE ROLE =# \c olleg test localhost 9700 SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) You are now connected to database "olleg" as user "test" on host "localhost" at port "9700". test(at)localhost:9700/olleg => select * from users; user_name | description -----------+------------- olleg | (1 row) test(at)localhost:9700/olleg => update users set user_name='test', description='a rude hack'; UPDATE 1 test(at)localhost:9700/olleg => select * from users; user_name | description -----------+------------- test | a rude hack (1 row) The right statement to not allow modify rows by other user will be CREATE POLICY user_policy ON users USING (user_name = current_user) WITH CHECK (user_name = current_user); end cite
Re: Incorrect description of the WITH CHECK in the row security can lead to the security issue
From
Tom Lane
Date:
=?utf-8?B?0J7Qu9C10LMg0KHQsNC80L7QudC70L7Qsg==?= <splarv@ya.ru> writes: > PostgreSQL 10 (in 11 the same > https://www.postgresql.org/docs/10/static/ddl-rowsecurity.html > cite > To use a different policy for rows that are being added to the table > compared to those rows that are visible, the WITH CHECK clause can be used. > This policy would allow all users to view all rows in the users table, but > only modify their own: > CREATE POLICY user_policy ON users > USING (true) > WITH CHECK (user_name = current_user); > end cite > This is is wrong description. Every one can steal other row with such > policy. Yup, you're right, this is too simple. > The right statement to not allow modify rows by other user will be > CREATE POLICY user_policy ON users > USING (user_name = current_user) > WITH CHECK (user_name = current_user); Well, that also hides the other users' rows, which is not what the example claims to do. To make it work as documented, we need something like CREATE POLICY user_sel_policy ON users FOR SELECT USING (true); CREATE POLICY user_mod_policy ON users USING (user_name = current_user); I've pushed a patch along that line. Thanks for the report! regards, tom lane