pg 查询对象的权限

参考：http://postgres.cn/docs/14/ddl-priv.html

## ACL权限缩写

| 权限         | 缩写         | 适用对象类型                                                 |
| ------------ | ------------ | ------------------------------------------------------------ |
| `SELECT`     | `r`  | `LARGE OBJECT`, `SEQUENCE`, `TABLE` (and table-like objects), table column |
| `INSERT`     | `a` | `TABLE`, table column                                        |
| `UPDATE`     | `w`   | `LARGE OBJECT`, `SEQUENCE`, `TABLE`, table column            |
| `DELETE`     | `d`          | `TABLE`                                                      |
| `TRUNCATE`   | `D`          | `TABLE`                                                      |
| `REFERENCES` | `x`          | `TABLE`, table column                                        |
| `TRIGGER`    | `t`          | `TABLE`                                                      |
| `CREATE`     | `C`          | `DATABASE`, `SCHEMA`, `TABLESPACE`                           |
| `CONNECT`    | `c`          | `DATABASE`                                                   |
| `TEMPORARY`  | `T`          | `DATABASE`                                                   |
| `EXECUTE`    | `X`          | `FUNCTION`, `PROCEDURE`                                      |
| `USAGE`      | `U`          | `DOMAIN`, `FOREIGN DATA WRAPPER`, `FOREIGN SERVER`, `LANGUAGE`, `SCHEMA`, `SEQUENCE`, `TYPE` |

## 访问权限摘要

| 对象类型                         | 所有权限  | 默认 `PUBLIC` 权限 | psql 命令 |
| -------------------------------- | --------- | ------------------ | --------- |
| `DATABASE`                       | `CTc`     | `Tc`               | `\l`      |
| `DOMAIN`                         | `U`       | `U`                | `\dD+`    |
| `FUNCTION` or `PROCEDURE`        | `X`       | `X`                | `\df+`    |
| `FOREIGN DATA WRAPPER`           | `U`       | none               | `\dew+`   |
| `FOREIGN SERVER`                 | `U`       | none               | `\des+`   |
| `LANGUAGE`                       | `U`       | `U`                | `\dL+`    |
| `LARGE OBJECT`                   | `rw`      | none               |           |
| `SCHEMA`                         | `UC`      | none               | `\dn+`    |
| `SEQUENCE`                       | `rwU`     | none               | `\dp`     |
| `TABLE` (and table-like objects) | `arwdDxt` | none               | `\dp`     |
| Table column                     | `arwx`    | none               | `\dp`     |
| `TABLESPACE`                     | `C`       | none               | `\db+`    |
| `TYPE`                           | `U`       | `U`                | `\dT+`    |

## aclitem 函数

| 名称                                                  | 返回类型       | 描述                                      |
| ----------------------------------------------------- | -------------- | ----------------------------------------- |
| `acldefault(type, ownerId)`                           | `aclitem[]`    | 获取属于*`ownerId`*的对象的默认访问权限。 |
| `aclexplode(aclitem[])`                               | `setof record` | 获取 `aclitem` 数组为元组                 |
| `makeaclitem(grantee, grantor, privilege, grantable)` | `aclitem`      | 从输入中建立一个`aclitem`。               |

> `acldefault`返回属于角色*`ownerId`*的*`type`*类型的对象的内置默认访问权限。 这些代表了当对象的ACL条目为空时将被假定的访问权限。
 *`type`* 参数是一个 `CHAR`:
'c' for `COLUMN`
'r' for `TABLE` 和类表对象
's' for `SEQUENCE`
'd' for `DATABASE`
'f' for `FUNCTION` 或者 `PROCEDURE`
'l' for `LANGUAGE`
'L' for `LARGE OBJECT`
'n' for `SCHEMA`
't' for `TABLESPACE`
'F' for `FOREIGN DATA WRAPPER`
'S' for `FOREIGN SERVER`
'T' for `TYPE` 或者 `DOMAIN`

> `aclexplode`返回一个`aclitem`数组作为行集。输出的列是grantor `oid`, grantee `oid` (`0` for `PUBLIC`), privilege_type `text` (`SELECT`, ...) 以及权限是否可以被授予`is_grantable`(`boolean`)。
`makeaclitem`执行反向操作，返回`aclitem`。

## 查询rel对象(表、视图、物化视图、序列、外部表、分区表)的权限

