没有postgresql超级用户的密码提示 [英] No password prompt for postgresql superuser

问题描述

在Ubuntu 12.04上安装PostgreSQL 9.1之后，我为postgres超级用户帐户设置了密码。我希望所有用户在登录时都必须输入他们的密码。这就是为什么我配置pg_hba.conf如下：

 ＃数据库管理登录Unix域socket 
 local all postgres md5 
 
＃TYPE DATABASE用户地址方法
 
＃local仅用于Unix域套接字连接
 local all all md5 
  

我在做了这些更改后重新启动了postgresql。当我这样做 psql -U testuser 我得到一个密码，但是当我用postgres帐户，如登录psql -U postgres 我没有得到密码提示并且登录。
如果我强制使用 psql -U postgres -W 通过键入正确的密码或完全不输入任何内容登录。输入错误的密码会被拒绝。

任何人都可以向我解释为什么会发生这种情况？

相关注意：我看到很多例子，人们使用ident作为postgres用户的身份验证方法，认为成为postgres用户需要机器的root密码。我假设的推理是，如果攻击者获得root访问，你的反正。我更喜欢用一个密码登录，一个与root密码不一样。我prefere有不同的密码不同的东西。这是否合理？

输出 grep'^ [^＃]'pg_hba.conf

  local all postgres md5 
 local all md5 
主机全部127.0.0.1/32 md5 
   
 
解决方案
您的 pg_hba.conf 确实需要为unix套接字连接提供密码，但仍有一些方法可以验证：
 
 
 
 a  .pgpass 文件（包含密码）（也请检查          //www.postgresql.org/docs/9.1/static/libpq-envars.htmlrel =noreferrer> PGPASSWORD 环境变量。
 
 
 
还有可能是你编辑了错误的pg_hba.conf文件。 
当作为postgres连接时，可以使用 SHOW hba_file  SQL命令获取正确的路径进行验证。
 
 
 
此外，您可能需要检查日志文件 /var/log/postgresql/postgresql-9.1-main.log ，以确认配置文件重新加载时请求它，并在身份验证期间查找任何可疑消息。
 
 
 
由于与postgres用户的无密码连接是常见的原因，debian PG-9.1  pg_hba.conf 有这个关于禁止他们的评论：
 ＃DO NOT DISABLE！ 
＃如果更改第一个条目，您将需要确保
＃数据库超级用户可以使用其他方法访问数据库。 
＃在自动
＃维护（自定义每日cronjobs，复制和类似任务）期间，需要对所有数据库进行非交互式访问。 
＃
＃Unix域套接字的数据库管理登录
 local all postgres peer 
  
由于Debian和Ubuntu使用相同的postgres软件包，这同样适用于Ubuntu。
 
After I installed PostgreSQL 9.1 on Ubuntu 12.04 I set the password for the "postgres" superuser account. I want all users to have to enter their password when loging in. This is why I configured pg_hba.conf like so:
#Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
I restarted postgresql after making those changes. When I do this psql -U testuser I get asked for a password, but when I log in with the "postgres" account like so psql -U postgres I get no password prompt and am logged in.
If I force the password prompt with psql -U postgres -W I can log in by typing the correct password or by typing nothing at all. Typing a wrong password gets rejected.

Can anybody please explain to me why this is happening?

On a related note: I see a lot of example where people use ident as authentication method for the "postgres" user, arguing that to become the "postgres" user one needs the root password of the machine. I assume that the reasoning is that if an attacker gets root access, your done anyways. I would prefer to log in with a password though, one which is not the same as the root password. I prefere having different passwords for different things. Is this reasonable?

Output of grep '^[^#]' pg_hba.conf
local   all             postgres                                md5
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5

解决方案
Your pg_hba.conf should indeed require a password for unix socket connections, but there are still ways around it that you should verify:

a .pgpass file in the postgres home directory containing the password (also check the PGPASSFILE environment variable for a non-standard path).
the PGPASSWORD environment variable could be set.
And there's also the possibility that you're editing the wrong pg_hba.conf file.
When connected as postgres, the correct path can be obtained for verification with the SHOW hba_file SQL command.

Also, you may want to check the log file, /var/log/postgresql/postgresql-9.1-main.log for confirmation that the configuration files are reloaded when you ask for it, and look for any suspect message during the authentication.

As for the reason why passwordless connections with the postgres user are common, the debian PG-9.1 pg_hba.conf has this comment about disallowing them:
# DO NOT DISABLE!  
# If you change this first entry you will need to make sure that the  
# database superuser can access the database using some other method.  
# Noninteractive access to all databases is required during automatic  
# maintenance (custom daily cronjobs, replication, and similar tasks).  
#  
# Database administrative login by Unix domain socket  
local   all             postgres                                peer  
Since Debian and Ubuntu use the same postgres packages, this applies to Ubuntu as well.

                        
这篇关于没有postgresql超级用户的密码提示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！