CODEWITHSUNDEEP
postgresqlamazon-web-servicesroles
ziggy
ziggy

Reputation: 1538

Amazon RDS - Postgresql role cannot access tables

created a postgresql instance on AWS with the username ziggy. I restored a database to that instance. however I cannot even select any of the tables 

select * FROM mac_childcare_parcels

gives me ERROR: permission denied for relation mac_childcare_parcels ********** Error **********

the owner of that table belongs to the postgres login.

so i tried running this: grant all privileges on all tables in schema public to ziggy but since I am not a superuser I cannot give myself privileges so that throws a permissions error. what do I have to do to get access to the tables?

this does not work either

grant select on mac_childcare_parcels to ziggy

this query returns successful but does not let the login ziggy access the tables

GRANT USAGE ON SCHEMA public TO ziggy;

Upvotes: 2

Views: 4705

Answers (3)

Krunal Barot
Krunal Barot

Reputation: 932

The superuser access is needed to run the access level queries. But as you said that access is not present then i would say copy the replica of the db which you have restored from backup and grant yourself as superuser. then provide all needed access to any users.

Upvotes: 0

YogeshR
YogeshR

Reputation: 1736

First login with superuser and provide all rds superuser access to the newly created user using a command like below

GRANT rds_superuser TO ziggy;

replace rds_superuser with your rds superuser.

Upvotes: 2

isapir
isapir

Reputation: 23503

You need to also GRANT USAGE on the SCHEMA, e.g.

GRANT USAGE ON SCHEMA public TO ziggy;

Upvotes: 0

Related Questions