PostgreSql 默認 SCHEMA 再探究

語言: CN / TW / HK

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; ```