Re: Foreign tables - oracle_fdw
| От | Edwin UY |
|---|---|
| Тема | Re: Foreign tables - oracle_fdw |
| Дата | |
| Msg-id | CA+wokJ8+WwpUVUfX7oxvhVaOaOyW0yoR_UK25fWRNuXcuE=eXg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Foreign tables - oracle_fdw (Laurenz Albe <laurenz.albe@cybertec.at>) |
| Ответы |
Re: Foreign tables - oracle_fdw
(Laurenz Albe <laurenz.albe@cybertec.at>)
|
| Список | pgsql-admin |
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, ...).
>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.albe@cybertec.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
В списке pgsql-admin по дате отправления: