Adding new user in Postgres

Question

I am using AWS RDS PostgreSQL and currently up and running. I would like to know how I can add a new user but only grant SELECT on an existing DB.

The way I connect to my RDS from my PC is by using a app called pgAdmin III (from Ubuntu).

I tried to create a group called rds_guest, assign a user (called webkul) and finally GRANT privileges (INSERT, SELECT, UPDATE etc) on the database I want for that particular group:

When I try to login using that user and try perform actions on the desired database I get an error saying:

Permission denied for relation xxx_yyy

Can anyone help me with this ? I am relatively new with Postgres.

Answer 1

You must grant a user:

• CONNECT on the database (if public doesn't have it by default)
• USAGE on any schemas like the public schema (again, if the public role doesn't have it by default)
• ... and the rights you want on a given table.

I suspect you (or RDS) have revoked CONNECT on the database and/or USAGE on the schema(s) you're using, so the role doesn't have the rights to get to the point where it can use the table privileges.