Thread: BUG #13694: Row Level Security by-passed with CREATEUSER permission

The following bug has been logged on the website:

Bug reference:      13694
Logged by:          Justin Catterson
Email address:      justin.catterson@sofiebio.com
PostgreSQL version: 9.5beta1
Operating system:   Ubuntu 14.10 x64
Description:

Users with the CREATEUSER permission do not evaluate Row Level Security
functions.  pg_user usebypassrls is set to false.

To repeat:
  CREATE POLICY ... WITH CHECK ((Select myFunction()))
  CREATE USER my_user;
  ALTER USER my_user WITH CREATEUSER;

Have myFunction() return a result of False;
   Update a record belonging to policy.

   Record will successfully update, when it should fail.

ALTER USER my_user WITH NOCREATEUSER;
   Update a record belonging to policy.
   Record will fail as expected.

On 10/21/2015 09:42 AM, justin.catterson@sofiebio.com wrote:
> The following bug has been logged on the website:
>=20
> Bug reference:      13694
> Logged by:          Justin Catterson
> Email address:      justin.catterson@sofiebio.com
> PostgreSQL version: 9.5beta1
> Operating system:   Ubuntu 14.10 x64
> Description:       =20
>=20
> Users with the CREATEUSER permission do not evaluate Row Level Security=

> functions.  pg_user usebypassrls is set to false.

Not a bug. See
 http://www.postgresql.org/docs/9.5/static/sql-createrole.html

"CREATEUSER
NOCREATEUSER

    These clauses are an obsolete, but still accepted, spelling of
SUPERUSER and NOSUPERUSER. Note that they are not equivalent to
CREATEROLE as one might naively expect!"

And:
 http://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html

"Table owners, superusers, and roles with the BYPASSRLS attribute bypass
the row security system when querying a table."

HTH,

Joe

--=20
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Joe Conway <mail@joeconway.com> writes:
> On 10/21/2015 09:42 AM, justin.catterson@sofiebio.com wrote:
>> Users with the CREATEUSER permission do not evaluate Row Level Security
>> functions.  pg_user usebypassrls is set to false.

> Not a bug. See
>  http://www.postgresql.org/docs/9.5/static/sql-createrole.html

> "CREATEUSER
> NOCREATEUSER

>     These clauses are an obsolete, but still accepted, spelling of
> SUPERUSER and NOSUPERUSER. Note that they are not equivalent to
> CREATEROLE as one might naively expect!"

I wonder if it's time yet to remove those keywords.  We've had the
SUPERUSER spelling since 8.1, and this report should remind us that
people get confused by the old spellings.

            regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Joe Conway <mail@joeconway.com> writes:
> > On 10/21/2015 09:42 AM, justin.catterson@sofiebio.com wrote:
> >> Users with the CREATEUSER permission do not evaluate Row Level Security
> >> functions.  pg_user usebypassrls is set to false.
>=20
> > Not a bug. See
> >  http://www.postgresql.org/docs/9.5/static/sql-createrole.html
>=20
> > "CREATEUSER
> > NOCREATEUSER
>=20
> >     These clauses are an obsolete, but still accepted, spelling of
> > SUPERUSER and NOSUPERUSER. Note that they are not equivalent to
> > CREATEROLE as one might naively expect!"
>=20
> I wonder if it's time yet to remove those keywords.  We've had the
> SUPERUSER spelling since 8.1, and this report should remind us that
> people get confused by the old spellings.

Probably past time, considering the obvious confusion to CREATEROLE and
NOCREATEROLE.

Thanks!

Stephen

On 2015-10-21 11:17:44 -0700, Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> > "CREATEUSER
> > NOCREATEUSER

> I wonder if it's time yet to remove those keywords.  We've had the
> SUPERUSER spelling since 8.1, and this report should remind us that
> people get confused by the old spellings.

+1 for doing that in 9.6.

Andres

On 10/21/2015 11:26 AM, Andres Freund wrote:
> On 2015-10-21 11:17:44 -0700, Tom Lane wrote:
>> Joe Conway <mail@joeconway.com> writes:
>>> "CREATEUSER
>>> NOCREATEUSER
>=20
>> I wonder if it's time yet to remove those keywords.  We've had the
>> SUPERUSER spelling since 8.1, and this report should remind us that
>> people get confused by the old spellings.
>=20
> +1 for doing that in 9.6.

1++


--=20
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Thanks  for the quick response,
  I had read the documentation but got a little confused with the
phrasing.  The option I wanted was CREATEROLE.

"These clauses are an obsolete, but still accepted, spelling of SUPERUSER
and NOSUPERUSER."

I saw obsolete prior to reaching out, but I still did not understand these
clauses had been replaced with SUPERUSER and NOSUPERUSER.  Perhaps those
options should be removed from the Synopsis to avoid tempting users.


Overall good work,
  I am looking forward to this release.

On Wed, Oct 21, 2015 at 11:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Joe Conway <mail@joeconway.com> writes:
> > On 10/21/2015 09:42 AM, justin.catterson@sofiebio.com wrote:
> >> Users with the CREATEUSER permission do not evaluate Row Level Security
> >> functions.  pg_user usebypassrls is set to false.
>
> > Not a bug. See
> >  http://www.postgresql.org/docs/9.5/static/sql-createrole.html
>
> > "CREATEUSER
> > NOCREATEUSER
>
> >     These clauses are an obsolete, but still accepted, spelling of
> > SUPERUSER and NOSUPERUSER. Note that they are not equivalent to
> > CREATEROLE as one might naively expect!"
>
> I wonder if it's time yet to remove those keywords.  We've had the
> SUPERUSER spelling since 8.1, and this report should remind us that
> people get confused by the old spellings.
>
>                         regards, tom lane
>