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 <c...@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 > >
