Search code examples
postgresqlpostgresql-12

Granting local user permissions to a foreign db table in postgres

I set up a connection to a foreign db using dblink_connect according to the docs:

CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'foreign_test_db');

CREATE USER test_user WITH PASSWORD 'secret';
CREATE USER MAPPING FOR test_user SERVER fdtest OPTIONS (user 'test_user', password 'secret');
GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user;
GRANT SELECT ON TABLE foo TO test_user;

\c - regress_dblink_user
SELECT dblink_connect('myconn', 'fdtest');


SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]);

The final 'GRANT SELECT' appears to infer that if it is meant to grant select permissions to local user test_user to the table foo on the fdtest foreign data wrapper. However, how I would interpret this command is that it is granting permissions to test_user to select on local table foo (which does not exist). As expected, when I run this command I get an error:

ERROR:  relation "foo" does not exist

I would love to know how to actually accomplish this. I would like to be able to restrict local users to only access certain tables from a foreign data wrapper.

Solution

  • You'll have to connect to the remote database and run the GRANT statement there.

    Of course you could do that via dblink, but then you'd have to connect with a superuser or the owner of the table.