数据库开发者社区 > 正文

PostgreSQL 对象权限如何在元数据中获取 - 权限解读、定制化导出权限

简介:
+关注继续查看

标签

PostgreSQL , 权限导出 , 定制化权限 , 元数据


背景

在PostgreSQL中,如何查询出指定用户的:系统权限、角色权限,以及其他用户的对象权限?

实际上PostgreSQL中所有权限都以ACL的形式存储在元数据中,所以权限并不是在某一张与用户挂钩的元数据表里面,而是分散在不同类型的对象的元数据中,以一个ACL字段存在。

导出与某个用户相关的权限方法1

用pgdump,导出DDL,以及PRIVILETE, 然后在导出文本中根据关键字filter

导出与某个用户相关的权限方法2

《PostgreSQL 逻辑结构 和 权限体系 介绍》

根据数据库的逻辑结构与权限体系,直接从元数据中获取对象权限。

1 数据库中有哪些对象可以赋权

通过grant命令可以看到,可以赋权的对象包括:

表、视图、物化视图、序列、外部表、数据库、域、类型、fdw、FS、函数、存储过程、routine、函数语言、大对象、SCHEMA、表空间、成员关系。

postgres=# \h grant  
Command:     GRANT  
Description: define access privileges  
Syntax:  
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON { [ TABLE ] table_name [, ...]  
         | ALL TABLES IN SCHEMA schema_name [, ...] }  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )  
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }  
    ON [ TABLE ] table_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { USAGE | SELECT | UPDATE }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON { SEQUENCE sequence_name [, ...]  
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }  
    ON DATABASE database_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON DOMAIN domain_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON FOREIGN DATA WRAPPER fdw_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON FOREIGN SERVER server_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { EXECUTE | ALL [ PRIVILEGES ] }  
    ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]  
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON LANGUAGE lang_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }  
    ON LARGE OBJECT loid [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }  
    ON SCHEMA schema_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { CREATE | ALL [ PRIVILEGES ] }  
    ON TABLESPACE tablespace_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON TYPE type_name [, ...]  
    TO role_specification [, ...] [ WITH GRANT OPTION ]  
  
where role_specification can be:  
  
    [ GROUP ] role_name  
  | PUBLIC  
  | CURRENT_USER  
  | SESSION_USER  
  
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]  

2 获取对象OWNER

owner 拥有对象的所有权限.

不同的对象类型,元数据在不同的元数据表里面,元数据里面包括了OWNER,OID,ACL等

1、表、视图、物化视图、序列、外部表、

pg_class  

2、数据库、

pg_database  

3、域、类型、

pg_type  

4、fdw、

pg_foreign_data_wrapper  

5、FS、

pg_foreign_server  

6、函数、存储过程、routine、

pg_proc  

7、函数语言、

pg_language  

8、大对象、

pg_largeobject_metadata  

9、SCHEMA、

pg_namespace  

10、表空间、

pg_tablespace  

11、成员关系。

pg_auth_members  
  
角色,角色中有哪些成员.   

从以上所有元数据中,可以获取到所有对象对应的OWNER。

3 获取对象的,已赋予给指定角色的权限

方法与2相同,从不同对象的元数据中,获取对象对应的ACL的信息。

pg_class 权限 (SEQUENCE, TABLE, 视图, 物化视图)  
  
pg_database 权限 (DATABASE)  
  
pg_type 权限 (DOMAIN, TYPE)  
  
pg_foreign_data_wrapper 权限 (FOREIGN DATA WRAPPER)  
  
pg_foreign_server 权限 (FOREIGN SERVER)  
  
pg_proc 权限 (FUNCTION | PROCEDURE | ROUTINE)  
  
pg_language 权限 (LANGUAGE)  
  
pg_largeobject_metadata 权限 (LARGE OBJECT)  
  
pg_namespace 权限 (SCHEMA)  
  
pg_tablespace 权限 (TABLESPACE)  
  
pg_auth_members 权限 (MEMBER SHIP)  

ACL解读含义如下

https://www.postgresql.org/docs/current/static/sql-grant.html

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  

