Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL: Script to Create a Read-Only Database User

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.


Popular posts from this blog

Script to find sessions that are blocking other sessions in PostgreSQL

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL pgBadger

How to Return a Result Set from a PostgreSQL Stored Procedure