postgres read only user not working

Question

I have Postgresql 9.2 with database running and now try to create a read only user. I followed these descriptions:

ERROR: permission denied for relation tablename on Postgres while trying a SELECT as a readonly user

How do you create a read-only user in PostgreSQL?

BasicallyI have done the following:

jbossmanager=# CREATE USER jbcust  WITH ENCRYPTED PASSWORD '#######';
CREATE ROLE
jbossmanager=# GRANT USAGE ON SCHEMA jbossmanager to jbcust;
GRANT
jbossmanager=# ALTER DEFAULT PRIVILEGES IN SCHEMA jbossmanager GRANT SELECT ON TABLES TO jbcust;
ALTER DEFAULT PRIVILEGES
jbossmanager=# GRANT CONNECT ON DATABASE jbossmanager to jbcust;
GRANT
jbossmanager=# GRANT SELECT ON ALL SEQUENCES IN SCHEMA jbossmanager TO jbcust;
GRANT
jbossmanager=# GRANT SELECT ON ALL TABLES IN SCHEMA jbossmanager TO jbcust;
GRANT

After this a "\l" shows the following:

jbossmanager=# \l
                                     List of databases
 Name     |    Owner     | Encoding |   Collate   |    Ctype    |       Access privileges
--------------+--------------+----------+-------------+-------------+-------------------------------
 jbossmanager | jbossmanager | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/jbossmanager             +
          |              |          |             |             | jbossmanager=CTc/jbossmanager+
          |              |          |             |             | jbcust=c/jbossmanager

which looks good to me, also a list of tables in that db / schema lokks fine:

jbossmanager=# \dp+
                                                   Access privileges
    Schema    |             Name              |   Type   |         Access privileges         | Column access privileges
--------------+-------------------------------+----------+-----------------------------------+--------------------------
 jbossmanager | env_server_standalone_info    | table    | jbossmanager=arwdDxt/jbossmanager+|
              |                               |          | jbcust=r/jbossmanager             |

But now when I connect as this jbcust user to database and try:

#psql -d jbossmanager -U jbcust -h 127.0.0.1
Password for user jbcust:
psql (9.2.4)
Type "help" for help.

jbossmanager=> \dp+
                          Access privileges
 Schema | Name | Type | Access privileges | Column access privileges
--------+------+------+-------------------+--------------------------
(0 rows)

I have no idea what is wrong or what is missing.

Thanks a lot for your help!

Using the same connection settings I couldn't reproduce the error. Are you connecting with psql in the same way in the first example? — Simo Kivistö
– Simo Kivistö, Commented Mar 13, 2015 at 20:58
Are you 100% sure you're connecting to the same DB on the same host? — Craig Ringer
– Craig Ringer, Commented Mar 16, 2015 at 3:08
In the first example I connected like: # psql -d jbossmanager -U jbossmanager -h 127.0.0.1 in the second case like this: # psql -d jbossmanager -U jbcust -h 127.0.0.1 Yes, I am sure that this is the same db on same host. — Sebi
– Sebi, Commented Mar 19, 2015 at 13:16

Sebi · Accepted Answer · 2015-03-19 19:12:40Z

0

The reason why I did not see any schema when connecting with read only user is that the default schema for this user was not set.

After this:

ALTER USER jbcust SET search_path = jbossmanager;

it works perfect.

answered Mar 19, 2015 at 19:12

Sebi

213 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jon Worek Over a year ago

My solution ended up being a bit different from yours, but I still wanted to mention that I found this question helpful. I had setup readonly users from several guides on the internet, but was missing GRANT USAGE ON SCHEMA jbossmanager to jbcust; after this GRANT, my read only user worked perfectly. Thank you!

Collectives™ on Stack Overflow

postgres read only user not working

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related