postgres foreign data wrapper for non admin user

Question

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.

Just to confirm - once you've switched the remote read-only user to the remote admin user in your user mapping without changing anything else, the same query works again? — Leo C
– Leo C, Commented Apr 18, 2017 at 0:01
What are the user mappings you created? Try \deu+ in psql. Does the user on the remote database have the USAGE right on the schema? — Laurenz Albe
– Laurenz Albe, Commented Apr 18, 2017 at 7:09
# psql -U readonly_qa -h xx -d local_db psql (9.6.2, server 9.6.1) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) local_db=> \deu+ List of user mappings Server | User name | FDW Options -------------+-------------+---------------------------------------------------- remote_db_fdw | gandalf | ("user" 'gandalf', password 'king') remote_db_fdw | readonly_qa | ("user" 'readonly_qa', password 'queen') remote_db_fdw1 | gandalf | (3 rows) local_db=> — virtual void
– virtual void, Commented Apr 18, 2017 at 15:24
@LeoC I have 2 user mappings, when I said the query worked, I meant I connected to the remote DB directly using the same user, both DBs are on same cluster so the admin and readonly users exist on same cluster. I will try to delete the readonly user's user mapping and see. — virtual void
– virtual void, Commented Apr 18, 2017 at 15:27

virtual void · Accepted Answer · 2017-04-18 16:28:19Z

2

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

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

edited Apr 18, 2017 at 16:28

user330315

answered Apr 18, 2017 at 16:19

virtual void

1432 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres foreign data wrapper for non admin user

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related