AWS re:Postを使用することにより、以下に同意したことになります AWS re:Post 利用規約

HELP with oracle_fdw - ERROR: permission denied for schema public

0

Hi, I am trying to get oracle_fdw working for PostgreSQL-Aurora to access an on-prem OracleDB following this example: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.foreign-data-wrappers.html#postgresql-oracle-fdw

When running the CREATE FOREIGN TABLE, it gives the error below, can anyone advise what could be wrong? Could it be the Security Group?

CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE');
ERROR:  permission denied for schema public
LINE 1: CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (tab...

From the example, I have tested the connection string below using SQL*Plus

test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name');

Also, the example appears to be creating a table on the Oracle Database, I only want to do select from exiting table/view on the Oracle on-prem from PostgreSQL-Aurora. Is there anyone who has an example for this? Thanks in advance.

質問済み 7ヶ月前950ビュー
4回答
0
0

It looks like user who is trying to build oracle_fdw doesn't have "write" permission on the directory where you unpacked the software, so it cannot create the file in that directory. Give the user permission and then try again

profile picture
回答済み 7ヶ月前
  • Sorry, I did not clarify where the error is coming from. It is from psql, so not during the installation or unpacking of the software.

0

Hi all,

After several Google searches

https://www.cybertec-postgresql.com/en/error-permission-denied-schema-public/

GRANT ALL ON SCHEMA public TO [username] ;

And it is working now.

This was mentioned on https://repost.aws/questions/QUfFqGdruLTna6sYRx3DFmdw/postgres-migration-10-6-11-1-permission-denied-for-schema-public. But I didn't think that is the answer as that link is not about oracle_fdw.

And had confirmed, it doesn't create the table on the Oracle side, I thought it has to, but obviously, the table has to exist on the Oracle side.

Would like to know if anyone else has more example or links to using oracle_fdw.

回答済み 7ヶ月前
0

Hi,

FYI.

Just an update, on the Oracle end, if the grant is to a view, oracle_fdw doesn't work.

I have to do the grant to the table instead and then create a view on the table and then oracle_fdw is happy with it.

I was thinking of posting this as an issue but after thinking about it, this result appears to be the correct one, the extension is not supposed to check permission and expects the object being accessed is by that of the user's.

回答済み 7ヶ月前

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