Thread

1. password rules

   raphi <raphi@crashdump.ch> — 2025-06-23T14:35:35Z

   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
   
   
   
   
   

2. Re: password rules

   Tom Lane <tgl@sss.pgh.pa.us> — 2025-06-23T15:05:11Z

   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
   
   
   
   

3. Re: password rules

   raphi <raphi@crashdump.ch> — 2025-06-23T18:44:58Z

   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
   
   
   
   
   

4. Re: password rules

   Christoph Berg <myon@debian.org> — 2025-06-23T20:39:29Z

   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
   
   
   
   

5. Re: password rules

   raphi <raphi@crashdump.ch> — 2025-06-24T05:18:21Z

   
   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
   
   
   
   

6. Re: password rules

   Gilles Darold <gilles@darold.net> — 2025-06-24T12:28:41Z

   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
   
   
   
   
   

7. Re: password rules

   Greg Sabino Mullane <htamfids@gmail.com> — 2025-06-24T23:20:21Z

   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
   

8. Re: password rules

   raphi <raphi@crashdump.ch> — 2025-06-25T06:14:48Z

   
   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
   
   
   
   

9. Re: password rules

   Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-06-25T11:55:35Z

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

10. Re: password rules

    raphi <raphi@crashdump.ch> — 2025-06-25T12:42:26Z

    
    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
    
    
    
    

11. Re: password rules

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-06-25T15:33:05Z

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

12. Re: password rules

    raphi <raphi@crashdump.ch> — 2025-06-25T15:55:12Z

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

13. Re: password rules

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-06-26T12:27:41Z

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

14. Re: password rules

    raphi <raphi@crashdump.ch> — 2025-06-27T17:00:36Z

    
    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
    
    
    
    

15. Re: password rules

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-06-28T13:59:23Z

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

16. Re: password rules

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-06-28T15:14:04Z

    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!
    

17. Re: password rules

    raphi <raphi@crashdump.ch> — 2025-06-28T16:06:51Z

    
    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
    
    
    
    
    
    

18. Re: password rules

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2025-06-29T13:32:10Z

    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] 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 ;-)
    
    -- 
       _  | Peter J. Holzer    | Story must make more sense than reality.
    |_|_) |                    |
    | |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
    __/   | http://www.hjp.at/ |       challenge!"