postgresql 超级用户没有密码提示

No password prompt for postgresql superuser

提问by Basil

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:

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

#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 testuserI get asked for a password, but when I log in with the "postgres" account like so psql -U postgresI get no password prompt and am logged in. If I force the password prompt with psql -U postgres -WI can log in by typing the correct password or by typing nothing at all. Typing a wrong password gets rejected.

进行这些更改后，我重新启动了 postgresql。当我这样做时，psql -U testuser我被要求输入密码，但是当我像这样使用“postgres”帐户登录时，psql -U postgres我没有收到密码提示并已登录。如果我强制输入密码提示，psql -U postgres -W我可以通过输入正确的密码登录或者根本不输入任何内容。输入错误的密码会被拒绝。

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?

相关说明：我看到很多示例，其中人们使用 ident 作为“postgres”用户的身份验证方法，认为要成为“postgres”用户，需要机器的 root 密码。我假设的理由是，如果攻击者获得 root 访问权限，无论如何你都完成了。不过，我更愿意使用密码登录，该密码与 root 密码不同。我更喜欢为不同的事情使用不同的密码。这合理吗？

Output of grep '^[^#]' pg_hba.conf

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

local   all             postgres                                md5
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5

采纳答案by Daniel Vérité

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

您pg_hba.conf确实应该需要 unix 套接字连接的密码，但仍有一些方法可以验证：

1. a .pgpassfile in the postgres home directory containing the password (also check the PGPASSFILEenvironment variable for a non-standard path).

2. the PGPASSWORDenvironment variable could be set.

1. 一个.pgpass含有密码的Postgres的主目录文件（也检查PGPASSFILE为非标准PATH环境变量）。

2. 在PGPASSWORD环境变量可以设置。

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_fileSQL command.

也有可能您正在编辑错误的 pg_hba.conf 文件。当以postgres 连接时，可以获取正确的路径以使用SHOW hba_fileSQL 命令进行验证。

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

此外，您可能需要检查日志文件，/var/log/postgresql/postgresql-9.1-main.log以确认在您要求时重新加载了配置文件，并在身份验证期间查找任何可疑消息。

As for the reason why passwordless connections with the postgres user are common, the debian PG-9.1 pg_hba.confhas this comment about disallowing them:

至于为什么与 postgres 用户无密码连接很常见的原因，debian PG-9.1pg_hba.conf有关于禁止它们的评论：

# 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.

由于 Debian 和 Ubuntu 使用相同的 postgres 包，这也适用于 Ubuntu。

回答by Craig Ringer

Re your odd behaviour, I think you've missed a line of pg_hba.confthat's specific to the postgresuser. Please show the output of:

关于您的奇怪行为，我认为您错过了pg_hba.conf特定于postgres用户的一行。请显示以下输出：

grep '^[^#]' pg_hba.conf 

As for ident vs md5; personally I prefer ident for interactive use in development, and it's fine for normal users, but I don't think giving access to the postgresuser via sudois a great idea. Both sudo -u postgres psqland psql -U postgres -Wgrant access to the postgres superuser role and thus file system access as the database user. Neither require a root password, and sudocan easily be constrained via sudoersto limit the invoking user to just running psql. However, with sudo -u postgres psqlthe clientcode runs as postgrestoo, so it's a bigger attack surface, and there's always the chance of the user finding a way to bypass your sudoerlimits.

至于 ident 与 md5；我个人更喜欢 ident 在开发中进行交互式使用，这对普通用户来说没问题，但我认为postgres通过 via访问用户并不是sudo一个好主意。双方sudo -u postgres psql并psql -U postgres -W授予访问Postgres超级用户的角色和数据库用户这样的文件系统访问。两者都不需要 root 密码，并且sudo可以很容易地通过sudoers将调用用户限制为仅运行psql. 但是，sudo -u postgres psql由于客户端代码postgres也会运行，因此它的攻击面更大，并且用户总是有机会找到绕过您的sudoer限制的方法。

I use identin dev, md5in production.

我ident在开发md5中使用，在生产中使用。