PostgreSQL-添加组角色后密码认证失败 [英] PostgreSQL - Password authentication fail after adding group roles
问题描述
我对PostreSQL很陌生。我花了一整天的时间试图使用户登录正常工作，而现在我非常沮丧！
I'm pretty new to PostreSQL. I've spent all morning trying to get user logins working properly, and I'm terribly frustrated by now!
所以，我有一个PostGIS数据库，版本9.2， OpenGeo软件套件的一部分。我可以用postgres用户访问数据库，但想成为具有数据库访问权限的组角色和用户，以便它可以在该数据库中创建表并更新/选择/删除等。
So, I have a PostGIS database, version 9.2, as part of the OpenGeo suite of software. I could access the database with the postgres user, but want to make a group role and user with access to a database so that it can create tables and update/select/delete etc. in that database.
我可以创建一个有效的用户，并且可以使用该用户登录。我可以创建一个组角色并为该角色分配特权。然后，我可以将用户添加到组角色，然后就不能再登录了！
I can create a user, that works, and I can login with that user. I can create a group role and assign privileges to the role. I can then add the user to the group role, and then can NO LONGER LOGIN!
它甚至得到了，所以当我将用户postgres添加到组时，该用户无法登录。我删除了该组，但仍然无法登录。
It even got so that when I added the user postgres to the group, that user can't login. I remove the group but the inability to login persists.
现在，我已经玩了很多pg_hba.conf。现在我可以以postgres身份登录，但前提是启用了信任功能，并且不能使用任何软件（例如PGAdminIII）登录。
Now, I have played with pg_hba.conf a lot. And I can now login as postgres, but only if 'trust' is enabled, and I can't login with any software, such as PGAdminIII.
那太好了以获得有关出问题的建议，并再次启用经过身份验证的登录。
It would be great to get some advice as to what is going wrong, and to enable authenticated logins again.
pg_hba：
Code:
# Database administrative login by Unix domain socket
local all postgres trust
local all gisadmin trust
# TYPE DATABASE USER ADDRESS METHOD
local all opengeo md5
local all opengeo md5
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all gisadmin localhost trust
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 md5
#host replication postgres ::1/128 md5
最后，我希望能够通过SSH隧道登录。我本来可以对'postgres'用户执行此操作，但是现在我无法将该用户添加到组中，就不能了。
Just as a final note, I would like to be able to login via a SSH tunnel. I could do that originally with the 'postgres' user, but now that I added and removed that user from a group, I can't.
编辑：错误消息...
the error messages...
如果我使用正确的密码通过PGAdmin中的SSH隧道登录，则会收到错误消息致命：用户的密码验证失败...
If I login via SSH tunnel in PGAdmin with the correct password, I get the error "FATAL: password authentication failed for the user ..."
如果我尝试在不设置密码的情况下通过PGAdmin中的SSH隧道登录而未设置密码，则会收到错误消息：连接服务器时出错：fe_sendauth：无密码
If I try to login via SSH tunnel in PGAdmin without a password, while the trust option is set, I get the error: "error connecting to the server: fe_sendauth: no password supplied".
第一个错误仍然是通过SSH和psql在本地登录时发生的，但是第二个错误消失了，我可以登录了。
The first error still happens when logging in locally, just via SSH and psql, but the second one goes away and I can log in.
输出：
\dg +
的输出
output of \dg+
推荐答案
我认为此行为可能与pgAdmi有关nIII，因为我遇到类似的问题，并且似乎每次我使用pgAIII以postgres身份连接到我的数据库并查看用户属性中的定义选项卡时，都会选中到期日期复选框，并且1/1 / 1970年或31/12/1969被设置为截止日期。 Daniel提出的解决方案有效，因此很明显的问题是密码已过期。
I think this behaviour may be related with pgAdminIII, because i'm having similar issues and it seems every time i connect to my db as postgres using pgAIII and look at the definition tab in user properties, the check box for the "expiring date" is checked and either 1/1/1970 or 31/12/1969 are set as expiring date. The solution proposed by Daniel works, so it's obvious the problem is the expiration of the password.
似乎可以在pgAdmin 1.16.2中更正此错误。更改日志：
Seems that this bug was corrected in pgAdmin 1.16.2 as you can see in the changelog:
http://www.pgadmin.org/ development / changelog.php
欢呼声
这篇关于PostgreSQL-添加组角色后密码认证失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
