PostgreSQL Audit by Database or Role wide parameter

本文基于postgresql 9.2講解.

審計的一個普遍用途就是記錄使用者在資料庫中都幹了些什麼?

如果系統級的參數設定日志全記錄下來當然也可以達到審計的目的, 但是這樣做的話很多不需要的日志也會輸出, 造成硬碟資源浪費甚至影響資料庫性能. 是以精細化的審計非常有必要.

之前寫過一篇關于postgresql借助pg_log_userqueries插件實作使用者或資料庫級審計的文章.

http://blog.163.com/digoal@126/blog/static/1638770402012019112218804/

但是實際上, 使用資料庫本身的配置就可以達到此目的. 主要用到postgresql的以下兩項配置.

1. postgresql 的配置檔案中對于日志的配置項what to log中包含了預設的記錄哪些日志的配置.

   這些配置預設情況下是全局生效的, 不管是哪個使用者連過來, 或者連到哪個資料庫. 都是取的這些配置項.

2. postgresql 提供了使用者級以及資料庫級的參數配置. 

【審計舉例】

假設目前的postgresql.conf log配置項如下 :

1. 審計使用者digoal連到資料庫digoal 的所有sql.

# 檢視目前的資料庫+role級别的配置 : 

# 測試 : 

# 打開一個終端觀察日志的輸出

# 使用digoal使用者連接配接到digoal資料庫, 并執行sql

# select current_user被記錄在日志中了

# digoal使用者連接配接到其他資料庫, 或者其他使用者連接配接到digoal資料庫的sql不會被記錄.

# 以上sql在日志中無記錄.

2. 審計使用者digoal 的所有sql.

# 測試, digoal使用者連接配接到postgres資料庫執行sql

# 日志輸出 : 

3. 審計任何使用者連接配接到資料庫digoal 的所有sql.

# 測試, postgres使用者連接配接到digoal資料庫執行sql

【清除以上審計配置】

【推薦的日志配置】

【其他】

1. 以上審計的粒度可能還是太大, 隻到了使用者和資料庫的層面. 

并且, 僅僅審計到使用者調用的sql, 而不能審計到變化前後的資料, 例如使用者執行一條update語句, 可能變更了整張表的資料, 但是使用log_statement隻能記錄下這條sql, 而不是記錄被變更前後的資料.

如果要對表或者表上面的資料進行審計的話, 需要用上觸發器.

例如 : 

http://blog.163.com/digoal@126/blog/static/163877040201252575529358/

如果不想将這些記錄寫在資料庫中, 而是輸出到日志的話 : 

1.  觸發器中使用raise warning '....' 列印消息.

2.  postgresql.conf 配置 log_min_messages = warning 

這樣的話'....'的消息就會列印到日志中.

具體的例子在另一篇文章中再詳細介紹一下.

3. postgresql 9.3還引入了事件觸發器的功能, 也可以作為審計的外圍工具之一.

感興趣的朋友可以參考

http://blog.163.com/digoal@126/blog/static/16387704020132131361949/

【注意】

1. 超級使用者可以修改這些配置項, 是以被審計的使用者最好是普通使用者. 否則使用者連上來可以修改這些審計項.

2. 程式用的資料庫賬号和個人用的資料庫賬号分開, 對于程式用的資料庫賬号可以隻審計ddl操作, 而對于個人使用的資料庫賬号, 建議審計所有的sql.

【參考】

1. postgresql.conf

2. http://www.postgresql.org/docs/9.2/static/sql-alterrole.html

3. http://www.postgresql.org/docs/9.2/static/sql-alterdatabase.html

4. http://www.postgresql.org/docs/9.2/static/sql-set.html

5. http://www.postgresql.org/docs/9.2/static/runtime-config.html

6. http://blog.163.com/digoal@126/blog/static/163877040201252575529358/

7. http://blog.163.com/digoal@126/blog/static/1638770402012019112218804/

8. http://blog.163.com/digoal@126/blog/static/16387704020132131361949/