From: | Stephen Frost <sfrost(at)snowman(dot)net> |
---|---|
To: | Susan Hurst <susan(dot)hurst(at)brookhurstdata(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: User Connecting to Remote Database |
Date: | 2017-11-28 23:31:53 |
Message-ID: | 20171128233153.GZ4628@tamriel.snowman.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Greetings Susan,
* Susan Hurst (susan(dot)hurst(at)brookhurstdata(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Crowell | 2017-11-29 03:16:51 | Re: large numbers of inserts out of memory strategy |
Previous Message | Susan Hurst | 2017-11-28 22:57:22 | User Connecting to Remote Database |