13

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?

13

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

2
  • That helped with permission denied but I am getting ERROR: only superuser can change options of a file_fdw foreign table now… ☹️
    – msciwoj
    Apr 24 '19 at 14:15
  • > That helped with permission denied but I am getting ERROR: only superuser can > change options of a file_fdw foreign table now… You need to connect on targetdb with superuser and after grant usage Sep 3 '19 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.