第 7 課 PostgreSQL 數(shù)據(jù)庫、模式款筑、表容达、空間、用戶間的關(guān)系

什么是schema?

這里只討論數(shù)據(jù)庫中的schema，而不討論XML中的schema谍倦。在wiki上塞赂，這樣解釋schema：

In a relational database, the schema defines the tables, views, indexes, packages,procedures, functions, queues, triggers, types, sequences, materialized views, synonyms,database links, directories, Java, XML schemas, and other elements.

而實際上，schema就是數(shù)據(jù)庫對象的集合昼蛀。

我們先來看一下schema的定義：

A schema is a collection of database objects (used by a user.).Schema objects are the logical structures that directly refer to the database’s data.A user is a name defined in the database that can connect to and access objects.schema and users help database administrators manage database security.

從定義中我們可以看出schema為數(shù)據(jù)庫對象的集合宴猾，為了區(qū)分各個集合圆存，我們需要給這個集合起個名字，這些名字就是我們在企業(yè)管理器的方案下看到的許多類似用戶名的節(jié)點仇哆，這些類似用戶名的節(jié)點其實就是一個schema沦辙，schema里面包含了各種對象如tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links。

一個用戶一般對應(yīng)一個schema讹剔，該用戶的schema名等于用戶名油讯，并作為該用戶缺省schema。這也就是我們在企業(yè)管理器的方案下看到schema名都為數(shù)據(jù)庫用戶名的原因延欠。Oracle數(shù)據(jù)庫中不能新創(chuàng)建一個schema陌兑，要想創(chuàng)建一個schema，只能通過創(chuàng)建一個用戶的方法解決(Oracle中雖然有create schema語句由捎，但是它并不是用來創(chuàng)建一個schema的)兔综，在創(chuàng)建一個用戶的同時為這個用戶創(chuàng)建一個與用戶名同名的schema并作為該用戶的缺省schema。即schema的個數(shù)同user的個數(shù)相同狞玛，而且schema名字同user名字一一對應(yīng)并且相同软驰，所有我們可以稱schema為user的別名，雖然這樣說并不準(zhǔn)確心肪，但是更容易理解一些碌宴。

一個用戶有一個缺省的schema，其schema名就等于用戶名蒙畴，當(dāng)然一個用戶還可以使用其他的schema。如果我們訪問一個表時呜象，沒有指明該表屬于哪一個schema中的膳凝，系統(tǒng)就會自動給我們在表上加上缺省的schema名。比如我們在訪問數(shù)據(jù)庫時恭陡，訪問freeoa用戶下的temp表蹬音，通過select * from temp; 其實，這sql語句的完整寫法為select * from freeoa.temp休玩。在數(shù)據(jù)庫中一個對象的完整名稱為schema.object著淆，而不屬user.object。類似如果我們在創(chuàng)建對象時不指定該對象的schema拴疤，在該對象的schema為用戶的缺省schema永部。這就像一個用戶有一個缺省的表空間，但是該用戶還可以使用其他的表空間呐矾，如果我們在創(chuàng)建對象時不指定表空間苔埋，則對象存儲在缺省表空間中，要想讓對象存儲在其他表空間中蜒犯，我們需要在創(chuàng)建對象時指定該對象的表空間组橄。

為什么schema有存在的必要荞膘？

為了區(qū)分各個集合，我們需要給這個集合起個名字玉工，其實這個名字就是schema羽资。

舉例說明：

訪問freeoa用戶下的temp表，通過select from temp 其實這條sql語句的完整寫法為select from freeoa.temp遵班。對于數(shù)據(jù)庫來說屠升，不同的用戶，有不同schema费奸。有不同的表弥激。實際在使用上，schema和user完全一樣愿阐，沒有什么區(qū)別微服，在出現(xiàn)schema名的地方也可以出現(xiàn)user名。

查看schema


postgres=# \dn

  List of schemas

  Name  |  Owner 

--------+----------

public | postgres

(1 row)

什么是表空間缨历？

表空間是實際的數(shù)據(jù)存儲的地方以蕴。一個數(shù)據(jù)庫schema可能存在于多個表空間，相似地辛孵，一個表空間也可以為多個schema服務(wù)丛肮。


postgres=# select * from pg_tablespace;

  spcname  | spcowner | spcacl | spcoptions

------------+----------+--------+------------

pg_default |      10 |        |

pg_global  |      10 |        |

(2 rows)

表空間的作用：

