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