From: | Susan Hurst <susan(dot)hurst(at)brookhurstdata(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | User Connecting to Remote Database |
Date: | 2017-11-28 22:57:22 |
Message-ID: | 36bb557d03ae8a3ab4508b3ce90e2b75@mail.brookhurstdata.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)hurst(at)brookhurstdata(dot)com
Mobile: 314-486-3261
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2017-11-28 23:31:53 | Re: User Connecting to Remote Database |
Previous Message | Peter J. Holzer | 2017-11-28 21:14:53 | Re: Plan for update ... where a is not distinct from b |