通過使用表空間，管理員可以控制磁盤的布局魄缚。表空間的最常用的作用是優(yōu)化性能宝与，例如，一個最常用的索引可以建立在非骋逼ィ快的硬盤上习劫，而不太常用的表可以建立在便宜的硬盤上，比如用來存儲用于進行歸檔文件的表嚼隘。

PostgreSQL表空間诽里、數(shù)據(jù)庫、模式飞蛹、表谤狡、用戶、角色之間的關(guān)系

角色與用戶的關(guān)系

在PostgreSQL中卧檐，存在兩個容易混淆的概念：角色/用戶墓懂。之所以說這兩個概念容易混淆，是因為對于PostgreSQL來說泄隔，這是完全相同的兩個對象拒贱。唯一的區(qū)別是在創(chuàng)建的時候：

我用下面的psql創(chuàng)建了角色freeoa：

CREATE ROLE freeoa PASSWORD 'freeoa';

接著我使用新創(chuàng)建的角色freeoa登錄，PostgreSQL給出拒絕信息：

FATAL：role 'freeoa' is not permitted to log in.

說明該角色沒有登錄權(quán)限，系統(tǒng)拒絕其登錄逻澳。

我又使用下面的psql創(chuàng)建了用戶freeoa2:

CREATE USER freeoa2 PASSWORD 'freeoa2';

接著我使用freeoa2登錄闸天，登錄成功。難道這兩者有區(qū)別嗎斜做？查看文檔苞氮，又這么一段說明：

"CREATE USER is the same as CREATE ROLE except that it implies LOGIN."----CREATE USER除了默認(rèn)具有LOGIN權(quán)限之外，其他與CREATE ROLE是完全相同的瓤逼。

為了驗證這句話笼吟，修改freeoa的權(quán)限，增加LOGIN權(quán)限：ALTER ROLE freeoa LOGIN;再次用freeoa登錄霸旗，成功贷帮！那么事情就明了了：CREATE ROLE freeoa PASSWORD 'freeoa' LOGIN 等同于CREATE USER freeoa PASSWORD 'freeoa'.這就是ROLE/USER的區(qū)別。

數(shù)據(jù)庫與模式的關(guān)系

模式(schema)是對數(shù)據(jù)庫(database)邏輯分割诱告。

在數(shù)據(jù)庫創(chuàng)建的同時撵枢，就已經(jīng)默認(rèn)為數(shù)據(jù)庫創(chuàng)建了一個模式--public，這也是該數(shù)據(jù)庫的默認(rèn)模式精居。所有為此數(shù)據(jù)庫創(chuàng)建的對象(表锄禽、函數(shù)、試圖靴姿、索引沃但、序列等)都是常見在這個模式中的：

1.創(chuàng)建一個數(shù)據(jù)庫dba----CREATE DATABASE dba;

2.用freeoa角色登錄到dbtt數(shù)據(jù)庫,查看dbtt數(shù)據(jù)庫中的所有模式：\dn; 顯示結(jié)果是只有public一個模式。

3.創(chuàng)建一張測試表----CREATE TABLE test(id integer not null);

4.查看當(dāng)前數(shù)據(jù)庫的列表：\d; 顯示結(jié)果是表test屬于模式public.也就是test表被默認(rèn)創(chuàng)建在了public模式中佛吓。

5.創(chuàng)建一個新模式freeoa宵晚，對應(yīng)于登錄用戶freeoa：CREATE SCHEMA freeoa OWNER freeoa;

6.再次創(chuàng)建一張test表，這次這張表要指明模式----CREATE TABLE freeoa.test (id integer not null);

7.查看當(dāng)前數(shù)據(jù)庫的列表：\d; 顯示結(jié)果是表test屬于模式freeoa.也就是這個test表被創(chuàng)建在了freeoa模式中维雇。

得出結(jié)論是：數(shù)據(jù)庫是被模式(schema)來切分的坝疼，一個數(shù)據(jù)庫至少有一個模式，所有數(shù)據(jù)庫內(nèi)部的對象(object)是被創(chuàng)建于模式的谆沃。用戶登錄到系統(tǒng)，連接到一個數(shù)據(jù)庫后仪芒，是通過該數(shù)據(jù)庫的search_path來尋找schema的搜索順序唁影，可以通過命令SHOW search_path；具體的順序掂名，也可以通過SET search_path TO 'schema_name'來修改順序据沈。

