Re: [HACKERS] Removing a user's password

Here is a patch to allow users to remove their passwords.  VALID UNTIL
does work for disabling an existing password, but only for superusers.

Is NONE the proper syntax?

    ALTER USER postgres PASSWORD NONE

Is NULL better?  I didn't like DROP PASSWORD because we don't use ADD
PASSWORD, particularly because we don't know at that point whether they
have a password or not.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> If you set VALID UNTIL to 'now' (or sometime in the past), you've
> >> effectively prevented him from logging in with the password ---
> >> more effectively than setting the password to NULL, since if the
> >> user is still logged in he can just undo that.  I don't think we
> >> really need to do anything more here.
>
> > Well, can they undo the VALID UNTIL too?
>
> No: a non-superuser can only set his password, not any other fields of
> his pg_shadow entry.
>
> > I think at a minimum we need
> > to document the proper procedure for removing a password.  I see NULL as
> > a more logical way of removing the password rather than playing with
> > VALID UNTIL.
>
> It may be more logical, but it doesn't work as well.
>
>             regards, tom lane
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/alter_user.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/alter_user.sgml,v
retrieving revision 1.27
diff -c -c -r1.27 alter_user.sgml
*** doc/src/sgml/ref/alter_user.sgml    15 Apr 2003 13:25:08 -0000    1.27
--- doc/src/sgml/ref/alter_user.sgml    6 Jun 2003 15:00:17 -0000
***************
*** 20,26 ****

  where <replaceable class="PARAMETER">option</replaceable> can be:

!     [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
      | CREATEDB | NOCREATEDB
      | CREATEUSER | NOCREATEUSER
      | VALID UNTIL '<replaceable class="PARAMETER">abstime</replaceable>'
--- 20,26 ----

  where <replaceable class="PARAMETER">option</replaceable> can be:

!     [ ENCRYPTED | UNENCRYPTED ] PASSWORD [ '<replaceable class="PARAMETER">password</replaceable>' | NONE ]
      | CREATEDB | NOCREATEDB
      | CREATEUSER | NOCREATEUSER
      | VALID UNTIL '<replaceable class="PARAMETER">abstime</replaceable>'
***************
*** 75,81 ****
        <term><replaceable class="PARAMETER">password</replaceable></term>
        <listitem>
         <para>
!     The new password to be used for this account.
         </para>
        </listitem>
       </varlistentry>
--- 75,82 ----
        <term><replaceable class="PARAMETER">password</replaceable></term>
        <listitem>
         <para>
!     The new password to be used for this account.  Set to <literal>NONE</>
!     to remove the password.
         </para>
        </listitem>
       </varlistentry>
Index: src/backend/commands/user.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/commands/user.c,v
retrieving revision 1.117
diff -c -c -r1.117 user.c
*** src/backend/commands/user.c    12 May 2003 23:08:50 -0000    1.117
--- src/backend/commands/user.c    6 Jun 2003 15:00:18 -0000
***************
*** 558,564 ****
      if (dvalidUntil)
          validUntil = strVal(dvalidUntil->arg);
      if (dpassword)
!         password = strVal(dpassword->arg);
      if (dgroupElts)
          groupElts = (List *) dgroupElts->arg;

--- 558,564 ----
      if (dvalidUntil)
          validUntil = strVal(dvalidUntil->arg);
      if (dpassword)
!         password = dpassword->arg ? strVal(dpassword->arg) : NULL;
      if (dgroupElts)
          groupElts = (List *) dgroupElts->arg;

***************
*** 767,773 ****
      if (dvalidUntil)
          validUntil = strVal(dvalidUntil->arg);
      if (dpassword)
!         password = strVal(dpassword->arg);

      if (password)
          CheckPgUserAclNotNull();
--- 767,773 ----
      if (dvalidUntil)
          validUntil = strVal(dvalidUntil->arg);
      if (dpassword)
!         password = dpassword->arg ? strVal(dpassword->arg) : NULL;

      if (password)
          CheckPgUserAclNotNull();
***************
*** 831,850 ****
      }

      /* password */
!     if (password)
      {
!         if (!encrypt_password || isMD5(password))
!             new_record[Anum_pg_shadow_passwd - 1] =
!                 DirectFunctionCall1(textin, CStringGetDatum(password));
          else
          {
!             if (!EncryptMD5(password, stmt->user, strlen(stmt->user),
!                             encrypted_password))
!                 elog(ERROR, "CREATE USER: password encryption failed");
!             new_record[Anum_pg_shadow_passwd - 1] =
!                 DirectFunctionCall1(textin, CStringGetDatum(encrypted_password));
          }
-         new_record_repl[Anum_pg_shadow_passwd - 1] = 'r';
      }

      /* valid until */
--- 831,858 ----
      }

      /* password */
!     if (dpassword)
      {
!         if (password)
!         {
!             if (!encrypt_password || isMD5(password))
!                 new_record[Anum_pg_shadow_passwd - 1] =
!                     DirectFunctionCall1(textin, CStringGetDatum(password));
!             else
!             {
!                 if (!EncryptMD5(password, stmt->user, strlen(stmt->user),
!                                 encrypted_password))
!                     elog(ERROR, "CREATE USER: password encryption failed");
!                 new_record[Anum_pg_shadow_passwd - 1] =
!                     DirectFunctionCall1(textin, CStringGetDatum(encrypted_password));
!             }
!             new_record_repl[Anum_pg_shadow_passwd - 1] = 'r';
!         }
          else
          {
!             new_record_nulls[Anum_pg_shadow_passwd - 1] = 'n';
!             new_record_repl[Anum_pg_shadow_passwd - 1] = 'r';
          }
      }

      /* valid until */
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.416
diff -c -c -r2.416 gram.y
*** src/backend/parser/gram.y    29 May 2003 20:40:36 -0000    2.416
--- src/backend/parser/gram.y    6 Jun 2003 15:00:26 -0000
***************
*** 640,645 ****
--- 640,649 ----
                  {
                      $$ = makeDefElem("password", (Node *)makeString($2));
                  }
+             | PASSWORD NONE
+                 {
+                     $$ = makeDefElem("password", (Node *)NULL);
+                 }
              | ENCRYPTED PASSWORD Sconst
                  {
                      $$ = makeDefElem("encryptedPassword", (Node *)makeString($3));