🙎‍♂️ Renaming a Role (User)

Rename a PostgreSQL role in Docker by creating a temporary role, switching, renaming and deleting

---

CHEATSHEET

1. docker exec -it <POSTGRESQL_CONTAINER_NAME> psql -U <POSTGRESQL_USERNAME> : Connect to the PostgreSQL server as <POSTGRESQL_USERNAME>
2. \du : List all PostgreSQL roles
3. CREATE ROLE <NEW_USER> SUPERUSER LOGIN PASSWORD '<USER_PASSWORD>'; : Create a new temporary role
4. \q : Exit the PostgreSQL prompt
5. ALTER USER <OLD_USER_NAME> RENAME TO <NEW_USER_NAME>; : Rename the desired role
6. DROP ROLE <TEMPORARY_ROLE>; : Delete the temporary role

---

DETAILS

Step 1: Connect to the PostgreSQL Server

To begin, connect to the PostgreSQL server within your Docker container using:

docker exec -it <POSTGRESQL_CONTAINER_NAME> psql -U <POSTGRESQL_USERNAME>

Step 2: Switch to a Different Role

⛔️ You’re likely connected with the role you intend to rename. PostgreSQL restricts renaming a role while connected to it.

✅ Solution: Connect as a Different Role

First, check existing roles:

\du

If there’s only one role (e.g., postgres), you’ll need to create a temporary role, tmp, switch to it, rename the target role, and then delete the temporary one.

Step 3: Create a Temporary Role

Create the new role by entering:

CREATE ROLE <NEW_USER> SUPERUSER LOGIN PASSWORD '<USER_PASSWORD>';

Exit the server using \q and reconnect with the new temporary role:

docker exec -it <POSTGRESQL_CONTAINER_NAME> psql -U <NEW_USER>

Step 4: Rename the Role

Now, rename the original role using:

ALTER USER <OLD_USER_NAME> RENAME TO <NEW_USER_NAME>;

Confirm the role changes by running \du. You should see both roles listed.

Step 5: Delete the Temporary Role

Finally, confirm the temporary role exists with \du:

Then, delete it with:

DROP ROLE <TEMPORARY_ROLE>;

Check your roles again with \du to verify the temporary role was removed.

---

Happy Me! 🌱