skip to Main Content

I have been using Postgres 12 on localhost on Windows host with password authentication without any problem.

Then I reinstalled computer and installed Postgres 15.3, repeat the same steps, but it denies to connect me with password authentication and complaining about SSPI authentication.

I created user this way

create role foo superuser login;

ALTER USER foo WITH ENCRYPTED PASSWORD 'foo';

CREATE DATABASE foo ;

GRANT ALL PRIVILEGES ON DATABASE foo TO foo;

Then I tried to connect

c:Program FilesPostgreSQL15bin>set PGPASSWORD=foo
c:Program FilesPostgreSQL15bin>psql -h localhost -p 5432 -U foo -d foo
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  SSPI authentication failed for user "foo"

"c:Program FilesPostgreSQL15datapostgresql.conf":

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'
                    # comma-separated list of addresses;
                    # defaults to 'localhost'; use '*' for all
                    # (change requires restart)
port = 5432             # (change requires restart)


..............


# - Authentication -

#authentication_timeout = 1min      # 1s-600s
#password_encryption = scram-sha-256    # scram-sha-256 or md5
#db_user_namespace = off

# GSSAPI using Kerberos
#krb_server_keyfile = 'FILE:${sysconfdir}/krb5.keytab'
#krb_caseins_users = off

Why it is using SSPI authentication? What am I doing wrong?

2

Answers


  1. Chosen as BEST ANSWER

    The problem was that Postgres 15 has password authentication disabled in config. It turned out that Postgres was installed as part of other product with non standard configuration.

    You can enable it in "c:Program FilesPostgreSQL15datapg_hba.conf"

    by replacing the following lines

    host    all             all             127.0.0.1/32            sspi map=domain
    host    all             all             ::1/128                 sspi map=domain
    

    with

    host    all             all             127.0.0.1/32            password
    host    all             all             ::1/128                 password
    

  2. I am facing the same problem.
    well, I solve it out by a new technique . which I will share with you

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search