password rules - Mailing list pgsql-general

Hello all,

I've been lurking for quite a while on the pg lists but now I need some 
help or rather, want to start a discussion:

We can set a password for a role in PG but there is no way to force a 
user to change it, prevent reuse or to enforce some complexity on it. As 
I understand, that's by choice and when I ask about this, the usual 
answer is "that's not the job of a database, use LDAP for it".

To be fair, setting up LDAP is very easy in PG, just one line in 
hba.conf and all is done. But sadly, that's only where the problems 
begin. The difficult part is to embedd this setup into a company, 
especially a large one as I work for with over 1000 PG databases and at 
least that many roles. Someone needs to be able to manage the passwords 
in LDAP and this means someone has to decide who can change which 
passwords, which is usually where some sort of Identity and Access 
Management (IAM) comes into place.

We already have LDAP and IAM in place in our organization for many other 
things, but IAM identities are coupled to a real person, not a team. 
Which means only one person in the team would be able to set a new 
password and when that person leaves the team, IAM rights need to be 
revoked and given to a new person. Doable, but quite a pane in the 
behind, especially when that one person happens to be on holidays. The 
prefered way would be to couple the rights to a dev-team-specific IAM 
role, which is something I am trying to get the okay for from our 
security for the past two years but failed so far (they argue it's a 
PCI/DSS requirement).

What I wish for are two seamingly simple features in PG that would solve 
all our problems without LDAP:

- enforce some password complexity and prevent reuse

- expire a password immediately after creating and prompt the user to 
change it upon first login try. They can connect with the initial 
password but cannot login until they've set a new password.

Background is: our developers can manage their own databases for their 
applications via a self service we've build for them. They can configure 
which databases and roles they need, our self service deployes 
everything, generates a password and sends the info to the dev via 
email. They idea would be, that the dev will change the password 
immediately but we cannot enforce that the dev will change the password, 
ever. And we also can't prevent the dev from setting "1234" as a 
password. With LDAP we could do all this but as stated above, it's not 
easy to implement (our "dev" is usually a team). We've reached a point 
where we (the dba team) are seriously discussing setting up our own LDAP 
server(s) without IAM, solely driven by our self service. But it will be 
tricky to find a setup without being a single point of failure for that 
many databases and get the okay for the resources needed to run and 
manage it, when we already have an "official" LDAP server.

I know there are extensions which are half-way there, like credcheck, 
but they suffer from the same drawback as most extensions, maintained 
only by a very small team or single person who after some years no 
longer has much time for it. Which is why we don't use any extension 
outside the official source code at all.

Sorry for this rather long (first) email on this list but I feel like I 
had to explain our usecase and why LDAP is not always as simple as 
adding a line to hba.conf. I understand the sentiment why some argue 
that this should not be the job of the DB but on the other hand, the DB 
already allows setting a password in the first place, hence why should 
it not be able to enforce some rules?

Is there any chance PG will provide this natively or are there any 
technical limitations I am unaware of? Can I do something to help 
bringing these feature into PG? My C knowledge is very limited so I 
won't be able to provide a patch but I'd be more than happy to test it. 
Also, I'll be at the Swiss PGday this week in Rapperswil if someone 
wants to discuss this in person ;)

have fun,

raphi