Subject: Re: password rules

From: raphi <[email-masked]>
Date: Wed, 25 Jun 2025 14:42:26 +0200

Am 25.06.2025 um 13:55 schrieb Peter J. Holzer:
> 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.
>
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. Technically possible and we have 
similar mechanisms in place for other access, but authentication is then 
made by key or certificate, ldap/iam is only used to control who can 
access what.

But "our" problem aside, I still don't quite understand the decision 
that this was never implemented. If password authentication is so bad, 
why allow it all then? And when it's allowed, why not provide some basic 
features to make it more secure? The lack of any password rules is in it 
self the reason why it is so dangerous to use passwords in PG. I'd argue 
that the use of passwords with complexity requirements and TTL settings 
over an encrypted connection, with firewall rules and proper hba.conf 
access lists, are quite safe. Maybe even safer than a central solution 
like LDAP or Kerberos which is a single point of entry for an attacker, 
be it by attacking the software itself or the backup of the data, 
potentially getting access to everything instead of "just" one hacked 
password. But now I'm definitely getting into "let's have a beer" 
territory... ;)

have fun
raphi

---

From: "Peter J. Holzer" <[email-masked]>
Date: Wed, 25 Jun 2025 17:33:05 +0200

On 2025-06-25 14:42:26 +0200, raphi wrote:
> 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.

> But "our" problem aside, I still don't quite understand the decision that
> this was never implemented. If password authentication is so bad, why allow
> it all then?

Legacy (posgresql is old). And also hard to avoid if you want to be
usable on a wide variety of platforms and in a wide variety of
situations.

> And when it's allowed, why not provide some basic features to
> make it more secure?

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

> The lack of any password rules is in it self the reason
> why it is so dangerous to use passwords in PG. I'd argue that the use of
> passwords with complexity requirements and TTL settings over an encrypted
> connection, with firewall rules and proper hba.conf access lists, are quite
> safe.

I agree with you that they can be quite safe. I would claim that
complexity requirements and TTL settings play only a miniscule role in
that.

> Maybe even safer than a central solution like LDAP or Kerberos which
> is a single point of entry for an attacker, be it by attacking the software
> itself or the backup of the data, potentially getting access to everything
> instead of "just" one hacked password.

Agree. In fact, all our databases are intentionally not linked to AD.

        hjp

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

---

From: raphi <[email-masked]>
Date: Wed, 25 Jun 2025 17:55:12 +0200

Am 25.06.2025 um 17:33 schrieb Peter J. Holzer:
> [snip]
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]
> 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 ;))

---

From: "Peter J. Holzer" <[email-masked]>
Date: Thu, 26 Jun 2025 14:27:41 +0200

On 2025-06-25 17:55:12 +0200, raphi wrote:
> > > 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.
> hmm I don't follow, maybe I was doing it wrong?

I'm thinking of something like this:

Roles assigned to people are in LDAP, and only they have passwords.
Application roles don't have to be in LDAP (maybe there are operational
reasons to have them there, but PostgreSQL doesn't need them) and don't
have passwords.

So assuming I have an application testapp which needs its own database
and and admin account and I am one of the admins, I would create it like
this:

postgres=# create role testapp_admin;
CREATE ROLE

postgres=# create database testapp owner testapp_admin;
CREATE DATABASE

postgres=# grant testapp_admin to hjp with inherit false, set true;
GRANT ROLE

Now, when I want to do some work on that database I can't login directly
as testapp_admin (that role doesn't have a password), but I can login as
hjp:

% psql -h localhost -U hjp -d testapp
Password for user hjp:
Null display is "(∅)".
Line style is unicode.
Border style is 2.
Unicode border line style is "double".
Timing is on.
Expanded display is used automatically.
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

testapp=>

Because of the "inherit false" I can't do anything as me:

testapp=> create table foo(id serial primary key, t text);
ERROR:  permission denied for schema public

But I can change my role to testapp_admin:

testapp=> set role testapp_admin ;
SET

And then I can do my work:

testapp=> create table foo(id serial primary key, t text);
CREATE TABLE

testapp=> \dt foo
            List of relations
╔════════╤══════╤═══════╤═══════════════╗
║ Schema │ Name │ Type  │     Owner     ║
╟────────┼──────┼───────┼───────────────╢
║ public │ foo  │ table │ testapp_admin ║
╚════════╧══════╧═══════╧═══════════════╝

If I turn on logging, these actions will also be attributed to me in the
log:

2025-06-26 14:10:26.458 CEST [32945] [unknown]@[unknown] LOG:
    connection received: host=127.0.0.1 port=45780
2025-06-26 14:10:26.499 CEST [32945] hjp@testapp LOG:
    connection authenticated: identity="hjp" method=scram-sha-256
    (/etc/postgresql/16/main/pg_hba.conf:125)
2025-06-26 14:10:26.499 CEST [32945] hjp@testapp LOG:
    connection authorized: user=hjp database=testapp application_name=psql
    SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)
