Creating a New User in Redshift and Promoting to Superuser

This article provides a step-by-step demo for creating a new User in Redshift and promoting it to a Superuser. I’ll include useful tips, best practices, and links to AWS Docs to help streamline the process.

In Amazon Redshift, a Superuser has the highest level of permissions, equivalent to the master user created during cluster setup. You’ll need to have this permission to be able to run the commands in this post.

Superuser access should only be granted in specific scenarios, such as local test environments or temporary infrastructures. In production environments, it’s best to follow the Principle of Least Privilege (PoLP) to ensure security and control.

1. Creating a New User in Redshift

Run the CREATE USER SQL command to create the user:

-- create new user redshift 
CREATE USER sysadmin_guy PASSWORD 'wdoajdajde3123EAK';
RedShift Create Super User

Note: Avoid using underscores (_) instead of hyphens (-) for usernames to avoid issues with quoting in SQL and Python code.

Once the user is created, you can check their attributes in the pg_user system table:

SELECT * 
FROM PG_USER;
RedShift SELECT FROM pg_user

The usesuper column indicates whether the user has superuser privileges (a tick in the fourth column).

2. Promoting the User to Superuser

To grant superuser access, use the ALTER USER command:

-- promote user to superuser 
ALTER USER sysadmin_guy CREATEUSER;
Alter User Create User SQL

Now, if you check the pg_user table again, we can check that the usesuper column is set to true for the user.

This query will show all superusers on your cluster:

-- show all superusers in redshift
SELECT usename, usesuper 
FROM PG_USER 
WHERE usesuper = TRUE;
RedShift SELECT FROM pg_user

Remember that these permissions should be handled with caution. Granting superuser access should be limited and based on specific needs to maintain security and control over your Redshift environment. Always adhere to the principle of least privilege, especially in production systems.

Hope this helps. Feel free to checkout my other random Redshift tips, from someone who isn’t a Redshift DBA!


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *