HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Permission Denied for Foreign Server

Submitted by: @import:stackexchange-dba··
0
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, 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?

Solution

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

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.