Superusers in RedShift hold the permissions to perform any action in the cluster, the same access as ‘master’ or whatever you named it during creation.
Giving Superuser power should only really happen in test environments or if it’s a throw-away infrastructure as code (IaC) environment. You need control on database servers you care about. If you care about the data or infrastructure, the preferred approach with permissions to follow the principal of least priviledge (PoLP).
This guide is simple a creation of a new user in RedShift and then updating the user with Superuser access.
Only Superusers can execute CREATE USER in RedShift. It’s a simple command to run :
CREATE USER sysadmin_guy PASSWORD 'wdoajdajde3123EAK';
I have a general rule to use underscores (_) instead of hyphens (-) to avoid qualifying the username with quotes (“). It can lead to annoyances with SQL/Python code.
When the new user has been created query the pg_user system table to show attributes of the new user, one of them (usesuper) includes whether the user is a Superuser or not.
SELECT * FROM PG_USER;
The SQL to give a user Superuser access is with ALTER USER.
The CREATEUSER parameter is an odd name I think, with the Postgres equivalent being ‘WITH SUPERUSER’.
ALTER USER sysadmin_guy CREATEUSER;
Now check the pg_user system table again, we can see ‘usesuper’ is true for sysadmin_guy.