+1 (726) 227-3497
RougeWarehouse

Single Sign-On for Amazon Redshift: IAM Identity Center, Trusted Identity Propagation and Private Connectivity

Every Redshift environment eventually hits the same question: who is this user, really? Local database users with passwords are easy to create and impossible to audit at scale. The modern answer is AWS IAM Identity Center (IdC) with trusted identity propagation: your workforce identity from Okta, Entra ID or Identity Center itself flows through the BI tool, through the Redshift connection, and all the way into SYS_QUERY_HISTORY and Lake Formation — no shared service account, no per-tool user table.

This tutorial sets it up end to end, then covers the private networking that usually lands in the same change ticket.

The three connection models

Before touching anything, know which model each client should use.

  1. Database users and passwords. Legacy. Keep only for break-glass and for tools that genuinely cannot do anything else.
  2. IAM-based temporary credentials (GetClusterCredentials / GetCredentials, or the Data API). Good for machine identities: Lambda, Airflow, dbt in CI. The caller is an IAM role; Redshift maps it to a database identity.
  3. IAM Identity Center with trusted identity propagation. Good for humans. The end user's IdC identity is the identity Redshift sees, even when QuickSight or Query Editor v2 sits in between.

Most warehouses want 2 for pipelines and 3 for people. Mixing is fine; they coexist in the same namespace.

Step 1: enable IdC and register the Redshift application

Identity Center must be enabled in the same region as the warehouse (an organization instance, with your IdP wired in via SAML or SCIM). Then create the Redshift IdC application, which is the trust anchor all clusters and workgroups attach to:

aws redshift create-redshift-idc-application \
  --idc-instance-arn arn:aws:sso:::instance/ssoins-1234567890abcdef \
  --redshift-idc-application-name analytics-warehouse \
  --identity-namespace awsidc \
  --idc-display-name "Analytics Warehouse" \
  --iam-role-arn arn:aws:iam::111111111111:role/RedshiftIdcServiceRole

The --identity-namespace matters: it becomes the prefix on every propagated identity inside the database. With awsidc, a user who signs in as dana@example.com arrives as awsidc:dana@example.com, and a group analysts arrives as role awsidc:analysts. Pick it once and never change it — it ends up embedded in every GRANT you are about to write.

RedshiftIdcServiceRole needs a trust policy for redshift.amazonaws.com and permissions to read Identity Center users and groups, plus Lake Formation permissions if you plan to propagate into the lakehouse.

To let QuickSight, Query Editor v2 or a JDBC client redeem tokens against this application, add the service integrations:

--service-integrations '[{"LakeFormation":[{"LakeFormationQuery":{"Authorization":"Enabled"}}]}]'

Step 2: attach the namespace to the application

Each Serverless namespace or provisioned cluster is then associated with the IdC application — in the console it is a checkbox on the namespace ("Connect to IAM Identity Center"), in the API it is create-redshift-idc-application's association call, and in Terraform it is the IdC application ARN on the namespace resource. Do it in code so staging and production cannot drift.

Verify from inside the database:

SELECT * FROM SVV_IDENTITY_PROVIDERS;

You should see one row for your application, carrying the namespace you chose.

Step 3: grant on IdC groups, not users

This is the whole payoff. Redshift auto-creates roles and users for propagated identities, so you grant to the group role and never manage membership in SQL again:

-- Role names are namespace-qualified and must be quoted
CREATE ROLE "awsidc:analysts";
CREATE ROLE "awsidc:finance_readers";

GRANT USAGE ON SCHEMA analytics TO ROLE "awsidc:analysts";
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO ROLE "awsidc:analysts";
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
  GRANT SELECT ON TABLES TO ROLE "awsidc:analysts";

GRANT USAGE ON SCHEMA finance TO ROLE "awsidc:finance_readers";
GRANT SELECT ON finance.gl_summary TO ROLE "awsidc:finance_readers";

Row-level security and dynamic data masking attach to these roles exactly as they would to local roles, so an IdC group can carry a masking policy:

ATTACH MASKING POLICY mask_email
  ON customers (email)
  TO ROLE "awsidc:analysts";

