在這篇教學中,我將解釋如何在 PostgreSQL 中管理使用者和權限。

在 PostgreSQL 中,一切都是圍繞著「角色(role)」的概念建立的。

在 macOS 安裝 PostgreSQL 時,安裝腳本會根據你的 macOS 使用者名稱建立一個角色,並且賦予一組權限。

在 PostgreSQL 中沒有使用者,只有角色。

透過在終端機中執行 psql postgres 命令,你會使用你的 macOS 使用者名稱自動登入 PostgreSQL,從而訪問被建立的角色。

在我的情況下,一個名為 flaviocopes 的角色已被建立,你可以使用 \du 命令來查看:

看到了嗎?我預設擁有以下的角色屬性:

  • Superuser
  • Create role
  • Create DB
  • Replication
  • Bypass RLS

而且,我不屬於任何其他角色(稍後會講到這個)。

建立新角色

可以使用 CREATE ROLE 命令來創建新的角色:

CREATE ROLE <role>;

例如:

CREATE ROLE testing;

我們創建了一個新的角色,預設帶有 Cannot login 角色屬性。我們新建的使用者將無法登入。

你可以嘗試輸入 \q 命令退出,然後使用 psql postgres -U testing 命令,但你會看到以下錯誤:

為了解決此問題,我們需要在創建時加上 LOGIN 角色屬性:

CREATE ROLE <role> WITH LOGIN;

如果我們刪除該角色:

DROP ROLE testing;

然後再次使用 WITH LOGIN

CREATE ROLE testing WITH LOGIN;

我們可以看到 testing 角色可以登入,因為這次我們沒有 Cannot login 角色屬性:

試著使用 \q 命令退出,然後使用 psql postgres -U testing 命令:

請注意,提示符=# 變成 =>,因為我們現在沒有 Superuser 角色屬性。

為角色設置密碼

在先前的 CREATE ROLE 命令中,我們創建了一個無密碼的角色。當然,擁有(安全的)密碼非常重要。你可以使用 PASSWORD 關鍵字來設置密碼:

CREATE ROLE <role> WITH LOGIN PASSWORD '<password>';

創建使用者

另一個定義帶有 LOGIN 屬性(實際上創建可以登入的使用者)的角色的替代方法是使用 CREATE USER

CREATE USER <role> PASSWORD '<password>';

為角色設置角色屬性

可以使用 ALTER ROLE 命令將角色屬性後續添加到角色中。

假設我們創建了一個沒有 LOGIN 屬性的角色:

CREATE ROLE <username> PASSWORD '<password>';

我們可以使用以下命令添加:

ALTER ROLE <role> WITH LOGIN;

內建的角色屬性

我們已經見過 LOGIN 角色屬性了,它允許角色登入。

還有其他內建的角色屬性可以使用嗎?

  • LOGIN / NOLOGIN:允許(或不允許)登入 PostgreSQL。
  • SUPERUSER / NOSUPERUSER:允許(或不允許)超級使用者權限。數據庫超級使用者將繞過其他權限檢查,除了 LOGIN(必須單獨授予)。
  • CREATEDB / NOCREATEDB:允許(或不允許)創建新的數據庫的能力。
  • CREATEROLE / NOCREATEROLE:允許(或不允許)創建新角色的能力。
  • CREATEUSER / NOCREATEUSER:允許(或不允許)創建新使用者的能力。
  • INHERIT / NOINHERIT:允許(或不允許)使權限繼承。
  • REPLICATION / NOREPLICATION:授予(或不授予)複製權限(這是一個我們不會涵蓋的高級主題)。

角色組

在 PostgreSQL 中,沒有使用者組。

相反,你可以創建帶有特定權限的角色,然後將這些角色授予其他角色。

如果授予的角色具有 INHERIT 屬性,角色將繼承授予給它的角色的權限。

創建角色組

要創建角色組,輸入:

CREATE ROLE <groupname>;

語法與創建角色相同。

一旦創建了角色組,可以使用 GRANT 命令將角色添加到該角色組中:

GRANT <groupname> TO <role>;

例如,我們可以創建一個名為 flavio 的使用者角色,一個名為「員工」的角色組,並將該使用者分配給該角色組:

CREATE USER flavio PASSWORD 'superSecret123$';
CREATE ROLE employee;
GRANT employee TO flavio;

你可以使用以下命令從角色組中刪除角色:

REVOKE <groupname> FROM <username>;

例如:

REVOKE employee FROM flavio;

角色組屬性

默認情況下,將角色添加到角色組中不會使該角色繼承角色組的屬性(權限)。

你需要在創建時使用 INHERIT 屬性創建角色組。

假設你創建了「員工」角色組,並為其分配 CREATEDB 屬性:

CREATE ROLE employee WITH CREATEDB INHERIT;

現在使用 INHERIT 創建新角色:

CREATE ROLE flavio;
GRANT employee TO flavio;