PostgreSQL【拓展篇】02：schema租户隔离注意事项

一、环境准备

1、环境信息

192.168.184.191 pg-01

2、数据库版本

安装好postgresql14.10，安装可参考：
CentOS7.5【脚本自动编译】安装PostgreSQL14.10

二、schema租户隔离的问题

众所周知，pg在同一个数据库里面可以用Schema进行隔离，不同的Schema允许存在相同表名，但是在\d的时候又无法查出。

1、创建数据库

create database mydb;
\l

2、创建schema

\c mydb;
create schema schema_test1;
create schema schema_test2;
\dn

3、创建表

（1）在默认的public这个schema创建表

不加schema名称默认是在public这个schema下进行创建的

create table t1(id int primary key,name varchar(20));
create table t2(id int primary key,name varchar(20));
create table t3(id int primary key,name varchar(20));

（2）在schema_test1创建表

create table schema_test1.t1(id int primary key,name varchar(20));
create table schema_test1.t2(id int primary key,name varchar(20));
create table schema_test1.t3(id int primary key,name varchar(20));
\d

查询发现还是只能查到public这个schema下的表

（3）在schema_test2创建表

create table schema_test2.t1(id int primary key,name varchar(20));
create table schema_test2.t2(id int primary key,name varchar(20));
create table schema_test2.t3(id int primary key,name varchar(20));
\d

查询发现还是只能查到public这个schema下的表

4、搜索路径

（1）查询搜索路径

show search_path

（2）设置搜索路径

set search_path to public,schema_test1,schema_test2;
show search_path;

（3）再次进行查询表

\d

发现还是只能查到public这个schema下面的表。

（4）在schema为schema_test2创建t4

create table schema_test2.t4(id int primary key,name varchar(20));
\d

（5）在schema为schema_test2创建t5

create table schema_test2.t5(id int primary key,name varchar(20));
\d

（6）结论

是搜索路径的问题，如果在不同的schema下有相同的表名，会安装搜索路径进行显示。

5、其他方法查询

（1）查询pg_stat_user_tables表

SELECT * FROM pg_stat_user_tables;

（2）查询pg_tables表

SELECT * FROM pg_tables;

（3）查询information_schema.tables表

SELECT * FROM information_schema.tables;

（4）指定模式查询

SELECT relname AS table_name
FROM pg_class
WHERE relkind = 'r' AND relnamespace IN (
    SELECT oid FROM pg_namespace WHERE nspname = 'public'
);

SELECT relname AS table_name
FROM pg_class
WHERE relkind = 'r' AND relnamespace IN (
    SELECT oid FROM pg_namespace WHERE nspname = 'schema_test1'
);

SELECT relname AS table_name
FROM pg_class
WHERE relkind = 'r' AND relnamespace IN (
    SELECT oid FROM pg_namespace WHERE nspname = 'schema_test2'
);

三、拓展：psql -E进行分析查询过程

1、psql -E

以psql -E方式进入进行查询

2、进行查询

\c mydb
\d

3、分析这个sql

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 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

4、进行修改

删除下面的判断就能查出来了，这个可能是postgresql内部的设定就是这样的。

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 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
ORDER BY 1,2;