給postgresql的登陸使用者佈建唯讀模式

標籤：postgresql   登陸使用者   設定   

一.說明:

讓資料庫變成唯讀模式，目前PostgreSQL沒有嚴格意義上的唯讀模式（如暫存資料表在唯讀事務中還是可以使用的）。通過調整參數或設定事務模式可以將後續登入的SESSION或者當前事務設定為唯讀模式。

在唯讀模式下,PostgreSQL不允許如下SQL:

When a transaction is read-only, the following SQL commands are disallowed: INSERT, UPDATE, DELETE, and COPY FROM if the table they would write to is not a temporary table; all CREATE, ALTER, and DROP commands; COMMENT, GRANT, REVOKE, TRUNCATE; and EXPLAIN ANALYZE and EXECUTE if the command they would execute is among those listed. This is a high-level notion of read-only that does not prevent all writes to disk.

上述描述引用地址：

http://blog.163.com/[email protected]/blog/static/163877040201111821118906/

二.給postgresql的登陸使用者佈建唯讀模式:

1.設定登陸資料庫的使用者為唯讀模式：

[[email protected] ~]$ psql -Uuser001 -dtestdb01 -p19086 -h127.0.0.1

Password for user user001: 

psql.bin (9.5.9)

Type "help" for help.

testdb01=> 

testdb01=> alter user user001 set default_transaction_read_only=on;（資料庫不需要重啟也永久生效）

ALTER ROLE

testdb01=> create database test001;

ERROR:  permission denied to create database

testdb01=> show default_transaction_read_only;

 default_transaction_read_only 

-------------------------------

 off

(1 row)

上述的參數設定，即使是重啟資料庫剛才設定的唯讀模式也是生效的：

pg_ctl -D /data/postgresql/data -l /data/postgresql/log/postgres.log stop

pg_ctl -D /data/postgresql/data -l /data/postgresql/log/postgres.log start

[[email protected] ~]$ psql -Uuser001 -dtestdb01 -p19086 -h127.0.0.1

Password for user user001: 

psql.bin (9.5.9)

Type "help" for help.

testdb01=> show default_transaction_read_only;

 default_transaction_read_only 

-------------------------------

 on

(1 row)

testdb01=> create database test001;

ERROR:  cannot execute CREATE DATABASE in a read-only transaction

2.設定關閉session層級的唯讀模式（當然在退出資料庫sql互動視窗的時候設定的模式會失效):

testdb01=> set session default_transaction_read_only=off;

SET

testdb01=> show default_transaction_read_only;

 default_transaction_read_only 

-------------------------------

 off

(1 row)

testdb01=> create database test001;

ERROR:  permission denied to create database

testdb01=> 

設定開啟session層級的唯讀模式（當然在退出資料庫sql互動視窗的時候設定的模式會失效）如果重啟資料庫，則以postgresql.conf檔案的配置參數default_transaction_read_only = 為準;

預設設定檔中此參數是關閉的#default_transaction_read_only = off

testdb01=> set session default_transaction_read_only=on;

SET

testdb01=>

testdb01=> show default_transaction_read_only;

 default_transaction_read_only 

-------------------------------

 on

(1 row)

testdb01=> create database test001;

ERROR:  permission denied to create database

3.不需要修改postgresql.conf設定檔參數，巧妙的解決登陸psql設定的登陸使用者的唯讀模式。

testdb01=> alter user user001 set default_transaction_read_only=on;

ALTER ROLE

testdb01=> show default_transaction_read_only;

 default_transaction_read_only 

-------------------------------

 on

(1 row)

testdb01=> create database test001;

ERROR:  cannot execute CREATE DATABASE in a read-only transaction

在此處可以設定session層級的讀寫入模式，

關閉session層級的唯讀模式(只是臨時關閉唯讀模式。退出psql互動視窗，剛才的設定便失效)

testdb01=> set session default_transaction_read_only=off;

testdb01=> alter user user001 set default_transaction_read_only=off;

永久關閉唯讀模式，這樣即使是退出pgsql資料庫的互動視窗，唯讀模式也是可以關閉的，除非修改設定檔參數為default_transaction_read_only =on來重啟postgresql服務才是唯讀模式;

本文出自 “10931853” 部落格，請務必保留此出處http://wujianwei.blog.51cto.com/10931853/1976446

給postgresql的登陸使用者佈建唯讀模式