User mapping error

[mlazlo]

I have attached DDL and DML related to an issue I seem to be having on AWS Aurora-Postgres v12.8 using oracle_fdw. NOTE: This issue DOES NOT happen with exactly the same setup using postgres_fdw instead. presto_viewer role can return data from a view that points at a foreign table.

There are too many steps to put here so I attached the steps and the end result.

I cannot figure out why a user that is mapped across an oracle_fdw connect that creates objects locally then creates a view and grants permissions to select for a roll, another user that has been granted that role generates the user mapping error as if he was directly running the underlying DDL for the view.

Thanks in advanced end_user_access_to_oracle_fdw.txt

[laurenz]

Your script contains

psql -h server.com -d feeds -U admin -W

CREATE USER MAPPING FOR edr SERVER edr_fdw OPTIONS (user 'eim_dev', password '');

and the error you get is

psql -h server.com -d feeds -U presto_viewer -W

select count(*) from asrs_nonrestrict.text;
ERROR:  user mapping not found for "presto_viewer"

The error message says it all!

You have to create a user mapping for presto_viewer with

CREATE USER MAPPING FOR presto_viewer ...

or

CREATE USER MAPPING FOR PUBLIC ...

[mlazlo]

this is the reason for posting this. The owner of a view needs select permissions against the base table. If I grant select permissions, as I have done here, to a role against the view that is owned by the table owner, then any user with permissions to the role with permissions to the select from the view does not require base table access. This is still true for the postgres_fdw. If you swap out what I attached oracle_fdw with postgres_fdw, and the other database was postgres, the presto_viewer can select against that view but not the base table. Creating a mapping for every user that is accessing a view on top of the foreign table is an issue and an administrator's nightmare. Why does this appear to be the only scenario in postgres where the view own is only required access to the base table?

From Postgresql documentation: "Access to tables referenced in the view is determined by permissions of the view owner. In some cases, this can be used to provide secure but restricted access to the underlying tables."

[chrullrich]

Isn't this #498 (fixed after the 2.4.0 release)?

[mlazlo]

This is the same! Ok so at this point it appears to be an AWS versioning issue. This is the definition to what gets created by default. https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.html

CREATE EXTENSION oracle_fdw SCHEMA "public" VERSION 1.2

I am going to try to pass in the version number 2.4 to see if that works. If not then I will open a ticket with aws support.

Chrullrich, thanks for realizing this!

[mlazlo]

Passing in 2.4 throws error. They currently on have version 1.2 available. Open aws support ticket.

[mlazlo]

Is the fix in version 2.4 and up or some over version?

[mlazlo]

Looking at the CHANGELOG, which 2.5 and 2.4 seem to be related to the same thing. But 2.5 seems to word it closer to issue at hand and references the #498 bug by user that reported it. So which version actually fixes this? Need this information for aws support.

Version 2.5.0

Bugfixes:

• Use the correct user mapping for views on foreign tables. This should use the mapping associated with the view owner. Reported by "JSilex".

Version 2.4.0, released 2021-09-24 Bugfixes:

• Use the correct user mapping in SECURITY DEFINER contexts. Before this, oracle_fdw always used the current user to determine the security context, which is wrong if a foreign table was accessed in a SECURITY DEFINER function. Reported by "ksmalara".

[laurenz]

Ah, I didn't see the view in your example.

What is fixed in 2.4.0 is using the correct user when running in a SECURITY DEFINER function. Your problem is fixed in b995b8fca720469409a2dfa828a0ed4056dfb907, which is not yet in a released version. So this is a duplicate of #498.

From this page I learn that Amazon provides version 2.3.0.

[mlazlo]

Yes they said we could probably go to 2.3 but that still doesn't address the issue. The 2.5 CHANGELOG appears to have the wording that supports #498. Is 2.5 not available yet to anyone?

[chrullrich]

There is no 2.5 yet; the change log just uses it in the sense of "next release".

Could you get AWS to give you 2.4 and then use a SECURITY DEFINER function in place of or in the view?

[mlazlo]

At this point they are stating that 2.3 is the latest version available whether 12.8 or 13.3 and up. Support is working on why my 12.8 instance only shows 1.2 available for use at this point. Even if I could implement SECURITY DEFINER I would have to build a function to return rows anyway which to me is still a work around to the original issue. So looks like I will be using the work around of creating a user mapping for the users of my views. I have validated that the work around does fix the issue.

[chrullrich]

From the README:

Note that the extension version as shown by the psql command \dx or the system catalog pg_available_extensions is not the installed version of oracle_fdw. To get the oracle_fdw version, use the function oracle_diag.

The 1.2 is the schema version of the extension. oracle_fdw does not have a lot of database objects but rather provides Oracle-specific implementations of the foreign data-related SQL commands. Its database-side interface rarely changes; there have been only two schema version changes in the last eight years that added auxiliary functions.

Most other kinds of extensions add and change database objects with every new release as they add features etc. FDWs, however, are particularly good candidates for very stable interfaces in this respect because they only need to register one database function, and its signature has never changed.

For an example of the alternative approach, see https://github.com/CartoDB/odbc_fdw that uses a single version numbering scheme and where (spoiler alert) all upgrade scripts are empty.

[laurenz]

It should be safe to use the current development versions. But you may have trouble convincing Amazon of that...

Anyway, is there anything else we can do for you or should I close the issue?

[mlazlo]

I opened a support ticket about this since the version in my 12.8 Aurora Postgres says version 1.2 is used. Here is what they state about that fact: _The team after further analysis has identified that the extension version 1.2 is equivalent to the community version of the extension 2.3.0. The Aurora PostgreSQL documentation versions do not correctly represent this, which has caused much confusion and the error "extension "oraclefdw" has no update path from version 1.2 to version 2.3.0". We sincerely apologize for this misinformation and ambiguity. The team is working on updating the documentation. We appreciate you for bringing this issue to our attention.

As for 2.5 you produced that fixes this issue, they have no timeline.

[mlazlo]

Thanks for all the information

[laurenz]

Thanks for the feedback!