Re: [BUGS] BUG #13694: Row Level Security by-passed with CREATEUSER permission - Mailing list pgsql-hackers

Joe Conway <mail@joeconway.com> writes:
> On 10/21/2015 11:26 AM, Andres Freund wrote:
>> On 2015-10-21 11:17:44 -0700, Tom Lane wrote:
>>> 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.

> 1++

Attached patch rips out CREATEUSER and NOCREATEUSER options lock, stock,
and barrel.

Another possibility is to change them to actually mean CREATEROLE and
NOCREATEROLE.  I think probably a clean break is better though.

            regards, tom lane

diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index 7638817b830a1eb73ab59174591fe6eaf3378453..da36ad96967e7c842c28f5787b5ec0dc48ec5a60 100644
*** a/doc/src/sgml/ref/alter_role.sgml
--- b/doc/src/sgml/ref/alter_role.sgml
*************** ALTER ROLE <replaceable class="PARAMETER
*** 28,34 ****
        SUPERUSER | NOSUPERUSER
      | CREATEDB | NOCREATEDB
      | CREATEROLE | NOCREATEROLE
-     | CREATEUSER | NOCREATEUSER
      | INHERIT | NOINHERIT
      | LOGIN | NOLOGIN
      | REPLICATION | NOREPLICATION
