Read Only User

[binface]

How do you create a read only user??

I would like to create a user that can select from a schema but not update or delete it.

Thanks

[JimKnicely]

binface,

I don't think we can create a user that is read only "generically" on all objects (i.e. tables and views) in a database. We have to grant the SELECT privilege on an object by object basis.

Basically, once we have a schema, we would have to grant the USAGE privilege on the schema to our read only user, and finally grant the SELECT privilege on the objects within that schema we want the user to be able to read.

Here is an example:

Create a schema and a new table in that schema:

Code: Select all

dbadmin=> create schema test;
CREATE SCHEMA

dbadmin=> create table test.tab1 (col1 varchar(100));
CREATE TABLE

dbadmin=> insert into test.tab1 values ('Can I read this?');
 OUTPUT
--------
      1
(1 row)

Create a user that you want to have read only access to the table:

Code: Select all

dbadmin=> create user test_read_only;
CREATE USER

dbadmin=> grant usage on schema test to test_read_only;
GRANT PRIVILEGE

dbadmin=> grant select on test.tab1 to test_read_only;
GRANT PRIVILEGE

Now connect as the test_read_only user and notice how the user is able to interact with the table...

The user can SELECT:

Code: Select all

dbadmin=> \c vtst1 test_read_only;
You are now connected to database "vtst1" as user "test_read_only".
vtst1=> select * from test.tab1;
       col1
------------------
 Can I read this?
(1 row)

But the user can not INSERT, UPDATE or DELETE:

Code: Select all

vtst1=> insert into test.tab1 values ('Can I insert this?');
ERROR 4367:  Permission denied for relation tab1

vtst1=> update test.tab1 set col1 = 'Can I update this?';
ERROR 4367:  Permission denied for relation tab1

vtst1=> delete from test.tab1;
ERROR 4367:  Permission denied for relation tab1

I wish we could grant the SELECT privilege on a schema to a user, but we can not.

Code: Select all

dbadmin=> grant select on schema test to test_read_only;
ROLLBACK 3732:  Invalid privilege type SELECT for schema

Here's a link to an older post that may help you to grant the SELECT privilege to all the tables within a schema to a user if that is what you desire.

viewtopic.php?f=3&t=83&hilit=grant

Thanks.

[binface]

Thanks Jim,

That's great.

Is there not a way to grant the select option to a role and then grant that role to the users?

[Rick]

Code: Select all

=> create role rolename;
=> grant usage on schema schemaname to rolename;
=> grant select on schemaname.table name to rolename;
=> grant rolename to username
=> alter user username default role rolename