3

I am trying to set up a new read only user in postgres. I did the following steps to create the new user:

sudo -u postgres createuser --interactive
    name: read_only_user
    superuser: n
    create database: n
    Create new roles: n

Then I logged into postgres with the postgres user and executed the following commands:

ALTER USER read_only_user WITH PASSWORD '123456';
GRANT CONNECT ON DATABASE myDB TO read_only_user;
GRANT USAGE ON SCHEMA public TO read_only_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_only_user;

But if I execute the following command:

SELECT * FROM mytable;

I get the follwing error:

ERROR:  permission denied for relation mytable

When logged in with the postgres account I can view the following settings - but I do not see what is missing. I checked a lot of other Questions/Answer here but could not find the reason for the missing permission.

List of roles:

Role name        |                         Attributes                         | Member of
-----------------+------------------------------------------------------------+-----------
 some_user       | Superuser, Create role, Create DB                          | {}
 read_only_user  |                                                            | {}
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

List of databases:

   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 myDB      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =Tc/postgres         +
           |          |          |            |            | postgres=CTc/postgres+
           |          |          |            |            | read_only_user=c/postgres
[...]

List of schemas:

  Name  |  Owner   |  Access privileges         |      Description
--------+----------+----------------------------+------------------------
 public | postgres | postgres=UC/postgres+      | standard public schema
        |          | =UC/postgres+              |
        |          | read_only_user=U/postgres  |

Access privileges:

 Schema |       Name        |   Type   |     Access privileges           | Column privileges | Policies
--------+-------------------+----------+---------------------------------+-------------------+----------
 public | mytable           | table    | postgres=arwdDxt/postgres+      |                   |
        |                   |          | read_only_user=r/postgres       |                   |
 public | mytable_id_seq    | sequence | postgres=rwU/postgres+          |                   |
        |                   |          | read_only_user=rU/postgres      |                   |

closed as off-topic by mustaccio, MDCCL, Marcello Miorelli, kevinsky, LowlyDBA Jul 31 at 20:25

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, MDCCL, Marcello Miorelli, kevinsky, LowlyDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

  • What you show in the question does work. I suspect the permission denied is due to a pilot error that does not appear in the question, such as recreating the table after the GRANT SELECT ON ALL TABLES, or read_only_user not being the actual user when the error occurs. – Daniel Vérité Oct 23 '18 at 17:50
  • Ok, it seems I found the problem. I did not log into the database before executing the GRANT commands. So a '\c mydb' needs to be added there... – mgerbracht Oct 24 '18 at 9:42
0

Instead of granting the access inside the shell try to do that outside the postgres shell like this

$ sudo -u postgres psql -d myDB -c "GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_user"

$ sudo -u postgres psql -d myDB -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user"

Not the answer you're looking for? Browse other questions tagged or ask your own question.