postgresql pgadmin：获取“详细信息：用户没有连接权限。” 错误

pgadmin: getting "DETAIL: User does not have CONNECT privilege." error

提问by Dave

I'm using pgAdmin III on Mac Yosemite. I created a role, “discount”, and created a database “discount”. In the pgadmin tool, how do I give the user “discount” connect privileges (and table read/write privileges) to the database “discount”? Currently, when I try and login at a command line I get this error

我在 Mac Yosemite 上使用 pgAdmin III。我创建了一个角色“折扣”，并创建了一个数据库“折扣”。在 pgadmin 工具中，如何给用户“discount”连接权限（和表读/写权限）到数据库“discount”？目前，当我尝试在命令行登录时，出现此错误

davea$ psql -h localhost -d discount -U discount
Password for user discount: 
psql: FATAL:  permission denied for database "discount"
DETAIL:  User does not have CONNECT privilege.

回答by MatheusOl

Permission to current objects

对当前对象的权限

To have read/write access to the tables, you need to use GRANTcommandin 3 levels:

要对表进行读/写访问，您需要在 3 个级别使用GRANT命令：

1. DATABASE
2. SCHEMA
3. TABLES, FUNCTIONS, SEQUENCES, etc.

1. 数据库
2. 架构
3. 表、函数、序列等

First, you need CONNECTprivilege on the database:

首先，您需要CONNECT对数据库的权限：

GRANT CONNECT ON DATABASE <dbname> TO <username>;

Second, you need USAGEprivilege on the schema inside the database (you must connect to the database before running it):

其次，您需要USAGE对数据库内部架构的特权（您必须在运行之前连接到数据库）：

GRANT USAGE ON SCHEMA <schemaname> TO <username>;

At last, you can give the privilege on the tables, suppose you want common DML and SELECT on all tables in the schema, and other permissions:

最后，您可以授予表的权限，假设您希望对架构中的所有表进行通用 DML 和 SELECT，以及其他权限：

GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA <schemaname> TO <username>;
-- and the sequences, for INSERT to work
GRANT USAGE ON ALL SEQUENCES IN SCHEMA <schemaname> TO <username>;
-- and the functions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA <schemaname> TO <username>;

Default permission for objects created in the future

未来创建的对象的默认权限

You must now notice something. Each database, and each schema, and each object (table, function, etc.) have an owner. The owner of is the user that will manage and run DDL commands on it. Generally you should run all the above commands while connected as the user which owns everything, because this user already has all permissions (you could use a SUPERUSERtoo, but I recommend keeping it only for DBA tasks).

你现在必须注意到一些事情。每个数据库、每个模式和每个对象（表、函数等）都有一个所有者。的所有者是将在其上管理和运行 DDL 命令的用户。通常，您应该在以拥有所有内容的用户身份连接时运行上述所有命令，因为该用户已经拥有所有权限（您也可以使用 a SUPERUSER，但我建议仅将其保留用于 DBA 任务）。

The above GRANT ... ON ALL ... IN SCHEMAcommands will give permissions to the objects already present in the database, but won't apply to new objects created. In order to do that, you can use ALTER DEFAULT PRIVILEGES(I'll call it ADP) command. As before, you should run that while connected as the owner, because you must keep in mind that ADP is applied only if the owner of the new object matches with the one used here (or set in FOR ROLEclause):

上述GRANT ... ON ALL ... IN SCHEMA命令将为数据库中已经存在的对象授予权限，但不适用于创建的新对象。为此，您可以使用ALTER DEFAULT PRIVILEGES（我称之为ADP） command。和以前一样，您应该在以所有者身份连接时运行它，因为您必须记住，只有当新对象的所有者与此处使用的所有者（或在FOR ROLE子句中设置）匹配时，才会应用 ADP ：

ALTER DEFAULT PRIVILEGES IN SCHEMA <schemaname>
    GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO <username>;
-- and the sequences, for INSERT to work
ALTER DEFAULT PRIVILEGES IN SCHEMA <schemaname>
    GRANT USAGE ON SEQUENCES TO <username>;
-- and the functions
ALTER DEFAULT PRIVILEGES IN SCHEMA <schemaname>
    GRANT EXECUTE ON FUNCTIONS TO <username>;

You can also skip IN SCHEMA <schemaname>from above and have it applied for any schema you have or create in the future. But again, be careful, even if you do not provide FOR ROLE <rolename>that means it will apply to the current user connected, so only objects created that is owned by that <rolename>will consider the ADP command.

您也可以IN SCHEMA <schemaname>从上面跳过并将其应用于您将来拥有或创建的任何架构。但同样，要小心，即使您不提供FOR ROLE <rolename>这意味着它将应用于当前连接的用户，因此只有创建的对象<rolename>才会考虑 ADP 命令。

For a good management of permissions, I highly recommend you keep an eye on which user owns the objects. In most cases, I also recommend you keep only one owner for everything inside the database (unless you are an advanced user and know what you are doing), that way permission management is easier.

为了更好地管理权限，我强烈建议您密切关注哪个用户拥有这些对象。在大多数情况下，我还建议您为数据库中的所有内容只保留一个所有者（除非您是高级用户并且知道自己在做什么），这样权限管理会更容易。

回答by Syed Kamran Ahmed

Make sure to verify the database name, to which you are trying to connect. I also faced the same issue initially, only to figure out later that I was connecting to a different database.

确保验证您尝试连接的数据库名称。我最初也遇到了同样的问题，后来才发现我正在连接到不同的数据库。

The one having the yellow-colored symbol will be your database.

具有黄色符号的那个将是您的数据库。