Postgresql多租户实践--使用多个Schema篇

长河的笔记 2020-07-17

4026

目标：每个schema对应不同的租户或应用，多schema架构通过权限进行数据隔离。

本文实验环境:PostgreSQL11.8

步骤:

创建测试数据库,2个用户及2个schema.

postgres=# create database testdb;
CREATE DATABASE
postgres=# create role fiona login encrypted password 'abc123';
CREATE ROLE
postgres=# create role sally login encrypted password 'abc123';
CREATE ROLE


postgres=# \c testdb;
You are now connected to database "testdb" as user "postgres".


testdb=# create schema finance;
CREATE SCHEMA
testdb=# create schema sales;
CREATE SCHEMA

在PG中当访问数据库对象时，用户参数"search_path"用来标识要在哪些schemas下进行查找对象, current_schema是search_path参数中的第一个schema值。在不采用全限定名称的时候，PG默认会在public schema下进行对象的查找。如下：

postgres=# select current_schema;
 current_schema 
----------------
 public
(1 row)

如果我们想让特定的用户只能够访问某个schema下的对象，可以通过控制current_schema参数实现。默认权限情况下fiona和sally这2个用户是能够互相看到对方存在的，这是由于这些用户均属于public角色的原因，所以这里我们还需要对默认权限进行限制。

如下：fiona是一个租户或应用的数据库帐户，它所创建的对象默认都对存放至finance这个schema下，同样sally对应着另一个租户或应用，它所创建的对象默认都会存放至sales这个Schema下。2个租户或应用的数据通过权限进行隔离而无法彼此互相访问。

testdb=# ALTER ROLE fiona SET search_path = 'finance';
ALTER ROLE
testdb=# ALTER ROLE sally SET search_path = 'sales';
ALTER ROLE


[postgres@bighouse3 ~/11/data]$ psql -h bighouse3 -p 15432
psql (11.8)
Type "help" for help.


postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# REVOKE ALL ON SCHEMA finance FROM public;
REVOKE
testdb=# GRANT USAGE ON SCHEMA finance TO fiona;
GRANT
testdb=# GRANT ALL ON SCHEMA finance TO fiona;
GRANT
testdb=# REVOKE ALL ON SCHEMA sales FROM public;
REVOKE
testdb=# GRANT USAGE ON SCHEMA sales TO sally;
GRANT
testdb=# GRANT ALL ON SCHEMA sales TO sally;
GRANT

测试

用户sally的数据存放在sales这个schema下。用户fiona的数据存放在finance这个schema下。通过权限进行了数据的隔离和访问。

[postgres@bighouse3 ~/11/data]$ psql -h bighouse3 -p 15432 -U fiona testdb
Password for user fiona: 
psql (11.8)
Type "help" for help.


testdb=> create table t_fiona (id int primary key);
CREATE TABLE
testdb=> \d
         List of relations
 Schema  |  Name   | Type  | Owner 
---------+---------+-------+-------
 finance | t_fiona | table | fiona
(1 row)


testdb=> \q


[postgres@bighouse3 ~/11/data]$ psql -h bighouse3 -p 15432 -U sally testdb
Password for user sally: 
psql (11.8)
Type "help" for help.
testdb=> create table t_sally (id int primary key);
CREATE TABLE
testdb=> \d
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 sales  | t_sally | table | sally
(1 row)


testdb=> select * from t_fiona;
ERROR:  relation "t_fiona" does not exist  -->>>>由于search_path的设置,sally无法查看fiona创建的对象
LINE 1: select * from t_fiona;
                      ^
testdb=> select * from finance.t_fiona;
ERROR:  permission denied for schema finance  -->>>>sally无法访问finance下的对象
LINE 1: select * from finance.t_fiona;
                      ^
[postgres@bighouse3 ~/11/data]$ psql -h bighouse3 -p 15432 -U fiona testdb
Password for user fiona: 
psql (11.8)
testdb=> select * from sales.t_sally;
ERROR:  permission denied for schema sales   ---->>>同理fiona也无法查看到sally创建的对象
LINE 1: select * from sales.t_sally;
                      ^

延伸

严格上讲，上面的步骤也没有做到完全隔离，还是有一些信息，比如对象的名称等还是可以被其它用户看到的。

[postgres@bighouse3 ~/11/data]$ psql -h bighouse3 -p 15432 -U sally testdb
Password for user sally: 
psql (11.8)
Type "help" for help.


testdb=> select * from pg_tables where schemaname in ('finance','sales');
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+------------+------------+----------+-------------+-------------
 finance    | t_fiona   | fiona      |            | t          | f        | f           | f
 sales      | t_sally   | sally      |            | t          | f        | f           | f
(2 rows)

如果要做到完全隔离，就需要对特定的数据字典进行限制，如下：

[postgres@bighouse3 ~/11/data]$ psql -h bighouse3 -p 15432 testdb
testdb=# revoke all on pg_tables from public;
REVOKE


[postgres@bighouse3 ~/11/data]$ psql -h bighouse3 -p 15432 -U sally testdb
testdb=> select * from pg_tables where schemaname in ('finance','sales');
ERROR:  permission denied for view pg_tables
testdb=> \d
        List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 sales  | t_sally | table | sally
(1 row)


-->>>>>类似的数据字典还包括
REVOKE ALL ON pg_user FROM public;
REVOKE ALL ON pg_roles FROM public;
REVOKE ALL ON pg_group FROM public;
REVOKE ALL ON pg_authid FROM public;
REVOKE ALL ON pg_auth_members FROM public;
REVOKE ALL ON pg_database FROM public;
REVOKE ALL ON pg_tablespace FROM public;
REVOKE ALL ON pg_settings FROM public;


-->>>>查看对哪些表有权限可能通过下面2个视图
可以通过information_schema.role_table_grants或information_schema.table_privileges查看。

这篇笔记参考自：PostgreSQL 10 Administration Cookbook

Postgresql多租户实践--使用多个Schema篇

Postgresql多租户实践--使用多个Database篇

Postgresql多租户实践--使用多个Instance篇

postgresql

文章转载自长河的笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Postgresql多租户实践--使用多个Schema篇

评论