Oracle error on a foreign table I have no privilege on

[philflorent]

Hi,

I noticed a behaviour I didn't expect. Not really a bug but I obtained an Oracle error instead of a PostgreSQL error with a foreign table I had no privilege on.

-- superuser
prodige31=*> select oracle_diag();
                                                           oracle_diag
----------------------------------------------------------------------------------------------------------------------------------
 oracle_fdw 2.5.0devel, PostgreSQL 13.7 (Debian 13.7-0+deb11u1), Oracle client 19.15.0.0.0, ORACLE_HOME=/opt/oracle/instantclient
(1 ligne)


drop foreign table if exists orcl_usr_prodige31.user_col_comments;

-- superuser, bad syntax
create foreign table if not exists orcl_usr_prodige31.user_col_comments(
table_name text,
column_nameBIDON  text,
comments text
)
SERVER orcl_usr_prodige31 OPTIONS (table '(SELECT table_name, column_nameBIDON, comments FROM user_col_comments where table_name not in (select mview_name from user_mviews) and table_name not in (select table_name from user_external_tables))');

\dE+ orcl_usr_prodige31.user_col_comments
                                             Liste des relations
       Schéma       |        Nom        |      Type      | Propriétaire | Persistence | Taille  | Description
--------------------+-------------------+----------------+--------------+-------------+---------+-------------
 orcl_usr_prodige31 | user_col_comments | table distante | postgres     | permanent   | 0 bytes |
(1 ligne)


-- normal user with usage on schema usr_prodige31 but no privilege on user_col_comments
prodige31=> select count(1) from orcl_usr_prodige31.user_col_comments ;
ERREUR:  error describing remote table: OCIStmtExecute failed to describe table
DÉTAIL : ORA-00904: "COLUMN_NAMEBIDON" : identificateur non valide

-- superuser
drop foreign table if exists orcl_usr_prodige31.user_col_comments;

-- syntax OK
create foreign table if not exists orcl_usr_prodige31.user_col_comments(
table_name text,
column_name  text,
comments text
)
SERVER orcl_usr_prodige31 OPTIONS (table '(SELECT table_name, column_name, comments FROM user_col_comments where table_name not in (select mview_name from user_mviews) and table_name not in (select table_name from user_external_tables))');

-- normal user with usage on schema usr_prodige31 but no privilege on user_col_comments
prodige31=> select count(1) from orcl_usr_prodige31.user_col_comments ;
ERREUR:  droit refusé pour la table distante user_col_comments

-- superuser
prodige31=# grant select on orcl_usr_prodige31.user_col_comments to usr_prodige31;
GRANT
prodige31=*# commit;
COMMIT

-- normal user 
prodige31=> select count(1) from orcl_usr_prodige31.user_col_comments ;
 count
-------
  1487
(1 ligne)
 

I thought I would obtain ERREUR: droit refusé pour la table distante user_col_comments (=> no privilege on foreign table) with my first select count(1) even if the Oracle DDL was wrong.

Best regards,
Phil

[laurenz]

oracle_fdw runs queries against the foreign table when it plans the statement, which obviously happens before permissions on the table are checked.
It seems to be normal PostgreSQL behavior that planning happens before the permission check. The oddness happens because that queries the Oracle table. I agree that it looks weird, but is it a problem?

[philflorent]

Not just a general question. I thought permissions were checked before planning since it sounds useless to plan something you won't have the right to execute and you have to check permission anyway but planning is light and most executions have the necessary privileges in a normal database...no matter.

[laurenz]

Still, I see your point. I debugged a little, and the permission error is thrown from the query executor after the query is planned.
I'll have to investigate some more if there is really no permission check before the call to set_rel_size from make_one_rel. Perhaps I should add one to oracle_fdw...

The root of this oddity is certainly that I connect to Oracle in the query planning phase, perhaps PostgreSQL does not anticipate that.

[philflorent]

Thanks. It's not critical at all and in fact my unprivileged user does have the right to query Oracle via its valid user mapping and to create foreign tables. Hence the situation I describe does not have any real-world logic : it's something I still have to deal with for legacy reasons in my ad-hoc Oracle=>PostgreSQL migration tool but my security model does not work like that anymore in the main programs.

[laurenz]

I have investigated some more, and postgres_fdw does the same thing when you turn on use_remote_estimate.
So I guess that is OK. You could bring it up on the -hackers mailing list, perhaps it should be fixed in core.
I will close this as "not a bug" for the time being.

Anyway, thanks for the heads up. It is certainly good to be aware of this.

[philflorent]

Thanks. I sent a mail to the hackers list as you suggested. Best regards, Phil

…

________________________________ De : Laurenz Albe ***@***.***> Envoyé : samedi 4 juin 2022 21:42 À : laurenz/oracle_fdw ***@***.***> Cc : philflorent ***@***.***>; Author ***@***.***> Objet : Re: [laurenz/oracle_fdw] Oracle error on a foreign table I have no privilege on (Issue #534) Closed #534<#534> as completed. — Reply to this email directly, view it on GitHub<#534 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AOP2WEEF2I7VNSYFIJWP45TVNOWTJANCNFSM5XVDORMQ>. You are receiving this because you authored the thread.Message ID: ***@***.***>