pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign tables - oracle_fdw
6+ messages / 3 participants
[nested] [flat]
* Foreign tables - oracle_fdw
@ 2024-05-05 03:07 Edwin UY <edwin.uy@gmail.com>
2024-05-05 03:57 ` Re: Foreign tables - oracle_fdw David G. Johnston <david.g.johnston@gmail.com>
2024-05-05 03:58 ` Re: Foreign tables - oracle_fdw Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-05 04:02 ` Re: Foreign tables - oracle_fdw Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 6+ messages in thread
From: Edwin UY @ 2024-05-05 03:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.Exte...
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.
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.Exte...,
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Foreign tables - oracle_fdw
2024-05-05 03:07 Foreign tables - oracle_fdw Edwin UY <edwin.uy@gmail.com>
@ 2024-05-05 03:57 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2024-05-05 03:57 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
https://www.postgresql.org/docs/current/role-membership.html
Though letting users select from the foreign table is just a permissions
thing.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Foreign tables - oracle_fdw
2024-05-05 03:07 Foreign tables - oracle_fdw Edwin UY <edwin.uy@gmail.com>
@ 2024-05-05 03:58 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-05 07:29 ` Re: Foreign tables - oracle_fdw Edwin UY <edwin.uy@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2024-05-05 03:58 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Foreign tables - oracle_fdw
2024-05-05 03:07 Foreign tables - oracle_fdw Edwin UY <edwin.uy@gmail.com>
2024-05-05 03:58 ` Re: Foreign tables - oracle_fdw Laurenz Albe <laurenz.albe@cybertec.at>
@ 2024-05-05 07:29 ` Edwin UY <edwin.uy@gmail.com>
2024-05-06 07:11 ` Re: Foreign tables - oracle_fdw Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 6+ messages in thread
From: Edwin UY @ 2024-05-05 07:29 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
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.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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Foreign tables - oracle_fdw
2024-05-05 03:07 Foreign tables - oracle_fdw Edwin UY <edwin.uy@gmail.com>
2024-05-05 03:58 ` Re: Foreign tables - oracle_fdw Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-05 07:29 ` Re: Foreign tables - oracle_fdw Edwin UY <edwin.uy@gmail.com>
@ 2024-05-06 07:11 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2024-05-06 07:11 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Foreign tables - oracle_fdw
2024-05-05 03:07 Foreign tables - oracle_fdw Edwin UY <edwin.uy@gmail.com>
@ 2024-05-05 04:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 6+ messages in thread
From: Laurenz Albe @ 2024-05-05 04:02 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-05-06 07:11 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-05 03:07 Foreign tables - oracle_fdw Edwin UY <edwin.uy@gmail.com>
2024-05-05 03:57 ` David G. Johnston <david.g.johnston@gmail.com>
2024-05-05 03:58 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-05 07:29 ` Edwin UY <edwin.uy@gmail.com>
2024-05-06 07:11 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-05-05 04:02 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox