权限相关

作者: 古飞_数据 | 来源:发表于2023-04-10 09:26 被阅读0次

权限相关
linux常用基本命令-3
开发框架收集
权限相关操作
Android 权限相关
iOS权限相关
iOS 相关权限
Android 相关权限
Android权限相关
Linux权限相关

数据库的权限：访问权限类型必须是CREATE、CONNECT、TEMPORARY、TEMP（等价于TEMPORARY）的一些组合
schema的权限：访问权限类型必须是CREATE、USAGE。
表的权限: SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER

DDL类权限ALTER、DROP、COMMENT、INDEX、VACUUM属于所有者固有的权限，隐式拥有。



对象:比如表、函数、视图、索引等


表空间的最常用的作用是优化性能，比如一个最常用的索引可以建立在非常快的硬盘上，而不太常用的表可以建立在便宜的硬盘上,或用来存储用于进行归档、日志表之类

你想要查看某个表的某一行数据，那么你得要能够登录数据库 (LOGIN) 这一系统权限，
表所在的数据库的连接权限 (CONNECT) ，
所在模式的使用权限 (USAGE) 和表本身的查看权限 (SELECT) ，
同时还要满足对这一行数据的行级策略 (row level security)以及是否有相应列的查询权限，层层关卡，缺一不可。


默认情况下，数据库在创建后，允许public角色连接，即允许任何人连接，需要
revoke connect on database xxx from public之后，
再显式执行grant connect on database xxx to xxx。

revoke connect on database jd from public;
grant connect on database jd to u1;

先revoke all吧，再按需授予权限



删除用户，先转移"资产"
REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
DROP ROLE doomed_role;


--查询某个用户拥有的对象
select 
    nsp.nspname as SchemaName
    ,cls.relname as ObjectName 
    ,rol.rolname as ObjectOwner
    ,case cls.relkind
        when 'r' then 'TABLE'
        when 'm' then 'MATERIALIZED_VIEW'
        when 'i' then 'INDEX'
        when 'S' then 'SEQUNCE'
        when 'v' then 'VIEW'
        when 'c' then 'TYPE'
        else cls.relkind::text
    end as ObjectType
from pg_class cls
join pg_roles rol
    on rol.oid = cls.relowner
join pg_namespace nsp 
    on nsp.oid = cls.relnamespace
where nsp.nspname not in ('information_schema', 'pg_catalog')
    and nsp.nspname not like 'pg_toast%'
    and rol.rolname = 'u1'  
order by nsp.nspname, cls.relname;


