Subject: CREATE/ALTER ROLE with NULL password

From: PG Doc comments form <[email-masked]>
Date: Wed, 21 Nov 2018 19:36:59 +0000

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/11/sql-alterrole.html
Description:

When creating a role with no password, or altering a role to remove its
password, the ENCRYPTED clause must not be present.
The current synopsis for CREATE / ALTER ROLE give one of the allowed options
as:
[ ENCRYPTED ] PASSWORD 'password'
and the current documentation for CREATE ROLE says:
"The ENCRYPTED keyword has no effect, but is accepted for backwards
compatibility."

I think it might be worth explicitly specifying the password-blanking form
for both commands as a new option in their synopses, e.g.:

"
CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    ...
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    ...
"

Also, there is inconsistency of quoting of 'password' in the synopsis for
CREATE/ALTER ROLE (has quotes) vs. their respective parameters sections (no
quotes).

---

From: Michael Paquier <[email-masked]>
Date: Thu, 22 Nov 2018 11:07:59 +0900

> "The ENCRYPTED keyword has no effect, but is accepted for backwards

The grammar is still supported, so keeping it documented has no actual
problems until it gets removed, if that happens.  Keeping it is not a
real maintenance burden either.

>     | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL

Yes, that the set of grammar combination supported, as ENCRYPTED
PASSWORD NULL is not possible.

