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';
CREATE ROLE
postgres=# alter user readonly set default_transaction_read_only = on;
ALTER ROLE
postgres=# GRANT select on employees to readonly;
GRANT
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
postgres=>

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;
CREATE DATABASE
postgres=# alter database readonly set default_transaction_read_only = on;
ALTER DATABASE
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
readonly=#

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.

10 Comments

  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,
    Reeshna

  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 install_pgstats.sh 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:
      http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/pgstatspack/pgstatspack/README?rev=1.1&content-type=text/plain

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

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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