--- title: PostgreSQL 預設權限設定 tags: FAQ --- # PostgreSQL 設定預設權限 預先規劃好資料庫權限,有助於安全原則的一致性。 本文提供一個範例,比較容易瞭解 PostgreSQL 的資料庫權限設定。 詳細指令說明請參閱使用手冊。 - [ALTER DEFAULT PRIVILEGES](https://docs.postgresql.tw/reference/sql-commands/alter-default-privileges) - [到社群討論吧!](https://www.facebook.com/groups/pgsql.tw/?multi_permalinks=2360908527547744) ## 目錄 [toc] ## 情境 - 新資料庫 `mydb`,僅能讓 `pguser1`, `pguser2` 和 `pgreader` 登入。 - `pguser1`, `pguser2` 和 `pgreader` 都只是資料使用者,無權變更結構。 - pguser1 可以讀取/變更 schema1 所屬的 TABLE 內容。 - 可能代表某業務A的資料。 - pguser2 可以讀取/變更 schema2 所屬的 TABLE 內容。 - 可能代表某業務B的資料。 - pgreader 可以讀取所有的 TABLE 內容。 - 可以讀取所有業務內容,進行綜合分析的帳號。 ## 範例 - **以下指令均使用 postgres 操作** ### 建立使用者 ```sql= CREATE USER pguser1; CREATE USER pguser2; CREATE USER pgreader; ``` ### 建立資料庫並設定權限 1. 移除資料庫每個使用者都能登入的權限 2. 指定可以登入的使用者 ```sql= CREATE DATABASE mydb; REVOKE CONNECT ON DATABASE mydb FROM public; GRANT CONNECT ON DATABASE mydb TO pguser1, pguser2, pgreader; ``` ### 設定預設權限 - 預設移除 public 權限,並加入 pgreader 的讀取權限 - **登入資料庫 mydb** ```sql= 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 及並設定預設權限 ```sql= 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 ```sql= 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 ```psql= 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 ```psql= 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 ```psql= 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 出來,看備份時權限會如何還原: ```bash $ pg_dump --schema-only mydb ``` 結果:(去除前面資料庫參數的部份) ```sql= -- -- 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 -- ```