> CREATE/ALTER ROLE (has quotes) vs. their respective parameters sections (no

Agreed, this should have quotes for consistency.  Any objections with
the attached set of fixes from anybody?
--
Michael
diff --git a/doc/src/sgml/ref/alter_role.sgml b/doc/src/sgml/ref/alter_role.sgml
index 573a3e80f7..dbf258ef50 100644
--- a/doc/src/sgml/ref/alter_role.sgml
+++ b/doc/src/sgml/ref/alter_role.sgml
@@ -33,7 +33,7 @@ ALTER ROLE <replaceable class="parameter">role_specification</replaceable> [ WIT
     | REPLICATION | NOREPLICATION
     | BYPASSRLS | NOBYPASSRLS
     | CONNECTION LIMIT <replaceable class="parameter">connlimit</replaceable>
-    | [ ENCRYPTED ] PASSWORD '<replaceable class="parameter">password</replaceable>'
+    | [ ENCRYPTED ] PASSWORD '<replaceable class="parameter">password</replaceable>' | PASSWORD NULL
     | VALID UNTIL '<replaceable class="parameter">timestamp</replaceable>'
 
 ALTER ROLE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
@@ -168,7 +168,8 @@ ALTER ROLE { <replaceable class="parameter">role_specification</replaceable> | A
       <term><literal>BYPASSRLS</literal></term>
       <term><literal>NOBYPASSRLS</literal></term>
       <term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term>
-      <term>[ <literal>ENCRYPTED</literal> ] <literal>PASSWORD</literal> <replaceable class="parameter">password</replaceable></term>
+      <term>[ <literal>ENCRYPTED</literal> ] <literal>PASSWORD</literal> '<replaceable class="parameter">password</replaceable>'</term>
+      <term><literal>PASSWORD NULL</literal></term>
       <term><literal>VALID UNTIL</literal> '<replaceable class="parameter">timestamp</replaceable>'</term>
       <listitem>
        <para>
diff --git a/doc/src/sgml/ref/alter_user.sgml b/doc/src/sgml/ref/alter_user.sgml
index 8f50f43089..6769c8ecc4 100644
--- a/doc/src/sgml/ref/alter_user.sgml
+++ b/doc/src/sgml/ref/alter_user.sgml
@@ -33,7 +33,7 @@ ALTER USER <replaceable class="parameter">role_specification</replaceable> [ WIT
     | REPLICATION | NOREPLICATION
     | BYPASSRLS | NOBYPASSRLS
     | CONNECTION LIMIT <replaceable class="parameter">connlimit</replaceable>
-    | [ ENCRYPTED ] PASSWORD '<replaceable class="parameter">password</replaceable>'
+    | [ ENCRYPTED ] PASSWORD '<replaceable class="parameter">password</replaceable>' | PASSWORD NULL
     | VALID UNTIL '<replaceable class="parameter">timestamp</replaceable>'
 
 ALTER USER <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml
index 9c3b6978af..db842732a8 100644
--- a/doc/src/sgml/ref/create_role.sgml
+++ b/doc/src/sgml/ref/create_role.sgml
@@ -33,7 +33,7 @@ CREATE ROLE <replaceable class="parameter">name</replaceable> [ [ WITH ] <replac
     | REPLICATION | NOREPLICATION
     | BYPASSRLS | NOBYPASSRLS
     | CONNECTION LIMIT <replaceable class="parameter">connlimit</replaceable>
-    | [ ENCRYPTED ] PASSWORD '<replaceable class="parameter">password</replaceable>'
+    | [ ENCRYPTED ] PASSWORD '<replaceable class="parameter">password</replaceable>' | PASSWORD NULL
     | VALID UNTIL '<replaceable class="parameter">timestamp</replaceable>'
     | IN ROLE <replaceable class="parameter">role_name</replaceable> [, ...]
     | IN GROUP <replaceable class="parameter">role_name</replaceable> [, ...]
@@ -210,7 +210,8 @@ CREATE ROLE <replaceable class="parameter">name</replaceable> [ [ WITH ] <replac
      </varlistentry>
 
      <varlistentry>
-      <term>[ <literal>ENCRYPTED</literal> ] <literal>PASSWORD</literal> <replaceable class="parameter">password</replaceable></term>
+      <term>[ <literal>ENCRYPTED</literal> ] <literal>PASSWORD</literal> '<replaceable class="parameter">password</replaceable>'</term>
+      <term><literal>PASSWORD NULL</literal></term>
       <listitem>
        <para>
         Sets the role's password.  (A password is only of use for
diff --git a/doc/src/sgml/ref/create_user.sgml b/doc/src/sgml/ref/create_user.sgml
index a51dc50c97..198e06e723 100644
--- a/doc/src/sgml/ref/create_user.sgml
+++ b/doc/src/sgml/ref/create_user.sgml
@@ -33,7 +33,7 @@ CREATE USER <replaceable class="parameter">name</replaceable> [ [ WITH ] <replac
     | REPLICATION | NOREPLICATION
     | BYPASSRLS | NOBYPASSRLS
     | CONNECTION LIMIT <replaceable class="parameter">connlimit</replaceable>
-    | [ ENCRYPTED ] PASSWORD '<replaceable class="parameter">password</replaceable>'
+    | [ ENCRYPTED ] PASSWORD '<replaceable class="parameter">password</replaceable>' | PASSWORD NULL
     | VALID UNTIL '<replaceable class="parameter">timestamp</replaceable>'
     | IN ROLE <replaceable class="parameter">role_name</replaceable> [, ...]
     | IN GROUP <replaceable class="parameter">role_name</replaceable> [, ...]

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 21 Nov 2018 23:58:25 -0700

On Wednesday, November 21, 2018, Michael Paquier <[email-masked]>
wrote:

> Any objections with

Should tweak the paragraph to point out this exception as well.

 The ENCRYPTED keyword has no effect, but is accepted for backwards
compatibility[, except in the PASSWORD NULL form.]

David J.

---

From: Michael Paquier <[email-masked]>
Date: Thu, 22 Nov 2018 16:58:06 +0900

> compatibility[, except in the PASSWORD NULL form.]

The docs list the following with the patch as supported grammar:
[ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
And it seems to me that '|' has priority over '[]', so ENCRYPTED does
not apply to PASSWORD NULL if phrased this way.
--
Michael

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 22 Nov 2018 09:54:07 -0700

> not apply to PASSWORD NULL if phrased this way.
Yes, the syntax block is perfectly clear but we still explain said grammer
in words and should be precise there as well, IMO.  Not a big deal though.

David J.

---

From: Michael Paquier <[email-masked]>
Date: Fri, 23 Nov 2018 09:13:26 +0900

> in words and should be precise there as well, IMO.  Not a big deal though.

Okay, thanks.  I have committed the simplest version.
--
Michael