PostgreSQL on Amazon RDS – How to Give Privileges to Group for Accessing Different Databases

amazon-rdspostgresql

I'm new to working on RDS postgresql instance, I've a group and added a couple of users to the group. I need to give permission to users in the group to allow them to access other dbs like x ,y and z

I logged in as admin user to y database and gave permission to the group, but the user is not able to view tables in other dbs. Group name is dev_role. All tables are in public schema.

y=> ALTER DEFAULT PRIVILEGES GRANT SELECT, INSERT, UPDATE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO dev_role;
ALTER DEFAULT PRIVILEGES

Even Tried 

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev_role;

The user logged into y db and was trying to run \dt, the user is getting an error: 

Did not find any relations.

psql (10.11, server 11.5)

What could be the issue and can someone point me correct way to give permissions.

Best Answer

Here is one thing, there is no difference between regular PostgreSQL and RDS PostgreSQL when it comes to ROLES,GRANT, PRIVILEGES, and GRANT. In your case, the user must be connecting to other than y DB. Maybe he connection default DB Postgres. Tested in my rds instance. 

GRANT USAGE ON SCHEMA public TO dev_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dev_role;

with this working fine for me, ref.docs: 

y=> \dt
         List of relations
 Schema |  Name  | Type  |  Owner   
--------+--------+-------+----------
 public | table1 | table | postgres
 public | table2 | table | postgres
(2 rows)

and when I connected to other than y DB,

postgres=> \dt
No relations found.

Since you didn't give permission here.

Related Question