4 获取对象的,已赋予给PUBLIC角色的权限

实际上方法还是与2相同,只是解读ACL时,需要注意:

PUBLIC角色代表所有用户,在ACL中显示为等号前面没有角色名。

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  

5 获取系统的默认权限

1、首先如何赋予将来创建的对象的默认权限

postgres=# \h alter default  
Command:     ALTER DEFAULT PRIVILEGES  
Description: define default access privileges  
Syntax:  
ALTER DEFAULT PRIVILEGES  
    [ FOR { ROLE | USER } target_role [, ...] ]  
    [ IN SCHEMA schema_name [, ...] ]  
    abbreviated_grant_or_revoke  
  
where abbreviated_grant_or_revoke is one of:  
  
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON TABLES  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { { USAGE | SELECT | UPDATE }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON SEQUENCES  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { EXECUTE | ALL [ PRIVILEGES ] }  
    ON { FUNCTIONS | ROUTINES }  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | ALL [ PRIVILEGES ] }  
    ON TYPES  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }  
    ON SCHEMAS  
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]  
  
REVOKE [ GRANT OPTION FOR ]  
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON TABLES  
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ CASCADE | RESTRICT ]  
  
REVOKE [ GRANT OPTION FOR ]  
    { { USAGE | SELECT | UPDATE }  
    [, ...] | ALL [ PRIVILEGES ] }  
    ON SEQUENCES  
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ CASCADE | RESTRICT ]  
  
REVOKE [ GRANT OPTION FOR ]  
    { EXECUTE | ALL [ PRIVILEGES ] }  
    ON { FUNCTIONS | ROUTINES }  
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ CASCADE | RESTRICT ]  
  
REVOKE [ GRANT OPTION FOR ]  
    { USAGE | ALL [ PRIVILEGES ] }  
    ON TYPES  
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ CASCADE | RESTRICT ]  
  
REVOKE [ GRANT OPTION FOR ]  
    { USAGE | CREATE | ALL [ PRIVILEGES ] }  
    ON SCHEMAS  
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ CASCADE | RESTRICT ]  

2、获取默认权限

postgres=# select * from pg_default_acl ;  
 defaclrole | defaclnamespace | defaclobjtype |   defaclacl     
------------+-----------------+---------------+---------------  
         10 |            2200 | r             | {=r/postgres}  
(1 row)  

6 获取用户的成员关系

1、角色 member ship

postgres=# select oid,* from pg_roles ;  
  oid  |          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid    
-------+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------  
  4200 | pg_signal_backend         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4200  
  4569 | pg_read_server_files      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4569  
    10 | postgres                  | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |    10  
  4570 | pg_write_server_files     | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4570  
  4571 | pg_execute_server_program | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4571  
  3375 | pg_read_all_stats         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3375  
 16487 | test                      | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 16487  
  3373 | pg_monitor                | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3373  
  3374 | pg_read_all_settings      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3374  
  3377 | pg_stat_scan_tables       | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3377  
(10 rows)  

2、

postgres=# select * from pg_auth_members ;  
 roleid | member | grantor | admin_option   
--------+--------+---------+--------------  
  
角色,成员,赋权者,成员是否可以二次赋权  
  
   3374 |   3373 |      10 | f  
   3375 |   3373 |      10 | f  
   3377 |   3373 |      10 | f  
     10 |  16487 |      10 | f  
(4 rows)  

小结

以上5个部分包含了所有的对象权限,用户可以根据需要自行组合导出。

除了以上提到的对象,还有索引、操作符等其他对象,他们没有单独的权限体系,但是他们依附于其他对象比如索引依附于表,操作符依附于函数。

https://www.postgresql.org/docs/10/static/catalogs.html

其他辅助工具

1、psql客户端

psql -E  
  
  
\?  
  