--- 28,33 ----
*************** ALTER ROLE { <replaceable class="PARAMET
*** 160,167 ****
        <term><literal>NOCREATEDB</></term>
        <term><literal>CREATEROLE</literal></term>
        <term><literal>NOCREATEROLE</literal></term>
-       <term><literal>CREATEUSER</literal></term>
-       <term><literal>NOCREATEUSER</literal></term>
        <term><literal>INHERIT</literal></term>
        <term><literal>NOINHERIT</literal></term>
        <term><literal>LOGIN</literal></term>
--- 159,164 ----
diff --git a/doc/src/sgml/ref/alter_user.sgml b/doc/src/sgml/ref/alter_user.sgml
index 0ffaa16da2fc75fcd1121dcde627837d6a8e9166..84a0c52191fc6c82687ef49df99ac30fdbfd8f1d 100644
*** a/doc/src/sgml/ref/alter_user.sgml
--- b/doc/src/sgml/ref/alter_user.sgml
*************** ALTER USER <replaceable class="PARAMETER
*** 28,34 ****
        SUPERUSER | NOSUPERUSER
      | CREATEDB | NOCREATEDB
      | CREATEROLE | NOCREATEROLE
-     | CREATEUSER | NOCREATEUSER
      | INHERIT | NOINHERIT
      | LOGIN | NOLOGIN
      | REPLICATION | NOREPLICATION
--- 28,33 ----
diff --git a/doc/src/sgml/ref/create_group.sgml b/doc/src/sgml/ref/create_group.sgml
index 981ce51e5f4aa7a4e54ed962c4675c80281137c8..1d5cc9b5969842985fc657c27d3becd3a3f6af0e 100644
*** a/doc/src/sgml/ref/create_group.sgml
--- b/doc/src/sgml/ref/create_group.sgml
*************** CREATE GROUP <replaceable class="PARAMET
*** 28,34 ****
        SUPERUSER | NOSUPERUSER
      | CREATEDB | NOCREATEDB
      | CREATEROLE | NOCREATEROLE
-     | CREATEUSER | NOCREATEUSER
      | INHERIT | NOINHERIT
      | LOGIN | NOLOGIN
      | [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
--- 28,33 ----
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 240c21ce85fcd1bd4cddd4bb6af109ef319f8175..38cd4c83696053f5b70fc514e9b3939cb842fb3a 100644
*** a/doc/src/sgml/ref/create_role.sgml
--- b/doc/src/sgml/ref/create_role.sgml
*************** CREATE ROLE <replaceable class="PARAMETE
*** 28,34 ****
        SUPERUSER | NOSUPERUSER
      | CREATEDB | NOCREATEDB
      | CREATEROLE | NOCREATEROLE
-     | CREATEUSER | NOCREATEUSER
      | INHERIT | NOINHERIT
      | LOGIN | NOLOGIN
      | REPLICATION | NOREPLICATION
--- 28,33 ----
*************** CREATE ROLE <replaceable class="PARAMETE
*** 125,143 ****
       </varlistentry>

       <varlistentry>
-       <term><literal>CREATEUSER</literal></term>
-       <term><literal>NOCREATEUSER</literal></term>
-       <listitem>
-        <para>
-         These clauses are an obsolete, but still accepted, spelling of
-         <literal>SUPERUSER</literal> and <literal>NOSUPERUSER</literal>.
-         Note that they are <emphasis>not</> equivalent to
-         <literal>CREATEROLE</literal> as one might naively expect!
-        </para>
-       </listitem>
-      </varlistentry>
-
-      <varlistentry>
        <term><literal>INHERIT</literal></term>
        <term><literal>NOINHERIT</literal></term>
        <listitem>
--- 124,129 ----
diff --git a/doc/src/sgml/ref/create_user.sgml b/doc/src/sgml/ref/create_user.sgml
index 065999c85a4df610908c1e762d7bc3435406986b..6c690b36df561573f234de073e4ddbb10e6e9ec7 100644
*** a/doc/src/sgml/ref/create_user.sgml
--- b/doc/src/sgml/ref/create_user.sgml
*************** CREATE USER <replaceable class="PARAMETE
*** 28,34 ****
        SUPERUSER | NOSUPERUSER
      | CREATEDB | NOCREATEDB
      | CREATEROLE | NOCREATEROLE
-     | CREATEUSER | NOCREATEUSER
      | INHERIT | NOINHERIT
      | LOGIN | NOLOGIN
      | REPLICATION | NOREPLICATION
--- 28,33 ----
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 8bd511952c9fd9706bc2c262e7f72154c91f99c6..fba91d53ac3f3e70e5b196ebfbd35efebf55eed3 100644
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
*************** AlterOptRoleElem:
*** 966,981 ****
                          $$ = makeDefElem("superuser", (Node *)makeInteger(TRUE));
                      else if (strcmp($1, "nosuperuser") == 0)
                          $$ = makeDefElem("superuser", (Node *)makeInteger(FALSE));
-                     else if (strcmp($1, "createuser") == 0)
-                     {
-                         /* For backwards compatibility, synonym for SUPERUSER */
-                         $$ = makeDefElem("superuser", (Node *)makeInteger(TRUE));
-                     }
-                     else if (strcmp($1, "nocreateuser") == 0)
-                     {
-                         /* For backwards compatibility, synonym for SUPERUSER */
-                         $$ = makeDefElem("superuser", (Node *)makeInteger(FALSE));
-                     }
                      else if (strcmp($1, "createrole") == 0)
                          $$ = makeDefElem("createrole", (Node *)makeInteger(TRUE));
                      else if (strcmp($1, "nocreaterole") == 0)
--- 966,971 ----
diff --git a/src/backend/utils/mb/conversion_procs/regress_prolog
b/src/backend/utils/mb/conversion_procs/regress_prolog
index f5c71790cf28cf06a4d3e0c8ff512bd2911d9f22..f35241a2d097918049a858d289b2131b84c4d509 100644
*** a/src/backend/utils/mb/conversion_procs/regress_prolog
--- b/src/backend/utils/mb/conversion_procs/regress_prolog
***************
*** 1,7 ****
  --
  -- create user defined conversion
  --
! CREATE USER conversion_test_user WITH NOCREATEDB NOCREATEUSER;
  SET SESSION AUTHORIZATION conversion_test_user;
  CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
  --
--- 1,7 ----
  --
  -- create user defined conversion
  --
! CREATE USER conversion_test_user WITH NOCREATEDB NOCREATEROLE;
  SET SESSION AUTHORIZATION conversion_test_user;
  CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
  --
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 4eb5058416c1dd3d24463e5511ddea5e6acccd98..0e8d3951a29ff24c1098f9d4acd09e72270d697b 100644
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*************** psql_completion(const char *text, int st
*** 1264,1271 ****
      {
          static const char *const list_ALTERUSER[] =
          {"BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE",
!             "CREATEUSER", "ENCRYPTED", "INHERIT", "LOGIN", "NOBYPASSRLS",
!             "NOCREATEDB", "NOCREATEROLE", "NOCREATEUSER", "NOINHERIT",
              "NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD", "RENAME TO",
              "REPLICATION", "RESET", "SET", "SUPERUSER", "UNENCRYPTED",
          "VALID UNTIL", "WITH", NULL};
--- 1264,1271 ----
      {
          static const char *const list_ALTERUSER[] =
          {"BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE",
!             "ENCRYPTED", "INHERIT", "LOGIN", "NOBYPASSRLS",
!             "NOCREATEDB", "NOCREATEROLE", "NOINHERIT",
              "NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD", "RENAME TO",
              "REPLICATION", "RESET", "SET", "SUPERUSER", "UNENCRYPTED",
          "VALID UNTIL", "WITH", NULL};
*************** psql_completion(const char *text, int st
*** 1282,1289 ****
          /* Similar to the above, but don't complete "WITH" again. */
          static const char *const list_ALTERUSER_WITH[] =
          {"BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE",
!             "CREATEUSER", "ENCRYPTED", "INHERIT", "LOGIN", "NOBYPASSRLS",
!             "NOCREATEDB", "NOCREATEROLE", "NOCREATEUSER", "NOINHERIT",
              "NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD", "RENAME TO",
              "REPLICATION", "RESET", "SET", "SUPERUSER", "UNENCRYPTED",
          "VALID UNTIL", NULL};
--- 1282,1289 ----
          /* Similar to the above, but don't complete "WITH" again. */
          static const char *const list_ALTERUSER_WITH[] =
          {"BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE",
!             "ENCRYPTED", "INHERIT", "LOGIN", "NOBYPASSRLS",
!             "NOCREATEDB", "NOCREATEROLE", "NOINHERIT",
              "NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD", "RENAME TO",
              "REPLICATION", "RESET", "SET", "SUPERUSER", "UNENCRYPTED",
          "VALID UNTIL", NULL};
*************** psql_completion(const char *text, int st
*** 2671,2678 ****
      {
          static const char *const list_CREATEROLE[] =
          {"ADMIN", "BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE",
!             "CREATEUSER", "ENCRYPTED", "IN", "INHERIT", "LOGIN", "NOBYPASSRLS",
!             "NOCREATEDB", "NOCREATEROLE", "NOCREATEUSER", "NOINHERIT",
              "NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD",
              "REPLICATION", "ROLE", "SUPERUSER", "SYSID", "UNENCRYPTED",
          "VALID UNTIL", "WITH", NULL};
--- 2671,2678 ----
      {
          static const char *const list_CREATEROLE[] =
          {"ADMIN", "BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE",
!             "ENCRYPTED", "IN", "INHERIT", "LOGIN", "NOBYPASSRLS",
!             "NOCREATEDB", "NOCREATEROLE", "NOINHERIT",
              "NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD",
              "REPLICATION", "ROLE", "SUPERUSER", "SYSID", "UNENCRYPTED",
          "VALID UNTIL", "WITH", NULL};
*************** psql_completion(const char *text, int st
*** 2690,2697 ****
          /* Similar to the above, but don't complete "WITH" again. */
          static const char *const list_CREATEROLE_WITH[] =
          {"ADMIN", "BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE",
!             "CREATEUSER", "ENCRYPTED", "IN", "INHERIT", "LOGIN", "NOBYPASSRLS",
!             "NOCREATEDB", "NOCREATEROLE", "NOCREATEUSER", "NOINHERIT",
              "NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD",
              "REPLICATION", "ROLE", "SUPERUSER", "SYSID", "UNENCRYPTED",
          "VALID UNTIL", NULL};
--- 2690,2697 ----
          /* Similar to the above, but don't complete "WITH" again. */
          static const char *const list_CREATEROLE_WITH[] =
          {"ADMIN", "BYPASSRLS", "CONNECTION LIMIT", "CREATEDB", "CREATEROLE",
!             "ENCRYPTED", "IN", "INHERIT", "LOGIN", "NOBYPASSRLS",
!             "NOCREATEDB", "NOCREATEROLE", "NOINHERIT",
              "NOLOGIN", "NOREPLICATION", "NOSUPERUSER", "PASSWORD",
              "REPLICATION", "ROLE", "SUPERUSER", "SYSID", "UNENCRYPTED",
          "VALID UNTIL", NULL};
diff --git a/src/test/regress/expected/conversion.out b/src/test/regress/expected/conversion.out
index 13f1cf3447b42c9be8303e7618eee566e44a4b14..96ea3b9e19089253d9d4e23afdb954ac5233e57b 100644
*** a/src/test/regress/expected/conversion.out
--- b/src/test/regress/expected/conversion.out
*************** SET bytea_output TO escape;
*** 3,9 ****
  --
  -- create user defined conversion
  --
! CREATE USER conversion_test_user WITH NOCREATEDB NOCREATEUSER;
  SET SESSION AUTHORIZATION conversion_test_user;
  CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
  --
--- 3,9 ----
  --
  -- create user defined conversion
  --
! CREATE USER conversion_test_user WITH NOCREATEDB NOCREATEROLE;
  SET SESSION AUTHORIZATION conversion_test_user;
  CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
  --
diff --git a/src/test/regress/sql/conversion.sql b/src/test/regress/sql/conversion.sql
index e27f06f5c7516df6fe83e599afc75546a80d4abd..fb184453c24a92e6eb7ba20a113d3c86038cd246 100644
*** a/src/test/regress/sql/conversion.sql
--- b/src/test/regress/sql/conversion.sql
*************** SET bytea_output TO escape;
*** 4,10 ****
  --
  -- create user defined conversion
  --
! CREATE USER conversion_test_user WITH NOCREATEDB NOCREATEUSER;
  SET SESSION AUTHORIZATION conversion_test_user;
  CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
  --
--- 4,10 ----
  --
  -- create user defined conversion
  --
! CREATE USER conversion_test_user WITH NOCREATEDB NOCREATEROLE;
  SET SESSION AUTHORIZATION conversion_test_user;
  CREATE CONVERSION myconv FOR 'LATIN1' TO 'UTF8' FROM iso8859_1_to_utf8;
  --