public inbox for [email protected]
help / color / mirror / Atom feedPasword expiration warning
8+ messages / 6 participants
[nested] [flat]
* Pasword expiration warning
@ 2021-11-19 14:49 Gilles Darold <[email protected]>
2021-11-19 15:16 ` Re: Pasword expiration warning Dinesh Chemuduru <[email protected]>
2021-11-19 15:55 ` Re: Pasword expiration warning Tom Lane <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Gilles Darold @ 2021-11-19 14:49 UTC (permalink / raw)
To: pgsql-hackers
Hi all,
Now that the security policy is getting stronger, it is not uncommon to
create users with a password expiration date (VALID UNTIL). The problem
is that the user is only aware that his password has expired when he can
no longer log in unless the application with which he is connecting
notifies him beforehand.
I'm wondering if we might be interested in having this feature in psql?
For example for a user whose password expires in 3 days:
gilles=# CREATE ROLE foo LOGIN PASSWORD 'foo' VALID UNTIL '2021-11-22';
CREATE ROLE
gilles=# \c - foo
Password for user foo:
psql (15devel, server 14.1 (Ubuntu 14.1-2.pgdg20.04+1))
** Warning: your password expires in 3 days **
You are now connected to database "gilles" as user "foo".
My idea is to add a psql variable that can be defined in psqlrc to
specify the number of days before the user password expires to start
printing a warning. The warning message is only diplayed in interactive
mode Example:
$ cat /etc/postgresql-common/psqlrc
\set PASSWORD_EXPIRE_WARNING 7
Default value is 0 like today no warning at all.
Of course any other client application have to write his own beforehand
expiration notice but with psql we don't have it for the moment. If
there is interest for this psql feature I can post the patch.
--
Gilles Darold
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pasword expiration warning
2021-11-19 14:49 Pasword expiration warning Gilles Darold <[email protected]>
@ 2021-11-19 15:16 ` Dinesh Chemuduru <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Dinesh Chemuduru @ 2021-11-19 15:16 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; +Cc: pgsql-hackers
On Fri, 19 Nov 2021 at 20:19, Gilles Darold <[email protected]> wrote:
> Hi all,
>
>
> Now that the security policy is getting stronger, it is not uncommon to
> create users with a password expiration date (VALID UNTIL). The problem
> is that the user is only aware that his password has expired when he can no
> longer log in unless the application with which he is connecting notifies
> him beforehand.
>
>
> I'm wondering if we might be interested in having this feature in psql? For
> example for a user whose password expires in 3 days:
>
> gilles=# CREATE ROLE foo LOGIN PASSWORD 'foo' VALID UNTIL '2021-11-22';
> CREATE ROLE
> gilles=# \c - foo
> Password for user foo:
> psql (15devel, server 14.1 (Ubuntu 14.1-2.pgdg20.04+1))
> ** Warning: your password expires in 3 days **
> You are now connected to database "gilles" as user "foo".
>
>
> My idea is to add a psql variable that can be defined in psqlrc to specify
> the number of days before the user password expires to start printing a
> warning. The warning message is only diplayed in interactive mode Example:
>
> $ cat /etc/postgresql-common/psqlrc
> \set PASSWORD_EXPIRE_WARNING 7
>
> +1
It is useful to notify the users about their near account expiration,
and we are doing that at client level.
Default value is 0 like today no warning at all.
>
>
> Of course any other client application have to write his own beforehand expiration
> notice but with psql we don't have it for the moment. If there is interest
> for this psql feature I can post the patch.
>
> --
> Gilles Darold
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pasword expiration warning
2021-11-19 14:49 Pasword expiration warning Gilles Darold <[email protected]>
@ 2021-11-19 15:55 ` Tom Lane <[email protected]>
2021-11-19 16:56 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2021-11-19 15:55 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; +Cc: pgsql-hackers
Gilles Darold <[email protected]> writes:
> Now that the security policy is getting stronger, it is not uncommon to
> create users with a password expiration date (VALID UNTIL).
TBH, I thought people were starting to realize that forced password
rotations are a net security negative. It's true that a lot of
places haven't gotten the word yet.
> I'm wondering if we might be interested in having this feature in psql?
This proposal kind of seems like a hack, because
(1) not everybody uses psql
(2) psql can't really tell whether rolvaliduntil is relevant.
(It can see whether the server demanded a password, but
maybe that went to LDAP or some other auth method.)
That leads me to wonder about server-side solutions. It's easy
enough for the server to see that it's used a password with an
expiration N days away, but how could that be reported to the
client? The only idea that comes to mind that doesn't seem like
a protocol break is to issue a NOTICE message, which doesn't
seem like it squares with your desire to only do this interactively.
(Although I'm not sure I believe that's a great idea. If your
application breaks at 2AM because its password expired, you
won't be any happier than if your interactive sessions start to
fail. Maybe a message that would leave a trail in the server log
would be best after all.)
> Default value is 0 like today no warning at all.
Off-by-default is pretty much guaranteed to not help most people.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pasword expiration warning
2021-11-19 14:49 Pasword expiration warning Gilles Darold <[email protected]>
2021-11-19 15:55 ` Re: Pasword expiration warning Tom Lane <[email protected]>
@ 2021-11-19 16:56 ` Gilles Darold <[email protected]>
2021-11-20 00:17 ` Re: Pasword expiration warning Bossart, Nathan <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Gilles Darold @ 2021-11-19 16:56 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
Le 19/11/2021 à 16:55, Tom Lane a écrit :
> Gilles Darold <[email protected]> writes:
>> Now that the security policy is getting stronger, it is not uncommon to
>> create users with a password expiration date (VALID UNTIL).
> TBH, I thought people were starting to realize that forced password
> rotations are a net security negative. It's true that a lot of
> places haven't gotten the word yet.
>
>> I'm wondering if we might be interested in having this feature in psql?
> This proposal kind of seems like a hack, because
> (1) not everybody uses psql
Yes, for me it's a comfort feature. When a user connect to a PG backend
using an account that have expired you have no information that the
problem is a password expiration. The message returned to the user is
just: "FATAL: password authentication failed for user "foo". We had to
verify in the log file that the problem is related to "DETAIL: User
"foo" has an expired password.". If the user was warned beforehand to
change the password it will probably saves me some time.
> (2) psql can't really tell whether rolvaliduntil is relevant.
> (It can see whether the server demanded a password, but
> maybe that went to LDAP or some other auth method.)
I agree, I hope that in case of external authentication rolvaliduntil is
not set and in this case I guess that there is other notification
channels to inform the user that his password will expire. Otherwise yes
the warning message could be a false positive but the rolvaliduntil can
be changed to infinity to fix this case.
> That leads me to wonder about server-side solutions. It's easy
> enough for the server to see that it's used a password with an
> expiration N days away, but how could that be reported to the
> client? The only idea that comes to mind that doesn't seem like
> a protocol break is to issue a NOTICE message, which doesn't
> seem like it squares with your desire to only do this interactively.
> (Although I'm not sure I believe that's a great idea. If your
> application breaks at 2AM because its password expired, you
> won't be any happier than if your interactive sessions start to
> fail. Maybe a message that would leave a trail in the server log
> would be best after all.)
I think that this is the responsibility of the client to display a
warning when the password is about to expire, the backend could help the
application by sending a NOTICE but the application will still have to
report the notice. I mean that it can continue to do all the work to
verify that the password is about to expire.
>> Default value is 0 like today no warning at all.
> Off-by-default is pretty much guaranteed to not help most people.
Right, I was thinking of backward compatibility but this does not apply
here. So default to 7 days will be better.
To sum up as I said on top this is just a comfort notification dedicated
to psql and for local pg account to avoid looking at log file for
forgetting users.
--
Gilles Darold
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pasword expiration warning
2021-11-19 14:49 Pasword expiration warning Gilles Darold <[email protected]>
2021-11-19 15:55 ` Re: Pasword expiration warning Tom Lane <[email protected]>
2021-11-19 16:56 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
@ 2021-11-20 00:17 ` Bossart, Nathan <[email protected]>
2021-11-20 05:46 ` Re: Pasword expiration warning Michael Paquier <[email protected]>
2021-11-20 13:48 ` Re: Pasword expiration warning Andrew Dunstan <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Bossart, Nathan @ 2021-11-20 00:17 UTC (permalink / raw)
To: Gilles Darold <[email protected]>; Tom Lane <[email protected]>; +Cc: pgsql-hackers
On 11/19/21, 7:56 AM, "Tom Lane" <[email protected]> wrote:
> That leads me to wonder about server-side solutions. It's easy
> enough for the server to see that it's used a password with an
> expiration N days away, but how could that be reported to the
> client? The only idea that comes to mind that doesn't seem like
> a protocol break is to issue a NOTICE message, which doesn't
> seem like it squares with your desire to only do this interactively.
> (Although I'm not sure I believe that's a great idea. If your
> application breaks at 2AM because its password expired, you
> won't be any happier than if your interactive sessions start to
> fail. Maybe a message that would leave a trail in the server log
> would be best after all.)
I bet it's possible to use the ClientAuthentication_hook for this. In
any case, I agree that it probably belongs server-side so that other
clients can benefit from this.
Nathan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pasword expiration warning
2021-11-19 14:49 Pasword expiration warning Gilles Darold <[email protected]>
2021-11-19 15:55 ` Re: Pasword expiration warning Tom Lane <[email protected]>
2021-11-19 16:56 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2021-11-20 00:17 ` Re: Pasword expiration warning Bossart, Nathan <[email protected]>
@ 2021-11-20 05:46 ` Michael Paquier <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Michael Paquier @ 2021-11-20 05:46 UTC (permalink / raw)
To: Bossart, Nathan <[email protected]>; +Cc: Gilles Darold <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Sat, Nov 20, 2021 at 12:17:53AM +0000, Bossart, Nathan wrote:
> I bet it's possible to use the ClientAuthentication_hook for this. In
> any case, I agree that it probably belongs server-side so that other
> clients can benefit from this.
ClientAuthentication_hook is called before the user is informed of the
authentication result, FWIW, so that does not seem wise.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pasword expiration warning
2021-11-19 14:49 Pasword expiration warning Gilles Darold <[email protected]>
2021-11-19 15:55 ` Re: Pasword expiration warning Tom Lane <[email protected]>
2021-11-19 16:56 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2021-11-20 00:17 ` Re: Pasword expiration warning Bossart, Nathan <[email protected]>
@ 2021-11-20 13:48 ` Andrew Dunstan <[email protected]>
2021-11-21 09:49 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Andrew Dunstan @ 2021-11-20 13:48 UTC (permalink / raw)
To: Bossart, Nathan <[email protected]>; Gilles Darold <[email protected]>; Tom Lane <[email protected]>; +Cc: pgsql-hackers
On 11/19/21 19:17, Bossart, Nathan wrote:
> On 11/19/21, 7:56 AM, "Tom Lane" <[email protected]> wrote:
>> That leads me to wonder about server-side solutions. It's easy
>> enough for the server to see that it's used a password with an
>> expiration N days away, but how could that be reported to the
>> client? The only idea that comes to mind that doesn't seem like
>> a protocol break is to issue a NOTICE message, which doesn't
>> seem like it squares with your desire to only do this interactively.
>> (Although I'm not sure I believe that's a great idea. If your
>> application breaks at 2AM because its password expired, you
>> won't be any happier than if your interactive sessions start to
>> fail. Maybe a message that would leave a trail in the server log
>> would be best after all.)
> I bet it's possible to use the ClientAuthentication_hook for this. In
> any case, I agree that it probably belongs server-side so that other
> clients can benefit from this.
>
+1 for a server side solution. The people most likely to benefit from
this are the people least likely to be using psql IMNSHO.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Pasword expiration warning
2021-11-19 14:49 Pasword expiration warning Gilles Darold <[email protected]>
2021-11-19 15:55 ` Re: Pasword expiration warning Tom Lane <[email protected]>
2021-11-19 16:56 ` Re: Pasword expiration warning Gilles Darold <[email protected]>
2021-11-20 00:17 ` Re: Pasword expiration warning Bossart, Nathan <[email protected]>
2021-11-20 13:48 ` Re: Pasword expiration warning Andrew Dunstan <[email protected]>
@ 2021-11-21 09:49 ` Gilles Darold <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Gilles Darold @ 2021-11-21 09:49 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; Bossart, Nathan <[email protected]>; Gilles Darold <[email protected]>; Tom Lane <[email protected]>; +Cc: pgsql-hackers
Le 20/11/2021 à 14:48, Andrew Dunstan a écrit :
> On 11/19/21 19:17, Bossart, Nathan wrote:
>> On 11/19/21, 7:56 AM, "Tom Lane" <[email protected]> wrote:
>>> That leads me to wonder about server-side solutions. It's easy
>>> enough for the server to see that it's used a password with an
>>> expiration N days away, but how could that be reported to the
>>> client? The only idea that comes to mind that doesn't seem like
>>> a protocol break is to issue a NOTICE message, which doesn't
>>> seem like it squares with your desire to only do this interactively.
>>> (Although I'm not sure I believe that's a great idea. If your
>>> application breaks at 2AM because its password expired, you
>>> won't be any happier than if your interactive sessions start to
>>> fail. Maybe a message that would leave a trail in the server log
>>> would be best after all.)
>> I bet it's possible to use the ClientAuthentication_hook for this. In
>> any case, I agree that it probably belongs server-side so that other
>> clients can benefit from this.
>>
> +1 for a server side solution. The people most likely to benefit from
> this are the people least likely to be using psql IMNSHO.
Ok, I can try to implement something at server side using a NOTICE message.
--
Gilles Darold
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2021-11-21 09:49 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-11-19 14:49 Pasword expiration warning Gilles Darold <[email protected]>
2021-11-19 15:16 ` Dinesh Chemuduru <[email protected]>
2021-11-19 15:55 ` Tom Lane <[email protected]>
2021-11-19 16:56 ` Gilles Darold <[email protected]>
2021-11-20 00:17 ` Bossart, Nathan <[email protected]>
2021-11-20 05:46 ` Michael Paquier <[email protected]>
2021-11-20 13:48 ` Andrew Dunstan <[email protected]>
2021-11-21 09:49 ` Gilles Darold <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox