PostgreSQL中search_path参数

PostgreSQL中search_path的使用说明

背景

Qualified names are tedious to write, and it's often best not to wire a particular schema name into applications anyway. Therefore tables are often referred to by *unqualified names*, which consist of just the table name. The system determines which table is meant by following a *search path*, which is a list of schemas to look in. The first matching table in the search path is taken to be the one wanted. If there is no match in the search path, an error is reported, even if matching table names exist in other schemas in the database.

编写限定的名称是很繁琐的，而且通常最好不要把特定的schema名称接入应用程序。因此，表经常被称为非限定名称，它只包括表的名称。系统通过search_path来确定哪张表是指哪张表，search_path是一个要查找的schema列表。search_path中第一个匹配的表被认为是想要的表。如果search_path中没有匹配的表，就会报告错误，即使数据库中其他schema中存在匹配的表名。

The ability to create like-named objects in different schemas complicates writing a query that references precisely the same objects every time. It also opens up the potential for users to change the behavior of other users' queries, maliciously or accidentally. Due to the prevalence of unqualified names in queries and their use in PostgreSQL internals, adding a schema tosearch_patheffectively trusts all users having CREATE privilege on that schema. When you run an ordinary query, a malicious user able to create objects in a schema of your search path can take control and execute arbitrary SQL functions as though you executed them.

在不同的schema中创建类似名称的对象的能力使编写每次都精确引用相同对象的查询变得复杂。它还为用户恶意或意外地改变其他用户的查询行为提供了可能。由于在查询中普遍存在不合格的名称，以及它们在PostgreSQL内部的使用，在search_path中添加一个schema，实际上是信任所有对该schema有CREATE权限的用户。当你运行一个普通的查询时，一个能够在你的search_path的schema中创建对象的恶意用户可以控制并执行任意的SQL函数，就像你执行这些函数一样。

参数描述

search_path中的第一个schema被称为当前schema。除了是第一个被搜索的schema之外，如果CREATE TABLE命令没有指定schema名称，它也是创建新表的schema。

查看参数

要显示当前的search_path，请使用下面的命令。

SHOW search_path。

在默认设置中，这将返回。

postgres=# SHOW search_path ;
  search_path   
-----------------
"$user", public
(1 row)

postgres=# 

第一个值指定一个与当前用户同名的schema被搜索。如果不存在这样的schema，该条目将被忽略。第二个元素指的是我们已经看到的public schema。

search_path中存在的第一个schema是创建新对象的默认位置。这就是默认情况下，对象是在public schema中创建的原因。当对象在没有schema限定的情况下被引用时（表修改、数据修改或查询命令），search_path被遍历，直到找到一个匹配的对象。因此，在默认配置中，任何未经限定的访问又只能引用public schema。

设置参数

为了把我们的新schema放在路径中，我们使用：

SET search_path TO myschema,public;

我们在这里省略了$user，因为我们没有立即需要它，然后我们可以在没有schema限定的情况下访问该表。

DROP TABLE mytable

另外，由于myschema是路径中的第一个元素，新的对象将默认在其中创建。

我们也可以这样写。

SET search_path TO myschema

然后我们就不再能够在没有明确限定的情况下访问public schema了。除了默认存在之外，public schema并没有什么特别之处。它也可以被放弃。

注意事项

1. 数据类型名、函数名和操作符名的作用与对于表名的用法相同。
2. 数据类型和函数名称的限定方式与表名完全相同。
3. 如果你需要在表达式中写一个限定的操作符名称，就需要特定标识。

案例使用

场景1

环境

1、search_path为 "$user", public，

2、连接用户为 postgres

3、存在于用户名相同的 postgres schema

4、创建表 t1

结论

表t1的schema自动为search_path的第一选择postgres

此时，对表t1的任何无前缀操作均在postgres.t1上

且只需\d或\dt t1时，均优先显示postgres schema中的表

示例

postgres=# select current_user;
 current_user 
--------------
 postgres
(1 row)

postgres=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

postgres=# create schema postgres;
CREATE SCHEMA
postgres=# \dn 
   List of schemas
   Name   |  Owner   
----------+----------
 postgres | postgres
 public   | postgres
(2 rows)

postgres=# create table t1 (id int);
CREATE TABLE
postgres=# \dt t1
         List of relations
  Schema  | Name | Type  |  Owner   
----------+------+-------+----------
 postgres | t1   | table | postgres
(1 row)

postgres=# create table public.t1 (id int);
CREATE TABLE
postgres=# \d
                      List of relations
  Schema  |       Name        |       Type        |  Owner   
----------+-------------------+-------------------+----------
 postgres | t1                | table             | postgres

postgres=# \dt t1
         List of relations
  Schema  | Name | Type  |  Owner   
----------+------+-------+----------
 postgres | t1   | table | postgres
(1 row)

postgres=# 

场景2

环境

1、search_path为 "$user", public，

2、连接用户为 postgres

3、没有 postgres schema

4、创建表 t2

结论

表t1的schma自动为search_path的第二选择public

此时，对表t2的任何无前缀操作均在public.t2上。

示例

postgres=# select current_user;
 current_user 
--------------
 postgres
(1 row)

postgres=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

postgres=# \dn 
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

postgres=# create table t2 (id int);
CREATE TABLE
postgres=# \dt t2
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t2   | table | postgres
(1 row)

postgres=# 

场景3

环境

1、创建schema：`myschema

2、设置search_path为myschema

3、创建表t3

4、无前缀查看表t3

结论

根据search_path的设置，无前缀创建及查看的表t3的模式均为myschema

示例

postgres=# select current_user;
 current_user 
--------------
 postgres
(1 row)

postgres=#create schema myschema;
CREATE SCHEMA
postgres=# set search_path to myschema;
SET
postgres=# create table t3 (id int);
CREATE TABLE
postgres=# \dt t3
         List of relations
  Schema  | Name | Type  |  Owner   
----------+------+-------+----------
 myschema | t3   | table | postgres
(1 row)

postgres=#