PostgreSQL MySQL 兼容性之 - 读写用户的只读影子用户

2022-05-06 05:35:24

在一些企业里面，通常会在数据库中创建一些只读用户，这些只读用户可以查看某些用户的对象，但是不能修改或删除这些对象的数据。
这种用户通常可以给开发人员，运营人员使用，或者数据分析师等角色的用户使用。
因为他们可能关注的是数据本身，并且为了防止他们误操作修改或删除线上的数据，所以限制他们的用户只有只读的权限。
MySQL这块的管理应该非常方便。
其实PostgreSQL管理起来也很方便。
用户可以先参考我前面写的两篇文章
PostgreSQL 逻辑结构和权限体系介绍
https://yq.aliyun.com/articles/41210

PostgreSQL 批量权限管理方法
https://yq.aliyun.com/articles/41512

PostgreSQL schema,database owner 的高危注意事项
https://yq.aliyun.com/articles/41514
建议用户使用超级用户创建schema和database，然后再把schema和database的读写权限给普通用户，这样就不怕被误删了。因为超级用户本来就有所有权限。

为了满足本文的需求, 创建读写用户的只读影子用户

1. 使用超级用户创建读写账号，创建数据库, 创建schema

postgres=# create role appuser login;
CREATE ROLE

postgres=# create database appuser;

postgres=# \c appuser postgres
appuser=# create schema appuser;  -- 使用超级用户创建schema

赋权
appuser=# grant connect on database to appuser;  -- 只赋予连接权限
appuser=# grant all on schema appuser to appuser;  -- 值赋予读和写权限

2. 假设该读写账号已经创建了一些对象

\c appuser appuser
appuser=> create table tbl1(id int);
CREATE TABLE
appuser=> create table tbl2(id int);
CREATE TABLE
appuser=> create table tbl3(id int);
CREATE TABLE

3. 创建只读影子账号

postgres=# create role ro login;
CREATE ROLE

postgres=# \c appuser postgres
appuser=# grant connect on database appuser to ro;
appuser=# grant usage on schema appuser to ro;

4. 创建隐藏敏感信息的视图

假设tbl2是敏感信息表，需要加密后给只读用户看

\c appuser appuser
appuser=> create view v as select md5(id::text) from tbl2;
CREATE VIEW

5. 修改已有权限

创建权限管理函数  
\c appuser appuser
appuser=> create or replace function g_or_v
(
  g_or_v text,   -- 输入 grant or revoke 表示赋予或回收
  own name,      -- 指定用户 owner 
  target name,   -- 赋予给哪个目标用户 grant privilege to who?
  objtyp text,   --  对象类别: 表, 物化视图, 视图 object type 'r', 'v' or 'm', means table,view,materialized view
  exp text[],    --  排除哪些对象, 用数组表示, excluded objects
  priv text      --  权限列表, privileges, ,splits, like 'select,insert,update'
) returns void as 
$$

declare
  nsp name;
  rel name;
  sql text;
  tmp_nsp name := '';
begin
  for nsp,rel in select t2.nspname,t1.relname from pg_class t1,pg_namespace t2 where t1.relkind=objtyp and t1.relnamespace=t2.oid and t1.relowner=(select oid from pg_roles where rolname=own)
  loop
    if (tmp_nsp = '' or tmp_nsp <> nsp) and lower(g_or_v)='grant' then
      -- auto grant schema to target user
      sql := 'GRANT usage on schema "'||nsp||'" to '||target;
      execute sql;
      raise notice '%', sql;
    end if;

    tmp_nsp := nsp;

    if (exp is not null and nsp||'.'||rel = any (exp)) then
      raise notice '% excluded % .', g_or_v, nsp||'.'||rel;
    else
      if lower(g_or_v) = 'grant' then
        sql := g_or_v||' '||priv||' on "'||nsp||'"."'||rel||'" to '||target ;
      elsif lower(g_or_v) = 'revoke' then
        sql := g_or_v||' '||priv||' on "'||nsp||'"."'||rel||'" from '||target ;
      else
        raise notice 'you must enter grant or revoke';
      end if;
      raise notice '%', sql;
      execute sql;
    end if;
  end loop;
end;

$$
 language plpgsql;  

appuser=> select g_or_v('grant', 'appuser', 'ro', 'r', array['public.tbl2'], 'select');
WARNING:  no privileges were granted for "public"
CONTEXT:  SQL statement "GRANT usage on schema "public" to ro"
PL/pgSQL function g_or_v(text,name,name,text,text[],text) line 13 at EXECUTE
NOTICE:  GRANT usage on schema "public" to ro
NOTICE:  grant select on "public"."tbl1" to ro
NOTICE:  grant excluded public.tbl2 .
NOTICE:  grant select on "public"."tbl3" to ro
 g_or_v 
--------
 
(1 row)

另外还提供了一种方法，但是一定要指定schema，所以用户自己要注意，如果要对所有schema操作，需要把所有的schema都写进去。  

grant select on all tables in schema public,schema1,schema2,schema3 to ro;  

并且这种方法还有一个弊端，如果这些schema下面有其他用户创建的对象，也会被赋予，如果赋权的账号没有权限，则会报错。  
所以还是建议使用我提供的函数来操作

6. 回收敏感表的权限

因为前面已经排除赋予了，所以不需要回收

7. 修改新建对象的默认权限

appuser=> alter default privileges for role appuser grant select on tables to ro;
ALTER DEFAULT PRIVILEGES
appuser=> \ddp+
               Default access privileges
  Owner   | Schema | Type  |     Access privileges     
----------+--------+-------+---------------------------
 appuser  |        | table | appuser=arwdDxt/appuser  +
          |        |       | ro=r/appuser

8. 未来如果有新增的敏感表，先创建视图，同时回收表的权限

appuser=> create table tbl4(id int);
CREATE TABLE
appuser=> create view v2 as select md5(id::text) from tbl4;
CREATE VIEW
appuser=> revoke select on tbl4 from ro;
REVOKE

权限检查

appuser=> \dp+ v2
                               Access privileges
 Schema | Name | Type |    Access privileges    | Column privileges | Policies 
--------+------+------+-------------------------+-------------------+----------
 public | v2   | view | appuser=arwdDxt/appuser+|                   | 
        |      |      | ro=r/appuser            |                   | 
(1 row)

希望本文对PostgreSQL用户有所帮助

码农公寓