Adding someone to analysts in Okta now gives them the right Redshift grants on their next sign-in; removing them revokes access. That is the audit story your security team keeps asking for.

Step 4: connect the clients

Query Editor v2 — sign in to the console through Identity Center and choose the IAM Identity Center authentication option on the connection. Nothing else to configure.

QuickSight — with the account configured for IdC and trusted identity propagation, the Redshift data source uses the reader's identity instead of a shared data-source credential. One dataset, per-user row-level results, and no duplicated RLS logic inside QuickSight.

JDBC/ODBC and Python — use the browser-based IdC plugin (driver 2.1.0.30 or newer):

jdbc:redshift:iam://analytics.111111111111.us-east-1.redshift-serverless.amazonaws.com:5439/dev
  ?plugin_name=com.amazon.redshift.plugin.BrowserIdcAuthPlugin
  &issuer_url=https://identitycenter.amazonaws.com/ssoins-1234567890abcdef
  &idc_region=us-east-1
  &listen_port=7890

The Python driver takes the same parameters:

import redshift_connector

conn = redshift_connector.connect(
    iam=True,
    credentials_provider="BrowserIdcAuthPlugin",
    issuer_url="https://identitycenter.amazonaws.com/ssoins-1234567890abcdef",
    idc_region="us-east-1",
    host="analytics.111111111111.us-east-1.redshift-serverless.amazonaws.com",
    database="dev",
)

The browser opens once, the token is cached, and refresh happens silently until the IdC session duration expires.

Step 5: confirm the identity actually propagated

SELECT current_user, session_user;
-- awsidc:dana@example.com

SELECT user_id, query_text, start_time
FROM SYS_QUERY_HISTORY
ORDER BY start_time DESC
LIMIT 20;

If current_user comes back as a shared name like quicksight_svc, propagation is not on and you are auditing a robot.

Machine identities: keep them on IAM roles

Do not push pipelines through IdC. Airflow, dbt, Lambda and Step Functions should assume an IAM role and use the Data API or temporary credentials:

aws redshift-data execute-statement \
  --workgroup-name analytics \
  --database dev \
  --sql "CALL elt.run_daily()"

Map the role to a database role once, and grant to that:

CREATE ROLE etl_writer;
GRANT ALL ON SCHEMA staging TO ROLE etl_writer;
-- "IAM:dbt_ci" is created on first connection by the dbt CI role
GRANT ROLE etl_writer TO "IAM:dbt_ci";

The networking half of the ticket

Identity settles who; the network still decides from where.

  • Put Serverless workgroups and provisioned clusters in private subnets with PubliclyAccessible = false. The security group should allow 5439 only from your VPC CIDRs and from the security groups of the ETL compute.
  • Use Redshift-managed VPC endpoints (PrivateLink) to reach a warehouse from another VPC or account without peering, and to give BI or a partner VPC a private path.
  • Require TLS: set require_ssl to true in the parameter group, and use sslmode=verify-full in clients so a rogue endpoint cannot impersonate the warehouse.
  • Scope cluster IAM roles per purpose — one for S3 COPY buckets, one for Lake Formation, one for streaming. A single wildcard role is the most common finding in a Redshift security review.
  • Enable audit logging to CloudWatch Logs and keep user-activity logging on. With IdC propagation those logs finally carry real human identities.
aws redshift enable-logging \
  --cluster-identifier analytics \
  --log-destination-type cloudwatch \
  --log-exports connectionlog useractivitylog userlog

A rollout order that avoids an outage

  1. Enable IdC, create the application, attach a non-production namespace.
  2. Create the awsidc: group roles and mirror existing grants onto them.
  3. Move Query Editor v2 users first — lowest blast radius, fastest feedback.
  4. Move BI next, and delete the shared data-source credential.
  5. Move JDBC/ODBC power users with the browser plugin.
  6. Leave pipelines on IAM roles. Then disable password logins for humans and keep one break-glass superuser in Secrets Manager, with rotation and an alarm on its use.

Done in that order nobody loses access mid-quarter, and you end with a warehouse where every query has a real name attached to it.

Need this designed and implemented against an existing warehouse, including the Lake Formation and BI sides? That is the kind of work our Redshift Administration and AWS Ecosystem Development teams do — get in touch.