PG中只讀帳號的授權操作

• 2019 年 10 月 4 日
• 筆記

日常工作中，我們通常開給研發2個帳號（一個只讀帳號，讀寫帳號）

讀寫帳號自不必說， ，每次用這個帳號建表後，自然就用了CRUD的許可權。

但是，只讀帳號稍微費事點，如果我們處理不好的話，每次新加表都要再執行一次對只讀帳號的重新授權操作。好在PG為我們考慮好了這個場景，也是有方法解決的。

看下面例子：

使用postgres超級帳號登錄PG，創建2個業務帳號

create user  rw login password '123456';   — 創建讀寫帳號

create user rd login password '123456';     — 創建只讀帳號

create database ticket;   — 創建測試用資料庫

alter database ticket owner to rw ;  — 將ticket庫owner改為 rw帳號

c ticket   — 切換到ticket庫下

grant select on all tables in schema public to rd ;   # 對rd帳號授權，當前已有的表的只讀許可權  （注意：這個命令對於當前已有的表生效。對於後期新創建的表，是沒有加其它的授權）

alter role rd  set default_transaction_read_only=true;   — 給rd用戶設置只讀模式

然後，我們使用rw帳號登錄PG：

c ticket

create table t (a int , b int ) ;  — 嘗試創建一張新表

ticket=> select * from  t;   這裡看到是可以執行查詢的

 a | b 

—+—

(0 rows)

然後，再開一個窗口，使用rd帳號登錄PG：

c ticket

ticket=> select * from  t;   這裡看到提示居然沒有查詢的許可權了

這時候，如果我們rd帳號需要訪問t表，則需要使用rw帳號再次執行下 grant SELECT on TABLE t to rd ; 命令，才能將rd帳號授予t表的select授權

更好用的解決方法（即，避免每次新加表後都要執行一次grant授權操作）：

使用rw帳號登錄PG

c ticket 

ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select on tables to rd;    — 對於後期使用rw帳號創建的表， rd帳號都可以讀取的（注意: 如果使用postgres超級帳號創建的表的話，rd帳號還是不能讀取的）

執行完上面的ALTER DEFAULT PRIVILEGES 命令操作後，使用rw帳號再去創建一個新的表，可以看到：

ticket=> create  table tb55(a int, b int);  ticket=> dp tb55                                Access privileges   Schema | Name | Type  | Access privileges | Column privileges | Policies   --------+------+-------+-------------------+-------------------+----------   public | tb55 | table | rw=arwdDxt/rw    +|                   |           |      |       | rd=r/rw           |                   | 

參考： 

        http://postgres.cn/docs/10/sql-alterdefaultprivileges.html

        https://www.postgresql.org/docs/11/runtime-config-client.html