GCLOUD Postgres, using foreign data wrapper extesion results permission denied for relation

Question

I'm really stuck with the following problem.

At GCloud SQL I have a running postgres' instance. That instance contains two databases. From one database (source_db) I want to access to another database's (another_db) table (foreign_table) using postgres_fdw extension. The recipe I'm employing currently is this:

1)

CREATE EXTENSION postgres_fdw; 

CREATE SERVER foreign_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'another_db', port '5432', host '<A_PRIVATE_IP>'); 

CREATE USER MAPPING for guest
SERVER foreign_db
OPTIONS (user 'guest', password 's3cr3t'); 

Update After Laurenz's answer

The thing was here I need to use an user/role that exists on the remote server and has access to the desired remote table.

So I needed to do something like this:

CREATE USER MAPPING for guest
SERVER foreign_db
OPTIONS (user 'foreign_db_role', password 's3cr3t'); 

CREATE FOREIGN TABLE foreign_table
(
 // columns descripions
)
SERVER foreign_db OPTIONS (table_name 'foreign_table');

-- Alternatively I also tried with
CREATE SCHEMA external;
IMPORT FOREIGN SCHEMA public from SERVER foreign_db into external;

GRANT SELECT ON TABLE foreign_table TO guest;

The above commands runs without error, but when I tried to actually access the table I got this:

If using "external" schema

source_db=> select 1 from external.foreign_table limit 1;
ERROR:  permission denied for relation foreign_table
CONTEXT:  Remote SQL command: SELECT NULL FROM public.foreign_table (*)

If not using "external" schema

source_db=> select 1 from foreign_table limit 1;
ERROR:  permission denied for relation foreign_table
CONTEXT:  Remote SQL command: SELECT NULL FROM public.foreign_table

The only thing that smells a little is that the error message (at *) displays "public.foreign_table" instead of "external.foreign_table" even when I'm using external schema... but i don't know is that actually means something :S

As far I researched there is no way to login into the posgres instance as a superuser as that is not allowed by the Gcloud's SQL services neither a way to edit the pg_hba.conf file in order to adjust client's authentication affairs.

I searched in a lot of places but without finding what i can do to sort this out. Among the sites and pages i looked are the below list

• The official documentation
• A personal blog's post
• This other SO post having a related issue
• This post and this other post regarding permissions and authorizations.
• A Nice tutorial about authentication and authorization

P.S.

I was able to make this on a postgres' instance that i ran locally.

Answer 1

User guest on the remote server doesn't have permissions to SELECT from the table. Since the query on the remote server is executed as user guest, you get an error.

GRANT the SELECT privilege on the table on the remote server to the user.