2025-06-26 14:11:38.641 CEST [32945] hjp@testapp LOG:
    statement: create table bar(id serial primary key, t text);

(There are variations of the theme. For example, if your databases are
on Linux and your developers/admins are familiar with the commandline
you could use pg_ident.conf - which I use a lot)

> An ansible playbook creates a role for someone and sends the password
> by email to the user who wanted the role created.

Right. That's what I would do, too. Except that I would not set a
password and grant the role to the intended user(s) instead.

(We do currently have role accounts with passwords - but they are a bit
of a pain, so maybe I should try to get rid of them.)

        hjp

---

From: raphi <[email-masked]>
Date: Fri, 27 Jun 2025 19:00:36 +0200

> reasons to have them there, but PostgreSQL doesn't need them) and don't
Thank you very much for the detailed test. It will be useful for other 
ideas I have but (I think) it does not solve our particular case. Maybe 
I wasn't clear enough and I'm sorry for that, but our problem lies in 
the way how applications connect. The passwords that devs are ordering 
via our self service is for the application that is connecting to the 
database, not for themselfs. It's the application's password that we 
want to ensure that it is complex and gets changed after we set an 
initial password for it. Because PG does not do it, we wanted to resolve 
this with LDAP but failed due to IAM.

But, I just returned from the once again phenomenal Swiss PGDays and I 
had the chance to talk to Laurenz and Dani and I can say, that I finally 
get it now. Laurenz explained very well how the whole authentication 
works and that the philosophy is that the postgres server should never 
know any passwords. I think that's what Tom tried to say but I failed to 
understand it correctly, I thought the issue is not having unencrypted 
passwords being sent over the network. So, why this also does not solve 
our problem, I am giving up on trying to use PG to enforce some password 
rules, I'm not even sure if credcheck is still a good idea. But the more 
I think about it the more I like switching to certificates, after all we 
already have mechanisms in place to automatically get new officially 
trusted (not selfsigned) certificates, it could be adoptable for PG 
connects too. I'm just not sure how and if all our applications can 
adopt this, but that's a "me" problem.

Anyways, thanks to everyone who helped me see the PG way, I won't bother 
anyone with this topic again ;)

have fun,
raphi

---

From: "Peter J. Holzer" <[email-masked]>
Date: Sat, 28 Jun 2025 15:59:23 +0200

On 2025-06-27 19:00:36 +0200, raphi wrote:
> Thank you very much for the detailed test. It will be useful for other ideas
> I have but (I think) it does not solve our particular case. Maybe I wasn't
> clear enough and I'm sorry for that, but our problem lies in the way how
> applications connect. The passwords that devs are ordering via our self
> service is for the application that is connecting to the database, not for
> themselfs.

Ok. I misunderstood that.

> It's the application's password that we want to ensure that it is
> complex and gets changed after we set an initial password for it.

Why let a human change that at all? Couldn't you just create a suitable
random password at deployment time? (And then automatically every n
months if you want to rotate it.)

> But the more I think about it the more I like switching to
> certificates, after all we already have mechanisms in place to
> automatically get new officially trusted (not selfsigned)
> certificates, it could be adoptable for PG connects too.

I agree. If you already have the infrastructure for that, that's a good
way to avoid some (but not all) of the problems with passwords.

        hjp

---

From: Ron Johnson <[email-masked]>
Date: Sat, 28 Jun 2025 11:14:04 -0400

On Sat, Jun 28, 2025 at 9:59 AM Peter J. Holzer <[email-masked]> wrote:

> ideas

"openssl rand -base64 48" or a couple of random words
from /usr/share/dict/words plus a random number.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!