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:
I hope above would help someone who is interested in having READ-ONLY user,database or backupuser.