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