2

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!

Community
  • 1
  • 1
Sebi
  • 21
  • 3
  • 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ö Mar 13 '15 at 20:58
  • Are you 100% sure you're connecting to the same DB on the same host? – Craig Ringer Mar 16 '15 at 03: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 Mar 19 '15 at 13:16

1 Answers1

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.

Sebi
  • 21
  • 3
  • 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! – Jon Worek Dec 07 '16 at 17:05