From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Edwin UY <edwin.uy@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Foreign tables - oracle_fdw
Date: Mon, 06 May 2024 09:11:10 +0200
Message-ID: <d32da6678e4092bcaec0fb77031a1f2d9224a2d4.camel@cybertec.at> (raw)
In-Reply-To: <CA+wokJ8+WwpUVUfX7oxvhVaOaOyW0yoR_UK25fWRNuXcuE=eXg@mail.gmail.com>
References: <CA+wokJ-WsCCdp+jihrC0VCwDroPjSfzy3fy46-N2yTG4p=xMfA@mail.gmail.com>
<0aea4ab65af243d2a326ee92df68bd9309c64433.camel@cybertec.at>
<CA+wokJ8+WwpUVUfX7oxvhVaOaOyW0yoR_UK25fWRNuXcuE=eXg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, edwin.uy@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Foreign tables - oracle_fdw
In-Reply-To: <d32da6678e4092bcaec0fb77031a1f2d9224a2d4.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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