Answer a question

I'm using postgresql and as part of learning, I tried to change to login methods to have a more secure login methods. e.g. using scram-sha-256 instead of md5. I tried to change my password_encryption to scram-sha256 in postgresql.conf file, and changed pg_hba.conf METHOD to scram-sha-256 as well, you can see the changes in the configuration below:

# - Authentication -

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

and

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

then after restarting the server I try to login using cmd when I get following error:

C:\Users\amir>psql -U postgres postgres
Password for user postgres:
psql: error: could not connect to server: FATAL:  password authentication failed for user "postgres"

I can solve the problem by changing everything to md5 method and ignoring password_encryption in postgresql.conf file. How can I resolve this issue? do I have to change the configuration to default then try to create user and assigning an encrypted password for them.

Answers

Each user password hash is saved in the table pg_authid. It includes the hashing algorithm that is used to transform the password to its hash.

When setting the password_encryption in postgresql.conf, you are setting the default encryption, i.e. the one used when creating a user or when (re)setting your password. The table pg_authid is not updated.

When changing pg_hba.conf, you are saying to accept only passwords hashed using the given method. The table pg_authid is not updated.

There is an important note in the doc:

To ease transition from the md5 method to the newer SCRAM method, if md5 is specified as a method in pg_hba.conf but the user's password on the server is encrypted for SCRAM (see below), then SCRAM-based authentication will automatically be chosen instead

So the solution is to

  1. start with the existing users, md5
  2. update postrgres.conf to use scram and reload the configuration
  3. reset the user password: it will now be saved as scram in pg_authid
  4. you can still use md5 in pg_hba.conf
  5. when happy with the move from md5 to scram, update pg_hba.conf to specify scram instead of md5
# postgresql
Logo
PostgreSQL

PostgreSQL社区为您提供最前沿的新闻资讯和知识内容

更多推荐

PostgreSQL 计数查询效率,物化视图 [重复]

问题:PostgreSQL 计数查询效率,物化视图 [重复] 可能重复: PostgreSQL 计数查询优化 使用 PostgreSQL 9.2,我们试图弄清楚是否有一种方法可以跟踪查询的结果数量,并以有效的方式返回该数字。这个查询应该每秒执行几次(可能几十到几百甚至几千次)。我们现在的查询看起来像这样,但我们想知道这是否效率低下: -- Get # of rows that do not hav

avatar PostgreSQL

多对多中的唯一性

问题:多对多中的唯一性 我无法弄清楚谷歌的哪些术语,所以帮助标记这个问题或只是以相关问题的方式向我指出会有所帮助。 我相信我有一个典型的多对多关系: CREATE TABLE groups ( id integer PRIMARY KEY); CREATE TABLE elements ( id integer PRIMARY KEY); CREATE TABLE groups_elements

avatar PostgreSQL

Django 与 postgresql - manage.py syncdb 返回错误

问题:Django 与 postgresql - manage.py syncdb 返回错误 我从 Django 开始。我设置了一些使用 SQLite 工作的站点,但是在将 DB 引擎更改为 postgresql manage.py syncdb 后返回错误。我已经用谷歌搜索了 2 天,但对我仍然没有任何作用。Postgres 用户 'joe' 具有超级用户权限和本地 'joe ' 数据库存在。

avatar PostgreSQL