PostgreSql 默認 SCHEMA 再探究
theme: channing-cyan
這是我參與11月更文挑戰的第6天，活動詳情查看：2021最後一次更文挑戰
為何建議為用户指定默認 SCHEMA
為了不用用户創建對象時，需要顯示地指定 schema，如：
sql
--不需要這樣
CREATE TABLE em.emp(id int);
--可以這樣
CREATE TABLE emp(id int);
PostgreSql中，所有新建的數據庫都會默認自帶一個 public 模式，所有用户均可在這個模式下創建管理自己的對象，且在數據庫中創建表、視圖等基本對象時，若沒有指定模式，這些對象默認也會存放在 pubilc 模式下。（用户名與模式名同名除外）
詳情參考：https://juejin.cn/post/6987199440620781575
上述情況發生的原因
上述原因在於 postgresql 數據庫中的默認搜索路徑：search_path。
```sql oa=# show search_path; search_path
"$user", public (1 row) ``` 觀察上述搜索路徑可看到，用户連入數據庫後首先會找尋跟自己用户名同名的 SCHEMA，若能找到，就它作為當前 schema，後續創建的所有對象都會存入到這個 schema 下。（前提是用户要有訪問這個schema 的權限）
```sql --測試名字與schema名不同的用户，即使有訪問 schema 的權限，連入時也不是當前 schema。 oa=# \dn List of schemas Name | Owner --------+---------- em | postgres op | postgres public | postgres rl | postgres (4 rows) oa=# \c - syd You are now connected to database "oa" as user "syd". oa=> select current_schema; current_schema
public (1 row)
oa=# grant USAGE on schema rl to syd; GRANT oa=# \c - syd You are now connected to database "oa" as user "syd". oa=> select current_schema; current_schema
public (1 row)
--測試與 schema 同名用户，需要有訪問該 schema 的權限，連入時才為當前 schema。（兩種授權訪問 schema 模式的方法） oa=> \c - em You are now connected to database "oa" as user "em". oa=> select current_schema; current_schema
public (1 row)
oa=> \dn List of schemas Name | Owner --------+---------- em | postgres op | postgres public | postgres rl | postgres (4 rows)
--1）更改 schema 的 owner 為這個用户，用户自然就具有了訪問 schema 的權限 oa=> \c - postgres You are now connected to database "oa" as user "postgres". oa=# alter schema em owner to em; ALTER SCHEMA oa=# \dn List of schemas Name | Owner --------+---------- em | em op | postgres public | postgres rl | postgres (4 rows) oa=# \c - em You are now connected to database "oa" as user "em". oa=> select current_schema; current_schema
em (1 row)
--2）直接授予用户訪問 schema 的權限 oa=# \dn List of schemas Name | Owner --------+---------- em | em op | postgres public | postgres rl | rl (4 rows)
oa=# \c - op You are now connected to database "oa" as user "op". oa=> select current_schema; current_schema
public (1 row)
oa=> \c - postgres oa=# grant USAGE on schema op to op; GRANT oa=# \c - op You are now connected to database "oa" as user "op". oa=> select current_schema; current_schema
op (1 row) ```
如何指定上述情況中普通用户 syd 的默認 schema
```sql oa=# alter user syd set search_path to rl; ALTER ROLE oa=# \c - syd You are now connected to database "oa" as user "syd". oa=> \dn List of schemas Name | Owner --------+---------- em | em op | postgres public | postgres rl | rl (4 rows)
oa=> select current_schema; current_schema
rl (1 row) ```
總結
```sql
--設置 apply_owner 用户連入任何數據庫，當前默認 schema 都為 apply(數據庫中沒有這 schema 時,默認 schema 為 public)
alter user apply_owner set search_path to apply;
--設置任何用户連入 chis 數據庫，當前默認 schema 都為 apply
alter database chis set search_path to apply;
--用户名與 schema 同名，且用户具有訪問改 schema 的權限，用户連入數據庫時，默認即為當前 schema
create database chis;
\c chis create schema apply;
create user apply password '11111';
grant USAGE on schema apply to apply; 或 alter schema apply owner to apply; ```