Re: Foreign tables - oracle_fdw

Hi Laurenz,

Thanks for your reply. Please find my reply below.

Sorry, I am a real newbie when it comes to PostgreSQL, got pulled into
doing the migration at the last minute literally. It would be nice to
intern at Cybertec remotely if I can :-)

>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, ...).

The examples that I followed does not have the instruction how to do the
grant to the foreign table created, following the AWS example and your
example https://github.com/laurenz/oracle_fdw and using user1, \dE shows
the owner as user1
As user1, I did grant all on [foreign_table] to [schema1_owner], then I
login as [schema1_owner] and tried doing select * from the [foreign_table]
or select * from [user1].[foreign_table] and it gives ERROR: relation
"[foreign_table]" does not exist.
I am pretty sure I get something wrong :-)

>You will need a shared schema that all users can use and place the foreign
table there.
>That schema need not be "public".
Yeah, this is one I am trying to do. On a non-public schema, create the
foreign tables there and grant everyone access to this one but so far no
luck with my Google searches. I've been searching for hours now and still
doing so, just had a wee hours break :-)

>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.
This is exactly what I would have wanted if creating on a non-public schema
does not work, but so far the examples I found is creating the user mapping
as non-public. Not sure how to make the foreign table PUBLIC, I can't login
as PUBLIC on Aurora-PostgresSQL or on PostgreSQL itself, can I? The only
command where I see the schema option is during the CREATE FOREIGN TABLE,
but I think that is for the Oracle side, not on the PostgreSQL side.

Thanks again for your reply. Very much appreciated.

On Sun, May 5, 2024 at 3:58 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> 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
>