0

How do foreign table privileges work? A simple example where both source_db and destination_db are Postgres databases.

source_db:

-- create user for user mapping
CREATE USER fdw_user WITH PASSWORD 'secret';
-- create table 
CREATE TABLE data (value TEXT);
-- set privileges
GRANT ALL ON TABLE data TO fdw_user;

destination_db:

-- create extension
CREATE EXTENSION postgres_fdw;
-- create server
CREATE SERVER remote_source
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'source.domain.com', dbname 'source_db');
-- create user mapping
CREATE USER MAPPING
  FOR PUBLIC
  SERVER remote_source
  OPTIONS (user 'fdw_user', password 'secret');
-- create foreign table
CREATE FOREIGN TABLE data_from_source(value TEXT)
   SERVER remote_source
   OPTIONS (table_name 'data');

Now setting privileges for any user in destination_db seems to have no effect, like

GRANT SELECT ON TABLE data_from_source TO localuser;

How can I set privileges on foreign table?

tok
  • 909
  • 1
  • 10
  • 21
  • it does what I expected. What did you expect to happen? What happened instead? – jjanes Jun 06 '22 at 02:14
  • The last "GRANT SELECT ON TABLE data_from_source TO localuser;" do not change the ACL of foreign table. Btw, I'm using Postgres version 9.3. – tok Jun 06 '22 at 06:39

1 Answers1

0

The problem was PgAdmin III. ACL of foreign table changed but PgAdmin did not show it. Psql on command line \dp+ data_from_source shows the ACL as expected.

tok
  • 909
  • 1
  • 10
  • 21