Feature Request: Support AWS AssumeRole for Enhanced Security

[duncancfraser]

What feature are you requesting?

Summary

Request to add support for AWS AssumeRole access patterns in pg_mooncake to enable more secure credential management and avoid storing long-term AWS credentials.

Why are you requesting this feature?

Problem Statement

Currently, pg_mooncake only supports static AWS credentials (AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY), which creates several security concerns:

1. Long-term credential exposure: Static credentials are more vulnerable to compromise
2. Credential rotation challenges: Manual rotation of static credentials across environments
3. Principle of least privilege violations: Static credentials often have broader permissions than needed
4. Compliance issues: Many organizations require temporary credentials for security compliance

Use Case

In our data pipeline architecture, we use pg_mooncake to load Parquet files from S3 to Neon PostgreSQL. Our current setup requires:

• Snowflake unloading data to S3 using storage integrations with assumeRole
• pg_mooncake loading data from S3 to Neon using static credentials

This creates an inconsistent security model where one part of the pipeline uses temporary credentials while another requires long-term credentials.

Security Benefits

1. Temporary credentials: Credentials expire automatically, reducing exposure window
2. Consistent security model: Aligns with other AWS services and best practices
3. External ID support: Enables cross-account access with additional security controls
4. Session naming: Better audit trails and monitoring
5. Credential rotation: Automatic rotation through role assumption

What is your proposed implementation for this feature?

Proposed Solution

Add support for AWS AssumeRole in pg_mooncake with the following capabilities:

Option 1: Environment Variable Configuration

-- Set assumeRole configuration via environment variables
SET mooncake.aws_role_arn = 'arn:aws:iam::123456789012:role/MyRole';
SET mooncake.aws_external_id = 'optional-external-id';
SET mooncake.aws_session_name = 'pg_mooncake_session';

-- Use temporary credentials automatically
SELECT * FROM mooncake.read_parquet('s3://bucket/path/file.parquet');

Option 2: Session-Level Configuration

-- Configure assumeRole for current session
SELECT mooncake.assume_role(
    role_arn := 'arn:aws:iam::123456789012:role/MyRole',
    external_id := 'optional-external-id',
    session_name := 'pg_mooncake_session'
);

-- Subsequent operations use temporary credentials
SELECT * FROM mooncake.read_parquet('s3://bucket/path/file.parquet');

Option 3: Per-Operation Configuration

-- Specify assumeRole parameters per operation
SELECT * FROM mooncake.read_parquet(
    's3://bucket/path/file.parquet',
    role_arn := 'arn:aws:iam::123456789012:role/MyRole',
    external_id := 'optional-external-id'
);

[Jan-M]

I am curious how others do this with open source Postgres on AWS.

It would be amazing if Postgres users could have individual AWS IAM Roles/Profiles. This would help in extensions that access AWS products that are specific to the Postgres users and not the Postgres infrastructure. E.g. the Postgres users gets S3 access for DuckDB/Mooncake but not to WAL/Backup buckets/paths.

On OS process level this can be done with profiles/credentials but in Postgres I have not seen a way to tweak the environment on a per user bases (which would kick in when the backend spawns).

Just using this issue as it has similar topic :D