Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mo7Bg-00018G-Kf for pgsql-hackers@arkaria.postgresql.org; Fri, 19 Nov 2021 16:56:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mo7Be-0004kS-Ht for pgsql-hackers@arkaria.postgresql.org; Fri, 19 Nov 2021 16:56:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mo7Be-0004kI-5f for pgsql-hackers@lists.postgresql.org; Fri, 19 Nov 2021 16:56:30 +0000 Received: from mail-wm1-x32b.google.com ([2a00:1450:4864:20::32b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1mo7BX-00011F-EX for pgsql-hackers@postgresql.org; Fri, 19 Nov 2021 16:56:29 +0000 Received: by mail-wm1-x32b.google.com with SMTP id 137so5579433wma.1 for ; Fri, 19 Nov 2021 08:56:23 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=migops.com; s=google; h=subject:to:cc:references:from:message-id:date:user-agent :mime-version:in-reply-to:content-language; bh=SJgh5yw3IRN1hX7CmwWHmbkM8gZVcMm5YWq918S6kiM=; b=psB/Jk7df8nE57p6ELkKzEK8sjni4bffh7JkoEQgKUSgTJmB4i0apQmSO+FBxXHMAW RiAXvuDtmavHVuGavFCIuw7iMBx446fqoT6pfSu6hTCZjvHE8SQazfaMOHA32o1nxwEN Jn0hXQglMoa+XBBB7mgOHCA8IVwnYBazLC3/ArNSyqKaqIxppx1WuNTqN446hxGiSb9m I7eXkLWrBZLFQy0mLy+3l93IKWe8umnnjSJRONhJrmJY2HQvPg2dsxMUlwXjcLNu1abv guyi4uFP/xm5cjML7eStlBT5Sx1HGcf5fqdZDxqPuWwQWeRT412OZFek28JcOOZD4vZa atcg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:subject:to:cc:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=SJgh5yw3IRN1hX7CmwWHmbkM8gZVcMm5YWq918S6kiM=; b=I4TbVv8+baI0AwNWx2LLxI0UUk2ZTDdunWHiqMC2KPbXXRLfSYPdYbgG+wrApVjXMK ukoh+MgQh3/Hue64t9Lh2wK8Deko9E2exaDkbixiShdko/a3TChpIKR30whUpQ+B8kq3 DJ8pKtacS84sbSCYmAlPjDsO1IJ0FLD1YqpoSiBkCNxdr97WWEfDdngwA8KQX91DP/0/ gkxSDxV3w5F02kPKO24aPZL+GfJlk1mSbRdO/+6suMW/rFleGlY9ryQFAQqNduPmk/Ki DJ9F2jE7G9jFPc47Gh2OWcKbt9cYS3XC75YMJLNtef7SaedN4LPRfghRLqI5Xu+XZl48 3RoQ== X-Gm-Message-State: AOAM53033N0T2xnTevgtoGIirQOEzv2gSCAdNiUseiQzZgN8TfYCdB6Z avprFrC2JcdPoPHU5zAI+SKKvCPAxIvmrw== X-Google-Smtp-Source: ABdhPJz1Wl/Aiy/L+g2Ez/E9yOV4kAM3BcpxE+iT5q44fNeVCJzoaI2dYlNbzKmz3/zGO7R7DabzPw== X-Received: by 2002:a05:600c:3b1b:: with SMTP id m27mr1325963wms.125.1637340981885; Fri, 19 Nov 2021 08:56:21 -0800 (PST) Received: from ?IPv6:2a01:cb14:51d:5100:11d4:49ba:48dc:8256? ([2a01:cb14:51d:5100:11d4:49ba:48dc:8256]) by smtp.gmail.com with ESMTPSA id t189sm198930wma.8.2021.11.19.08.56.21 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 19 Nov 2021 08:56:21 -0800 (PST) Subject: Re: Pasword expiration warning To: Tom Lane Cc: PostgreSQL Hackers References: <129bcfbf-47a6-e58a-190a-62fc21a17d03@migops.com> <3046422.1637337334@sss.pgh.pa.us> From: Gilles Darold Message-ID: Date: Fri, 19 Nov 2021 17:56:20 +0100 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.14.0 MIME-Version: 1.0 In-Reply-To: <3046422.1637337334@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------8A2CAC1840CF294DAEA612F4" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------8A2CAC1840CF294DAEA612F4 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Le 19/11/2021 à 16:55, Tom Lane a écrit : > Gilles Darold 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 --------------8A2CAC1840CF294DAEA612F4 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit
Le 19/11/2021 à 16:55, Tom Lane a écrit :
Gilles Darold <gilles@migops.com> 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
--------------8A2CAC1840CF294DAEA612F4--