FDW Instance Setup - Operational Guide ¶

Overview ¶

Create an RDS instance with postgres_fdw foreign tables pointing to another RDS instance.

Architecture ¶

Steps to Create a New FDW Instance ¶

1. Create FDW reader user on source database ¶

CREATE USER fdw_reader WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE <dbname> TO fdw_reader;
GRANT USAGE ON SCHEMA public TO fdw_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO fdw_reader;

2. Create RDS instance ¶

aws rds create-db-instance \
  --db-instance-identifier <instance-name> \
  --db-instance-class db.t4g.micro \
  --engine postgres \
  --engine-version 14 \
  --master-username postgres \
  --master-user-password <password> \
  --allocated-storage 20 \
  --storage-type gp3 \
  --vpc-security-group-ids <sg-id> \
  --db-subnet-group-name <subnet-group> \
  --no-publicly-accessible \
  --storage-encrypted \
  --backup-retention-period 7 \
  --deletion-protection

3. Configure postgres_fdw ¶

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER <server_name>
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '<source-endpoint>', port '5432', dbname '<dbname>');

CREATE USER MAPPING FOR postgres
  SERVER <server_name>
  OPTIONS (user 'fdw_reader', password '<password>');

4. Create foreign tables ¶

CREATE FOREIGN TABLE public.<table_name> (
    -- columns matching source table
)
SERVER <server_name>
OPTIONS (schema_name 'public', table_name '<source_table_name>');

5. Create application user ¶

CREATE USER <app_user> WITH PASSWORD '<password>';
GRANT USAGE ON FOREIGN SERVER <server_name> TO <app_user>;
GRANT USAGE ON SCHEMA public TO <app_user>;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO <app_user>;

Useful Commands ¶

-- List foreign servers
\des+

-- List foreign tables
\dE+

-- View table grants
\dp

-- Drop user with foreign server grants
REVOKE ALL ON FOREIGN SERVER <server_name> FROM <user>;
DROP USER <user>;

Files ¶

• fdw-dev-setup.sql - Complete DDL for fdw-dev instance