官方建議是這樣的：在管理員創(chuàng)建一個具體數(shù)據(jù)庫后，應(yīng)該為所有可以連接到該數(shù)據(jù)庫的用戶分別創(chuàng)建一個與用戶名相同的模式饺蔑，然后將search_path設(shè)置為"$user"锌介，這樣，任何當(dāng)某個用戶連接上來后，會默認(rèn)將查找或者定義的對象都定位到與之同名的模式中孔祸。對于一個應(yīng)用程序分很多相對獨立的模塊隆敢，每個模塊有相對獨立的數(shù)據(jù)結(jié)構(gòu)，可以采用每個模塊一個數(shù)據(jù)庫用戶及與其名字相同的schema來組織數(shù)據(jù)庫崔慧，并且整個的物理數(shù)據(jù)庫放在一個單獨的表空間中拂蝎。使用這種數(shù)據(jù)庫管理模式，可以撤銷掉對public schema的訪問許可惶室，甚至把public schema直接移除温自，這樣每個用戶就真正的限定在了他們自己的schema里。

表空間與數(shù)據(jù)庫的關(guān)系

數(shù)據(jù)庫創(chuàng)建語句CREATE DATABASE dbname 默認(rèn)的數(shù)據(jù)庫所有者是當(dāng)前創(chuàng)建數(shù)據(jù)庫的角色皇钞，默認(rèn)的表空間是系統(tǒng)的默認(rèn)表空間--pg_default悼泌。

為什么是這樣的呢？因為在PostgreSQL中夹界，數(shù)據(jù)的創(chuàng)建是通過克隆數(shù)據(jù)庫模板來實現(xiàn)的馆里，這與SQL SERVER是同樣的機制。由于CREATE DATABASE dbname并沒有指明數(shù)據(jù)庫模板掉盅，所以系統(tǒng)將默認(rèn)克隆template1數(shù)據(jù)庫也拜，得到新的數(shù)據(jù)庫dbname。(By default, the new database will be created by cloning the standard system database template1).

而template1數(shù)據(jù)庫的默認(rèn)表空間是pg_default趾痘，這個表空間是在數(shù)據(jù)庫初始化時創(chuàng)建的慢哈，所以所有template1中的對象將被同步克隆到新的數(shù)據(jù)庫中。相對完整的語法應(yīng)該是這樣的：CREATE DATABASE dbname OWNER freeoa TEMPLATE template1 TABLESPACE tablespacename;

1.連接到template1數(shù)據(jù)庫永票，創(chuàng)建一個表作為標(biāo)記：CREATE TABLE tbl_flag(id integer not null);向表中插入數(shù)據(jù)INSERT INTO tbl_flag VALUES (1);

2.創(chuàng)建一個表空間:CREATE TABLESPACE tsfreeoa OWNER freeoa LOCATION '/tmp/data/tsfreeoa';在此之前應(yīng)該確保目錄/tmp/data/tsfreeoa存在卵贱，并且目錄為空。

3.創(chuàng)建一個數(shù)據(jù)庫侣集，指明該數(shù)據(jù)庫的表空間是剛剛創(chuàng)建的tsfreeoa：CREATE DATABASE dbfreeoa TEMPLATE template1 OWNERE freeoa TABLESPACE tsfreeoa;

4.查看系統(tǒng)中所有數(shù)據(jù)庫的信息：\l键俱；可以發(fā)現(xiàn)，dbfreeoa數(shù)據(jù)庫的表空間是tsfreeoa,擁有者是freeoa;

5.連接到dbfreeoa數(shù)據(jù)庫世分，查看所有表結(jié)構(gòu):\d;可以發(fā)現(xiàn)编振，在剛創(chuàng)建的數(shù)據(jù)庫中居然有了一個表tbl_flag,查看該表數(shù)據(jù)，輸出結(jié)果一行一列臭埋，其值為1踪央，說明，該數(shù)據(jù)庫的確是從template1克隆而來瓢阴。

仔細(xì)分析后畅蹂，不難得出結(jié)論：在PostgreSQL中，表空間是一個目錄荣恐，里面存儲的是它所包含的數(shù)據(jù)庫的各種物理文件液斜。

總結(jié)一下它們之間的關(guān)系

表空間是一個存儲區(qū)域累贤，在一個表空間中可以存儲多個數(shù)據(jù)庫，盡管PostgreSQL不建議這么做少漆，但我們這么做完全可行臼膏。一個數(shù)據(jù)庫并不知直接存儲表結(jié)構(gòu)等對象的，而是在數(shù)據(jù)庫中邏輯創(chuàng)建了至少一個模式检疫，在模式中創(chuàng)建了表等對象讶请，將不同的模式指派該不同的角色，可以實現(xiàn)權(quán)限分離屎媳，又可以通過授權(quán)夺溢，實現(xiàn)模式間對象的共享，并且還有一個特點就是：public模式可以存儲大家都需要訪問的對象烛谊。

