Posts

Showing posts from March, 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'; 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 | e