AWS RDS PostgreSQL FDW with IAM authentication

< Home

Want to connect AWS RDS PostgreSQL instances using Foreign Data Wrappers and IAM authentication? Yes, it’s totally possible and here is how you can do it.

What is postgres_fdw?

PostgreSQL Foreign Data Wrapper is a Foreign Data Wrapper that lets one PostgreSQL database query another like it’s a local table—without replicating data. On AWS RDS, this can enable cross-database querying securely, especially when paired with IAM authentication tokens. Note that you have many FWD’s and you can even query S3 buckets, Excel files,… with it as long as the extension is available.

Architectural overview

We’ll spin up two RDS PostgreSQL instances:

sales: contains the sales table
users: contains the users table and will use postgres_fdw to access sales-db

Authentication will be done via IAM tokens instead of passwords. We’ll include a Lambda function to refresh the token every 10 minutes.

Such great architectural overview

Setting Up PostgreSQL + FDW

Step 1: Create the Tables

Sales-DB

We do a quick connection to the sales database

psql -h sales.chcamhlst0m.eu-central-1.rds.amazonaws.com -U salesadmin sales

and quickly create and populate the sales table

CREATE TABLE sales.sales (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    product VARCHAR(100),
    amount DECIMAL(10,2)
);

INSERT INTO sales.sales (user_id, product, amount) VALUES
(1, 'Laptop', 1200.00),
(2, 'Phone', 800.00),
(1, 'Monitor', 250.00),
(3, 'Keyboard', 100.00);

Users-DB

We do a quick connection to the users database

psql -h users.chcamhlst0m.eu-central-1.rds.amazonaws.com -U useradmin users

and quickly create and populate the users table

CREATE TABLE users.users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO users.users (name, email) VALUES
('Tom Stroobants', '[email protected]'),
('Bob Smith', '[email protected]'),
('Charlie Lee', '[email protected]');

Step 2: Enable and Configure FDW

Users-DB

On the users-db (we will refer to this as the source database as well) we enable FDW and define our target (sales in this case).

-- Enable extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Define foreign server
CREATE SERVER salesdb_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'sales.chcamhlst0m.eu-central-1.rds.amazonaws.com', dbname 'sales', port '5432');

Sales-DB

On the sales-db (target-db) we create a role that the users-db (source-db) will use and we define that it can only login with IAM tokens. The grants are basic grants to allow read permissions on the sales-schema (target-schema).

CREATE USER userdb_role WITH LOGIN;
GRANT rds_iam TO userdb_role;
GRANT CONNECT ON DATABASE sales TO userdb_role;
GRANT USAGE ON SCHEMA public TO userdb_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO userdb_role;

Step 3: Generate an IAM token

We do a standard generate-db-auth-token for this.

aws rds generate-db-auth-token \
  --hostname sales.chcamhlst0m.eu-central-1.rds.amazonaws.com \
  --port 5432 \
  --username userdb_role \
  --region eu-central-1

which will return a 15 minute token looking like this

sales.chcamhlst0m.eu-central-1.rds.amazonaws.com:5432/?Action=connect&DBUser=userdb_role&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAZ6C27ST0MYDCEL%2F20250618%2Feu-central-1%2Frds-db%2Faws4_request&X-Amz-Date=20250618T185757Z&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Signature=1aa0c49d00cd18f4e2346a8905ceec017bcfea3fe64f97a36f313be5

We can then create a USER MAPPING on our user-db (source-db) that says “the role useradmin will use the role ‘userdb_role’ with password ‘iam token’ to connect to the foreign server ‘salesdb_server’”.

CREATE USER MAPPING FOR useradmin SERVER salesdb_server OPTIONS (user 'userdb_role', password 'sales.chcamhlst0m.eu-central-1.rds.amazonaws.com:5432/?Action=connect&DBUser=userdb_role&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAZ6C27ST0MYDCEL%2F20250618%2Feu-west-1%2Frds-db%2Faws4_request&X-Amz-Date=20250618T185406Z&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Signature=1aa0c49d00cd18f4e2346a8905ceec017bcfea3fe64f97a36f313be5')

4. Import Foreign Schema