postgres=# \?  
General  
  \copyright             show PostgreSQL usage and distribution terms  
  \crosstabview [COLUMNS] execute query and display results in crosstab  
  \errverbose            show most recent error message at maximum verbosity  
  \g [FILE] or ;         execute query (and send results to file or |pipe)  
  \gdesc                 describe result of query, without executing it  
  \gexec                 execute query, then execute each value in its result  
  \gset [PREFIX]         execute query and store results in psql variables  
  \gx [FILE]             as \g, but forces expanded output mode  
  \q                     quit psql  
  \watch [SEC]           execute query every SEC seconds  
  
Help  
  \? [commands]          show help on backslash commands  
  \? options             show help on psql command-line options  
  \? variables           show help on special variables  
  \h [NAME]              help on syntax of SQL commands, * for all commands  
  
Query Buffer  
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor  
  \ef [FUNCNAME [LINE]]  edit function definition with external editor  
  \ev [VIEWNAME [LINE]]  edit view definition with external editor  
  \p                     show the contents of the query buffer  
  \r                     reset (clear) the query buffer  
  \s [FILE]              display history or save it to file  
  \w FILE                write query buffer to file  
  
Input/Output  
  \copy ...              perform SQL COPY with data stream to the client host  
  \echo [STRING]         write string to standard output  
  \i FILE                execute commands from file  
  \ir FILE               as \i, but relative to location of current script  
  \o [FILE]              send all query results to file or |pipe  
  \qecho [STRING]        write string to query output stream (see \o)  
  
Conditional  
  \if EXPR               begin conditional block  
  \elif EXPR             alternative within current conditional block  
  \else                  final alternative within current conditional block  
  \endif                 end conditional block  
  
Informational  
  (options: S = show system objects, + = additional detail)  
  \d[S+]                 list tables, views, and sequences  
  \d[S+]  NAME           describe table, view, sequence, or index  
  \da[S]  [PATTERN]      list aggregates  
  \dA[+]  [PATTERN]      list access methods  
  \db[+]  [PATTERN]      list tablespaces  
  \dc[S+] [PATTERN]      list conversions  
  \dC[+]  [PATTERN]      list casts  
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere  
  \dD[S+] [PATTERN]      list domains  
  \ddp    [PATTERN]      list default privileges  
  \dE[S+] [PATTERN]      list foreign tables  
  \det[+] [PATTERN]      list foreign tables  
  \des[+] [PATTERN]      list foreign servers  
  \deu[+] [PATTERN]      list user mappings  
  \dew[+] [PATTERN]      list foreign-data wrappers  
  \df[antw][S+] [PATRN]  list [only agg/normal/trigger/window] functions  
  \dF[+]  [PATTERN]      list text search configurations  
  \dFd[+] [PATTERN]      list text search dictionaries  
  \dFp[+] [PATTERN]      list text search parsers  
  \dFt[+] [PATTERN]      list text search templates  
  \dg[S+] [PATTERN]      list roles  
  \di[S+] [PATTERN]      list indexes  
  \dl                    list large objects, same as \lo_list  
  \dL[S+] [PATTERN]      list procedural languages  
  \dm[S+] [PATTERN]      list materialized views  
  \dn[S+] [PATTERN]      list schemas  
  \do[S]  [PATTERN]      list operators  
  \dO[S+] [PATTERN]      list collations  
  \dp     [PATTERN]      list table, view, and sequence access privileges  
  \drds [PATRN1 [PATRN2]] list per-database role settings  
  \dRp[+] [PATTERN]      list replication publications  
  \dRs[+] [PATTERN]      list replication subscriptions  
  \ds[S+] [PATTERN]      list sequences  
  \dt[S+] [PATTERN]      list tables  
  \dT[S+] [PATTERN]      list data types  
  \du[S+] [PATTERN]      list roles  
  \dv[S+] [PATTERN]      list views  
  \dx[+]  [PATTERN]      list extensions  
  \dy     [PATTERN]      list event triggers  
  \l[+]   [PATTERN]      list databases  
  \sf[+]  FUNCNAME       show a function's definition  
  \sv[+]  VIEWNAME       show a view's definition  
  \z      [PATTERN]      same as \dp  
  
