patternsqlModerate
Permission Denied for Foreign Server
Viewed 0 times
permissionforeigndeniedforserver
Problem
I'm trying to set up a user with limited permissions that would be able to create foreign tables. I have two databases,
I set up a connection to
I then added a mapping for the
The password for
I can create a regular table in the
But if I try to create a foreign table:
As a superuser, I can create the
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_dbAs 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?Solution
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
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
Code Snippets
GRANT USAGE ON FOREIGN SERVER hr_db TO accounting_user;Context
StackExchange Database Administrators Q#156928, answer score: 16
Revisions (0)
No revisions yet.