PostgreSQL 表和列权限解读
http://www.postgres.cn/docs/10/catalogs.html
postgres=# create user cmft with ENCRYPTED PASSWORD 'cmrh1875!';
CREATE ROLE
postgres=# create user cmrh with ENCRYPTED PASSWORD 'cmrh1875!';
CREATE ROLE
--查看用户储存处元数据
postgres=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
cmft | 16385 | f | f | f | f | ******** | |
cmrh | 16386 | f | f | f | f | ******** | |
(3 rows)
--角色储存处
postgres=# select * from pg_roles ;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200
pg_read_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4569
cmrh | f | t | f | f | t | f | -1 | ******** | | f | | 16386
postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10
pg_write_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4570
pg_execute_server_program | f | t | f | f | f | f | -1 | ******** | | f | | 4571
pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375
pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373
pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374
pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377
cmft | f | t | f | f | t | f | -1 | ******** | | f | | 16385
(11 rows)
--密码存放处
postgres=# select * from pg_authid ;
oid | rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
-------+---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------------------------------+---------------
3373 | pg_monitor | f | t | f | f | f | f | f | -1 | |
3374 | pg_read_all_settings | f | t | f | f | f | f | f | -1 | |
3375 | pg_read_all_stats | f | t | f | f | f | f | f | -1 | |
3377 | pg_stat_scan_tables | f | t | f | f | f | f | f | -1 | |
4569 | pg_read_server_files | f | t | f | f | f | f | f | -1 | |
4570 | pg_write_server_files | f | t | f | f | f | f | f | -1 | |
4571 | pg_execute_server_program | f | t | f | f | f | f | f | -1 | |
4200 | pg_signal_backend | f | t | f | f | f | f | f | -1 | |
10 | postgres | t | t | t | t | t | t | t | -1 | md5c01a28208aaaaeecf23b3e5da788c352 |
16385 | cmft | f | t | f | f | t | f | f | -1 | md5ec4d058f572e407dc0a1cc08efc809c1 |
16386 | cmrh | f | t | f | f | t | f | f | -1 | md53b2defdd33c706a8867de33616e4e74f |
(11 rows)
cmft=# select * from pg_shadow ;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
postgres | 10 | t | t | t | t | md5c01a28208aaaaeecf23b3e5da788c352 | |
cmrh | 16386 | f | f | f | f | md53b2defdd33c706a8867de33616e4e74f | |
cmft | 16385 | t | f | f | f | md5ec4d058f572e407dc0a1cc08efc809c1 | |
(3 rows)
分析Postgres数据库中最为常用的两个系统Schema： information_schema 和 pg_catalog
其中information_schema是方便用户查看表／视图／函数信息提供的
pg_catalog是系统Schema，包含了系统的自带函数／数据类型定义等，pg_catalog是保障postgres正常运转的重要基石
postgres=# create database cmft;
CREATE DATABASE
postgres=> \c cmft cmft
You are now connected to database "cmft" as user "cmft".
cmft=> \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
cmft=> select oid,* from pg_catalog.pg_namespace;
oid | oid | nspname | nspowner | nspacl
-------+-------+--------------------+----------+-------------------------------------
99 | 99 | pg_toast | 10 |
12314 | 12314 | pg_temp_1 | 10 |
12315 | 12315 | pg_toast_temp_1 | 10 |
11 | 11 | pg_catalog | 10 | {postgres=UC/postgres,=U/postgres}
2200 | 2200 | public | 10 | {postgres=UC/postgres,=UC/postgres}
13293 | 13293 | information_schema | 10 | {postgres=UC/postgres,=U/postgres}
(6 rows)
postgres=# select relname, relkind from pg_catalog.pg_class;
pg_authid | r
pg_shadow | v
pg_statistic_ext_data | r
pg_roles | v
pg_settings | v
pg_file_settings | v
pg_hba_file_rules | v
pg_config | v
pg_cursors | v
pg_user_mapping | r
pg_stat_bgwriter | v
pg_replication_origin_status | v
pg_subscription | r
pg_stat_progress_vacuum | v
pg_stat_progress_cluster | v
pg_attribute | r
pg_proc | r
pg_class | r
pg_attrdef | r
pg_constraint | r
pg_inherits | r
pg_index | r
通过查看information_chema.tables, information_schema.columns可以方便的获取表／字段信息。
create table cmft_t1(id int,age int);
insert into cmft_t1 select t,t from generate_series(1,10) as t;
cmft=> select * from pg_catalog.pg_class where relname='cmft_t1';
oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence |
relkind | relnatts | relchecks | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relmin
mxid | relacl | reloptions | relpartbound
-------+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+
---------+----------+-----------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+-------
-----+--------+------------+--------------
16388 | cmft_t1 | 2200 | 16390 | 0 | 16385 | 2 | 16388 | 0 | 0 | 0 | 0 | 0 | f | f | p |
r | 2 | 0 | f | f | f | f | f | t | d | f | 0 | 491 |
1 | | |
(1 row)
cmft=> select * from information_schema.tables where table_catalog='cmft' and table_schema='public' and table_name='cmft_t1';
table_catalog | table_schema | table_name | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertabl
e_into | is_typed | commit_action
---------------+--------------+------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+-------------
-------+----------+---------------
cmft | public | cmft_t1 | BASE TABLE | | | | | | YES
| NO |
(1 row)
cmft=> select * from information_schema.columns where table_catalog='cmft' and table_schema='public';
table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | character_octet_length | numeric_precision | numeric_pre
cision_radix | numeric_scale | datetime_precision | interval_type | interval_precision | character_set_catalog | character_set_schema | character_set_name | collation_catalog | collation_schema | collatio
n_name | domain_catalog | domain_schema | domain_name | udt_catalog | udt_schema | udt_name | scope_catalog | scope_schema | scope_name | maximum_cardinality | dtd_identifier | is_self_referencing | is_id
entity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle | is_generated | generation_expression | is_updatable
---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+------------
-------------+---------------+--------------------+---------------+--------------------+-----------------------+----------------------+--------------------+-------------------+------------------+---------
-------+----------------+---------------+-------------+-------------+------------+----------+---------------+--------------+------------+---------------------+----------------+---------------------+------
-------+---------------------+----------------+--------------------+------------------+------------------+----------------+--------------+-----------------------+--------------
cmft | public | cmft_t1 | age | 2 | | YES | integer | | | 32 |
2 | 0 | | | | | | | | |
| | | | cmft | pg_catalog | int4 | | | | | 2 | NO | NO
| | | | | | NO | NEVER | | YES
cmft | public | cmft_t1 | id | 1 | | YES | integer | | | 32 |
2 | 0 | | | | | | | | |
| | | | cmft | pg_catalog | int4 | | | | | 1 | NO | NO
| | | | | | NO | NEVER | | YES
(2 rows)
cmft=> \dp+
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------+-------------------+-------------------+----------
public | cmft_t1 | table | | |
(1 row)
cmft=> \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------+-------+-------+------------+-------------
public | cmft_t1 | table | cmft | 8192 bytes |
(1 row)
cmft=> select user;
user
------
cmft
(1 row)
切换用户 cmrh用户
cmft=> \c cmft cmrh
You are now connected to database "cmft" as user "cmrh".
cmft=> select user;
user
------
cmrh
(1 row)
发现无法访问
cmft=> select * from cmft_t1 limit 2;
ERROR: permission denied for table cmft_t1
cmft=> \dp+
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------+-------------------+-------------------+----------
public | cmft_t1 | table | | |
(1 row)
cmft=> SELECT has_table_privilege('cmrh','public.cmft_t1', 'select');
has_table_privilege
---------------------
f
(1 row)
cmft=> \dp+
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------+-------------------+-------------------+----------
public | cmft_t1 | table | cmft=arwdDxt/cmft+| |
| | | cmrh=r/cmft | |
(1 row)
cmrh=r/cmft --代表可以对cmft_t1进行查询
--cmft cmft_t1 属主用户给 cmrh 用户授 select 权限
cmft=> \dp+
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------+-------------------+-------------------+----------
public | cmft_t1 | table | cmft=arwdDxt/cmft+| |
| | | cmrh=r/cmft | |
(1 row)
--cmrh用户执行
cmft=> SELECT has_table_privilege('cmrh','public.cmft_t1', 'select');
has_table_privilege
---------------------
t
(1 row)
cmft=> select * from cmft_t1 limit 2;
id | age
----+-----
1 | 1
2 | 2
(2 rows)
--授予cmft用户在cmft库创建db的权限
cmft=> \c cmft postgres
You are now connected to database "cmft" as user "postgres".
cmft=# grant create on database cmft to cmft;
GRANT
cmft=# \c cmft cmft
You are now connected to database "cmft" as user "cmft".
cmft=> create schema cmft_schema;
CREATE SCHEMA
--查看默认schema访问权限
cmft=> \dnp+
List of schemas
Name | Owner | Access privileges | Description
-------------+----------+----------------------+------------------------
cmft_schema | cmft | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
cmft用户执行
cmft=> create table cmft_schema.cmft_t2(id int,age int);
CREATE TABLE
cmft=> insert into cmft_schema.cmft_t2 select t,t from generate_series(1,10) as t;
INSERT 0 10
cmrh用户
cmft=> \dnp+
List of schemas
Name | Owner | Access privileges | Description
-------------+----------+----------------------+------------------------
cmft_schema | cmft | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(2 rows)
无法访问
cmft=> select * from cmft_schema.cmft_t2 ;
ERROR: permission denied for schema cmft_schema
LINE 1: select * from cmft_schema.cmft_t2 ;
^
cmft=>
cmft授权select权限
cmft=> grant select on cmft_schema.cmft_t2 to cmrh;
GRANT
cmrh查询 无法访问
postgres=# \c cmft cmrh
You are now connected to database "cmft" as user "cmrh".
cmft=> select * from cmft_schema.cmft_t2 ;
ERROR: permission denied for schema cmft_schema
LINE 1: select * from cmft_schema.cmft_t2 ;
cmft授予cmrh usgae权限
cmft=> grant usage on schema cmft_schema to cmrh;
GRANT
cmft查看
cmft=> \dnp+
List of schemas
Name | Owner | Access privileges | Description
-------------+----------+----------------------+------------------------
cmft_schema | cmft | cmft=UC/cmft +|
| | cmrh=U/cmft |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(2 rows)
cmrh=U/cmft --代表 cmrh用户能够访问cmft_schema
cmft=> select * from cmft_schema.cmft_t2 limit 2;
id | age
----+-----
1 | 1
2 | 2
(2 rows)
http://www.postgres.cn/docs/10/functions-info.html
psql \dp 如何获取权限列表的
使用psql -E选项，可以将psql的内部操作也打印出来，这样就能得到\dp都干了什么
cmft=> \dp+ *.*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------------------+---------------------------------------+-------+---------------------------+-------------------+----------
cmft_schema | cmft_t2 | table | cmft=arwdDxt/cmft +| |
| | | cmrh=r/cmft | |
information_schema | _pg_foreign_data_wrappers | view | | |
information_schema | _pg_foreign_servers | view | | |
information_schema | _pg_foreign_table_columns | view | | |
information_schema | _pg_foreign_tables | view | | |
information_schema | _pg_user_mappings | view |
cmft=# revoke usage on schema cmft_schema from cmrh;
REVOKE
--查看指定用户对那些schema具有权限
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
pg_catalog.array_to_string(n.nspacl, E'\n') AS "Access privileges",
pg_catalog.obj_description(n.oid, 'pg_namespace') AS "Description"
FROM pg_catalog.pg_namespace n
where pg_catalog.array_to_string(n.nspacl, E'\n') like '%cmrh%'
ORDER BY 1;
--查看指定表、用户对那些表具有权限
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(c.relacl, 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 OPERATOR(pg_catalog.~) '^(cmft_schema)$'
ORDER BY 1, 2;
Schema | Name | Type | Access privileges | Column privileges | Policies
-------------+---------+-------+-------------------+-------------------+----------
cmft_schema | cmft_t2 | table | cmft=arwdDxt/cmft+| |
| | | cmrh=rd/cmft | |
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
https://yq.aliyun.com/articles/69387
扫一扫
9359
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
