当前位置：文江博客话题详情

PostgreSQL：查看数据库连接权限

发布于 2024-11-02 07:04:19 字数 178 浏览 0 评论 0原文

如何查看已颁发的用户GRANT CONNECT ON DATABASE致<用户>？

\dp - 列出表/视图权限
\dn+ - 列出架构权限
\l+ 不列出可以访问数据库的所有用户

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

×纯※雪 2024-11-09 07:04:19

如果 \l+ 命令只显示一些具有连接到数据库的权限/特权的用户，那就有点奇怪了。我自己无法在 PostgreSQL 8.4 安装（Ubuntu 10.04 LTS）上重复这一点。您使用什么版本？

无论如何，也许您可以检查保存该特定数据库的 ACL 的表，并从中推断出用户是否具有正确的权限：

SELECT datname as "Relation", datacl as "Access permissions" FROM pg_database WHERE datname = 'databasename';

如果您只想检查一个用户，您可以执行以下操作：

SELECT * FROM has_database_privilege('username', 'database', 'connect');

权限/特权如何来解释？权限应这样理解：

user = privileges / granted by

省略用户意味着 PUBLIC 被授予权限，即所有角色。例如，如果权限是=Tc/postgres，那么所有角色都可以连接并在该特定数据库中创建临时表，并且授予该权限的是postgres用户。

PostgreSQL 站点上有一个概要解释了不同的权限： https://www.postgresql.org/docs/current/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE。

rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

        /yyyy -- role that granted this privilege

A bit odd if the \l+ command just displays some of the users that have permission/privilege to connect to the database. I could not repeat that myself on a PostgreSQL 8.4 installation (Ubuntu 10.04 LTS). What version are you using?

Anyway, perhaps you could check the table holding the ACL's for that particular database and from that deduce whether the user has the correct privileges or not:

SELECT datname as "Relation", datacl as "Access permissions" FROM pg_database WHERE datname = 'databasename';

If you just want to check one user you could do something like this:

SELECT * FROM has_database_privilege('username', 'database', 'connect');

How are the permissions/privileges to interpreted? The privileges are to be read like this:

user = privileges / granted by

Omitting user means that PUBLIC is granted the privilege, ie all roles. For example if the privilege is =Tc/postgres then all roles may connect and create temporary tables in that particular database and it is the postgres user who granted the privilege.

There is a synopsis at the PostgreSQL site explaining the different privileges: https://www.postgresql.org/docs/current/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE.

rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

        /yyyy -- role that granted this privilege

回复收藏 0 原文