ANXS / postgresql

Fairly full featured Ansible role for Postgresql.
http://anxs.io/
MIT License
849 stars 573 forks source link

Possible misconfiguration of postgresql_pg_hba_default? #473

Closed velis74 closed 6 months ago

velis74 commented 4 years ago

I'm attempting to get pgbouncer work nicely with pg using this role. The problem is that pgbouncer can't use peer authentication if I want it to keep users identifiable and secure. So I need md5 authentication even for local socket connections.

So I attempted to modify postgresql_default_auth_method to md5. The last line of postgresql_pg_hba_default says that postgres user should always use peer authentication anyway, right?

Naturally, I get a peer authentication error on the next task from the one setting pg_hba.... The role assumes peer auth for postgres user.

So - wrong: it's the LAST line that says that and pg matches the configuration until the first line with a match. And that is the first line which now says that local connections for everything should be using md5 authentication.

So, am I right that default postgresql_pg_hba_default variable is actually configured wrong? I mean, just by moving last line to first place immediately got it working again, as - as I understand it - it should...

I think this may have some relation to PR #344, but also that this little detail snuck under the radar there.

velis74 commented 4 years ago

I believe #459 is suffering from the same problem, just going about it in a different way

gclough commented 3 years ago

@velis74 , I'm not sure if I understand completely... but can you just use the postgresql_pg_hba_default parameter to include what you want?

  postgresql_pg_hba_default:
    - comment: "Local postgresql Unix user"
      type: local
      database: all
      user: postgres
      address: ""
      method: "md5"
velis74 commented 3 years ago

Yes, that's exactly what I did. The issue I opened to tell you that your default config produces configurations that won't get used by postgres because the ordering is incorrect. My final setting:

postgresql_pg_hba_default:
  - { type: local, database: all, user: "{{ postgresql_admin_user }}", address: "", method: "peer map=root_as_{{ postgresql_admin_user }}", comment: "Local root Unix user, passwordless access" }
  - { type: local, database: all, user: all, address: "",             method: "{{ postgresql_default_auth_method }}", comment: '"local" is for Unix domain socket connections only' }
  - { type: host,  database: all, user: all, address: "127.0.0.1/32", method: "{{ postgresql_default_auth_method_hosts }}", comment: "IPv4 local connections:" }
  - { type: host,  database: all, user: all, address: "::1/128",      method: "{{ postgresql_default_auth_method_hosts }}", comment: "IPv6 local connections:" }
gclough commented 3 years ago

Thanks @velis74 , I'll investigate.

github-actions[bot] commented 8 months ago

This issue has been marked 'stale' due to lack of recent activity. If there is no further activity, the issue will be closed in another 30 days. Thank you for your contribution!

github-actions[bot] commented 6 months ago

This issue has been closed due to inactivity. If you feel this is in error, please reopen the issue or file a new issue with the relevant details.