CODEWITHSUNDEEP
postgresqlpostgresql-12
zachary luther
zachary luther

Reputation: 53

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.

Upvotes: 0

Views: 1115

Answers (2)

jjanes
jjanes

Reputation: 44285

The connection they show is a loop back connection, it just connects back to the same server and (apparently) database you are already in. This is useful for testing purposes. So granting the permission on the local server is the same thing as granting it on the remote server, as they are the same server.

They do not show the creation of the table, you can find it in the regression test contrib/dblink/sql/dblink.sql (from which the example in the doc derives) as:

CREATE TABLE foo(f1 int, f2 text, f3 text[], primary key (f1,f2));

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247235

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.

Upvotes: 1

Related Questions