From: | Niels Jespersen <NJN(at)dst(dot)dk> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Foreign tables, user mappings and privilege setup |
Date: | 2020-09-21 08:21:12 |
Message-ID: | d0306b1670674d70ab923c42d4340db3@dst.dk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello all
I am a bit confused about how to design privileges properly around foreign tables.
Here's the setup.
-- As superuser
create server s.... foreign data wrapper postgres_fdw options (host 'hhh.d', dbname 'db1', port '5432');
grant usage on foreign server s... to public; -- This does not give access to anything beyond allowing user created foreign tables and user mappings.
-- As user with create privileges in schema
create user mapping for current_user server s... (user 'remoteuser', password 'remotepassword');
create foreign table t.... ( a int) server s... options (table_name 't_remote');
This works fine. Except only the user who created the user mapping can select from foreign table, even if other users have select privilege on the table. They will get a "user mapping not found for...".
Now, I could, as superuser:
create user mapping for public server s.... -- But that would give anyone access the whatever thre remote user has access to. Not good.
I am unsure of the best solution to this. I can see a few, but I have not tested them. There may be other solutions that are much better.
One way is a public user mapping on top of a server, and only give a specific role usage privileges.
Another way is to create a view on top of the foreign table and give select privileges on that. I'm not sure that would work, actually.
Yet another is a set returning function on top of the foreign table defined as 'security definer'.
That's basically it. Shoot.
Regards Niels Jespersen
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2020-09-21 14:10:06 | Re: Foreign tables, user mappings and privilege setup |
Previous Message | Durumdara | 2020-09-21 07:43:15 | CRL reloading info - is it refreshed? |