pg 的許可權儲存在幾個地方.

1. 使用者許可權
對例項的登入或者對db 的ddl 許可權方面是放在 pg_role 表中的.

pconline=> select * from pg_roles ;
-[ RECORD 1 ]--+---------------------------------
rolname       | repl
rolsuper    | f
rolinherit    | t
rolcreaterole  | f
rolcreatedb | f
rolcatupdate | f
rolcanlogin | t
rolreplication | t
rolconnlimit | -1
rolpassword | ********
rolvaliduntil  |
rolconfig    |
oid          | 16384

針對 TABLE ,  COLUMN  , 函式, 自定義函式, 以及usage 許可權,  是存在information_schema下的表中的.

information_schema 儲存了當前db 下面的物件的許可權. 例如:
select * from information_schema.role_table_grants  where grantee='lsliang';
-[ RECORD 2 ]--+-----------------------------------------
grantor       | pc
grantee       | lsliang
table_catalog  | pc
table_schema | pc
table_name    | temp_20160513
privilege_type | SELECT
is_grantable | NO
with_hierarchy | YES

2. 物件上的許可權
如果要檢視某個表上有哪些許可權.

可以用命令列命令:

pconline=> \dp temp_20160513
Access privileges
-[ RECORD 1 ]------------+-----------------------------
Schema                | pc
Name                   | temp_20160513
Type                   | table
Access privileges       | pc=arwdDxt/pc
                     | pc_reader=r/pc
                     | pgreader_pc=r/pc
                     | u1=r/pc
                     | u2=r/pc
                     | user3=r/pc
Column access privileges |

其中的許可權欄位 arwdDxt

a = insert
r = select
w= update
d = delete
D = truncate
x = reference
t = trigger

還有一個是查詢PG_CLASS 系統檢視:

pconline=> select * from pg_class where relname='temp_20160513';
-[ RECORD 1 ]--+----------------------------------------------------------------------------------------------------------------------------------------------------
relname       | temp_20160513
relnamespace | 24585
reltype       | 5471707
reloftype    | 0
relowner    | 24577
relam       | 0
relfilenode | 5491139
reltablespace  | 0
relpages    | 285
reltuples    | 64310
relallvisible  | 285
reltoastrelid  | 0
reltoastidxid  | 0
relhasindex | f
relisshared | f
relpersistence | p
relkind       | r
relnatts    | 1
relchecks    | 0
relhasoids    | f
relhaspkey    | f
relhasrules | f
relhastriggers | f
relhassubclass | f
relfrozenxid | 1102384891
relacl       | {pc=arwdDxt/pc,pc_reader=r/pc,pgreader_pc=r/pc,u1=r/pc,u2=r/pc,user3=r/pc}

檢視PG資料庫的許可權情況

相關文章