redshift-mcp-server: support AWS IAM Identity Center (trusted identity propagation) for per-user database identities

[jbclink]

Summary

Please consider supporting AWS IAM Identity Center (IdC) authentication in redshift-mcp-server, so each user resolves to their own IdC-group-mapped Redshift identity (awsidc:<user>) rather than everyone sharing one permission-set role.

Current behavior

Authentication is the ambient boto3 credential chain and nothing else:

session = boto3.Session(profile_name=self.aws_profile, region_name=self.aws_region)
self._redshift_data_client = session.client('redshift-data', config=self._config)

The only auth-related environment variables read are AWS_PROFILE, AWS_REGION, and AWS_DEFAULT_REGION. There's no configuration for DbUser, a Secrets Manager ARN, an IdC token, federation parameters, or any auth-mode selection — and no hook for supplying a custom credentials provider. Because _execute_statement never passes DbUser or SecretArn, the Data API falls through to GetClusterCredentialsWithIAM, so the database identity is always IAMR:<RoleSessionName> — the permission-set role.

The practical effect: everyone on a given SSO permission set connects as the same Redshift user. SELECT CURRENT_USER returns an identical IAMR:AWSReservedSSO_<PermissionSet>_<suffix> for all of them, so Redshift's own logs — SYS_QUERY_HISTORY, stl_connection_log — cannot separate one person's activity from another's.

To be fair to the current design: this isn't a total attribution blackout. CloudTrail does resolve the individual, because the assumed-role ARN's session name carries the IdC user (.../AWSReservedSSO_<PermissionSet>_<suffix>/person@example.com), and Data API calls log under redshift-data.amazonaws.com. So "who called ExecuteStatement" is answerable today.

But it's a different system answering a narrower question, and the seams show:

• It attributes the API call, not what happened inside the database — no object-level view of what was read, which is what SOX/PII-scoped auditing actually needs.
• requestParameters.sessionId is never populated, so CloudTrail events can't be correlated back to a Data API session.
• Retention and ergonomics differ sharply (90 days, paged at 50 events/request and throttled, with lookup-events silently truncating on wide EventName-filtered scans) versus just reading the identity off the query in Redshift.

So CloudTrail is a workable forensic backstop, not a substitute for the database knowing who is connected. With IdC, SELECT CURRENT_USER simply returns awsidc:person@example.com and every in-database log inherits that for free.

This already works elsewhere in the AWS stack

The Redshift Data API already resolves IdC identities, with no new parameters required. An ordinary execute-statement call maps to an awsidc:<user> database user automatically, provided the caller holds an identity-enhanced role session (one carrying sts:identity_context). Query Editor v2 and SageMaker Unified Studio Query Editor both rely on exactly this — I've confirmed a Data-API-backed session from SageMaker Unified Studio resolving as awsidc:<my-email> with database roles derived from IdP group membership.

So the gap isn't on the Data API side. It's that this server has no way to obtain identity-enhanced credentials.

Scoping — not a one-liner, but not blocked on anything unbuilt

• The primitives exist in boto3 today. sso-oidc.create_token_with_iam() and sts.assume_role(ProvidedContexts=[...]) are both available, so an identity-enhanced session is fully constructible with the current SDK. What's missing is any convenience layer — no identity-aware Session, no credential_process that produces one — so the server would need to perform the exchange itself.
• One trap worth flagging, since it's easy to design around incorrectly: the cached aws sso login token in ~/.aws/sso/cache is scoped to sso:account:access and carries no audience claim for a Redshift IdC application, so it can't be replayed as the subject token. A usable assertion needs either a Trusted Token Issuer configured in IdC (the IdP issuing audience-scoped JWTs — see aws-samples/access-aws-services-programmatically-using-tip) or a browser-based OIDC flow.
• There's already working client code inside AWS to borrow from. redshift_connector ships BrowserIdcAuthPlugin, which performs the browser OIDC flow and produces awsidc: identities end-to-end with no Trusted Token Issuer setup. To be clear, I'm not suggesting switching transport to redshift_connector — staying on the Data API seems right: no network path to the cluster required, identical for provisioned and Serverless, and it preserves the async execution model this server is built on. The point is narrower: the hard part (token acquisition) is already solved and shipping, and that logic could be reused to obtain the session while execution continues through the Data API unchanged.

If the cleanest answer is actually an SDK-level improvement — a first-class identity-enhanced session helper in boto3 that this server and other tools could just consume — that would solve it far more broadly, and I'd support that framing. The ask is the capability, not a particular implementation.

To be clear though: wherever the plumbing ends up living, IdC support through the Redshift MCP server itself is the outcome that's needed. An SDK helper is a means to it, not a substitute for it — the server still has to opt into using it and expose the configuration. So if this turns out to be blocked on or better solved by an upstream SDK change, I'd ask that this issue stay open tracking the server-side adoption rather than be closed as out-of-scope, and I'm glad to file the corresponding SDK request and link the two.

The Redshift side is straightforward (and already works)

For anyone else wiring this up, the cluster-side registration is a single statement:

CREATE IDENTITY PROVIDER <provider_name> TYPE AWSIDC
    NAMESPACE 'awsidc'
    APPLICATION_ARN 'arn:aws:sso::<idc-account-id>:application/<ssoins-id>/<apl-id>'
    IAM_ROLE 'arn:aws:iam::<cluster-account-id>:role/<TrustedIdentityPropagationRole>';

...plus namespace-prefixed wrapper roles mapping IdC groups onto existing database roles:

CREATE ROLE "awsidc:<IdC-group-display-name>";
GRANT ROLE <existing_db_role> TO ROLE "awsidc:<IdC-group-display-name>";

(Leaving AUTO_CREATE_ROLES off means new mappings require explicit administrator action.) With that in place, every AWS-native client resolves users correctly — this MCP server is the only one that can't take advantage of it.

