0

New to PostgreSQL. Installed Postgresql 16 on Windows server. On pg_ident.conf, I added

sspi userxyz@domain userxyz

On pg_hba.conf, I added

host   all   userxyz   0.0.0.0/0   sspi   map=sspi

In PGAdmin, I added username userxyz with no password.

But, I'm getting connection failed: :1), port 5432 failed: fe_sendauth: no password supplied error.

Just trying to authenticate userxyz to PostgreSQL on Windows. What am I missing? Thank you

4
  • There must be some other line in your pg_hba which is matching for your connection attempt. Remember, the first matching line is the one that gets used. If you check the db server log file, it will clearly show you which line was the one being used in the attempt, so you don't need to guess about which one.
    – jjanes
    Dec 14, 2023 at 18:54
  • Thank you for viewing and guidance. Yes, there is another line - the default. # IPv4 local connections: host all all 0.0.0.0/0 scram-sha-256 host all all 127.0.0.1/32 scram-sha-256
    – Student
    Dec 14, 2023 at 19:29
  • From the db server log file, it looks like it's using ipv6 - # IPv6 local connections: host all all ::1/128 scram-sha-256. But, if I comment that line out, I'm getting error "connection failed: :1), port 5432 failed: FATAL: no pg_hba.conf entry for host "::1", user "userxyz", database "postgres", no encryption"
    – Student
    Dec 14, 2023 at 19:38
  • So, I was able to make it work if I comment everything out and just have "host all userxyz ::1/128 sspi map=sspi". Does this mean if I have multiple AD accounts to add, I'll have to add multiple lines on the pg_hba? Will it work with AD groups?
    – Student
    Dec 14, 2023 at 20:03

1 Answer 1

0

The "default" lines (which are not PostgreSQL project-wide defaults, as packagers install their own values here) would not mess things up if they occurred after, not before, the SSPI line.

If you want the rule to apply to both ipv4 and ipv6, you need two lines or to use the magic word "samehost" to cover both. But it is also kind of weird to use SSPI only for loopback connections. You could instead use the magic word "all" which does apply to both ipv4 and ipv6 coming from anywhere if that is what you want.

You can use 'all' for the username if you want the line to apply all accounts. Normally you would make a 'local' line that uses peer or something (possibly just for 'postgres' user), and a 'host' line which uses SSPI for all users. That way your administrator can still bypass SSPI by connecting over the socket rather than TCP.

If you want some users to use SSPI and some to use SCRAM based on the username, you would need to somehow specify which users are in which group. I don't think there is a way to outsource this specification to AD groups. You can specify by separate pg_hba lines for each user, or by including all the names separated by commas in one line, or by using an @includedfile, or by referencing a +role within the database. Note that this choice is made by which db user they asked to connect as, not by which username SSPI validates them as being. Any users not caught by the condition on one line would fall through to the next pg_hba line, so you would want the exceptions line to be earlier and the all-the-rest line later.

The "all" is for pg_hba. For pg_ident, you would want to use a regexp (untested example from the docs):

mymap   /^(.*)@mydomain\.com$      \1

Unless of course you wanted more fine control over who (on the OS) can log in as who (in the database). And each of the roles needs to exist in the database before anyone can log in as that role.

2
  • Thank you for the information. That was very helpful.
    – Student
    Dec 15, 2023 at 14:21
  • Implementing your suggestions - and a question on your comment "You can use 'all' for the username if you want the line to apply all accounts. " - I'm guessing this change is in pg_hba. Whereas in pg_ident, I need to list all the accounts? Or do I change that to SYSTEM-USERNAME and PG-USERNAME to all as well? There's no all pg-username to tie that too though, right? So, will i associate that with postgres username? Thank you
    – Student
    Dec 15, 2023 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.