TransWikia.com

Connection to an RDS/Postgres foreign table is not working from outside AWS

Stack Overflow Asked by StephenBoesch on January 29, 2021

Consider a foreign data server on another database on the same host:

As postgres:

CREATE SERVER keys
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '3.128.X.Y', port '12345', dbname 'keys');
>> CREATE SERVER

Here is the foreign table:

create foreign table pkey (
     uuid varchar not null,
     pkey varchar not null,
     provisioned boolean not null default false,
     created timestamp default current_timestamp not null,
     modified timestamp default current_timestamp not null
 ) server keys options (schema_name 'public', table_name 'pkey');
>> CREATE FOREIGN TABLE

And the user mapping:

CREATE USER MAPPING FOR clip
        SERVER pkey
        OPTIONS (user 'pubkey', password 'pubkey');
>> CREATE USER MAPPING

Permissions are in place:

grant all on pkey to clip;
>> GRANT

Now let’s try to use the foreign table: as ‘clip’ user:

select count(1) from pkey;
>> ERROR:  could not connect to server "pkey"
>> DETAIL:  could not connect to server: Connection timed out
     Is the server running on host "3.128.X.Y" and accepting
     TCP/IP connections on port 12345?

Note: this connection does work when run from another ec2 instance but does not work from my laptop.

The Security Groups for the RDS instance include individually whitelisted entries for the ec2 instance

Security group
Type                            Rule
default (sg-058f283ad029bf244)  CIDR/IP - Inbound   73.63.Y.Z/32    # Laptop
default (sg-058f283ad029bf244)  CIDR/IP - Inbound   3.128.A.B/32    # EC2 instance
  • The connection from my laptop to the RDS works to the clip DB
  • The connection from my laptop to the RDS does not work to access the pkey foreign table defined in the clip DB
  • The connection from the other ec2 instance to RDS does work for all – including to access the pkey foreign table defined in the clip DB

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP