Thread: BUG #13004: PostgreSQL 9.5 policy for table bug?

The following bug has been logged on the website:

Bug reference:      13004
Logged by:          digoal
Email address:      digoal@126.com
PostgreSQL version: Unsupported/Unknown
Operating system:   CentOS 6
Description:

postgres=# \d test
     Table "public.test"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 r      | name    |
Policies:
    POLICY "p4" FOR UPDATE
      TO r3
      USING (r = "current_user"())
postgres=# \c postgres r3
You are now connected to database "postgres" as user "r3".

postgres=> update test set id=4 where r='r3';
UPDATE 1

There is no policy for r3's select command , why cann't see rows?

postgres=> select * from test;
 id | r
----+---
(0 rows)

postgres=# create policy p1 on test for select to r3 using ( r =
current_user);
CREATE POLICY
postgres=# \d+ test
                         Table "public.test"
 Column |  Type   | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
 id     | integer |           | plain   |              |
 r      | name    |           | plain   |              |
Policies:
    POLICY "p1" FOR SELECT
      TO r3
      USING (r = "current_user"())
    POLICY "p4" FOR UPDATE
      TO r3
      USING (r = "current_user"())
postgres=# \c postgres r3
You are now connected to database "postgres" as user "r3".
postgres=> select * from test;
 id | r
----+----
  4 | r3
(1 row)

There is no policy for r3's delete command , why cann't delete?

postgres=> delete from test ;
DELETE 0


another nonsuper role , and not owner:

postgres=> \c postgres r1
You are now connected to database "postgres" as user "r1".
postgres=# select * from test;
 id | r
----+----
  1 | r1
  2 | r2
  4 | r1
  4 | r2
  4 | r1
  4 | r2
  4 | r1
  4 | r3
(8 rows)

* digoal@126.com (digoal@126.com) wrote:
> There is no policy for r3's select command , why cann't see rows?

As documented, if row-level security is enabled on the table and there
is no policy which applies, then a default-deny policy will be used.

If you want there to be no filtering on SELECTs for this user, simply
create a policy with 'true' as the USING clause.

> There is no policy for r3's delete command , why cann't delete?

Same here.

> another nonsuper role , and not owner:
>=20
> postgres=3D> \c postgres r1
> You are now connected to database "postgres" as user "r1".
> postgres=3D# select * from test;
>  id | r =20
> ----+----
>   1 | r1
>   2 | r2
>   4 | r1
>   4 | r2
>   4 | r1
>   4 | r2
>   4 | r1
>   4 | r3
> (8 rows)

Are you sure that r1 isn't a superuser?  Certainly, the prompt displayed
above (postgres=3D#) implies that it is.  Further, I'm unable to reproduce
this issue with current master, based on what I understand of the setup.

A self-contained test case, including the exact commands used from the
start, would help immensely.

    Thanks!

        Stephen