On Sun, 2024-05-05 at 15:07 +1200, Edwin UY wrote:
> I followed this link for using oracle_fdw to access Oracle from PostgreSQL.
> Oracle is ON-PREM and PostgreSQL is Aurora-PostgreSQL-RDS-Version 15.
>
> As administrator:
>
> CREATE EXTENSION oracle_fdw;
> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name');
> GRANT USAGE ON FOREIGN SERVER oradb TO user1;
> As user1
>
> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword');
> CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE');
> SELECT * FROM mytab;
> The SELECT gives ERROR: permission denied for schema public
> I have to do GRANT ALL ON SCHEMA public TO user1, this resolves the permission denied error.
>
> Ideally, I don't really want each user to be doing the create user mapping and create foreign table.
>
> Is it possible that as schema owner of either schema1 or schema2, i.e. administrator,
> I create the user mapping and the foreign tables and then grant access to
> user1 to user5 and schema2/schema1?
> I can't find any reference/doc/example on whether this is possible or not.
>
> I also don't have the password of each user. Can I use set role user1 to user5 to do
> the CREATE USER MAPPING and CREATE FOREIGN TABLE.
>
> In summary, I am wanting to do the CREATE USER MAPPING and CREATE FOREIGN TABLE once
> only in either SCHEMA1 and SCHEMA2 and grant any role/user to be able to access these
> foreign tables, mainly just a select.
The foreign table needs to be created only once, but you must give all users privileges
to access the schema (USAGE) and the foreign table (SELECT, ...).
You will need a shared schema that all users can use and place the foreign table there.
That schema need not be "public".
If you don't want to create a user mapping for each user, you can also create a user
mapping for PUBLIC. Sich a user mapping will work for all authenticated users, and they
will all use the same Oracle user to connect to the remote database.
If you want your users to use different Oracle users, you need to create a user mapping
for each individual user.
Yours,
Laurenz Albe