既然一個表在創(chuàng)建的時候可以指定表空間风响，那么是否可以給一個表指定它所在的數(shù)據(jù)庫表空間之外的表空間呢？

答案是肯定的丹禀，這么做完全可以：那這不是違背了表屬于模式状勤，而模式屬于數(shù)據(jù)庫，數(shù)據(jù)庫最終存在于指定表空間這個網(wǎng)的模型了嗎双泪？

是的持搜，看上去這確實是不合常理的，但這么做又是有它的道理的焙矛，而且現(xiàn)實中葫盼，我們往往需要這么做：將表的數(shù)據(jù)存在一個較慢的磁盤上的表空間，而將表的索引存在于一個快速的磁盤上的表空間村斟。但我們再查看表所屬的模式還是沒變的贫导，它依然屬于指定的模式。所以這并不違反常理蟆盹。實際上PostgreSQL并沒有限制一張表必須屬于某個特定的表空間孩灯，我們之所以會這么認(rèn)為，是因為在關(guān)系遞進時逾滥，偷換了一個概念：模式是邏輯存在的峰档，它不受表空間的限制。

表空間寨昙、數(shù)據(jù)庫面哥、角色、模式及表之間的關(guān)系

表空間用于定義數(shù)據(jù)庫對象在物理存儲設(shè)備上的位置毅待，不特定于某個單獨的數(shù)據(jù)庫。數(shù)據(jù)庫是數(shù)據(jù)庫對象的物理集合归榕，而schema則是數(shù)據(jù)庫內(nèi)部用于組織管理數(shù)據(jù)庫對象的邏輯集合尸红，schema名字空間之下則是各種應(yīng)用程序會接觸到的對象，比如表、索引外里、數(shù)據(jù)類型怎爵、函數(shù)、操作符等盅蝗。

角色(用戶)則是數(shù)據(jù)庫服務(wù)器(集群)全局范圍內(nèi)的權(quán)限控制系統(tǒng)鳖链，用于各種集群范圍內(nèi)所有的對象權(quán)限管理。因此角色不特定于某個單獨的數(shù)據(jù)庫墩莫，但角色如果需要登錄數(shù)據(jù)庫管理系統(tǒng)則必須連接到一個數(shù)據(jù)庫上芙委。角色可以擁有各種數(shù)據(jù)庫對象。對模式還是有疑問狂秦，那繼續(xù)讀下面的章節(jié)吧灌侣。

再說一遍模式(Schema)

一個 PostgreSQL 數(shù)據(jù)庫集群包含一個或多個命名的數(shù)據(jù)庫。用戶和用戶組在整個集群的范圍內(nèi)是共享的裂问，但是其它數(shù)據(jù)并不是共享的侧啼。任何給定的與服務(wù)器的客戶連接都只能訪問在一個數(shù)據(jù)庫里的數(shù)據(jù)，就是那個在連接請求里聲明的堪簿。

注意：一個集群的用戶并不一定要有訪問集群內(nèi)所有數(shù)據(jù)庫的權(quán)限痊乾。共享用戶名的意思是不能有同名用戶，也就是在同一個集群里的兩個數(shù)據(jù)庫里都有叫 oafree 的用戶椭更，但是系統(tǒng)可以配置成只允許 oafree 訪問某些數(shù)據(jù)庫哪审。

一個數(shù)據(jù)庫包含一個或多個命名的模式，模式又包含表甜孤。模式還包含其它命名的對象协饲，包括數(shù)據(jù)類型、函數(shù)缴川、序列以及操作符茉稠。同一個對象名可以在不同的模式里使用而不會導(dǎo)致沖突；比如把夸，schema1 和 myschema 都可以包含叫做 mytable 的表而线。和數(shù)據(jù)庫不同，模式不是嚴(yán)格分離的：一個用戶可以訪問他所連接的數(shù)據(jù)庫中的任意模式中的對象恋日，只要他有權(quán)限膀篮。

我們需要模式的原因有好多：

允許多個用戶使用一個數(shù)據(jù)庫而不會干擾其它用戶。

把數(shù)據(jù)庫對象組織成邏輯組岂膳，讓它們更便于管理誓竿。

第三方的應(yīng)用可以放在不同的模式中，這樣它們就不會和其它對象的名字沖突谈截。

模式類似于操作系統(tǒng)層次的目錄筷屡，只不過模式不能嵌套涧偷。

創(chuàng)建一個schema

創(chuàng)建一個模式(schema)使用CREATE SCHEMA命令，如：

create schema freeoa_schema;

在指定模式里創(chuàng)建表毙死，如：

