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](https://peter-whyte.com/wp-content/uploads/2020/09/create_user_sysadmin_guy.png)
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](https://peter-whyte.com/wp-content/uploads/2020/09/select_from_pg_user.png)
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](https://peter-whyte.com/wp-content/uploads/2020/11/alter_user_create_user.png)
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](https://peter-whyte.com/wp-content/uploads/2020/09/select_from_pg_user_no_sql.png)
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!
Leave a Reply