Wednesday, March 21, 2012

READ-ONLY user,READ-ONLY Database,READ-ONLY backup user in PostgreSQL/PPAS

This has been asked me many times. Many users has asked me how they can create READ-ONLY user, READ-ONLY Database or How they can create backup user which can be use only for backup (no modification)

Well answer is in parameter called default_transaction_read_only.

If you want to make a user READ-ONLY, then you can follow steps given below:
1. CREATE normal user.
2. Use ALTER USER command to set this parameter for this user as given below:
ALTER USER  set default_transaction_read_only = on;
3. GRANT SELECT to this user for tables/object using GRANT. And you are good to go.
Below is snapshot:
postgres=# create user readonly password 'test';
postgres=# alter user readonly set default_transaction_read_only = on;
postgres=# GRANT select on employees to readonly;
edbs-MacBook-Pro:data postgres$ psql -U readonly -W
Password for user readonly: 
psql (9.1.1)
Type "help" for help.

postgres=> select * from employees ;
 employee_name | entity_name 
 Smith         | HR
 Jones         | HR
 Taylor        | SALES
 Brown         | SALES
(4 rows)

postgres=> CREATE table test_readonly(id numeric);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
Similarly, If you want to make a Database READ-ONLY, then you can use following command
ALTER DATABASE  set default_transaction_read_only=on;
Below is snapshot:
postgres=# CREATE database readonly;
postgres=# alter database readonly set default_transaction_read_only = on;
postgres=# \q
edbs-MacBook-Pro:data postgres$ psql readonly
psql (9.1.1)
Type "help" for help.

readonly=# create table test_readonly(id numeric);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
Now, if you want a seperate backup user which you want to use for Online/Hot Backup,Logical Backup (using pg_dump), then you can create a super user with default_transaction_read_only = on and can use it for backup purpose. As given below:
CREATE USER backupuser SUPERUSER  password 'backupuser';
ALTER USER backupuser set default_transaction_read_only = on;
Using default_transaction_read_only parameter, user can also make a Session Readonly by executing following command:
set default_transaction_read_only=on;

I hope above would help someone who is interested in having READ-ONLY user,database or backupuser.


  1. Unfortunately, this doesn't make the user truly read-only. User can do "start transaction read write" and then write to database.

    Here is an example:
    pgbench=> update pgbench_accounts set abalance='100500' where aid=1;
    ERROR: cannot execute UPDATE in a read-only transaction
    pgbench=> start transaction read write;
    pgbench=> update pgbench_accounts set abalance='100500' where aid=1;
    UPDATE 1
    pgbench=> commit;
    pgbench=> select * from pgbench_accounts where aid=1;
    -[ RECORD 1 ]--------------------------------------------------------
    aid | 1
    bid | 1
    abalance | 100500
    filler |

    So I strongly warn you against using this technique when you need read-only user for security reasons. This technique suits only against involuntary writes to database, it does not ENFORCE read/only transactions.

  2. I agree. This is not true read-only since, it doesn't enforce user from switching to READ WRITE mode. Its a start for users.

    I would be posting a function which will make REVOKE write privileges from a user.

  3. Interesting,

    And how do you disable the user from doing a write transaction?...


    1. As mentioned setting role attribute default_transaction_read_only=on will disable write transaction. This is option is good, to move application connection in read-only mode if application is not hard coded for using “start transaction read write” in session.

      Other option is to revoke write privilege of a user from tables and revoke CREATE privilege from user on Database. I am planning to work on function which can be useful for revoking all write privilege from non-super user.