Postgresql – Permission Denied for Foreign Server

postgresqlpostgresql-fdw

I'm trying to set up a user with limited permissions that would be able to create foreign tables. I have two databases, hr_db and accounting_db. I have created an hr_user user for hr_db and a accounting_user user for accounting_db. I only want the accounting_user user to have select rights on some hr_db tables, such as the users table. To do this, as a superuser I went to the hr_db database and ran:

GRANT CONNECT ON DATABASE hr_db TO accounting_user;
GRANT SELECT ON people TO accounting_user;

I set up a connection to hr_db from accounting_db using a foreign data wrapper:

CREATE SERVER hr_db FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'hr_db', port '5432');

I then added a mapping for the accounting_user user:

CREATE USER MAPPING FOR accounting_user SERVER hr_db
OPTIONS (user 'accounting_user', password 'secretpassword');

The password for accounting_user is the same as the one I use to log in from the command line. This works fine:

psql -U accounting_user -W hr_db
[enter accounting_user password]
SELECT * FROM people LIMIT 10;

I can create a regular table in the accounting_db database as the accounting_user user:

psql -U accounting_user -W accounting_db
[enter accounting_user password]
CREATE TABLE test (person_id integer NOT NULL);
DROP TABLE test;

But if I try to create a foreign table:

CREATE FOREIGN TABLE hr_people (person_id integer NOT NULL)
SERVER hr_db OPTIONS (table_name 'people');
ERROR:  permission denied for foreign server hr_db

As a superuser, I can create the hr_people foreign table and the accounting_user will have access to it. So the foreign data connection to hr_db seems correct. What else do I need to give the accounting_user in order for it to be able to create and drop foreign tables?

Best Answer

To grant permissions for the foreign server:

GRANT USAGE ON FOREIGN SERVER hr_db TO accounting_user;

More details available in the example on official page https://www.postgresql.org/docs/9.6/static/contrib-dblink-connect.html