Set read-only mode for PostgreSQL login user

A. Description:

To make the database read-only, PostgreSQL does not have a strictly read-only mode (such as temporary tables that are available in read-only transactions). You can set the session or the current transaction for subsequent logons to read-only mode by adjusting the parameters or setting the transaction mode.

In read-only mode, PostgreSQL does not allow the following SQL:

When a transaction is read-only, the following SQL commands be Disallowed:insert, UPDATE, DELETE, and COPY from if the T Able they would write to was 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, does not prevent all writes to disk.

The above description refers to the address:

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

Two. Set the read-only mode for PostgreSQL's Landing User:

1. Set the user logging database to read-only mode:

[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; (The database does not need to be restarted and is permanently active )

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)

The above parameters are set, even the read-only mode that was set up to restart the database is in effect:

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. Set the read-only mode to turn off session level (of course, when exiting the database SQL Interactive window, the mode set will be invalidated):

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=>

Set the session-level read-only mode (of course, the mode will be invalidated when exiting the database SQL Interactive window) If you restart the database, the configuration parameter of the postgresql.conf file Default_transaction_read_only = ;

This parameter in the default configuration file is off #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. No need to modify the postgresql.conf configuration file parameters, smart to solve the login psql settings of the login user's read-only mode.

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

Here you can set the session level read-write mode,

Turn off read-only mode at session level (just temporarily turn off read-only mode.) Exit Psql Interactive window, the settings just expire)

Testdb01=> set session Default_transaction_read_only=off;

testdb01=> alter user user001 set Default_transaction_read_only=off;

To permanently turn off read-only mode so that even if you exit the interactive window of the Pgsql database, the read-only mode can be turned off, unless you modify the configuration file parameter to default_transaction_read_only =on to restart the PostgreSQL service is read-only mode;

This article is from the "10931853" blog, please be sure to keep this source http://wujianwei.blog.51cto.com/10931853/1976446

Set read-only mode for PostgreSQL login user