【PG】PostgreSQL 模式（Schema）

這篇具有很好參考價(jià)值的文章主要介紹了【PG】PostgreSQL 模式（Schema）。希望對大家有所幫助。如果存在錯(cuò)誤或未考慮完全的地方，請大家不吝賜教，您也可以點(diǎn)擊"舉報(bào)違法"按鈕提交疑問。

1 Schema的概念

database schema table之間的關(guān)系?

引入schema的原因

創(chuàng)建Schema

查看Schema

刪除Schema

2 Public Schema

3 Schema的索索路徑

4 Schema 與權(quán)限

5 系統(tǒng)Catalog Schema

6?使用方式（最佳實(shí)踐）

7?可移植性

1 Schema的概念

一個(gè)PostgreSQL數(shù)據(jù)庫集群中包含一個(gè)或更多的數(shù)據(jù)庫。角色和一些其他對象類型被整個(gè)集群共享，連接到服務(wù)器的客戶端只能訪問單個(gè)數(shù)據(jù)庫中的數(shù)據(jù)，在連接請求中指定的那一個(gè)。

一個(gè)數(shù)據(jù)庫包含一個(gè)或多個(gè)模式，模式中包含著表。模式還包含其他類型的命名對象，包括數(shù)據(jù)類型、函數(shù)和操作符。相同的對象名稱可以被用于不同的模式中而不會出現(xiàn)沖突，例如schema1和myschema都可以包含名為mytable的表。和數(shù)據(jù)庫不同，模式并不是被嚴(yán)格地隔離：一個(gè)用戶可以訪問他們所連接的數(shù)據(jù)庫中的所有模式內(nèi)的對象，只要他們有足夠的權(quán)限。

重點(diǎn)：

cluster可以包含多個(gè)database ，每個(gè)database可以包含多個(gè)schema，schema中包含table和其他對象

database schema table之間的關(guān)系?

database
每個(gè)PG服務(wù)可以包含多個(gè)獨(dú)立的database

schema
如果把databases比作一個(gè)國家，那么schema就是一些獨(dú)立的省。大多數(shù)對象是隸屬于某個(gè)schema的，然后schema又隸屬于某個(gè)databases。在創(chuàng)建一個(gè)新的database時(shí)，PG會自動(dòng)為其創(chuàng)建一個(gè)名為public的schema。如果未設(shè)置searc_path變量，那么PG會將你創(chuàng)建的所有對象默認(rèn)放入public schema中。如果表的數(shù)量較少，這是沒問題的，但是如果你有幾千張表，那么我們還是建議你將他們分門別類放入不同的schema中。

表
任何一個(gè)數(shù)據(jù)庫中，表都是最核心的對象類型。在PG中，表首先屬于某個(gè)schema，而schema有屬于某個(gè)database，這樣就構(gòu)成一種三級存儲結(jié)構(gòu)。PG的表支持兩種很強(qiáng)大的功能。第一種是繼承，即一張表可以有父表和子表，這種層次化的結(jié)構(gòu)可以極大的簡化數(shù)據(jù)庫設(shè)計(jì)，還可以為你省掉大量的重復(fù)查詢代碼。第二種是創(chuàng)建一張表的同時(shí)，系統(tǒng)會自動(dòng)為此表創(chuàng)建一種對應(yīng)的自定義數(shù)據(jù)類型。
?

psql 切換schema,PostgreSQL,postgresql

引入schema的原因

允許多個(gè)用戶使用一個(gè)數(shù)據(jù)庫并且不會互相干擾。
將數(shù)據(jù)庫對象組織成邏輯組以便更容易管理。
第三方應(yīng)用的對象可以放在獨(dú)立的模式中，這樣它們就不會與其他對象的名稱發(fā)生沖突。

創(chuàng)建Schema

# 切換到db3數(shù)據(jù)庫
postgres=# \c db3
You are now connected to database "db3" as user "postgres".
db3=#

# 創(chuàng)建schema
db3=# create SCHEMA myschema;
CREATE SCHEMA


# 在myschema中創(chuàng)建表cities
CREATE TABLE db3.myschema.cities (
    name            varchar(80),
    location        point
);


# 創(chuàng)建一個(gè)由其他人所擁有的模式（因?yàn)檫@是將用戶動(dòng)作限制在良定義的名字空間中的方法之一）。其語法是：
db3=# CREATE SCHEMA myschema3 AUTHORIZATION role3;
CREATE SCHEMA
db3=#
db3=#
db3=# \dn
   List of schemas
   Name    |  Owner
-----------+----------
 myschema  | postgres
 myschema2 | postgres
 myschema3 | role3
 public    | postgres
(4 rows)

查看Schema

db3=# \dn
   List of schemas
   Name    |  Owner
-----------+----------
 myschema  | postgres
 myschema2 | postgres
 public    | postgres
(3 rows)

刪除Schema

# 刪除不為空的schema會報(bào)錯(cuò)

db3=# drop schema myschema;
ERROR:  cannot drop schema myschema because other objects depend on it
DETAIL:  table cities depends on schema myschema
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

# 要?jiǎng)h除一個(gè)模式以及其中包含的所有對象，可用

db3=# DROP SCHEMA myschema CASCADE;
NOTICE:  drop cascades to table cities
DROP SCHEMA

2 Public Schema

創(chuàng)建的表如果不指定任何schema名稱。默認(rèn)情況下這些表（以及其他對象）會自動(dòng)的被放入一個(gè)名為“public”的schema中。任何新數(shù)據(jù)庫都包含這樣一個(gè)模式。因此，下面的命令是等效的：