--查询被授予的角色
SELECT 
      r.rolname, 
      ARRAY(SELECT b.rolname
            FROM pg_catalog.pg_auth_members m
            JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
            WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE r.rolname NOT IN ('pg_signal_backend','rds_iam',
                        'rds_replication','rds_superuser',
                        'rdsadmin','rdsrepladmin')
ORDER BY 1;


--用户嵌套查询
WITH RECURSIVE x AS
(
  SELECT member::regrole,
         roleid::regrole AS role,
         member::regrole || ' -> ' || roleid::regrole AS path
  FROM pg_auth_members AS m
  WHERE roleid > 16384
  UNION ALL
  SELECT x.member::regrole,
         m.roleid::regrole,
         x.path || ' -> ' || m.roleid::regrole
 FROM pg_auth_members AS m
    JOIN x ON m.member = x.role
  )
  SELECT member, role, path
  FROM x
  ORDER BY member::text, role::text;
  
  
  








select * from information_schema.usage_privileges where object_schema not in ('pg_catalog');
 
 
information_schema.role_table_grants和information_schema.table_privileges是用来描述表的权限信息的两个系统表



--当前数据库下表的权限
select * from information_schema.table_privileges where table_name='t1';
grantor  | grantee  | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy

select * from information_schema.table_privileges where table_schema='public';


select * from information_schema.role_table_grants;
grantor  | grantee  | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 



SELECT grantee,table_schema,table_name,string_agg( privilege_type,', ' ) as privilege_type
FROM information_schema.role_table_grants
WHERE table_name='t1'
group by grantee,table_schema,table_name;


根据条件查看用户对部分表的权限


SELECT grantee,table_schema,table_name, string_agg( privilege_type,', ' ) as privilege_type 
FROM information_schema.role_table_grants 
where grantee='u1' group by table_name,table_schema,grantee;



information_schema.role_table_grants视图，是通过information_schema.table_privileges和information_schema.enabled_roles两个视图关联得到的



--根据用户查看schema的权限
select a.nspname,b.rolname,string_agg(a.pri_t,',') from 
(select nspname,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema') a,pg_authid b 
where (a.grantee=b.oid or a.grantee=0) and b.rolname='u1' 
group by a.nspname,b.rolname;

revoke USAGE on schema sche1 from u1;


在PostgreSQL里，有着一些权限验证相关的函数，这些函数以’has_'开头，'privilege’结尾
\df has_*privilege

select has_database_privilege(user,database,privilege);
select has_database_privilege(database,privilege);



https://mp.weixin.qq.com/s/jQP36rXZb4sgA71AaIJ-Sw    又被权限搞晕了？拿捏！
https://www.modb.pro/db/622817                       PG如何查看用户拥有的各类对象的操作权限
https://mp.weixin.qq.com/s/zMjtR25aap0nXx6TcFtmPQ    PostgreSQL权限的使用，小心再被拿捏
https://mp.weixin.qq.com/s/bsFGLcx-xd9t8ufhTP-pXg    再唠唠晕乎的权限体系
https://mp.weixin.qq.com/s/3QYZ6AXalIy93HLWZAEFww    PostgreSQL中public schema的权限和安全

--创建一个名称sel的用户
create user sel with nosuperuser nocreatedb nocreaterole noinherit login noreplication nobypassrls password '123';

create user sel2 with noinherit password '123';

--登录到core数据库
psql -h 192.168.56.91 -p 5432 -U sel -d core

--查看角色使用\duS+
\duS+ sel

--查看用户在schema的权限
\dp





删除用户
3.1 使用postgrese用户连接至core数据库
psql -h 172.17.23.152 -p 5432 -U postgres -d core
--回收分配的权限并删除用户
revoke select on all tables in schema public from sel;
revoke usage on schema public from sel;
revoke connect on database core from sel;
drop role sel;

删除角色通常不仅仅是快速DROP ROLE的问题。角色拥有的任何对象都必须首先删除或重新分配给其他所有者；
REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
DROP ROLE doomed_role;


1 public权限解读
   用户默认情况下具有public权限，public默认具有创建和使用schema的权限，因此意味着可以在schema中创建对象（包括表）、列出schema中的对象，并在其权限允许时访问它们。

     所以创建数据库或schema完成后做的第一件事是
     
1.1 创建数据库完成后
--回收schema的public权限
--revoke all on schema public from public; --正常情况使用这个
revoke all on schema public,tiger,tiger_data,topology from public; --使用postgis扩展使用这个

--从core数据库收回public的所有权限
revoke all on database core from public;
revoke all on database postgres from public;     

系统权限、对象权限

表的权限
INSERT     
SELECT     
UPDATE     
DELETE     
TRUNCATE   
REFERENCES 
TRIGGER    

1、查看某用户的系统权限
SELECT * FROM  pg_roles WHERE rolname='postgres';

2、查看某用户的表权限
select * from information_schema.table_privileges where grantee='mes';

3、查看某用户的usage权限
select * from information_schema.usage_privileges where grantee='cjc';

4、查看某用户在存储过程函数的执行权限
select * from information_schema.routine_privileges where grantee='postgres'; 

5、查看某用户在某表的列上的权限
select * from information_schema.column_privileges where grantee='postgres'; 

6、查看当前用户能够访问的数据类型
select * from information_schema.data_type_privileges ;

7、查看用户自定义类型上授予的USAGE权限
select * from information_schema.udt_privileges where grantee='postgres';



--登录到core数据库,并创建表
psql -h 172.17.23.152 -p 5432 -U sel -d core
create table aaa(objectid integer);   --报错，无权限,  14版本可以创建


--用postgrese用户登录core数据库为sel用户授权
core=> \c core postgres
psql -h localhost -p 5432 -U postgres -d core

grant usage on schema public to sel;
grant select on all tables in schema public to sel;


再次转到在另一个进程中运行

psql -h 172.17.23.152 -p 5432 -U sel -d core
select * from features;
delete  from features;  --失败

华为云RDS PostgreSQL

psql -h 124.70.135.213 -U root -d postgres -p 5432


postgres=> select version();
                                   version                                    
------------------------------------------------------------------------------
 PostgreSQL 12.13 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.0, 64-bit
(1 row)

postgres=> \du
                                                                 List of roles
 Role name |                         Attributes                         |                              Member of                               
-----------+------------------------------------------------------------+----------------------------------------------------------------------
 rdsAdmin  | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
           | 100000 connections                                         | 
 rdsBackup | No inheritance, Replication                               +| {}
           | 100000 connections                                         | 
 rdsMetric | No inheritance                                            +| {pg_read_all_stats}
           | 100000 connections                                         | 
 rdsRepl   | No inheritance, Replication                               +| {pg_read_all_stats}
           | 100000 connections                                         | 
 root      | Create role, Create DB, Replication                        | {pg_monitor,pg_read_all_stats,pg_stat_scan_tables,pg_signal_backend}
 
postgres=# \du
                                     List of roles
  Role name   |                         Attributes                         | Member of 
--------------+------------------------------------------------------------+-----------
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 readonly     | Cannot login                                               | {}
 sampleru     |                                                            | {}
 sampleuser   |                                                            | {}
 sampleuserdc |                                                            | {}
 
 
postgres=> \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | rdsAdmin | rdsAdmin=UC/rdsAdmin+| standard public schema
        |          | =U/rdsAdmin          | 
(1 row) 

--------------

postgres=# \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   | 
(1 row)


postgres=# revoke usage on schema public from public;
REVOKE
postgres=# \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner | standard public schema
(1 row)

--同时取消create、usage权限
REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC;

--甚至可以取消schema的owner的权限
postgres=# revoke all privileges on schema public from pg_database_owner;
REVOKE
postgres=# \dn+
                             List of schemas
  Name  |       Owner       | Access privileges |      Description       
--------+-------------------+-------------------+------------------------
 public | pg_database_owner |                   | standard public schema
(1 row)

create user ru1 with password '123';
create user ru2 with password '123';
create uesr u1 with password '123';
create uesr u2 with password '123';


grant readonly to ru1;
grant readonly to ru2;
grant readwrite to u1;
grant readwrite to u2;

\c db1 u1
alter default privileges for role u1 grant select,update,delete,insert on tables to readwriter;
alter default privileges for role u1 grant usage on sequences to readwrite;
alter default privileges for role u1 grant select on tables to readonly;


\c db1 u2
alter default privileges for role u2 grant select,update,delete,insert on tables to readwrite;
alter default privileges for role u2 grant usage on sequences to readwrite;
alter default privileges for role u2 grant select on tables to readonly;




grant usage on schema public to 用户;
grant insert on all tables in schema public to 用户;
alter default privileges in schema public grant insert on tables to 用户;




//创建角色不允许登陆
create role todo_user nologin;    
//赋予角色与postgres用户   
grant todo_user to postgres;    
 //schema api允许todo_user查找该Schema下的对象
grant usage on schema api to todo_user;   
 //单表赋予todo_user角色对api.todos表所有权限
grant all on api.todos to todo_user;     
//赋予todo_user 对于当前api下的所有表的所有权限
//grant select on ALL tables in schema api to web_anon ;   
//查询此表序列号的权限也就是 序列号自动增长的权限
grant usage, select on sequence api.todos_id_seq to todo_user;  



================
CREATE SCHEMA app;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE TABLE app.stock_data(
  symbol text NOT NULL,
  tick_at timestamptz,
  value float
);
CREATE INDEX ON app.stock_data USING brin(tick_at);

INSERT INTO app.stock_data
SELECT 'NOTREAL123', tick, 50 + random()
FROM generate_series(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + '7 days'::interval, '1 second'::interval) tick;

CREATE FUNCTION app.hi_lo(timestamptz, timestamptz)
RETURNS TABLE (hi float, lo float)
AS $$
  SELECT max(value) AS hi, min(value) AS lo
  FROM app.stock_data
  WHERE tick_at >= $1 AND tick_at < $2
$$ LANGUAGE SQL STABLE;


SELECT *
FROM app.hi_lo(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + '1 hour'::interval);


CREATE ROLE scraper LOGIN;
SELECT * FROM app.stock_data ORDER BY tick_at LIMIT 1;

GRANT USAGE ON SCHEMA app TO scraper;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO scraper;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA app TO scraper;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA app TO scraper;


SELECT * FROM app.stock_data ORDER BY tick_at LIMIT 1;

SELECT * FROM app.hi_lo(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + '1 hour'::interval);


CREATE TABLE app.nope (id int);
ERROR: permission denied for schema app

INSERT INTO app.stock_data
SELECT 'NOTREAL123', tick, 50 + random()
FROM generate_series(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + '7 days'::interval, '1 second'::interval) tick;
ERROR: permission denied for table stock_data

CREATE TABLE app.cant_access (id int);


ALTER DEFAULT PRIVILEGES
IN SCHEMA app
GRANT SELECT ON TABLES TO scraper;

ALTER DEFAULT PRIVILEGES
IN SCHEMA app
GRANT EXECUTE ON FUNCTIONS TO scraper;

CREATE VIEW app.stats AS
SELECT date_trunc('day', tick_at) AS hour, max(value) AS high, min(value) AS low, avg(value)
FROM app.stock_data
GROUP BY date_trunc('day', tick_at)
ORDER BY date_trunc('day', tick_at);

SELECT * FROM app.stats;


Postgres 14 通过 Stephen 实现的“pg_read_all_data”角色使这变得更加容易。“pg_read_all_data” 允许您为用户分配只读权限；用户能够对数据库中任何用户创建的任何对象拥有只读权限。
GRANT pg_read_all_data TO scraper;