PostgreSQL 模式操作

標籤：訪問   check   系統   view   資料庫   title   指定   ons   lis   

一個PostgreSQL資料庫叢集包含一個或多個已命名資料庫。使用者和使用者組在整個叢集範圍內是共用的，但是其它資料並不共用。任何與伺服器串連的客戶都只能訪問那個在串連請求裡聲明的資料庫。

注意: 叢集中的使用者並不一定要有訪問叢集內所有資料庫的許可權。共用使用者名稱的意思是不能有重名使用者。假定同一個叢集裡有兩個資料庫和一個joe使用者，系統可以配置成只允許joe 訪問其中的一個資料庫。

一個資料庫包含一個或多個已命名的模式，模式又包含表。模式還可以包含其它對象，包括資料類型、函數、操作符等。同一個對象名可以在不同的模式裡使用而不會導致衝突；比如，schema1和myschema都可以包含一個名為mytable的表。和資料庫不同，模式不是嚴格分離的：只要有許可權，一個使用者可以訪問他所串連的資料庫中的任意模式中的對象。

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

• 允許多個使用者使用一個資料庫而不會干擾其它使用者。
• 把資料庫物件組織成邏輯組，讓它們更便於管理。
• 第三方的應用可以放在不同的模式中，這樣它們就不會和其它對象的名字衝突。

模式類似於作業系統層次的目錄，只不過模式不能嵌套。

建立模式

CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ]CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ]CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ]CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name

例子

建立一個模式：

CREATE SCHEMA myschema;

為使用者joe建立模式，模式名也叫joe：

CREATE SCHEMA AUTHORIZATION joe;

建立一個名為test的模式，該模式被使用者joe所擁有， 除非已經有一個名為test的模式。 （這與joe是否擁有早已存在的模式無關。）

CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;

建立一個模式並且在裡面建立一個表：

CREATE SCHEMA hollywood    CREATE TABLE films (title text, release date, awards text[])    CREATE VIEW winners AS        SELECT title, release FROM films WHERE awards IS NOT NULL;

請注意上面獨立的子命令不是由分號結尾的

下面的命令是實現同樣結果的等效語句：

CREATE SCHEMA hollywood;CREATE TABLE hollywood.films (title text, release date, awards text[]);CREATE VIEW hollywood.winners AS    SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;

public模式

當建立一個新的資料庫時，PostgreSQL都會自動建立該模式。當登入到該資料庫時，如果沒有特殊的指定，將以該模式(public)的形式操作各種資料對象，如：

CREATE TABLE products ( ... ) 等同於 CREATE TABLE public.products ( ... )

相容性

SQL 標準允許在CREATE SCHEMA裡面有一個 DEFAULT CHARACTER SET子句以及比目前PostgreSQL 可以接受的更多的子命令。

SQL 標準聲明在CREATE SCHEMA裡的子命令可以以任意順序出現。 目前PostgreSQL裡的實現還不能處理所有子命令裡前向引用的情況； 有時候可能需要重排一下子命令的順序以避免前向引用。

在 SQL 標準裡，模式的所有者總是擁有其中的所有對象。 PostgreSQL允許模式包含非模式所有者擁有的對象。 只有在模式所有者將自己模式的CREATE許可權給了其他人， 或者超級使用者選擇在該模式中建立對象時，才可能出現。IF NOT EXISTS選項是一個PostgreSQL擴充。

修改模式

ALTER SCHEMA name RENAME TO new_nameALTER SCHEMA name OWNER TO new_owner

ALTER SCHEMA修改一個模式的定義。

要使用ALTER SCHEMA，你必須是該模式的所有者。 要給一個模式重新命名，你必須在該資料庫上有CREATE許可權。 要修改所有者，你還必須是新的所有角色的直接或間接成員， 並且該成員必須在此資料庫上有CREATE許可權。 （超級使用者自動擁有全部許可權。）

參數

• name

現有模式的名字。

• new_name

模式的新名字，新名字不能以pg_開頭，因為這些名字是保留給系統模式的。

• new_owner

模式的新所有者。

相容性

SQL標準裡沒有ALTER SCHEMA語句。

刪除模式

DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

DROP SCHEMA從資料庫中刪除模式。

