Re: Foreign tables - oracle_fdw

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