SV: Foreign tables, user mappings and privilege setup

>>On Mon, 2020-09-21 at 08:21 +0000, Niels Jespersen wrote:
>> 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...".
>
>You have to create a user mapping for every user that is to access the foreign table.
>
>If several users should have the same credentials, use a group.

Thank you for replying.

I can create a user mapping for a group (role). This works, but it is not enough to grant the role to the end user (that has inherit defined). The end user must explicitly use "set role" before accessing the foreign table.

Is this what Oracle Corporation would call "Intended Behavior" ;=) ?

Regards Niels