模式只能被它的所有者或者超級使用者刪除。請注意， 所有者即使沒有擁有模式中任何對象也可以刪除模式(以及模式中的所有對象)。

參數

• IF EXISTS

如果指定的模式不存在，那麼發出一個 notice 而不是拋出一個錯誤。

• *name*

模式的名字。

• CASCADE

自動刪除包含在模式中的對象(表、函數等等)。

• RESTRICT

如果模式包含任何對象，則拒絕刪除它。這個是預設。

例子

從資料庫中刪除模式mystuff以及它包含的所有東西：

DROP SCHEMA mystuff CASCADE;

模式許可權

預設時，使用者看不到模式中不屬於他們所有的對象。為了讓他們看得見，模式的所有者需要在模式上賦予USAGE許可權。為了讓使用者使用模式中的對象，我們可能需要賦予額外的許可權，只要是適合該對象的。PostgreSQL根據不同的對象提供了不同的權限類別型，如：

GRANT ALL ON SCHEMA myschema TO public; 

上面的ALL關鍵字將包含CREATE和USAGE兩種許可權。如果public模式擁有了myschema模式的CREATE許可權，那麼登入到該模式的使用者將可以在myschema模式中建立任意對象，如：

CREATE TABLE myschema.products (        product_no integer,        name text,        price numeric CHECK (price > 0),);

在為模式下的所有表賦予許可權時，需要將許可權拆分為各種不同的表操作，如：

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema    GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO public;

在為模式下的所有函數賦予許可權時，僅考慮執行許可權，如：

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema    GRANT EXECUTE ON FUNCTIONS TO public;

撤銷模式許可權可以這麼做

REVOKE [CREATE...] ON SCHEMA public FROM PUBLIC;

可以看出，通過以上方式在public模式下為myschema模式建立各種對象是極為不方便的。下面我們將要介紹另外一種方式，即通過role對象，直接登入並關聯到myschema對象，之後便可以在myschema模式下直接建立各種所需的對象了。

--建立了和該模式關聯的角色對象。CREATE ROLE myschema LOGIN PASSWORD '123456'; --將該模式關聯到指定的角色，模式名和角色名稱可以不相等。CREATE SCHEMA myschema AUTHORIZATION myschema; 

在Linux Shell下，以myschema的角色登入到資料庫MyTest，在密碼輸入正確後將成功登入到該資料庫。

 /> psql -d MyTest -U myschema    Password:    MyTest=> CREATE TABLE test(i integer);    CREATE TABLE    MyTest=> \d   --查看該模式下，以及該模式有許可權看到的tables資訊列表。              List of relations     Schema     |   Name   | Type  |  Owner    ------------+---------+------+----------     myschema |   test     | table  | myschema    (1 rows)

模式搜尋路徑

在使用一個資料庫物件時可以使用它的全稱來定位對象，然而這樣做往往也是非常繁瑣的，每次都不得不鍵入owner_name.object_name。PostgreSQL中提供了模式搜尋路徑，這有些類似於Linux中的$PATH環境變數，當執行一個Shell命令時，只有該命令位於$PATH的目錄列表中，才可以通過命令名直接執行，否則就需要輸入它的全路徑名。PostgreSQL同樣也通過尋找一個搜尋路徑來判斷一個表究竟是哪個表，這個路徑是一個需要尋找的模式列表。在搜尋路徑裡找到的第一個表將被當作選定的表。如果在搜尋路徑中 沒有匹配表，那麼就報告一個錯誤，即使匹配表的名字在資料庫其它的模式中存在也如此。
在搜尋路徑中的第一個模式叫做當前模式。除了是搜尋的第一個模式之外，它還是在CREATE TABLE沒有聲明模式名的時候，建立表所屬於的模式。要顯示當前搜尋路徑，使用下面的命令：

MyTest=> SHOW search_path;      search_path    ----------------     "$user",public    (1 row)

可以將新模式加入到搜尋路徑中，如：

SET search_path TO myschema,public;

為搜尋路徑設定指定的模式，如：

SET search_path TO myschema; --當前搜尋路徑中將只是包含myschema一種模式。

PostgreSQL 模式操作