Create a Superuser in RedShift

Superusers in RedShift hold the permissions to perform any action in the cluster, the same access as ‘master’ or whatever you named the superuser during creation.

Giving Superuser power should only really happen in local test environments, or if it’s a throw-away infrastructure as code (IaC) environment. You need to have control over the database servers you care about. If the data or infrastructure is business-critical, the preferred approach with permissions to follow the principle of least priviledge (PoLP).

This post here is me creating a new user in RedShift and then promoting the user with Superuser access.

# Create a User
# Promote User to Superuser

---

Create a User

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 having to qualify 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 the attributes of the new user. One of them (usesuper) includes whether the user is a Superuser or not.

SELECT * 
FROM PG_USER;

The 4th column in the query results above shows a tick if the user is a superuser. So far it’s just my own user and the default rdsdb user who are superusers.

---

Promote User to Superuser

The SQL to give a user Superuser access is done with the ALTER USER command.

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‘ column is true for the sysadmin_guy user we created above.

---