mysql 权限与安全

--创建schema
create schema rudy;
--创建一个他人拥有的schema
create schema rudy_schema authorization rudy;
--在创建表时,如果没有指定模式则在默认为public模式
create table rudy.t(id serial);

--设置模式的搜索路径
show search_path;
set search_path to rudy_schema,public;


--创建用户,用户和用户组在整个集群范围内共享
create user rudy with password '123456';
select * from pg_roles;
select * from pg_user;

--赋予权限角色
CREATE ROLE postgres SUPERUSER;
grant postgres to wcs_p;
grant all privileges on database wcsdb to postgres;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO wcsdb;


--创建只读账号
create user rudy with password '123456';
create schema rudy;
--切换连接到super用户下，其中横线(-)表示仍然连接当前数据库，如果是其它的数据库需要带上数据库的名字
\c - postgres
grant USAGE on SCHEMA rudy to rudy;
grant SELECT ON all tables in schema rudy to rudy;


--查询可以
mydb=> select * from rudy.t;
 id 
----
(0 rows)
--但没有insert权限
mydb=> insert into rudy.t values(1);
ERROR:  permission denied for relation t

--赋予插入权限
grant insert on table rudy.t to rudy;


--设置user/role为只读，注意它只对之后建立的连接起作用，对当前连接不起作用
alter user rudy set default_transaction_read_only=true;
alter role rudy reset default_transaction_read_only;

--此时用户即使有插入权限也不能执行插入
mydb=> insert into rudy.t values(1);
ERROR:  cannot execute INSERT in a read-only transaction

--查看用户的隔离级别
show default_transaction_read_only;

--查看哪些用户对表有权限
\z rudy.t 或者 \dp rudy.t
select relname,relacl from pg_class where relname='t';

--权限字母代表的权限的意思如下：
a: insert
r: select
w: update
d: delete
x: references
t: trigger
D: truncate

--查看系统表，查看角色权限
select * from INFORMATION_SCHEMA.role_table_grants where grantee='ztravel';

--查看一个表被授予了哪些权限给用户
select * from information_schema.role_table_grants where grantee='ztravel' and table_name='new_year_user_award_record';

--查看用户与角色之间的关系
select pr1.rolname 角色名,pr2.rolname 成员名 from pg_auth_members pam,pg_roles pr1,pg_roles pr2 where pam.roleid=pr1.oid and pam.member=pr2.oid;
select pr1.rolname 角色名,pr2.usename 成员名 from pg_auth_members pam,pg_roles pr1,pg_user pr2 where pam.roleid=pr1.oid and pam.member=pr2.usesysid; 


--对于USAGE权限，必须赋予用户，否则用户不能使用相对应的schema权限（即使给用户赋予了其它权限）
postgres=# revoke usage on SCHEMA statsrepo from dba; 
REVOKE
postgres=# grant select on table statsrepo.test to dba;
GRANT
--用户拥有select权限但没有usage权限，不能查询表
postgres=> select * from statsrepo.test;
ERROR:  permission denied for schema statsrepo
LINE 1: select * from statsrepo.test;
--给用户usage权限，能够正确查询
postgres=# grant USAGE on SCHEMA statsrepo to dba;  
postgres=> select * from statsrepo.test;
 id 
----
(0 rows)

--查看某个sequence的权限给了哪些用户或role角色
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' 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 access privileges"
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')
  AND c.relname ~ '^(t_seq)$'
  AND n.nspname !~ '^pg_' AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1, 2


--如果有以下错误，请查看数据库的监听地址
psql: could not connect to server: Connection refused
        Is the server running on host "rudy_01" (10.10.10.219) and accepting
        TCP/IP connections on port 5431?
		
listen_addresses = '*'    

--如果有 psql: FATAL:  no pg_hba.conf entry for host "10.10.10.219", user "repuser", database "repuser" 错误
--请检查连接的ip以及连接的数据库与连接的用户名
 host   all     repuser          192.168.174.0/24         md5
 host   all     repuser          0.0.0.0/32         md5  ##匹配唯一的ip
 host   all     repuser          0.0.0.0/0         md5   ##匹配所有的ip
 
 
 --查看用户是否有表列上的权限
postgres=> select has_any_column_privilege('dba','t2','insert,update');
 has_any_column_privilege 
--------------------------
 t 
 
 --查看用户是否有role
 postgres=> select pg_has_role('rudy','dba','member');                  
 pg_has_role 
-------------
 f

 
 --查看用户能否看到某张表的内容，pg_table_is_visible可用于表，视图，索引等
 postgres=> select pg_table_is_visible('t'::regclass);
 pg_table_is_visible 
---------------------
 t
(1 row)

postgres=> SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
                 relname                 
-----------------------------------------
 pg_statistic
 pg_type