User Connecting to Remote Database
От | Susan Hurst |
---|---|
Тема | User Connecting to Remote Database |
Дата | |
Msg-id | 36bb557d03ae8a3ab4508b3ce90e2b75@mail.brookhurstdata.net обсуждение исходный текст |
Ответы |
Re: User Connecting to Remote Database
|
Список | pgsql-general |
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; Thanks for your help! Sue -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Susan E Hurst Principal Consultant Brookhurst Data LLC Email: susan.hurst@brookhurstdata.com Mobile: 314-486-3261
В списке pgsql-general по дате отправления: