postgresql权限管理和查询

查询一个表的权限：使用psql:dp命令

test-# \z
                              Access privileges
 Schema | Name | Type  |     Access privileges     | Column access privileges
--------+------+-------+---------------------------+--------------------------
 public | t1   | table | postgres=arwdDxt/postgres+|
        |      |       | test=arwdDxt/postgres     |
(1 row)

test-# \dp
                              Access privileges
 Schema | Name | Type  |     Access privileges     | Column access privileges
--------+------+-------+---------------------------+--------------------------
 public | t1   | table | postgres=arwdDxt/postgres+|
        |      |       | test=arwdDxt/postgres     |
(1 row)

test-#

pg数据库有的相关权限为：

SELECT
INSERT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
CREATE
CONNECT
TEMPORARY
EXECUTE
USAGE
###
ALL PRIVILEGES

\z权限解读：

testdb=> \z
                               Access privileges
 Schema | Name  | Type  |     Access privileges     | Column access privileges
--------+-------+-------+---------------------------+--------------------------
 public | tab01 | table | postgres=arwdDxt/postgres+|
        |       |       | test001=r*w/postgres     +|
        |       |       | =r/postgres              +|
        |       |       | test002=w/postgres       +|
        |       |       | test002=r/test001         |

postgres 用户拥有所有权限；

test001用户拥有rw权限，r权限可以授权给其他用户/postgres 用户赋权的；

public角色r权限，/postgres 用户赋权的；

test002角色w权限，/postgres 用户赋权的；

test002角色r权限，/test001 用户赋权的；

# \dp 命令解析 

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

测试脚本：

-- 创建测试数据库
create database testdb;
\c testdb;
-- 创建测试用户test001,test002
create user test001 with replication createrole createdb login password 'test123';
create user test002 with replication createrole createdb login password 'test123';
-- 创建测试表
create table tab01(id int);
\z

-- 给test01 授权
grant select on tab01 to test001 WITH GRANT OPTION;
grant update on tab01 to test001;
-- 给publice 授权
grant select on tab01 to public ;
-- 给test02授权
grant update on tab01 to test002;
-- test01给test02授权
\c testdb test001
grant select on tab01 to test002;
-- 检查表权限
\z