[PSQL] PostgreSQL 角色權限 Roles and Privileges

PostgreSQL Administration @ PostgreSQL Tutorial

TL;DR

-- create superuser of "postgres"
CREATE ROLE postgres LOGIN SUPERUSER CREATEDB CREATEROLE REPLICATION BYPASSRLS;
GRANT ALL ON ALL TABLES IN SCHEMA "public" TO postgres;

role 是針對 database 操作的權限，例如，能否新增刪除角色、能夠建立新增刪除 database 等等。使用 CREATE ROLE 來建立新角色：

-- 建立角色
CREATE ROLE role_name
LOGIN
SUPERUSER
CREATEDB
CREATEROLE
PASSWORD 'Abcd1234'
CONNECTION LIMIT 1000
VALID UNTIL '2030-01-01';

-- 移除角色
DROP ROLE [IF EXISTS] role_name;

privilege 是針對某一 database 中 table 操作的權限，例如 SELECT, INSERT, DELETE 等等。使用 GRANT 來給予 privilege：

-- 賦予某一 role 所有該 database 內所有的 privilege
GRANT ALL
ON ALL TABLES
IN SCHEMA "public"
TO role_name;

列出所有使用者

-- 在 psql CLI 中可以輸入 \dq
SELECT rolname FROM pg_roles;

在 PostgreSQL 中的「帳號（account）」就是「角色（role）」。角色登入後稱作 login roles 或 users。包含許多 roles 的角色則稱作 group roles。

預設 postgres 是 superuser

Create role with attributes

PostgreSQL CREATE ROLE @ PostgreSQL Tutorial

在 PostgreSQL 中使用「角色（roles）」的概念來代表使用者的帳號，和其他資料庫不同，PostgreSQL 並沒有「使用者（user）」的概念。當 roles 登入後，就稱作 login roles，它就和其他資料庫的使用者概念相同。當一個角色包含許多不同的 roles 時，就稱作「group roles」。

PostgreSQL Roles

圖片來源 PostgreSQL CREATE ROLE @ PostgreSQL Tutorial

建立角色（CREATE ROLE）

-- 建立角色
-- CREATE ROLE role_name;

-- 建立一個名為 developer 的 role
CREATE ROLE developer;

-- 建立帶有 role attribute 的角色
-- CREATE ROLE role_name WITH option;
CREATE ROLE developer [WITH] LOGIN SUPERUSER CREATEDB CREATEROLE;

-- 建立可以 login 的 role
CREATE ROLE alice LOGIN PASSWORD '12345678';

-- 建立可以 login 的 super user role
CREATE ROLE john SUPERUSER LOGIN PASSWORD '12345678';

-- 登入
$ psql -U alice -W postgres

預設建立好的角色是沒有登入（LOGIN）的權限，因此需要先賦予它這個屬性（attribute）。
可以使用的 role attribute。

檢視角色

-- 檢視所有的角色（rolname 的前面是 rol 不是 role！）
SELECT rolname FROM pg_roles;

角色名稱帶有 pg_ 前綴的是系統角色。

# 在 psql 的 shell 中檢視所有角色
$ psql
$> \du      # 檢視所有角色

如果是在 psql 的 CLI 介面中，可以使用 \du 來檢視所有角色

修改角色（ALTER ROLE）

PostgreSQL ALTER ROLE @ postgreSQL tutorial

ALTER ROLE role_name [WITH] option;   -- 修改 role 的權限
ALTER ROLE role_name [RENAME] TO new_name;   -- 修改 role 的名稱

修改某一個 role 的 configurations：

-- 修改 role 的 configuration
-- ALTER ROLE role_name SET param=value;

ALTER ROLE role_name | CURRENT_USER | SESSION_USER | ALL
[IN DATABASE database_name]
SET configuration_param = { value | DEFAULT }

移除角色（DROP ROLE）

DROP ROLE [IF EXISTS] target_role;

但如果這個 role 是有參照到任何資料庫的話或是某個 table 的 owner 的話，將會無法移除，需要先把這個 role 與資料庫的關係解除後才能刪除：

-- execute these statements in the database that contains
-- the object owned by the target role
REASSIGN OWNED BY target_role TO postgres;
DROP OWNED BY target_role;

-- drop the role
DROP ROLE target_role;

GRANT role with privileges

grant @ postgreSQL tutorial

在建立好帶有 LOGIN attribute 的 role 之後，該 role 雖然可以登入 PostgreSQL 的 database server，但它並不能對 database 的 object（例如，tables, views, functions）進行操作。要讓這些 user role 可以對 database 的物件進行操作的話，並需要透過 GRANT 指令賦予它們 privilege。

新增權限（GRANT）

GRANT privilege_list -- 可以使用 ALL 提供所有 privilege
ON  table_name   -- 可以使用 ALL TABLES 來對所有 tables
TO  role_name;

privilege_list 包含 SELECT, INSERT, UPDATE, DELETE, TRUNCATE 或直接使用 ALL 來給予所有 privilege。
ON 表示要針對哪個 table
TO 表示要指定哪個 role 擁有此 privilege

如果想要 grant database 中所有 tables 的 privilege 給該 role 可以使用：

GRANT ALL
ON ALL TABLES
IN SCHEMA "public"
TO role_name;

因此實際的操作流程會是：

使用 postgres 的角色連進去 PostgreSQL database
建立帶有 login attribute 的 role
建立資料表（table）
透過 grant 給予該 role 所需的 privilege

移除權限（REVOKE）

使用 REVOKE 指令可以移除特定角色的權限：

REVOKE privilege | ALL
ON TABLE table_name |  ALL TABLES IN SCHEMA schema_name
FROM role_name;

