Greetings Susan, * Susan Hurst (susan.hurst@xxxxxxxxxxxxxxxxxx) 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
Attachment:
signature.asc
Description: Digital signature