CREATE TABLE myschema.mytable (

...

);

刪除一個空的schema燎潮，如：

drop schema myschema;

刪除一個模式以及模式里面所有的對象，如：

drop schema myschema CASCADE;

pulic schema(public 模式)

在創(chuàng)建表時扼倘，如果沒有指定schema确封，則表會自動被歸屬到一個叫做'public‘的模式中，每一個數(shù)據(jù)庫中都會有一個這樣的模式再菊，它會自動創(chuàng)建爪喘。下面兩種創(chuàng)建表的方式是等效的：

CREATE TABLE tableName(...);

和

CREATE TABLE public.tableName(...);

模式和權(quán)限

缺省時，用戶看不到模式中不屬于他們所有的對象袄简。為了讓他們看得見腥放，模式的所有者需要在模式上賦予 USAGE 權(quán)限。為了讓用戶使用模式中的對象绿语，我們可能需要賦予額外的權(quán)限秃症，只要是適合該對象的。

用戶也可以允許在別人的模式里創(chuàng)建對象吕粹。要允許這么做种柑，我們需要賦予在該模式上的 CREATE 權(quán)限。請注意匹耕，缺省每個人都在 public 模式上有 CREATE 權(quán)限聚请。這樣就允許所有可以連接到指定數(shù)據(jù)庫上的用戶在這里創(chuàng)建對象。如果你不允許這么做稳其，你可以撤銷這個權(quán)限：

REVOKE CREATE ON public FROM PUBLIC;

(第一個 "public" 是模式驶赏，第二個 "public" 意思是"所有用戶"。第一句里它是個標(biāo)識符既鞠，而第二句里是個關(guān)鍵字煤傍，所以有不同的大小寫)

模式搜索路徑

全稱的名字寫起來非常費勁，并且我們最好不要在應(yīng)用里直接寫上特定的模式名嘱蛋。因此蚯姆，表通常都是用未修飾的名字引用的，這樣的名字里只有表名字洒敏。系統(tǒng)通過查找一個搜索路徑來判斷一個表究竟是哪個表龄恋，這個路徑是一個需要查找的模式列表。在搜索路徑里找到的第一個表將被當(dāng)作選定的表凶伙。如果在搜索路徑中沒有匹配表郭毕，那么就報告一個錯誤，即使匹配表的名字在數(shù)據(jù)庫其它的模式中存在也如此函荣。

在搜索路徑中的第一個模式叫做當(dāng)前模式显押。除了是搜索的第一個模式之外链韭，它還是在 CREATE TABLE 沒有聲明模式名的時候，新建表所在的地方煮落。這個與shell的PATH變量很相似。

查看當(dāng)前搜索路徑踊谋，使用命令：

SHOW search_path;

在缺省的設(shè)置中蝉仇，返回下面的東西：

search_path

$user,public

第一個值聲明將要搜索一個和當(dāng)前用戶同名的模式。因為還沒有這樣的模式存在殖蚕，所以這條記錄被忽略轿衔。第二個值指向public模式。

要把新的模式放到路徑中來睦疫，我們用

SET search_path TO myschema,public;

使用方式

模式可以以多種方式組織你的數(shù)據(jù)害驹。下面是一些建議使用的模式，它們也很容易在缺省配置中得到支持：

如果你沒有創(chuàng)建任何模式蛤育，那么所有用戶隱含都訪問public模式宛官。這樣就模擬了還沒有模式的時候的情景。這種設(shè)置建議主要用在只有一個用戶或者數(shù)據(jù)庫里只有幾個合作用戶的情形瓦糕。這樣的設(shè)置也允許我們平滑地從無模式的環(huán)境過渡底洗。

你可以為每個用戶創(chuàng)建一個模式，名字和用戶相同咕娄。要記得缺省的搜索路徑從$user開始亥揖，它會解析為用戶名。如果每個用戶都有一個獨立的模式圣勒，那么他們?nèi)笔r訪問他們自己的模式费变。如果你使用了這樣的設(shè)置，那么你可能還想撤銷對public模式的訪問(或者一并刪除)圣贸，因此用戶就真的限制于他們自己的模式中了挚歧。

要安裝共享的應(yīng)用(被所有人使用的表，第三方提供的額外的函數(shù)等等)旁趟，可以把它們放到獨立的模式中昼激，只要記得給訪問它們的用戶賦予合適的權(quán)限就可以了。然后用戶就可以通過用一個模式名修飾這些名字來使用這些額外的對象锡搜，或者可以把額外的模式放到他們的搜索路徑中橙困，由用戶來定。