Formatting  
  \a                     toggle between unaligned and aligned output mode  
  \C [STRING]            set table title, or unset if none  
  \f [STRING]            show or set field separator for unaligned query output  
  \H                     toggle HTML output mode (currently off)  
  \pset [NAME [VALUE]]   set table output option  
                         (NAME := {border|columns|expanded|fieldsep|fieldsep_zero|  
                         footer|format|linestyle|null|numericlocale|pager|  
                         pager_min_lines|recordsep|recordsep_zero|tableattr|title|  
                         tuples_only|unicode_border_linestyle|  
                         unicode_column_linestyle|unicode_header_linestyle})  
  \t [on|off]            show only rows (currently off)  
  \T [STRING]            set HTML <table> tag attributes, or unset if none  
  \x [on|off|auto]       toggle expanded output (currently off)  
  
Connection  
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}  
                         connect to new database (currently "postgres")  
  \conninfo              display information about current connection  
  \encoding [ENCODING]   show or set client encoding  
  \password [USERNAME]   securely change the password for a user  
  
Operating System  
  \cd [DIR]              change the current working directory  
  \setenv NAME [VALUE]   set or unset environment variable  
  \timing [on|off]       toggle timing of commands (currently off)  
  \! [COMMAND]           execute command in shell or start interactive shell  
  
Variables  
  \prompt [TEXT] NAME    prompt user to set internal variable  
  \set [NAME [VALUE]]    set internal variable, or list all if no parameters  
  \unset NAME            unset (delete) internal variable  
  
Large Objects  
  \lo_export LOBOID FILE  
  \lo_import FILE [COMMENT]  
  \lo_list  
  \lo_unlink LOBOID      large object operations  

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PostgreSQL技术大讲堂 - Part 8:PG对象权限管理
PostgreSQL技术大讲堂 - 对象权限管理
72 0
PostgreSQL 多维空间几何对象 相交、包含 高效率检索实践 - cube
标签 PostgreSQL , cube , 空间 , 几何 , 相交 , 包含 背景 多维空间对象的几何运算,高效率检索实践。 例如我们在数据库中存储了多维几何对象,可以使用lower, upper的数组来表达,例如3维度对象: CUBE [ xmin1 ymin1 zmin1 , xmax1 ymax1 zmax1 ] 在介绍CUBE类型前,我们可以使用6个字段(xmin,xmax,ymin,ymax,zmin,zmax)来表达一个立方体。
878 0
把 PostgreSQL 当做对象数据库使用
本文阐述了如何把 PostgreSQL 当做一个对象数据库使用,及其好处。
1666 0
PostgreSQL 10.1 手册_部分 IV. 客户端接口_第 34 章 大对象_34.3. 客户端接口
34.3. 客户端接口 34.3.1. 创建一个大对象 34.3.2. 导入一个大对象 34.3.3. 导出一个大对象 34.3.4. 打开一个现有的大对象 34.3.5. 向一个大对象写入数据 34.3.6. 从一个大对象读取数据 34.3.7. 在一个大对象中查找 34.3.8. 获取一个大对象的查找位置 34.3.9. 截断一个大对象 34.3.10. 关闭一个大对象描述符 34.3.11. 移除一个大对象 本节描述PostgreSQL的libpq客户端接口为访问大对象所提供的功能。
1204 0
PostgreSQL 10.1 手册_部分 IV. 客户端接口_第 34 章 大对象_34.2. 实现特性
34.2. 实现特性 大对象的实现将大对象分解成很多“数据块”并且将这些数据块存储在数据库的行中。一个B-tree索引用来保证在进行随机访问读写时能够根据数据块号快速地搜索到正确的数据块。 为一个大对象存储的数据块并不需要是连续的。
981 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义_5.12. 其他数据库对象
5.12. 其他数据库对象 表是一个关系型数据库结构中的核心对象,因为它们承载了我们的数据。但是它们并不是数据库中的唯一一种对象。有很多其他种类的对象可以被创建来使得数据的使用和刮泥更加方便或高效。
926 0
数据库领域前沿技术分享与交流
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
视频
相关电子书
更多
金融级 PostgreSQL监控及优化
立即下载
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
相关实验场景
更多
相关镜像