pgadmin:获得“详细信息:用户没有CONNECT特权."错误 [英] pgadmin: getting "DETAIL: User does not have CONNECT privilege." error

问题描述

我在Mac Yosemite上使用pgAdmin III.我创建了一个角色折扣"，并创建了一个数据库折扣".在pgadmin工具中，如何给用户折扣"连接特权(和表读/写特权)到数据库折扣"?当前，当我尝试在命令行登录时出现此错误

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

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.

推荐答案

对当前对象的权限

要对表具有读/写访问权限，您需要使用 GRANT命令分为3个级别:

Permission to current objects

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

1. 数据库
2. SCHEMA
3. 表，功能，序列等

首先，您需要对数据库具有CONNECT特权:

First, you need CONNECT privilege on the database:

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

第二，您需要对数据库内部的架构具有USAGE特权(在运行数据库之前必须连接到数据库):

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

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

最后，您可以在表上赋予特权，假设您想要在架构中的所有表上使用通用DML和SELECT，以及其他许可权:

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:

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>;

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

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

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 SUPERUSER too, but I recommend keeping it only for DBA tasks).

上面的GRANT ... ON ALL ... IN SCHEMA命令将授予数据库中已经存在的对象的权限，但不适用于创建的新对象.为此，您可以使用 ALTER DEFAULT PRIVILEGES(我称ADP)命令.与以前一样，您应该在以所有者身份连接时运行该命令，因为您必须记住，只有当新对象的所有者与此处使用的对象(或在

The above GRANT ... ON ALL ... IN SCHEMA commands 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 ROLE clause):

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>;

您还可以从上方跳过IN SCHEMA <schemaname>并将其应用于您拥有的或将来创建的任何模式.但是，再次提醒您，即使您不提供FOR ROLE <rolename>，这也要小心 ，这意味着它将适用于当前连接的用户，因此，仅创建的对象属于该<rolename>将考虑ADP命令.

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.

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

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.

这篇关于pgadmin:获得“详细信息:用户没有CONNECT特权."错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！