```sql
SELECT n.nspname as "Schema",
    c.relname as "Name",
    CASE c.relkind WHEN 'r' THEN 'table'
        WHEN 'v' THEN 'view'
        WHEN 'm' THEN 'materialized view'
        WHEN 'S' THEN 'sequence'
        WHEN 'f' THEN 'foreign table'
        WHEN 'p' THEN 'partitioned table' END as "Type",
    pg_catalog.array_to_string(coalesce(c.relacl,acldefault('r',c.relowner)), E'\n') AS "Access privileges",
    pg_catalog.array_to_string(ARRAY(
        SELECT attname || E':\n  ' || pg_catalog.array_to_string(attacl, E'\n  ')
        FROM pg_catalog.pg_attribute a
        WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
    ), E'\n') AS "Column privileges",
    pg_catalog.array_to_string(ARRAY(
        SELECT polname
        || CASE WHEN NOT polpermissive THEN
           E' (RESTRICTIVE)'
           ELSE '' END
        || CASE WHEN polcmd != '*' THEN
               E' (' || polcmd || E'):'
           ELSE E':'
           END
        || CASE WHEN polqual IS NOT NULL THEN
               E'\n  (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)
           ELSE E''
           END
        || CASE WHEN polwithcheck IS NOT NULL THEN
               E'\n  (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)
           ELSE E''
           END    || CASE WHEN polroles <> '{0}' THEN
               E'\n  to: ' || pg_catalog.array_to_string(
                   ARRAY(
                       SELECT rolname
                       FROM pg_catalog.pg_roles
                       WHERE oid = ANY (polroles)
                       ORDER BY 1
                   ), E', ')
           ELSE E''
        END
    FROM pg_catalog.pg_policy pol
    WHERE polrelid = c.oid), E'\n') AS "Policies"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','p')
AND n.nspname = 'public'
AND c.relname = 'test'
ORDER BY 1, 2;
```

## 查询数据库哪些用户具有什么权限(create, connect, temp)

```sql
select usename,
    datname,
    pri,
    has_database_privilege (usename, datname, pri) as granted
from pg_user,
    pg_database,
    (values ('CREATE'),('CONNECT'),('TEMP')) t (pri)
order by 2,1,3;

--以数据库维度聚合
select
    datname,
    array_agg(usename) filter (where pri='CONNECT' and has_database_privilege (usename, datname, pri)=true) as connect_granted ,
    array_agg(usename) filter (where pri='CREATE' and has_database_privilege (usename, datname, pri)=true) as create_granted ,
    array_agg(usename) filter (where pri='TEMP' and has_database_privilege (usename, datname, pri)=true) as temp_granted
from pg_user,
    pg_database,
    (values ('CREATE'),('CONNECT'),('TEMP')) t (pri) 
group by datname;
```

## 查询用户是哪些数据库的OWNER

```sql
select datdba::regrole,
    array_agg(datname) datname
from pg_database
group by 1
```

## 指定用户对哪些数据库有权限

```sql
select
    usename,
    array_agg(datname) filter (where pri='CONNECT' and has_database_privilege (usename, datname, pri)=true) as connect_granted ,
    array_agg(datname) filter (where pri='CREATE' and has_database_privilege (usename, datname, pri)=true) as create_granted ,
    array_agg(datname) filter (where pri='TEMP' and has_database_privilege (usename, datname, pri)=true) as temp_granted
from pg_user,
    pg_database,
    (values ('CREATE'),('CONNECT'),('TEMP')) t (pri)
group by usename;
```

## 查询OWNER的指定对象类型的默认权限

```sql
--数据库默认权限
select * from acldefault('d','postgres'::regrole);
--relation默认权限
select * from acldefault('r','postgres'::regrole);
```

## 使用aclexplode解读acl含义

```sql
select (aclexplode(acldefault)).grantor::regrole,
    (aclexplode(acldefault)).grantee::regrole,
    (aclexplode(acldefault)).*
from acldefault('r','postgres'::regrole)
;
```

## 列出权限检查函数

```sql
SELECT n.nspname as "Schema",
    p.proname as "Name",
    pg_catalog.pg_get_function_result(p.oid) as "Result data type",
    pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
    CASE p.prokind
        WHEN 'a' THEN 'agg'
        WHEN 'w' THEN 'window'
        WHEN 'p' THEN 'proc'
        ELSE 'func'
    END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname OPERATOR(pg_catalog.~) '^(.*privile.*)$' COLLATE pg_catalog.default
ORDER BY 1, 2, 4;
```