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

背景

在一些企业里面，通常会在数据库中创建一些只读用户，这些只读用户可以查看某些用户的对象，但是不能修改或删除这些对象的数据。

这种用户通常可以给开发人员，运营人员使用，或者数据分析师 等角色的用户使用。

因为他们可能关注的是数据本身，并且为了防止他们误操作修改或删除线上的数据，所以限制他们的用户只有只读的权限。

MySQL这块的管理应该非常方便。

其实PostgreSQL管理起来也很方便。

用户可以先参考我前面写的两篇文章

PostgreSQL 逻辑结构 和 权限体系 介绍

《PostgreSQL 逻辑结构 和 权限体系 介绍》

PostgreSQL 批量权限 管理方法

《PostgreSQL 批量权限 管理方法》

PostgreSQL schema,database owner 的高危注意事项

《PostgreSQL schema,database owner 的高危注意事项》

建议用户使用超级用户创建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用户有所帮助

digoal’s 大量PostgreSQL文章入口