Removing a user's password
What capabilities do we have for removing a user's password? The only
way I can see to do it is to insert a NULL into pg_shadow, but clearly
that is pretty ugly. I don't see any mention in the ALTER USER manual
page, and assigning a password of NULL using ALTER user fails.
--
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
Bruce Momjian <pgman@candle.pha.pa.us> writes:
What capabilities do we have for removing a user's password?
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.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
What capabilities do we have for removing a user's password?
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? 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.
--
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 19073Bruce 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
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.
If a non-super user sets his own password, how does he unset it if he
can't use VALID UNTIL? Is there no valid reason to unset it?
Obviously, almost no one is asking for this feature so we may be OK, but
I do want to document using VALID UNTIL to disable a password.
--
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
Is setting password to NULL better then deleting user?
Mike
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Bruce Momjian
Sent: Monday, May 26, 2003 2:32 PM
To: Tom Lane
Cc: PostgreSQL-development
Subject: Re: [HACKERS] Removing a user's password
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
What capabilities do we have for removing a user's password?
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? 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.
--
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---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Michael Brusser wrote:
Is setting password to NULL better then deleting user?
Mike
They are different. Remove password allows the user to still log into
other non-password databases. Removing the user prevents any
connection.
---------------------------------------------------------------------------
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Bruce Momjian
Sent: Monday, May 26, 2003 2:32 PM
To: Tom Lane
Cc: PostgreSQL-development
Subject: Re: [HACKERS] Removing a user's passwordTom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
What capabilities do we have for removing a user's password?
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? 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.-- 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---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
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 19073Here 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 19073Attachments:
/pgpatches/usertext/plainDownload
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));
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Here is a patch to allow users to remove their passwords.
Uh ... why should a user be able to remove their password (as opposed to
changing it to something else)? I'm having a difficult time visualizing
a use for this. Seems more like a way to create extra work for DBAs,
putting back passwords for people who stupidly removed theirs.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Here is a patch to allow users to remove their passwords.
Uh ... why should a user be able to remove their password (as opposed to
changing it to something else)? I'm having a difficult time visualizing
a use for this. Seems more like a way to create extra work for DBAs,
putting back passwords for people who stupidly removed theirs.
Well, they are not actually removing it, just setting it to NULL, as
though a password was never assigned. We have a way for users to set
it, but no way for them to remove it.
--
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 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
Well, they are not actually removing it, just setting it to NULL, as
though a password was never assigned. We have a way for users to set
it, but no way for them to remove it.
If they had a way to remove it, then they'd no longer be able to log in
(assuming password auth is being used, and if it isn't then who cares?)
I'm not seeing much of anything here except a foot-gun. It might look
nice on the argument of symmetry, but as far as actual functionality
goes, I think it'd be a waste of code space. How many people have
actually asked for this, in all the years the password support has
been there?
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Well, they are not actually removing it, just setting it to NULL, as
though a password was never assigned. We have a way for users to set
it, but no way for them to remove it.If they had a way to remove it, then they'd no longer be able to log in
(assuming password auth is being used, and if it isn't then who cares?)
I'm not seeing much of anything here except a foot-gun. It might look
nice on the argument of symmetry, but as far as actual functionality
goes, I think it'd be a waste of code space. How many people have
actually asked for this, in all the years the password support has
been there?
I assume there are some database that require passwords and some that
don't. I don't think we have had any requests for it. The only recent
request was someone asking for how to alter the password after using
'createuser', and we fixed that by modifying the manual page.
I added a doc mention of also allowing ALTER USER to remove a password,
so I will just remove that mention and add this patch in the future this
if anyone sees value in removing a password.
--
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