Обсуждение: 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>:
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