In Amazon RedShift, superusers wield unparalleled authority within the cluster, comparable to the access granted to the ‘master’ user or its equivalent during creation. While it’s imperative to exercise caution, granting superuser powers is occasionally necessary in local test environments or transient Infrastructure as Code (IaC) setups. However, in business-critical scenarios, the Principle of Least Privilege (PoLP) is the preferred approach for permissions.

This post guides you through the process of creating a new user in RedShift and elevating their privileges to superuser status.

Create a User in RedShift

Creating a user in RedShift involves executing the CREATE USER command, a task reserved for superusers. Follow these steps to create a new user with sysadmin capabilities:

-- Create new user (redshift)
CREATE USER sysadmin_guy PASSWORD 'wdoajdajde3123EAK';

Consider using underscores (_) instead of hyphens (-) to streamline username qualification in SQL/Python code.

After creating the user, query the pg_user system table to display the attributes of the new user and confirm their sysadmin capabilities:

-- Show database users (redshift)
SELECT * 
FROM PG_USER;

The results will indicate whether the user is a superuser, with a tick in the ‘usesuper’ column.

Promote User to Superuser in Redshift

Elevating a user to superuser status in Redshift involves using the ALTER USER command. Execute the following command to grant sysadmin permissions:

-- Alter user, give sysadmin permissions (redshift)
ALTER USER sysadmin_guy CREATEUSER;

After executing the command, recheck the pg_user system table. The ‘usesuper’ column should now reflect true for the sysadmin_guy user, indicating their superuser status.

Conclusion

This guide equips you with the necessary steps to create a new user in RedShift and elevate their privileges to superuser status if needed. Exercise caution when assigning superuser powers and adhere to the Principle of Least Privilege for heightened security. Whether you are navigating RedShift for the first time or refining your access management strategy, these steps ensure a seamless process for creating and promoting superusers.

For more information, you can also check out the Redshift docs on creating, deleting, and altering users.