From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Update "usename" in pg_user
Date: Tue, 14 Nov 2023 08:57:11 -0600
Message-ID: <0098d2d1-c8a5-42a4-9be3-3a854cb2ff17@gmail.com> (raw)
In-Reply-To: <2143ad5560114b9c854858763b6326a3@med.uni-muenchen.de>
References: <PR3PR04MB73409EB3F2695DA61418F784D6B2A@PR3PR04MB7340.eurprd04.prod.outlook.com>
<2143ad5560114b9c854858763b6326a3@med.uni-muenchen.de>
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@email.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@example.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@email.com;" to the
> following line:
> BashALTER USER $user RENAME TO $user@example.com;
>
> Best,
>
> Anton
>
--
Born in Arizona, moved to Babylonia.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Update "usename" in pg_user
In-Reply-To: <0098d2d1-c8a5-42a4-9be3-3a854cb2ff17@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox