Thread: password rules

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

raphi <raphi@crashdump.ch> writes:
> 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".
> ...
> Is there any chance PG will provide this natively or are there any 
> technical limitations I am unaware of?

I think we have pretty much decided that that will never be part of
core Postgres.  If you don't like using an extension for it, you're
out of luck.  (The core developers have too much to do already, so
we are never going to be receptive to arguments like "I don't want
to use an extension".  But in this case the space of possible
requirements is so large that it doesn't make sense to try to build
a one-size-fits-all in-core solution.)

Extension or not, there are serious objections to many aspects of
such a feature, namely that they can't be enforced without requiring
clients to send cleartext passwords to the server.  That in itself
is a security problem.  For that matter, the whole business of using
passwords rather than other ID technologies (SSL certificates,
Kerberos/GSS tickets, etc) is feeling pretty twentieth-century.

            regards, tom lane

Am 23.06.2025 um 22:39 schrieb Christoph Berg:
> Re: raphi
>> 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.
> Did you give the "pam" method a try? T
Not really because it's a local solution. How do you change passwords or 
keep history on your standby nodes? Besides, the documentation says that 
postgres can't handle /etc/shadow because it runs unprivileged, only 
pam_ldap would work. Or am I missing something?

have fun,
raphi

Le 24/06/2025 à 07:18, raphi a écrit :
>
>
> Am 23.06.2025 um 22:39 schrieb Christoph Berg:
>> Re: raphi
>>> 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.
>> Did you give the "pam" method a try? T
> Not really because it's a local solution. How do you change passwords
> or keep history on your standby nodes? Besides, the documentation says
> that postgres can't handle /etc/shadow because it runs unprivileged,
> only pam_ldap would work. Or am I missing something?
>
> have fun,
> raphi


I think the credcheck extension has been created to handle the features
you are requesting.

 > - enforce some password complexity and prevent reuse

This is already implemented.

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

I have started to work some weeks ago and it just need more time to
end/polish the job.

 > the password history is not being replicated to the standby so we can
not use it.

It is in my TODO list for a year as you noted and will try to implement
it this summer.

--
Gilles Darold

Am 25.06.2025 um 01:20 schrieb Greg Sabino Mullane:
> On Mon, Jun 23, 2025 at 2:45 PM raphi <raphi@crashdump.ch> wrote:
>
>     As of now though we cannot use PG for any PCI/DSS certified
>     application
>     because we can't enforce either complexity nor regular password
>     changes,
>
>
> You can, and many, many companies do, but you need a modern auth 
> system like Kerberos. Even if we were to put something into Postgres 
> today (and given the MFA and re-use requirements, it's near 
> impossible), PCI DSS keeps evolving and getting stricter, so keeping 
> up with it would get harder with each release.
>
>     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.
>
>
> Your energy would be much better used in bringing Kerberos into your 
> organization. :)
>
Well as said, we have LDAP and IAM widely in use for everything except 
database access. It's the IAM part that's making it difficult for us to 
implement it for PG application/user roles, this wouldn't change by 
using Kerberos instead of LDAP. I thought we'll get the exception from 
our security to use IAM roles instead of physical persons defined as the 
owner of the PG accounts but now they are against it. Main reason is 
because they are looking into a completely different solution with 
Vault, which would fix some other issues and make it more robust towards 
PCI, and they prefer a solution for everything rather than making 
another exception. But we are speaking about years here, 2027 earliest 
and they haven't even talked to us yet how this would work with PG, only 
other DB products.

have fun,
raphi

On 2025-06-23 16:35:35 +0200, raphi wrote:
> 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.

I don't see why that should be the case. You could either grant
privileges to more than one person or - preferrably - to a role which is
then granted to the personal roles.

So for example you would authenticate as «raphi» and I as «hjp» but we
could both change to «foo_admin» or whatever. That would even have the
advantage that we leave an audit trail with our "real" identities.

        hjp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Am 25.06.2025 um 17:33 schrieb Peter J. Holzer:
> On 2025-06-25 14:42:26 +0200, raphi wrote:
> [snip]
>> That's not how the identiy principle works, at least not how it's implement
>> in our company. A user in ldap has a direct relation to one digital entity,
>> either a token from an application or certificate from a physical person
>> (maybe some AD shenanigans also). We don't have digital entities for teams,
>> that's what's missing. For it to work they (security) would need to allow to
>> weaken this principle and as you said, allow everyone who has a certain role
>> to manage the associated user in LDAP, like setting a new password.
> That user shouldn't have a password, since nobody is authenticating as
> that user. It also doesn't have to exist in LDAP. It's just a role in
> the database.
hmm I don't follow, maybe I was doing it wrong? In my tests I configured 
LDAP as described in the documentation: 
https://www.postgresql.org/docs/current/auth-ldap.html

[quote]
Once the user has been found in this search, the server re-binds to the 
directory as this user, using the password specified by the client, to 
verify that the login is correct
[/quote]

And this worked as expected, when the user provided the password stored 
in LDAP, they could login and couldn't when given a wrong (or empty) 
password. Can you elaborate on what you mean?

[snip]
> But do they? "Complexity" (scare quotes intentional) rules are easy to
> circumvent and when people don't see the need for strong passwords, they
> will do so. If they do see the need they will use strong passwords on
> their own and the rules are somewhere between unnecessary and
> counter-productive. Most guidelines also have stopped recommending
> mandatory password rotations quite some time ago.
>
> These features provide convenient boxes for auditors to tick off and
> security for management who can claim that they did something.
> Operational security? Not so much.
>
> (just my personal opinion as someone who's been a sysadmin for over 20
> years (although not recently))
Well, PCI still does. But checking auditors boxes is not the main reason 
I am looking for a solution (because we don't have any PCI applications 
on PG yet anyway) but our self service: An ansible playbook creates a 
role for someone and sends the password by email to the user who wanted 
the role created. This password ought to be temporary and should be 
changed upon first connect. Without any checks we can't neither ensure 
that the password will be changed nor that it won't be reused. We make 
it clear that this should happen and most devs probably do the sensible 
thing and set a new complex password but as you said, some people are 
just lazy and there's no feasable way for us to verify this.

have fun
raphi (who also was once in an almost forgotten lifetime a solaris admin 
for over 20 years ;))