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. A logged-in user can always change this parameter for the current session by using the SET command:

    SET default_transaction_read_only = off;

    Is there a way to stop this? I think not.

  2. Hi,
    Could you please let me know which version of PGSTATSPACK works
    specifically for postgresql-9 ?
    I’ve been trying to find out which version to download,
    however the website don’t seem to have any info on which version is
    compatible with which database versions..

    Thanks in advance,

  3. so user can set this parameters to off in session
    we need grant select privileges on objects to him,
    he can only create new objects ,and can not modify objects

    1. As mentioned setting role attribute default_transaction_read_only=on. 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.

  4. Hi,

    I was looking for pgstatspack docs and was unable to find any.. Could you please update a step by step doc for the same..
    Found some trouble while executed to install the script on all databases except the postgres db and templates ie it mentioned psql: FATAL: database “#” does not exist

    Thanks in advance 🙂

    1. pgstatspack README already have sufficient steps which user can use.
      Below is pgstatspack README link:

      Error message which have mentioned that it seems psql is trying to connect to Database which doesn’t exists in PG. uses following sql to list the database names:

      If you still getting error message, then you can add “set -x” in top of to track on which line issue is coming.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s