How to Manage Users in Redshift

How do I manage users in Amazon Redshift?

Redshift user management covers creating, altering, dropping, and granting privileges to database users.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does “managing users” in Redshift involve?

Managing users means creating logins, setting passwords, assigning default schemas, adjusting quotas, revoking access, and deleting accounts when no longer needed. The goal is least-privilege access and easy auditing.

How do I create a new user in Redshift?

Run CREATE USER with a strong password and optional defaults.Always assign a group or role immediately to enforce privilege boundaries.

CREATE USER analyst_pw WITH PASSWORD 'S3cure#2024' VALID UNTIL '2025-12-31' IN GROUP analysts;

How do I change a user’s password or attributes?

Use ALTER USER to rotate passwords, lock accounts, or move users between groups without impacting their objects.

ALTER USER analyst_pw PASSWORD 'N3wS3cure#2025';

How do I drop a user safely?

First transfer or delete owned objects, then execute DROP USER.Add IF EXISTS to avoid errors in automation scripts.

REASSIGN OWNED BY analyst_pw TO admin; DROP USER IF EXISTS analyst_pw;

How do I grant privileges to users?

Prefer group or role grants.Use GRANT SELECT ON for read-only analytics, and GRANT USAGE ON SCHEMA so users can access objects inside schemas.

GRANT USAGE ON SCHEMA sales TO analysts; GRANT SELECT ON ALL TABLES IN SCHEMA sales TO analysts;

What are best practices for Redshift user management?

Automate password rotation, map users to IAM roles, disable unused accounts, and log all DDL in STL tables for auditing.Keep groups task-oriented, not person-oriented.

How do I audit existing users quickly?

Query PG_USER, PG_GROUP, and SVL_QLOG to list accounts, group memberships, and recent activity. Export results for security reviews.

Why How to Manage Users in Redshift is important

How to Manage Users in Redshift Example Usage


-- Create a read-only reporting user for ecommerce analysts
CREATE USER ecommerce_ro WITH PASSWORD 'Ecomm#2024' VALID UNTIL '2026-01-01' IN GROUP analysts;
GRANT USAGE ON SCHEMA public TO analysts;
GRANT SELECT ON Customers, Orders, Products, OrderItems TO analysts;

How to Manage Users in Redshift Syntax


-- CREATE USER
CREATE USER user_name [ WITH ]
    { PASSWORD 'password' [ VALID UNTIL 'timestamp' ] |
      PASSWORD DISABLE } 
    [ IN GROUP group_name [, ...] ]
    [ SYSLOG ACCESS { RESTRICTED | UNRESTRICTED } ];

-- ALTER USER
ALTER USER user_name 
    { PASSWORD 'new_password' [ VALID UNTIL 'timestamp' ] |
      PASSWORD DISABLE };

-- DROP USER
DROP USER [ IF EXISTS ] user_name;

-- GRANT EXAMPLE (ecommerce)
GRANT SELECT ON TABLE Orders, Customers TO sales_read;
GRANT INSERT, UPDATE ON TABLE Orders TO sales_write;

Common Mistakes

Creating users without adding them to a group. This scatters privilege management. Fix by assigning each new user to a role-based group in the same transaction.
Dropping users before reassigning objects. This blocks the DROP and leaves orphaned objects. Always run REASSIGN OWNED (or drop objects) first.

Frequently Asked Questions (FAQs)

Can I disable a user without deleting it?

Yes. Run ALTER USER username PASSWORD DISABLE;. The account remains but cannot log in until a new password is set.

How do I force password rotation?

Set VALID UNTIL when creating or altering a user. Your IAM or CI pipeline can alert and reset before expiration.

Is it better to use groups or roles?

Groups are simpler in Redshift. If you use Redshift-compatible roles, stick to them consistently; but never mix direct object grants and group grants for the same privilege set.