Why it matters

Enterprises overwhelmingly manage workforce identity through an external IdP federated via IdC — that's the assumed model for essentially every other Redshift access path AWS ships.

The part that matters most in practice isn't the identity string, it's how access gets administered. With IdC, entitlements live in the IdP: a person is added to an IdP group, that group syncs to IdC (SCIM), and the IdC group maps to a Redshift database role via a namespaced wrapper role. Granting someone the right level of warehouse access becomes a group membership change — no per-person database grants to hand-maintain, no drift between "who should have PII access" and who actually does, and offboarding is the same single action that removes their other access. Existing database roles are reused rather than rebuilt, and leaving AUTO_CREATE_ROLES off keeps new mappings a deliberate administrator action.

That's what makes it scale. We have this working today against a provisioned cluster for SageMaker Unified Studio Query Editor: a user signs in through the IdP, SELECT CURRENT_USER returns awsidc:<their-email>, and all of their database roles — including separately-scoped PII and finance-regulated ones — resolve from their IdP group membership with no per-user database administration at all. It took one CREATE IDENTITY PROVIDER statement plus one wrapper role per group.

None of that is available through this MCP server, so the same organization ends up running two different access models side by side: group-driven per-user identity for every other client, and a single shared role for the MCP path. The shared role is a reasonable short-term stopgap — it's what we're running — but it's the one path where entitlements and attribution both stop working the way the rest of the estate works.

There's also a least-privilege problem that per-user identity solves and a shared role structurally cannot. A permission-set role has to carry the union of what everyone using it needs. So the moment one person on that permission set requires PII or finance-regulated access, the shared database identity carries those roles for everyone on it — including people whose own entitlements are far narrower. Every query through the MCP path then runs with more privilege than the person behind it actually holds. With awsidc:<user>, each person connects with exactly their own group-derived roles and nothing more.

That directly limits how far the tool can be rolled out. We deliberately restrict MCP access to a small group of power users, and the shared over-privileged identity is a significant reason why — broadening it would mean handing that same union of roles to everyone added. Per-user IdC identity is the thing that would let this scale past a pilot group to the analysts and engineers who'd benefit most.

Finally, attribution arguably matters more here than for a conventional SQL client, not less. When a person types a query, there's a human who knows what they ran and why. When an assistant composes and executes SQL on someone's behalf — at higher volume, and increasingly unattended — the identity on the connection is the primary record of whose intent a statement represents. An MCP server is exactly the wrong place for that identity to collapse into a shared role, and that becomes more pointed if opt-in write capability lands (see the companion request): "someone on this permission set created and populated this table" is not an audit trail anyone wants to defend.

One tailwind: Redshift patch 204 (2026-08-11) removed the last infrastructure caveat here — "AWS IAM Identity Center authentication now works on clusters and workgroups with enhanced VPC routing enabled." That had been the main configuration-level blocker to standardizing on IdC for Redshift.

What we're doing in the meantime, and why it's a poor substitute

Since we don't know the timeline here, we're rolling out an interim workaround: a separate AWS SSO permission set per role-combination. Rather than one shared identity carrying the union of everyone's entitlements, we create a permission set for each distinct combination of Redshift access someone might need, map each to its own Redshift database user, and grant that user only the database roles for that combination. People then assume whichever SSO role matches their actual entitlements.

It restores least privilege and it will work. But the shape of it shows what the gap costs. Our database role model is small and deliberately stable — one baseline role everyone holds, plus three independently-granted sensitive-data roles that have been in place, unchanged, for a long time. Covering every valid combination of those three still means eight permission sets, each needing its own SSO permission set, IAM role, Redshift database user, and role grants, all hand-maintained. Eight objects of scaffolding to express what the database already models as four roles.

That's manageable for us precisely because we've kept the role model tight and don't plan to grow it — the workaround isn't earning that, our design discipline is. The multiplier is still structural: each additional independently-granted dimension doubles the combinations, so this approach only stays tractable for organizations whose role model already looks like ours.

Two things it doesn't fix at all:

• Entitlements move back out of the IdP. The thing IdC does well — group membership driving database roles — is exactly what this gives up. Access changes become permission-set assignment plus database-side maintenance, instead of adding someone to a group.
• Attribution doesn't improve. Everyone sharing a given combination still shares one database identity. It narrows privilege; it doesn't restore per-user identity.

With IdC, none of this scaffolding exists — one identity provider registration plus one wrapper role per group, and combinations resolve naturally from group membership rather than being enumerated in advance.

We're hopeful this lands, and we're not assuming it's been ignored — most AWS services and tools already support IdC with Redshift today, and in our experience the surrounding tooling tends to catch up somewhat later than the core service does. That's the pattern we'd expect here too; this issue is mainly to make sure it's on the list, and to offer a customer with a working IdC deployment to test against.

Possible shapes

1. Perform the CreateTokenWithIAM → AssumeRole(ProvidedContexts=...) exchange in the server, gated behind opt-in config (IdC application ARN / issuer URL).
2. Reuse redshift_connector's BrowserIdcAuthPlugin OIDC flow for token acquisition while keeping Data API execution.
3. Expose a pluggable auth / credentials-provider interface so operators can supply an identity-enhanced session themselves — the most flexible, and it would also serve #917 and #3708.

Happy to test against a real IdC-integrated provisioned cluster and report results — as I did for #4587.

Related

• #3708 — optional DbUser parameter; its stated motivation is precisely this gap (teams on shared SSO permission sets wanting individual audit trails)
• #917 — Secrets Manager integration; another request for an alternative to the single hardcoded auth path
• Companion request: opt-in write capability — redshift-mcp-server: add opt-in write capability (temporary tables and scoped writes) #4618