A part of FDW is that on the source-db a query still wants to know the structure of how it looks like on the target-db. Instead of doing a lot of manual syncs (like with pglogical), the PostgreSQL FDW provides a handy feature where you can just import the foreign tables with a simple command or even the whole schema (and you can import a whole schema but exclude some tables). This makes it much easier to automate the whole process. Note that this won’t start importing data! With FDW, no foreign data will ever be storaged on the source-database.

To keep everything nice and tidy I always create a separate schema to import these foreign tables into.

CREATE SCHEMA fdw_sales;
IMPORT FOREIGN SCHEMA sales FROM SERVER salesdb_server INTO fdw_sales;

5. Run the Cross-Database Query

users=> SELECT * FROM fdw_sales.sales;
 id | user_id | product  | amount
----+---------+----------+---------
  1 |       1 | Laptop   | 1200.00
  2 |       2 | Phone    |  800.00
  3 |       1 | Monitor  |  250.00
  4 |       3 | Keyboard |  100.00
(4 rows)

6. Run a JOIN query to showcase the power of FDW

users=> SELECT u.name, sum(s.amount) FROM users.users u JOIN fdw_sales.sales s ON u.id = s.user_id GROUP BY u.name;
      name      |   sum
----------------+---------
 Bob Smith      |  800.00
 Charlie Lee    |  100.00
 Tom Stroobants | 1450.00

7. Token Expiry & Auto-Refresh

So, what happens after 15 minutes and you run the query another time?

users=> SELECT u.name, sum(s.amount) FROM users.users u JOIN fdw_sales.sales s ON u.id = s.user_id GROUP BY u.name;
    name      |   sum
----------------+---------
Bob Smith      |  800.00
Charlie Lee    |  100.00
Tom Stroobants | 1450.00

Eh? Well, as long as you are logged in. FDW will keep the connection alive. But as soon as you log out that connection will die. So remember, the connection created by FDW is kept alive as long as the session that started it!

as soon as your session dies and you create a new session and try to run the query again you will see

users=> SELECT * FROM fdw_sales.sales;
ERROR:  could not connect to server "salesdb_server"
DETAIL:  connection to server at "sales.chcamhlst0m.eu-central-1.rds.amazonaws.com" (3.74.100.225), port 5432 failed: FATAL:  PAM authentication failed for user "userdb_role"
connection to server at "sales.chcamhlst0m.eu-central-1.rds.amazonaws.com" (3.74.100.225), port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "3.124.69.222", user "userdb_role", database "sales", no encryption

At this point we will need to refresh the token (step 3 but we alter now the user mapping)

users=> ALTER USER MAPPING FOR useradmin SERVER salesdb_server OPTIONS (SET password 'sales.chcamhlst0m.eu-central-1.rds.amazonaws.com:5432/?Action=connect&DBUser=userdb_role&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAZ6C27ST0MYDCEL%2F20250618%2Feu-central-1%2Frds-db%2Faws4_request&X-Amz-Date=20250618T195729Z&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Signature=ba1e3adef84515208273d01faee47ed42fb56457a1e6d2f2c19f');
ALTER USER MAPPING
users=> SELECT * FROM fdw_sales.sales WHERE id > 0;
 id | user_id | product  | amount
----+---------+----------+---------
  1 |       1 | Laptop   | 1200.00
  2 |       2 | Phone    |  800.00
  3 |       1 | Monitor  |  250.00
  4 |       3 | Keyboard |  100.00
(4 rows)

What happens inside 15 minutes

What happens if your session is killed but you log back in before the 15 minutes have passed? That will not be a problem at all. The IAM Authentication token at this point is still valid.

users=> exit


PS C:\Users\tom> psql -h users.chcamhlst0m.eu-central-1.rds.amazonaws.com -U useradmin users
Password for user useradmin:

psql (17.5)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)
Type "help" for help.

users=> SELECT * FROM fdw_sales.sales WHERE id > 0;
 id | user_id | product  | amount
----+---------+----------+---------
  1 |       1 | Laptop   | 1200.00
  2 |       2 | Phone    |  800.00
  3 |       1 | Monitor  |  250.00
  4 |       3 | Keyboard |  100.00
(4 rows)

Automating with Lambda

1. Creating the Pyscopg2 layer

We are going to do it with Python! First we will need the python library psycopg2 for Postgresql connections. Best way to do this is with a Lambda-layer so you can easily re-use this over all your Lambda’s

1.1 Retrieve the correct psycopg2-binary

