PostgreSQL 設定預設權限

預先規劃好資料庫權限，有助於安全原則的一致性。
本文提供一個範例，比較容易瞭解 PostgreSQL 的資料庫權限設定。
詳細指令說明請參閱使用手冊。

• ALTER DEFAULT PRIVILEGES
• 到社群討論吧!

目錄

• PostgreSQL 設定預設權限
  • 目錄
  • 情境
  • 範例
    • 建立使用者
    • 建立資料庫並設定權限
    • 設定預設權限
    • 建立 Schema 及並設定預設權限
    • 建立 TABLE
  • 測試
    • 角色 pguser1
    • 角色 pguser2
    • 角色 pgreader
  • pg_dump

情境

• 新資料庫 mydb，僅能讓 pguser1, pguser2 和 pgreader 登入。
• pguser1, pguser2 和 pgreader 都只是資料使用者，無權變更結構。
• pguser1 可以讀取／變更 schema1 所屬的 TABLE 內容。
  • 可能代表某業務A的資料。
• pguser2 可以讀取／變更 schema2 所屬的 TABLE 內容。
  • 可能代表某業務B的資料。
• pgreader 可以讀取所有的 TABLE 內容。
  • 可以讀取所有業務內容，進行綜合分析的帳號。

範例

• 以下指令均使用 postgres 操作

建立使用者

CREATE USER pguser1;
CREATE USER pguser2;
CREATE USER pgreader;

建立資料庫並設定權限

1. 移除資料庫每個使用者都能登入的權限
2. 指定可以登入的使用者

CREATE DATABASE mydb;
REVOKE CONNECT ON DATABASE mydb FROM public;
GRANT CONNECT ON DATABASE mydb TO pguser1, pguser2, pgreader;

設定預設權限

• 預設移除 public 權限，並加入 pgreader 的讀取權限
• 登入資料庫 mydb

REVOKE USAGE ON SCHEMA public FROM public;
ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM public;
ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM public;
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO pgreader;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO pgreader;

建立 Schema 及並設定預設權限

CREATE SCHEMA schema1;
GRANT USAGE ON SCHEMA schema1 TO pguser1;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema1 GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pguser1;
CREATE SCHEMA schema2;
GRANT USAGE ON SCHEMA schema2 TO pguser2;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema2 GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO pguser2;

建立 TABLE

CREATE TABLE schema1.mytable (id INT, value TEXT);
CREATE TABLE schema2.mytable (id INT, value TEXT);

測試

角色 pguser1

1. 切換角色為 pguser1
2. 可以 INSERT schema1.mytable
3. 不可以 INSERT schema2.mytable
4. SELECT OK

mydb=# SET ROLE pguser1;
SET
mydb=> INSERT INTO schema1.mytable (id, value) VALUES (1, 'abc');
INSERT 0 1
mydb=> INSERT INTO schema2.mytable (id, value) VALUES (1, 'abc');
ERROR:  permission denied for schema schema2
LINE 1: INSERT INTO schema2.mytable (id, value) VALUES (1, 'abc');
                    ^
mydb=> SELECT * FROM schema1.mytable;
 id | value
----+-------
  1 | abc
(1 row)

角色 pguser2

1. 切換角色為 pguser2
2. 可以 INSERT schema2.mytable
3. 不可以 INSERT schema1.mytable
4. SELECT OK

mydb=# SET ROLE pguser2;
SET
mydb=> INSERT INTO schema2.mytable (id, value) VALUES (2, 'def');
INSERT 0 1
mydb=> INSERT INTO schema1.mytable (id, value) VALUES (2, 'def');
ERROR:  permission denied for schema schema2
LINE 1: INSERT INTO schema1.mytable (id, value) VALUES (2, 'def');
                    ^
mydb=> SELECT * FROM schema2.mytable;
 id | value
----+-------
  2 | def
(1 row)

角色 pgreader

1. 切換角色為 pgreader
2. 可以 SELECT 兩個 Schema 的 TABLE
3. 但無權更新 TABLE

mydb=# set role pgreader;
SET
mydb=> SELECT * FROM schema1.mytable UNION SELECT * FROM schema2.mytable;
 id | value
----+-------
  2 | def
  1 | abc
(2 rows)

mydb=> UPDATE schema1.mytable SET value='def' WHERE id=1;
ERROR:  permission denied for table mytable

pg_dump

把資料庫結構 dump 出來，看備份時權限會如何還原：

$ pg_dump --schema-only mydb

結果：(去除前面資料庫參數的部份)

--
-- Name: schema1; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA schema1;


ALTER SCHEMA schema1 OWNER TO postgres;

--
-- Name: schema2; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA schema2;


ALTER SCHEMA schema2 OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: mytable; Type: TABLE; Schema: schema1; Owner: postgres
--

CREATE TABLE schema1.mytable (
    id integer,
    value text
);


ALTER TABLE schema1.mytable OWNER TO postgres;

--
-- Name: mytable; Type: TABLE; Schema: schema2; Owner: postgres
--

CREATE TABLE schema2.mytable (
    id integer,
    value text
);


ALTER TABLE schema2.mytable OWNER TO postgres;

--
-- Name: SCHEMA public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA public TO PUBLIC;


--
-- Name: SCHEMA schema1; Type: ACL; Schema: -; Owner: postgres
--

GRANT USAGE ON SCHEMA schema1 TO pgreader;
GRANT USAGE ON SCHEMA schema1 TO pguser1;


--
-- Name: SCHEMA schema2; Type: ACL; Schema: -; Owner: postgres
--

GRANT USAGE ON SCHEMA schema2 TO pgreader;
GRANT USAGE ON SCHEMA schema2 TO pguser2;


--
-- Name: TABLE mytable; Type: ACL; Schema: schema1; Owner: postgres
--

GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE schema1.mytable TO pguser1;
GRANT SELECT ON TABLE schema1.mytable TO pgreader;


--
-- Name: TABLE mytable; Type: ACL; Schema: schema2; Owner: postgres
--

GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE schema2.mytable TO pguser2;
GRANT SELECT ON TABLE schema2.mytable TO pgreader;


--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: schema1; Owner: postgres
--

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA schema1 REVOKE ALL ON TABLES  FROM postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA schema1 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES  TO pguser1;


--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: schema2; Owner: postgres
--

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA schema2 REVOKE ALL ON TABLES  FROM postgres;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA schema2 GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES  TO pguser2;


--
-- Name: DEFAULT PRIVILEGES FOR SCHEMAS; Type: DEFAULT ACL; Schema: -; Owner: postgres
--

ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT USAGE ON SCHEMAS  TO pgreader;


--
-- Name: DEFAULT PRIVILEGES FOR TABLES; Type: DEFAULT ACL; Schema: -; Owner: postgres
--

ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON TABLES  TO pgreader;


--
-- PostgreSQL database dump complete
--