Re: Update "usename" in pg_user

On 11/14/23 08:38, Dischner, Anton wrote:
>
> Hi all,
>
> thats what Bard has to offer:
>
> write me a bash script to rename all users for postgresql. i want to
> append "@email.com"
>
> Sure, here is a bash script to rename all users for PostgreSQL and append
> "@email.com":
> Bash#!/bin/bash
>
> # Get all users
> users=$(psql -c "SELECT usename FROM pg_user;" | grep -v "postgres")
>
> # Rename all users
> for user in $users; do
> psql -c "ALTER USER $user RENAME TO $user(at)email(dot)com;"
> done
>

A one-liner, that might have to be modified for double-quote handling:
psql -c "SELECT format('ALTER ROLE %s RENAME TO \"%s(at)example(dot)com\";',
username, username) FROM pg_user WHERE username != 'postgres'\gexec"

The two username uses can be shrunk to one with appropriate use of %s
modifiers that I don't remember off the top of my head.

>
> # Display success message
> echo "All users have been renamed successfully."
>
> To use this script, simply save it as a file with a .sh extension (e.g.,
> rename_users.sh) and make it executable by running the following command:
> Bashchmod +x rename_users.sh
>
> Then, you can run the script to rename all users by running the following
> command:
> Bash./rename_users.sh
>
> This script will rename all users in the current PostgreSQL database. If
> you want to rename all users in a specific database, you can specify the
> database name as an argument to the psql command. For example, to rename
> all users in the database "my_database", you would run the following command:
> Bashpsql -d my_database -c "SELECT usename FROM pg_user;" | grep -v "postgres"
>
> You can also modify the script to append a different string to the end of
> the usernames. For example, to append the string "@example.com", you would
> change the line "ALTER USER $user RENAME TO $user(at)email(dot)com;" to the
> following line:
> BashALTER USER $user RENAME TO $user(at)example(dot)com;
>
> Best,
>
> Anton
>

--
Born in Arizona, moved to Babylonia.