Обсуждение: Record last password change
Hello, a customer recently mentioned that they'd like to be able to see when a (md5, scram) role had their password last changed. Use-cases for this would be issueing an initial password and then later making sure it got changed, or auditing that all passwords get changed once a year. You can do that via external authentication methods like ldap/gss-api/pam but in some setups those might not be available to the DBAs. I guess it would amount to adding a column like rolpasswordchanged to pg_authid and updating it when rolpassword changes, but maybe there is a better way? The same was requested in https://dba.stackexchange.com/questions/91252/ how-to-know-when-postgresql-password-is-changed so I was wondering whether this would be a welcome change/addition, or whether people think it's not worth bothering to implement it? Thoughts? Michael -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: michael.banck@credativ.de credativ GmbH, HRB Mönchengladbach 12080 USt-ID-Nummer: DE204566209 Trompeterallee 108, 41189 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer Unser Umgang mit personenbezogenen Daten unterliegt folgenden Bestimmungen: https://www.credativ.de/datenschutz
On 11/12/2018 23:33, Michael Banck wrote: > Hello, > > a customer recently mentioned that they'd like to be able to see when a > (md5, scram) role had their password last changed. > > Use-cases for this would be issueing an initial password and then later > making sure it got changed, or auditing that all passwords get changed > once a year. You can do that via external authentication methods like > ldap/gss-api/pam but in some setups those might not be available to the > DBAs. > > I guess it would amount to adding a column like rolpasswordchanged to > pg_authid and updating it when rolpassword changes, but maybe there is a > better way? > > The same was requested in https://dba.stackexchange.com/questions/91252/ > how-to-know-when-postgresql-password-is-changed so I was wondering > whether this would be a welcome change/addition, or whether people think > it's not worth bothering to implement it? > > Thoughts? > > > > Michael > Forcing people to change their password on a regular basis is a bad idea, tends to make people choose easier to guess passwords. Do you regularly change the locks on your house? My root password is 16 characters that was computer generated -- not worth memorising, if I had to regularly change it! Example password: q!5H!A:xa$3l%o.y Good luck trying to crack my system using it! If anyone is interested, I can publish the Java program I wrote to generate my passwords. Cheers, Gavin
Hi, Am Dienstag, den 11.12.2018, 23:45 +1300 schrieb Gavin Flower: > On 11/12/2018 23:33, Michael Banck wrote: > > a customer recently mentioned that they'd like to be able to see when a > > (md5, scram) role had their password last changed. > > > > Use-cases for this would be issueing an initial password and then later > > making sure it got changed, or auditing that all passwords get changed > > once a year. You can do that via external authentication methods like > > ldap/gss-api/pam but in some setups those might not be available to the > > DBAs. > > > > I guess it would amount to adding a column like rolpasswordchanged to > > pg_authid and updating it when rolpassword changes, but maybe there is a > > better way? > > > > The same was requested in https://dba.stackexchange.com/questions/91252/ > > how-to-know-when-postgresql-password-is-changed so I was wondering > > whether this would be a welcome change/addition, or whether people think > > it's not worth bothering to implement it? > > Forcing people to change their password on a regular basis is a bad > idea, tends to make people choose easier to guess passwords. Do you > regularly change the locks on your house? This proposal is not about forcing password changes, so I am not sure why you ask? > My root password is 16 characters that was computer generated -- not > worth memorising, if I had to regularly change it! > > Example password: q!5H!A:xa$3l%o.y Good luck trying to crack my system > using it! > > If anyone is interested, I can publish the Java program I wrote to > generate my passwords. I see your point about security of strong passwords, but that seems largely orthogonal to the desire to know when a password was last changed. Michael -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: michael.banck@credativ.de credativ GmbH, HRB Mönchengladbach 12080 USt-ID-Nummer: DE204566209 Trompeterallee 108, 41189 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer Unser Umgang mit personenbezogenen Daten unterliegt folgenden Bestimmungen: https://www.credativ.de/datenschutz
Michael Banck <michael.banck@credativ.de> writes: > The same was requested in https://dba.stackexchange.com/questions/91252/ > how-to-know-when-postgresql-password-is-changed so I was wondering > whether this would be a welcome change/addition, or whether people think > it's not worth bothering to implement it? This has all the same practical problems as recording object creation times, which we're not going to do either. (You can consult the archives for details, but from memory, the stickiest aspects revolve around what to do during dump/reload. Although even CREATE OR REPLACE offers interesting definitional questions. In the end there are just too many different behaviors that somebody might want.) I've heard that if you want to implement a password aging policy, PAM authentication can manage that for you; but I don't know the details. regards, tom lane
On 12/11/18 9:56 AM, Tom Lane wrote: > I've heard that if you want to implement a password aging policy, PAM > authentication can manage that for you; but I don't know the details. Interesting idea ... could use pam-pgsql[1] and PAM as the authentication method. Might result in another connection (from PAM) to authenticate every connection, though. I suppose the module could use a daemon keeping one connection open for auth queries, but the README doesn't *say* it does. Could set up a pooler just for the auth module to connect through, I guess. It allows you to configure arbitrary auth_query, acct_query, pwd_query, etc., so you could conceivably join pg_authid with some other table where you'd keep expiration info. Looks like our PAM authentication might not support some PAM capabilities like conducting additional message exchanges (for example, to prompt for a new password on the spot if the old one has expired). It might be possible to shoehorn that capability into the existing fe-be protocol by calling it a custom SASL method, something analogous to ssh's "keyboard-interactive"[2]. -Chap [1] https://github.com/pam-pgsql/pam-pgsql [2] https://tools.ietf.org/html/rfc4256
Could you add your own UPDATE trigger to the password table? It can write an entry to a new table, e.g., (userid, current date) whenever a record in that table is modified.
On an earlier question - the issue isn't whether someone can crack your password, it's possible disclosure in archive media somewhere. E.g., a classic example is someone who accidently commits source code that contains a password and then reverts it. It's not in the current source code but without a lot of effort (not always possible) it could be retrieved by anyone with access to the commit history. If you change your password every few months this will soon be a moot issue even if the person doesn't mention this to someone who can change the password immediately.
A more subtle point is backups. An attacker might have had access to encrypted backups (or regular backups containing encrypted records) for a very long time and held onto them against the chance discovery of the password. Once they learn it they have access to all of that data. If you rotate the passwords they might have access to a few months of data but no more than that. It's bad, but a few months is far better than a few years if your data contains information that requires notification of everyone affected and the offer of credit monitoring, etc.
I agree that people may choose bad passwords if forced to change them too frequently but I'm in the camp that says it's fine to use a password manager or even to write them down on a card kept in the person's wallet.
BTW another solution is SSO, e.g., Kerberos. I still need to submit a patch to pgsql to handle it better(*) but with postgresql itself you sign into the system and then the database server will just know who you are. You don't have to worry about remembering a new password for postgresql. X.509 (digital certs) are another possibility and I know you can tie them to a smart card but again I don't know how well we could integrate it into pgsql.
(*) I haven't looked at the code recently but the last time I checked pgsql used the username/password combo. Enterprise environments usually use keytab files instead of (u/p). It should also be smart enough to check if the user already has a kerberos ticket and use it if nothing else is specified. The latter would usually work with people. (I'm not sure what happens in a more secure environment where the database expects the service to be specified as well - users would need the username/postgresql@REALM identity, not the more generic username@REALM identity.)
On Tue, Dec 11, 2018 at 12:04 PM Chapman Flack <chap@anastigmatix.net> wrote:
On 12/11/18 9:56 AM, Tom Lane wrote:
> I've heard that if you want to implement a password aging policy, PAM
> authentication can manage that for you; but I don't know the details.
Interesting idea ... could use pam-pgsql[1] and PAM as the
authentication method. Might result in another connection (from PAM)
to authenticate every connection, though. I suppose the module could
use a daemon keeping one connection open for auth queries, but the
README doesn't *say* it does. Could set up a pooler just for the auth
module to connect through, I guess.
It allows you to configure arbitrary auth_query, acct_query, pwd_query,
etc., so you could conceivably join pg_authid with some other table
where you'd keep expiration info.
Looks like our PAM authentication might not support some PAM
capabilities like conducting additional message exchanges (for
example, to prompt for a new password on the spot if the old
one has expired).
It might be possible to shoehorn that capability into the existing
fe-be protocol by calling it a custom SASL method, something analogous
to ssh's "keyboard-interactive"[2].
-Chap
[1] https://github.com/pam-pgsql/pam-pgsql
[2] https://tools.ietf.org/html/rfc4256
Greetings, * Michael Banck (michael.banck@credativ.de) wrote: > a customer recently mentioned that they'd like to be able to see when a > (md5, scram) role had their password last changed. There is an awful lot here that we really should be doing. For a long time, that felt prettty stalled because of the md5 mechanism being used, but now that we've got SCRAM, there's a number of things we should be doing: - Password aging (which requires knowing when it was last changed) - Password complexity - Disallow repeated use of the same password - Requiring password change on first/next connection - User/Password profiles more... > Use-cases for this would be issueing an initial password and then later > making sure it got changed, or auditing that all passwords get changed > once a year. You can do that via external authentication methods like > ldap/gss-api/pam but in some setups those might not be available to the > DBAs. Agreed. > I guess it would amount to adding a column like rolpasswordchanged to > pg_authid and updating it when rolpassword changes, but maybe there is a > better way? That could be a start, but I do expect that we'll grow at least one other table eventually to support user profiles. > The same was requested in https://dba.stackexchange.com/questions/91252/ > how-to-know-when-postgresql-password-is-changed so I was wondering > whether this would be a welcome change/addition, or whether people think > it's not worth bothering to implement it? Definitely a +1 from me, but I'd like us to be thinking about the other things we should be doing in this area to bring our password-based authentication mechanism kicking-and-screaming into the current decade. Thanks! Stephen
Вложения
Greetings, * Tom Lane (tgl@sss.pgh.pa.us) wrote: > Michael Banck <michael.banck@credativ.de> writes: > > The same was requested in https://dba.stackexchange.com/questions/91252/ > > how-to-know-when-postgresql-password-is-changed so I was wondering > > whether this would be a welcome change/addition, or whether people think > > it's not worth bothering to implement it? > > This has all the same practical problems as recording object creation > times, which we're not going to do either. (You can consult the > archives for details, but from memory, the stickiest aspects revolve > around what to do during dump/reload. Although even CREATE OR REPLACE > offers interesting definitional questions. In the end there are just > too many different behaviors that somebody might want.) I disagree with these being serious practical problems- we just need to decide which was to go when it comes to dump/restore here and there's an awful lot of example systems out there to compare to for this case. > I've heard that if you want to implement a password aging policy, PAM > authentication can manage that for you; but I don't know the details. That's ridiculously ugly; I know, because I've had to do it, more than once. In my view, it's past time to update our password mechanisms to have the features that one expects a serious system to have these days. Thanks! Stephen
Вложения
Stephen Frost <sfrost@snowman.net> writes:
> ... Definitely a +1 from me, but I'd like us to be thinking about the other
> things we should be doing in this area to bring our password-based
> authentication mechanism kicking-and-screaming into the current decade.
I'm not really excited about reinventing the whole of PAM, which is
where this argument seems to be leading.
regards, tom lane
Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> This has all the same practical problems as recording object creation
>> times, which we're not going to do either. (You can consult the
>> archives for details, but from memory, the stickiest aspects revolve
>> around what to do during dump/reload. Although even CREATE OR REPLACE
>> offers interesting definitional questions. In the end there are just
>> too many different behaviors that somebody might want.)
> I disagree with these being serious practical problems- we just need to
> decide which was to go when it comes to dump/restore here and there's an
> awful lot of example systems out there to compare to for this case.
Yeah, an awful lot of systems with an awful lot of different behaviors.
This doesn't exactly refute my point.
For the specific case of password aging, it's possible we could agree
on a definition, but that remains to be seen.
regards, tom lane
Greetings, * Tom Lane (tgl@sss.pgh.pa.us) wrote: > Stephen Frost <sfrost@snowman.net> writes: > > ... Definitely a +1 from me, but I'd like us to be thinking about the other > > things we should be doing in this area to bring our password-based > > authentication mechanism kicking-and-screaming into the current decade. > > I'm not really excited about reinventing the whole of PAM, which is > where this argument seems to be leading. PAM isn't supported on all of our platforms and, really, even where we do support it, it's frankly beyond impractical to actually use the PAM modules because they expect to be run as root, which we don't do. I can understand that you're not excited about it, and I'm not keen to reinvent all of PAM (there's an awful lot of it which we really don't need), but there are features that happen to also exist in PAM (and Kerberos, and LDAP, and RADIUS, and...) that we really should have in our own password-based authentication system because our users are expecting them. Looking at the various forks of PG that are out there shows that quite clearly, I don't imagine they implemented these features out of pure fun, and they obviously also realized that trying to actually use PAM from PG was ultimately a bad idea. Thanks! Stephen
Вложения
On Tue, Dec 11, 2018 at 09:56:54AM -0500, Tom Lane wrote: > Michael Banck <michael.banck@credativ.de> writes: > > The same was requested in https://dba.stackexchange.com/questions/91252/ > > how-to-know-when-postgresql-password-is-changed so I was wondering > > whether this would be a welcome change/addition, or whether people think > > it's not worth bothering to implement it? > > This has all the same practical problems as recording object creation > times, which we're not going to do either. (You can consult the > archives for details, but from memory, the stickiest aspects revolve > around what to do during dump/reload. Although even CREATE OR REPLACE > offers interesting definitional questions. In the end there are just > too many different behaviors that somebody might want.) I wrote a blog on this topic in 2017: https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017 -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
On Wed, Dec 12, 2018 at 07:30:18AM -0700, Bear Giles wrote:
> BTW another solution is SSO, e.g., Kerberos. I still need to submit a patch to
> pgsql to handle it better(*) but with postgresql itself you sign into the
> system and then the database server will just know who you are. You don't have
> to worry about remembering a new password for postgresql. X.509 (digital certs)
> are another possibility and I know you can tie them to a smart card but again I
> don't know how well we could integrate it into pgsql.
(Good to talk to you again.) I recently wrote a blog entry about
putting the certificate and its private key on removable media:
https://momjian.us/main/blogs/pgblog/2019.html#January_16_2019
and mentioned the value of PIV over removable media:
https://momjian.us/main/blogs/pgblog/2019.html#January_14_2019
I can't think of a way to access a smart card for authentication, though
I did wrote a presentation on how to use PIV devices for server-side and
client-side encryption:
https://momjian.us/main/writings/crypto_hw_use.pdf
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +