User mapping: view over a foreign table
Supposing I have a foreign table F and a view V that selects rows from F.
The owner of F is USER1.
The owner of V is USER2.
The currently logged user is "me".
When I query the foreign table F, the DBMS engine looks for USER MAPPING
for "me", regardless of who is the owner of the table.
When I query the view V, the engine searches the USER MAPPING for USER2 who
is the owner of the view.
This looks inconsistent and misleading. I wonder, are there any reasons
behind this design decision? Is it documented somewhere (I did not find it)?
Regards,
Vlad
My DB version(): PostgreSQL 10.3 (Ubuntu 10.3-1.pgdg14.04+1) on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.4) 4.8.4,
64-bit
ср, 17 июл. 2019 г. в 14:24, Vladimir Ryabtsev <greatvovan@gmail.com>:
Show quoted text
Supposing I have a foreign table F and a view V that selects rows from F.
The owner of F is USER1.
The owner of V is USER2.
The currently logged user is "me".When I query the foreign table F, the DBMS engine looks for USER MAPPING
for "me", regardless of who is the owner of the table.When I query the view V, the engine searches the USER MAPPING for USER2
who is the owner of the view.This looks inconsistent and misleading. I wonder, are there any reasons
behind this design decision? Is it documented somewhere (I did not find it)?Regards,
Vlad
Vladimir Ryabtsev <greatvovan@gmail.com> writes:
Supposing I have a foreign table F and a view V that selects rows from F.
The owner of F is USER1.
The owner of V is USER2.
The currently logged user is "me".
When I query the foreign table F, the DBMS engine looks for USER MAPPING
for "me", regardless of who is the owner of the table.
When I query the view V, the engine searches the USER MAPPING for USER2 who
is the owner of the view.
This looks inconsistent and misleading.
AFAICS, it's exactly parallel to the handling of SQL permissions for
the foreign table. If you query F directly, you must have SELECT
permissions on F (or whatever is appropriate for your query type).
If you query F via V, you must have SELECT on V and the owner of V
must have SELECT on F.
regards, tom lane