Setting privileges on foreign table on Postgres

482 views Asked by At

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?

1

There are 1 answers

0
tok On

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.