Search Postgresql Archives

User Connecting to Remote Database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxxxxx
Mobile: 314-486-3261




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux