User Connecting to Remote Database

Greetings Susan,

* Susan Hurst (susan.hurst@brookhurstdata.com) wrote:

I would welcome your comments and suggestions for connecting a user
(not a superuser) to a foreign server.

I have a database, named geo, in which I have geospatial and
geopolitical data. I want to be able to select data from geo from
other databases.

The database that I want to connect up to geo is named stp. I have
a foreign data wrapper in stp that defines geo as the data source
for the foreign server named geoserver.

User stp is defined in both geo and stp as superusers, so I am able
to select geo data just fine from stp. However, when I try to
select geo data as user geo_user, I get this error:

ERROR: permission denied for relation geoadm_l0
SQL state: 42501

What am I missing? Here are the relevant grants etc that I set up
in both geo and stp.

-- user and user mapping in stp database
create user geo_user with login nosuperuser inherit nocreatedb
nocreaterole noreplication password '**********';
CREATE USER MAPPING FOR geo_user SERVER geoserver OPTIONS (password
'**********', "user" 'geo_user');
grant usage on foreign data wrapper postgres_fdw to geo_user;
GRANT USAGE ON FOREIGN SERVER geoserver TO geo_user;

-- user in geo database
create user geo_user with login nosuperuser inherit nocreatedb
nocreaterole noreplication password '**********';

-- grants in geo database
GRANT ALL ON TABLE public.geoadm_l0 TO susan;
GRANT SELECT ON TABLE public.geoadm_l0 TO geo_user;
GRANT SELECT ON TABLE public.geoadm_l0 TO read;
GRANT ALL ON TABLE public.geoadm_l0 TO geo;
GRANT INSERT, UPDATE, DELETE ON TABLE public.geoadm_l0 TO write;

You don't appear to have done any GRANTs to the geo_user in the stp
database..? Note that we check the privileges on the FOREIGN TABLE
defined in the source database too.

Thanks!

Stephen