CREATE TABLE products ( ... );
CREATE TABLE public.products ( ... );

3 Schema的索索路徑

搜索路徑中的第一個(gè)schema被稱為當(dāng)前schema。除了是第一個(gè)被搜索的schema外，如果CREATE TABLE命令沒有指定schema名，它將是新創(chuàng)建表所在的schema。

# 顯示當(dāng)前的搜索路徑
db3=# SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

第一個(gè)元素說明一個(gè)和當(dāng)前用戶同名的schema會被搜索。如果不存在這個(gè)schema，該項(xiàng)將被忽略。第二個(gè)元素指向我們已經(jīng)見過的public schema。

搜索路徑中的第一個(gè)模式是創(chuàng)建新對象的默認(rèn)存儲位置。這就是默認(rèn)情況下對象會被創(chuàng)建在公共模式中的原因。當(dāng)對象在任何其他沒有模式限定的環(huán)境中被引用（表修改、數(shù)據(jù)修改或查詢命令）時(shí)，搜索路徑將被遍歷直到一個(gè)匹配對象被找到。因此，在默認(rèn)配置中，任何非限定訪問將只能指向公共模式。

# 修改搜索路徑
SET search_path TO myschema,public;

SET search_path TO myschema;

4 Schema 與權(quán)限

5 系統(tǒng)Catalog Schema

除public和用戶創(chuàng)建的模式之外，每一個(gè)數(shù)據(jù)庫還包括一個(gè)pg_catalog模式，它包含了系統(tǒng)表和所有內(nèi)建的數(shù)據(jù)類型、函數(shù)以及操作符。pg_catalog總是搜索路徑的一個(gè)有效部分。如果沒有在路徑中顯式地包括該模式，它將在路徑中的模式?之前?被搜索。這保證了內(nèi)建的名稱總是能被找到。然而，如果我們希望用用戶定義的名稱重載內(nèi)建的名稱，可以顯式的將pg_catalog放在搜索路徑的末尾。

由于系統(tǒng)表名稱以pg_開頭，最好還是避免使用這樣的名稱，以避免和未來新版本中可能出現(xiàn)的系統(tǒng)表名發(fā)生沖突。系統(tǒng)表將繼續(xù)采用以pg_開頭的方式，這樣它們不會與非限制的用戶表名稱沖突。

6?使用方式（最佳實(shí)踐）

模式能夠以多種方式組織數(shù)據(jù)。一個(gè)安全模式使用方式可以防止不受信任的用戶更改其他用戶的查詢行為。當(dāng)數(shù)據(jù)庫沒有使用安全模式使用方式時(shí)，希望安全地查詢該數(shù)據(jù)庫的用戶將在每個(gè)會話開始時(shí)采取保護(hù)操作。具體的說，他們開始會話前會設(shè)置search_path為空字符串，或者刪除search_path中非超級用戶可寫的模式。以下的一些使用方式在默認(rèn)配置下可以很容易實(shí)現(xiàn)。

將普通用戶約束在其私有的方案中。要實(shí)現(xiàn)這一點(diǎn)，執(zhí)行REVOKE CREATE ON SCHEMA public FROM PUBLIC，并且為每一個(gè)用戶創(chuàng)建一個(gè)用其用戶名命名的模式?；叵胍幌?，默認(rèn)搜索路徑開頭的$user會解析為用戶名。因此，如果每個(gè)用戶都有單獨(dú)的模式，則默認(rèn)情況下他們訪問自己的模式。在不受信任的用戶已經(jīng)登錄的數(shù)據(jù)庫中采用此使用方式后，請考慮檢查公共模式中名字與模式pg_catalog中對象相類似的對象。此方式是一種安全模式的使用方式，除非不受信任的用戶是數(shù)據(jù)庫所有者或擁有CREATEROLE權(quán)限，在這種情況下將不存在沒有安全模式使用方式。
從默認(rèn)搜索路徑中刪除公共模式，通過修改postgresql.conf或執(zhí)行ALTER ROLE ALL SET search_path ="$user"。每一個(gè)角色都仍然保留在公共模式中創(chuàng)建對象的能力，但是只有符合的名稱才會選擇到這些對象。雖然符合的的表引用是安全的，但是調(diào)用公共模式中的函數(shù)會是不安全或不可靠的。如果要在公共模式中創(chuàng)建函數(shù)或擴(kuò)展，請改用第一個(gè)方式。否則，與第一個(gè)使用方式一樣，這是安全的，除非不受信任的用戶是數(shù)據(jù)庫所有者或擁有CREATEROLE權(quán)限。
保持默認(rèn)。所有用戶都隱式地訪問公共模式。這模擬了根本沒有使用模式的情況，可以用于從無模式的世界平滑過渡。但是，這絕不是一個(gè)安全的使用方式。只有當(dāng)數(shù)據(jù)庫僅有單個(gè)用戶或者少數(shù)相互信任的用戶時(shí)，才可以接受。

對于任何一種模式，如果要安裝共享的應(yīng)用（所有人都要用的表、第三方提供的額外函數(shù)，等等），可把它們放在單獨(dú)的模式中。記得授予適當(dāng)?shù)臋?quán)限以允許其他用戶訪問它們。然后用戶可以通過以模式名限定名稱的方式來引用這些額外的對象，或者他們可以把額外的模式放在自己的搜索路徑中。文章來源地址http://www.zghlxwxcb.cn/news/detail-755167.html