CODEWITHSUNDEEP
postgresqlamazon-rdsforeign-data-wrapper
virtual void
virtual void

Reputation: 143

postgres foreign data wrapper for non admin user

I created a foreign data wrapper and created the user mappings for 2 users one is admin and the other only has readonly access to the tables. When I try to query using the readonly user I get an error:

ERROR: permission denied for schema testing LINE 1: SELECT * FROM testing.bldg ^ ********** Error ********** ERROR: permission denied for schema testing SQL state: 42501 Character: 15

Here is my setup: Postgres 9.6.1 in Amazon RDS, both DBs are part of same AWS RDS instance. When I connect to remote database directly using the readonly user I am able to query the table, problem only happens when using the fdw.

As the readonly use when I query this "select * from pg_foreign_table;" I see all the foreign tables.

I have tried the following:

grant usage on schema ...
grant select on table...
GRANT USAGE ON FOREIGN SERVER ...

Any ideas.

Upvotes: 1

Views: 3815

Answers (1)

virtual void
virtual void

Reputation: 143

I was able to resolve the issue, here are the steps:

  1. create readonly user on local DB
  2. create readonly user on remote DB
  3. create fdw and user mapping for readonly user
  4. grant usage privs on remote and local db (I was missing this on local)
  5. grant select privs on local and remote db to readonly user.

Upvotes: 2

Related Questions