角色群組（role membership）

role membership @ postgreSQL tutorial

在 PostgreSQL 中，以群組（group）的概念來管理角色會比較容易，如此你可以針對一個群組來賦予（grant）或移除（revoke）權限（privilege），而不用針對個別的角色。

慣例上來說，group role 並不會有 LOGIN 的權限，因此你並不會使用 group role 來登入 PostgreSQL 中
一個帶有 INHERIT 屬性（attribute）的 user role ，會自動享有其 group roles 中的所有 privilege。
一個 user role 可以使用 SET ROLE 的語法來暫時變成 group role，這個 user role 會有該 group role 所擁有的 privileges 而非他原本的 login role。同時，由原本這個 user role 所建立的物件，其擁有者都將變成 group role 而非原本的 user role。

建立 group role 的方式和建立一般 role 的方式完全一樣：

-- CREATE ROLE group_role_name;
CREATE ROLE sales;

接著要把某個別 role 加入到 group role 中，一樣是使用 GRANT：

GRANT group_role to individual_role;

例如 sales 是一個 group role，要把 alice 這個 individual role 加入到 sales 中，只需要使用：

GRANT sales to alice;

如此 alice 就會是 sales 的其中一個 member（可以透過 \du 查看）。

如果要把 individual role 從 group role 中移除，一樣可以使用 REVOKE 指令：

-- REVOKE group_role FROM individual_role;
REVOKE sales FROM alice;

如果要還原某個 individual role 的權限，可以使用：

RESET ROLE;

操作流程

-- 建立名為 jane 的 user role，帶有 INHERIT 和 LOGIN 的屬性
CREATE ROLE jane [WITH] INHERIT LOGIN password 'securePass1';

-- 把對 forecasts table 的 SELECT 權限給 jane
GRANT SELECT ON forecasts TO jane;

-- 建立名為 marketing 的 group role
CREATE ROLE marketing NOINHERIT;

-- 把所有對 forecasts table 的權限（SELECT, INSERT, ...）都給 marketing (group role)
GRANT ALL ON forecasts TO marketing;

-- 把 jane (user role) 加入 marketing (group role) 中
GRANT marketing TO jane;

這時當開發者使用 jane 登入 PostgreSQL 的資料庫時，開發者將會帶有 jane 原本自己的權限，再加上 marketing 的權限，因為 jane 可以繼承（inherit）marketing 的權限

列出所有使用者

List User Roles @ postgreSQL tutorial

如果是在 psql 的 CLI 中可以直接使用 \du 來查看所有的角色，使用 \du+ 則會顯示所有角色的詳細資料。

如果是想用 SQL 的方式，則可以寫：

SELECT usename AS role_name,
  CASE
     WHEN usesuper AND usecreatedb THEN
    CAST('superuser, create database' AS pg_catalog.text)
     WHEN usesuper THEN
     CAST('superuser' AS pg_catalog.text)
     WHEN usecreatedb THEN
     CAST('create database' AS pg_catalog.text)
     ELSE
     CAST('' AS pg_catalog.text)
  END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

權限管理

此段落非原創內容，資料來源為：Golang - 如何當好一個 junior backend developer - PostgreSQL 安裝與權限探討 @ iT 邦鐵人賽

Application Server 對資料庫的權限

Application Server（例如，Golang Gin, Node Express）連接 DB 的帳密不應該擁有 super admin 權限
一般只會允許資料表中 record 的 CRUD，且不會允許去做 Drop Table

建立角色及賦予權限

需要具有 login 權限的使用者才能連結資料庫
可以針對一個 group 來設定權限

-- 資料來源：Golang - 如何當好一個junior backend developer - PostgreSQL安裝與權限探討
-- https://ithelp.ithome.com.tw/articles/10238811

--建立帶有不同權限的「角色」
CREATE ROLE test_admin LOGIN PASSWORD 'admin_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE test_user LOGIN PASSWORD 'user_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE test_readonly LOGIN PASSWORD 'readonly_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE ROLE pjchender LOGIN PASSWORD '12345678' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

-- CREATE DATABASE test_db;
-- ALTER DATABASE test_db OWNER TO test_admin;

privilege

PUBLIC 可以視為當前的「所有角色」
先把所有角色的 USAGE 權限收回，USAGE 可以算是最基本的權限，沒有它也不能做 SELECT, INSERT, UPDATE, DELETE 等等

-- 把所有角色的 USAGE 權限收回
REVOKE USAGE ON SCHEMA public FROM PUBLIC;

GRANT USAGE ON SCHEMA public to meow_admin;
GRANT CREATE ON SCHEMA public to meow_admin;
GRANT USAGE ON SCHEMA public to meow_user;
GRANT USAGE ON SCHEMA public to meow_readonly;

GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLE users to meow_user;
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLE cats  to meow_user;

GRANT SELECT ON TABLE users to meow_readonly;
GRANT SELECT ON TABLE cats  to meow_readonly;

參考資料

PostgreSQL Administration @ PostgreSQL Tutorial
Golang - 如何當好一個 junior backend developer - PostgreSQL 安裝與權限探討 @ iT 邦鐵人賽

TL;DR​

Create role with attributes​

建立角色（CREATE ROLE）​

檢視角色​

修改角色（ALTER ROLE）​

移除角色（DROP ROLE）​

GRANT role with privileges​

新增權限（GRANT）​

移除權限（REVOKE）​

角色群組（role membership）​

操作流程​

列出所有使用者​

權限管理​

Application Server 對資料庫的權限​

建立角色及賦予權限​

privilege​

參考資料​