Summary and recommendation
Redshift user management can be run manually, but complexity usually increases with role models, licensing gates, and offboarding dependencies. This guide gives the exact mechanics and where automation has the biggest impact.
Amazon Redshift user management operates across two distinct layers that must be configured independently: IAM (controlling AWS console and API access) and the Redshift database itself (controlling query and object access via SQL).
Every app or service connecting to Redshift needs a database-level identity - either a native SQL user created with CREATE USER, or a federated IAM-authenticated user auto-created on first login.
Neither layer automatically reflects changes in the other, so access reviews must cover both.
Redshift supports four user types: superusers, standard database users, IAM-federated users, and RBAC roles. Roles are the recommended approach for privilege management; system-defined roles (sys:dba, sys:operator, sys:secadmin, sys:monitor) are predefined and cannot be modified.
Custom roles can be created for any privilege combination and granted to users or other roles.
Quick facts
| Admin console path | AWS Management Console → Amazon Redshift → [Cluster or Serverless Workgroup] → Query Editor v2 or via SQL client; for Serverless: Amazon Redshift Serverless → Namespace → Security and encryption |
| Admin console URL | Official docs |
| SCIM available | Yes |
| SCIM tier required | AWS IAM Identity Center |
| SSO prerequisite | Yes |
User types and roles
| Role | Permissions | Cannot do | Plan required | Seat cost | Watch out for |
|---|---|---|---|---|---|
| Superuser | Full database-level privileges: create/drop users, grant/revoke any privilege, access all schemas and tables, bypass row-level security, manage roles. Equivalent to database owner for all objects. | Cannot be dropped if it is the last superuser. Superuser status does not grant AWS console or IAM permissions. | The initial admin user created at cluster/namespace provisioning is a superuser. Additional superusers must be created via SQL (CREATE USER … CREATEUSER). Superuser access is database-level only; AWS console access is controlled separately via IAM. | ||
| Standard database user | Privileges explicitly granted via GRANT statements or role membership. Can own objects, create objects in schemas where CREATE is granted, and connect to databases where CONNECT is granted. | Cannot perform actions not explicitly granted. Cannot create other users unless CREATEUSER privilege is granted. Cannot access system tables beyond pg_catalog defaults without explicit grants. | Users created via CREATE USER exist only at the database level. They are separate from IAM users/roles. Password authentication or IAM-based temporary credentials must be configured explicitly. | ||
| IAM-authenticated user (federated) | Database access via temporary credentials generated through GetClusterCredentials or GetClusterCredentialsWithIAM API. Privileges in the database are determined by the mapped database user or auto-created user. | IAM policies control which clusters/namespaces can be accessed; database-level privileges still require GRANT statements inside Redshift. | When using IAM authentication, a database user is auto-created on first login if AutoCreate is enabled. These auto-created users have no privileges by default and must be granted roles or privileges separately. | ||
| Role (RBAC) | Named collection of privileges that can be granted to users or other roles. Supports system-defined roles (sys:superuser, sys:dba, sys:operator, sys:secadmin, sys:monitor) and custom roles. | Roles are not users; they cannot log in. Role hierarchies are supported but circular role grants are not permitted. | RBAC was introduced in Redshift and is the recommended approach over direct privilege grants. System roles (sys:dba, sys:operator, etc.) are predefined and cannot be modified. |
Permission model
- Model type: hybrid
- Description: Redshift uses a hybrid model combining traditional PostgreSQL-style GRANT/REVOKE privilege assignments with role-based access control (RBAC). Privileges can be granted directly to users or to roles, which are then granted to users. System-defined roles provide predefined privilege sets; custom roles can be created for any privilege combination. Row-level security (RLS) policies and column-level security are also supported.
- Custom roles: Yes
- Custom roles plan: Not documented
- Granularity: Object-level (table, view, schema, database, function, procedure), column-level, and row-level (via RLS policies). Privileges include SELECT, INSERT, UPDATE, DELETE, REFERENCES, CREATE, CONNECT, EXECUTE, USAGE, and TEMP.
How to add users
- Connect to the Redshift cluster or serverless namespace using a superuser account via Query Editor v2 (console) or a SQL client.
- Execute: CREATE USER username PASSWORD 'password' [options]; - where options can include CREATEDB, CREATEUSER, SYSLOG ACCESS, CONNECTION LIMIT, VALID UNTIL, etc.
- Grant necessary database privileges: GRANT CONNECT ON DATABASE dbname TO username;
- Grant schema usage: GRANT USAGE ON SCHEMA schemaname TO username;
- Grant object-level privileges or assign a role: GRANT rolename TO username; or GRANT SELECT ON TABLE tablename TO username;
- For IAM-based access, configure the IAM policy to allow redshift:GetClusterCredentials or redshift-serverless:GetCredentials and map the IAM principal to a database user.
Required fields: username (unique within the cluster/namespace), PASSWORD (required unless using IAM authentication with DISABLE PASSWORD)
Watch out for:
- Usernames are case-insensitive and stored in lowercase. Maximum length is 127 characters.
- Passwords must meet complexity requirements: at least 8 characters, mix of uppercase, lowercase, digits, and special characters.
- Users created via CREATE USER are cluster/namespace-scoped; they do not exist across multiple clusters automatically.
- For Redshift Serverless, user management is performed via SQL in the namespace's database, not through a dedicated console UI for individual user creation.
- IAM Identity Center integration (for SSO/SCIM) requires additional configuration and is separate from native database user creation.
- The CREATEUSER privilege allows a user to create other superusers - grant it only to trusted administrators.
| Bulk option | Availability | Notes |
|---|---|---|
| CSV import | No | Not documented |
| Domain whitelisting | No | Automatic domain-based user add |
| IdP provisioning | Yes | Requires AWS IAM Identity Center configuration; available at no additional Redshift cost but requires IAM Identity Center setup (free tier available in AWS). |
How to remove or deactivate users
- Can delete users: Yes
- Delete/deactivate behavior: Redshift supports DROP USER to permanently delete a database user. Before dropping, the user must not own any database objects and must not have any privileges on objects; otherwise the command fails. Objects must be transferred or dropped first. There is no native 'deactivate' or 'suspend' state for database users; to prevent access without deletion, a superuser can use ALTER USER username PASSWORD DISABLE or set CONNECTION LIMIT 0.
- To disable login without deleting: ALTER USER username PASSWORD DISABLE; (prevents password-based login; IAM-based login may still function depending on configuration)
- Alternatively, set connection limit to zero: ALTER USER username CONNECTION LIMIT 0;
- To fully delete: First reassign or drop all objects owned by the user (use REASSIGN OWNED BY username TO other_user; DROP OWNED BY username; if supported, or manually transfer ownership).
- Then execute: DROP USER username;
| Data impact | Behavior |
|---|---|
| Owned records | DROP USER fails if the user owns any database objects (tables, schemas, views, functions, etc.). Ownership must be transferred or objects dropped before the user can be deleted. |
| Shared content | Privileges granted by the user to others remain in effect on the objects (not the user). Privileges granted TO the user are removed when the user is dropped. |
| Integrations | Any external applications or ETL processes authenticating as the dropped user will lose access immediately upon DROP USER execution. |
| License freed | Redshift uses usage-based pricing (compute hours/RPUs), not per-seat licensing. Removing a user does not directly reduce billing; compute costs depend on cluster/workgroup usage, not user count. |
Watch out for:
- DROP USER will return an error if the user owns objects or has privileges on objects in any database in the cluster. Run DROP OWNED BY or REASSIGN OWNED BY first.
- There is no recycle bin or undo for DROP USER; the action is permanent.
- Disabling a password (ALTER USER … PASSWORD DISABLE) does not prevent IAM-based temporary credential login if the user's IAM policy still permits GetClusterCredentials.
- In Redshift Serverless, the same SQL-based user management applies; there is no console toggle to deactivate a user.
License and seat management
| Seat type | Includes | Cost |
|---|---|---|
| Provisioned cluster (On-Demand) | Compute billed per node-hour based on node type (e.g., ra3.xlplus from $0.543/hour). Storage billed separately via Redshift Managed Storage (RMS) from $0.024/GB-month. | From $0.543/hour per node (On-Demand); up to 75% discount with 3-year Reserved Instances. |
| Serverless | Compute billed per Redshift Processing Unit (RPU) hour. No cluster management required. Storage billed via RMS. | From $0.375/RPU-hour (base rate from $1.50/hour for 4 RPU minimum); 20–24% discount available with 1-year commitment (as of 2025). |
- Where to check usage: AWS Management Console → Cost Explorer or AWS Cost and Usage Reports for billing; Amazon Redshift Console → Clusters or Serverless → Monitoring tab for compute utilization metrics.
- How to identify unused seats: Query the SVL_USER_INFO or PG_USER system views to list users and last login activity. Use STL_CONNECTION_LOG to identify users with no recent connections. No built-in 'inactive user' report in the console; requires SQL queries against system tables.
- Billing notes: Redshift does not charge per database user or seat. Costs are based on compute (node-hours for provisioned, RPU-hours for serverless) and storage (GB-month). Adding or removing users has no direct billing impact. AWS free trial provides $300 credit with 90-day expiration for new accounts.
The cost of manual management
All user administration in Redshift requires SQL execution - there is no console UI for creating, modifying, or deleting individual database users. Administrators must connect via Query Editor v2 or an external SQL client as a superuser to run CREATE USER, GRANT, REVOKE, ALTER USER, and DROP USER statements.
Drop operations are the most operationally expensive step. DROP USER fails if the target user owns any objects or holds any privileges across any database in the cluster. Administrators must first run REASSIGN OWNED BY and DROP OWNED BY (or manually transfer ownership), then execute the drop - across every schema and database in scope.
There is no built-in inactive-user report; identifying stale accounts requires querying STL_CONNECTION_LOG and SVL_USER_INFO via SQL.
Disabling access without deletion requires either ALTER USER … PASSWORD DISABLE or setting CONNECTION LIMIT 0. Neither fully blocks IAM-authenticated users if their IAM policy still permits GetClusterCredentials - a gap that requires a coordinated IAM policy revocation to close.
The decision
Redshift's native user management is sufficient for small, SQL-fluent teams managing a single cluster with a stable user base. The SQL-only administration model, absence of bulk user tooling, and dual IAM/database access layers add meaningful overhead as team size or cluster count grows.
For organizations already using Okta, Microsoft Entra ID, or OneLogin, the IAM Identity Center SCIM integration reduces provisioning overhead - but deprovisioning still requires a separate SQL DROP USER step that cannot be automated through SCIM alone. Teams without an IdP integration should plan for manual SQL-based offboarding as a standing operational task.
License cost is not a factor in user management decisions: Redshift charges on compute and storage, not per database user. Adding or removing users has no direct billing impact.
Bottom line
Redshift's user management is functional but deliberately low-level: every provisioning and deprovisioning action ultimately requires SQL execution against a live database connection, with no console shortcuts for bulk operations or offboarding workflows.
The split between IAM-layer access and database-layer privileges is the most common source of operational error - particularly when revoking access quickly, since disabling a database password does not block IAM-authenticated sessions.
Teams managing every app that connects to Redshift should treat the IAM policy revocation and the SQL DROP USER as two separate, required steps in any offboarding checklist, and should build audit coverage on top of STL_USERLOG and STL_CONNECTION_LOG rather than relying on console visibility.
Automate Redshift workflows without one-off scripts
Stitchflow builds and maintains end-to-end IT automation across your SaaS stack, including apps without APIs. Built for exactly how your company works, with human approvals where they matter.