mkdir python
cd python
pip3 install --platform manylinux2014_x86_64 --target . --python-version 3.13 --only-binary=:all: psycopg2-binary

1.2 Package the psycopg2-binary into a Lambda-layer zip

MacOS, Linux

cd ..
zip -r psycopg2-lambda-layer.zip python

Windows

cd ..
Compress-Archive python/ psycopg2-lambda-layer.zip

1.3 Upload the zip as a Lambda Layer

Follow the official documentation or try it out with Terraform

Make sure when uploading it as a Lambda Layer to select the correct architecture (x86_64) and Python version (3.13)

The RDS IAM token Lambda

1.1 Example Lambda

Here is a quickly created Lambda that showcases how it works.

When your RDS is using master-credentials rotation, rework it to use the secret.
The Lambda-role will need following permissions
RDS IAM Token generator
EC2 permissions to run in a subnet (if your RDS is privately set)
KMS permissions if you work with secrets

import boto3
import psycopg2
import json

def lambda_handler(event, context):
    source = {
        "db_name": event.get('source_db_name', 'users'),
        "region": event.get('source_region', 'eu-central-1'),
        "hostname": event.get('source_hostname'),
        "port": event.get('source_port', 5432),
        "master_username": event.get('source_master_username'),
        "master_password": event.get('source_master_password'),
        "fwd_server_name": event.get('source_fwd_server_name'),
        "fwd_user_mapping_name": event.get('source_fwd_user_mapping_name')
    }

    target = {
        "region": event.get('target_region', 'eu-central-1'),
        "hostname": event.get('target_hostname'),
        "port": event.get('target_port', 5432),
        "username": event.get("target_username")
    }

    # Step 1: Generate an IAM authentication token for the target database

    token = boto3.client('rds', region_name=target['region']).generate_db_auth_token(
        DBHostname=target['hostname'],
        Port=target['port'],
        DBUsername=target['username'],
        Region=target['region']
    )

    # Step 2: Connect to the source database using master credentials
    try:
        conn = psycopg2.connect(
            host=source['hostname'],
            port=source['port'],
            user=source['master_username'],
            password=source['master_password'],
            dbname=source['db_name'],
            sslmode='require'
        )
        conn.autocommit = True
        cursor = conn.cursor()

        # Step 3: Enable IAM auth for the given user
        # This grants the rds_iam role to allow IAM-based login
        cursor.execute(f" ALTER USER MAPPING FOR {source['fwd_user_mapping_name']} SERVER {source['fwd_server_name']} OPTIONS (SET password '{token}');")

        cursor.close()
        conn.close()
        return {
            'statusCode': 200,
            'message': f"Token updated"
        }
    except Exception as e:
        return {
            'statusCode': 500,
            'error': str(e)
        }

1.2 The trigger event

{
  "source_db_name":"users",
  "source_hostname": "users.chcamhlst0m.eu-central-1.rds.amazonaws.com",
  "source_master_username": "useradmin",
  "source_master_password": "userpass",
  "source_fwd_server_name": "salesdb_server",
  "source_fwd_user_mapping_name": "useradmin",
  "target_hostname": "sales.chcamhlst0m.eu-central-1.rds.amazonaws.com",
  "target_username": "userdb_role"
}

1.3 Amazon EventBridge Scheduler

Very straightforward and again, the official documentation is much better in explanation than I ever will be.

< Home

AWS RDS PostgreSQL FDW with IAM authentication

Tom Stroobants

2025-06-18

What is postgres_fdw?

Architectural overview

Setting Up PostgreSQL + FDW

Step 1: Create the Tables

Sales-DB

Users-DB

Step 2: Enable and Configure FDW

Users-DB

Sales-DB

Step 3: Generate an IAM token

4. Import Foreign Schema

5. Run the Cross-Database Query

6. Run a JOIN query to showcase the power of FDW

7. Token Expiry & Auto-Refresh

What happens inside 15 minutes

Automating with Lambda

1. Creating the Pyscopg2 layer

1.1 Retrieve the correct psycopg2-binary

1.2 Package the psycopg2-binary into a Lambda-layer zip

MacOS, Linux

Windows

1.3 Upload the zip as a Lambda Layer

The RDS IAM token Lambda

1.1 Example Lambda

1.2 The trigger event

1.3 Amazon EventBridge Scheduler