Обсуждение: Foreign tables - oracle_fdw
Hi,
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 TABLEmytab(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.
At the moment, I am planning to do an Oracle to Aurora-PostgreSQL migration
I have 2 schema, schema1 and schema2.
schema1 will contain new tables.
schema2 has some tables that I will either be using ora2pg or DMS to migrate data from Oracle to PostgreSQL. The data on schema1 will be populated with data from schema2 depending on some logic condition of the tables on schema2.
So the developer will be having some API to run on schema1 do some logic of some on the tables on schema2 and insert the values into the new tables on schema1.
After this, I need oracle_fdw to access some tables in the Oracle Database and then update some column of some table/s on schema1.
And then there will be several users, user1 to user5 for example.
From https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.foreign-data-wrappers.html#postgresql-oracle-fdw, it appears user1 to user5 need to create user mapping and create foreign tables.
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.
Any guidance will be much appreciated. Please advise. Thanks in advance.
Regards,
Ed
On Saturday, May 4, 2024, Edwin UY <edwin.uy@gmail.com> wrote:
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.Any guidance will be much appreciated. Please advise. Thanks in advance.
I’m not really following the specifics but most likely appropriate usage of group roles will ease things.
Though letting users select from the foreign table is just a permissions thing.
David J.
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
On Sun, 2024-05-05 at 15:07 +1200, Edwin UY wrote: > 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. I missed that part. As the documentation says: "The owner of a foreign server can create user mappings for that server for any user." Yours, Laurenz Albe
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
On Sun, 2024-05-05 at 19:29 +1200, Edwin UY wrote: > > 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 See the GRANT statement: https://www.postgresql.org/docs/current/sql-grant.html > 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 :-) If it is not on your "search_path", use schema_name.table_name to reference the (foreign) table. > > 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 :-) It is all in the PostgreSQL documentation. > > I can't login as PUBLIC on Aurora-PostgresSQL or on PostgreSQL itself, can I? That is nologin role and no real role at all. It is a way to say "everybody". I recommend that you spend a few hours with the PostgreSQL documentation, reading up on schemas (https://www.postgresql.org/docs/current/ddl-schemas.html), privileges (https://www.postgresql.org/docs/current/ddl-priv.html) and roles (https://www.postgresql.org/docs/current/user-manag.html). Perhaps then it will start making more sense. Yours, Laurenz Albe