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:
More details available in the example on official page https://www.postgresql.org/docs/9.6/static/contrib-dblink-connect.html