password rules

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 17:05 schrieb Tom Lane:

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?

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

I'd be open to use an extension for this if there'd be one that is still
maintained. The seemingly most popular one, credcheck, has an issue open
for over a year, the password history is not being replicated to the
standby so we can not use it. The other one, passwordpolicy, hasn't been
updated in 6 years.

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.

We only allow encrypted connections as (hopefully) most do and don't log
any passwords but I see your point. As said, it's an ongoing battle
between what DBAs need and what is possible in our environment. After my
discussion today with our security officer, LDAP will probably never be
the solution for us because of IAM (which is why I wrote here). He
mentioned a project for next year where they want to look into a Vault
solution. It's still password authentication but with complexity, TTL
and "hidden" from users.

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,
which is required in PCI - and they are fine with using passwords per
se, but with constraints. We can with other DB products, which is a
pitty, it disqualifies PG automatically from the discussion for certain
applications even when PG would be the better fit. I would've thought
that this alone would put password handling back on the todo list,
providing PCI compliance out-of-the-box without the need of additional
infrastructure would be something to brag about :D Because 20th or 21th
century, password authentication will probably be used for a long time,
especially when it's still allowed by PCI and other industry standards.

One last thing, any chance that "valid until" could get a flag where DBA
can choose if the user will have a chance to set a new password when it
expires instead of just locking the account? So when it expires, the
user can still connect but not login, they can only set a new password,
idealy with some mechanism preventing the user from setting the same
password as before (compare the input with the current one, e.g. login
in the background with the pass and when it succeeds, ask the user to
give a different password or something like that).

have fun,
raphi

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? There are PAM modules for all
sorts of password checks.

Christoph

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

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

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

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

On 2025-06-25 14:42:26 +0200, raphi wrote:

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.

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.
|_|_) | |
| | | 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 ;))

On 2025-06-25 17:55:12 +0200, raphi wrote:

Am 25.06.2025 um 17:33 schrieb Peter J. Holzer:

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.

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

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

Am 26.06.2025 um 14:27 schrieb Peter J. Holzer:

On 2025-06-25 17:55:12 +0200, raphi wrote:

Am 25.06.2025 um 17:33 schrieb Peter J. Holzer:

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.

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.

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

On 2025-06-27 19:00:36 +0200, raphi wrote:

Am 26.06.2025 um 14:27 schrieb Peter J. Holzer:

On 2025-06-25 17:55:12 +0200, raphi wrote:

Am 25.06.2025 um 17:33 schrieb Peter J. Holzer:

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.

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.

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

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

On Sat, Jun 28, 2025 at 9:59 AM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

On 2025-06-27 19:00:36 +0200, raphi wrote:

Am 26.06.2025 um 14:27 schrieb Peter J. Holzer:

On 2025-06-25 17:55:12 +0200, raphi wrote:

Am 25.06.2025 um 17:33 schrieb Peter J. Holzer:

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.

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.

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

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

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

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

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

Am 28.06.2025 um 15:59 schrieb Peter J. Holzer:

On 2025-06-27 19:00:36 +0200, raphi wrote:

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

Because someone has to configure the password in the application, mostly
within WLS or Tomcat and that's definitely not something that we DBA
want to touch, that's the devs job. Which means we would have to provide
some mechanism for the application to grab the password, say from a file
or something, which has it's own pitfalls. Not to mention that we DBA
usually don't want to know any application passwords. The only feasable
way to implement this is with hashicorp Vault or something similar, then
no one knows the password, neither DBA nor Dev and it would be
guaranteed that it's complex. And application maintenance by a dev
directly in the DB could then be made with personal logins via LDAP and
switching to the application role as you so splendidly described ;) Same
would be true for SSL certificates, only the application would need it
and the devs could login via LDAP.

have fun
raphi

On 2025-06-28 18:06:51 +0200, raphi wrote:

Am 28.06.2025 um 15:59 schrieb Peter J. Holzer:

On 2025-06-27 19:00:36 +0200, raphi wrote:

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

Because someone has to configure the password in the application, mostly
within WLS or Tomcat

Yeah, my aim would be to eliminate that "somebody" and automate it with
an ansible playbook (or whatever).

and that's definitely not something that we DBA want to touch, that's
the devs job. Which means we would have to provide some mechanism for
the application to grab the password, say from a file or something,
which has it's own pitfalls. Not to mention that we DBA usually don't
want to know any application passwords.

If it's automated the DBA wouldn't know the password. The playbook would
generate a random password, create the user in the database and create
an XML file for Tomcat[1]There are probably different methods, but wherever I see Java, I also see XML ;-) with the connection details (including the
password). It seems to me that this would be a relatively simple change
to your existing "database self service" mechanism.

The only feasable way to implement this is with hashicorp Vault or
something similar, then no one knows the password, neither DBA nor Dev
and it would be guaranteed that it's complex.

That's another possibility. Might be a bit more complex, but I've never
used it so I don't really know.

hjp

[1]: There are probably different methods, but wherever I see Java, I also see XML ;-)
also see XML ;-)

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