Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r2uqx-001MqV-GH for pgsql-admin@arkaria.postgresql.org; Tue, 14 Nov 2023 14:57:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r2uqv-008C0b-7g for pgsql-admin@arkaria.postgresql.org; Tue, 14 Nov 2023 14:57:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r2uqu-008C0T-Rr for pgsql-admin@lists.postgresql.org; Tue, 14 Nov 2023 14:57:20 +0000 Received: from mail-yw1-x112a.google.com ([2607:f8b0:4864:20::112a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1r2uqo-006PAj-Ef for pgsql-admin@lists.postgresql.org; Tue, 14 Nov 2023 14:57:20 +0000 Received: by mail-yw1-x112a.google.com with SMTP id 00721157ae682-5c08c47c055so44449097b3.1 for ; Tue, 14 Nov 2023 06:57:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1699973832; x=1700578632; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=XtjgpfmhClL/gwR2bc1TYeEBtabeb9FXwEV4AIxlknE=; b=AhoY6P9zRXFhTKYi+cteHnpNLODlGqC7c2XafsyBqCLpia6LPreAoQN0V4FWqHN09V 9/GHFZvS74shUcGxzrIp7PNRPMfdMmAfxIwQfXNbotd46uU4r7sQMjmovuUL8EO/SgDo au0R55yRNtxAVO4xJGj5sHMNyElZ5RI/PuqXw5PuLXzsAQUDnE12K9X9JIn7H9RJ0d4I ZZ3FijkqMtKxWxVINsKBQubOlIlW02+qwVgct6bS4L2lXJ81j/4SbrQUdUhSrxqqnB2v saphjqfxGl7UHgBlT6yQhp9IfbdK+sML3511sgB5FcnfRvWnNeHESpGYKxouFarPoFVa tiMA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1699973832; x=1700578632; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=XtjgpfmhClL/gwR2bc1TYeEBtabeb9FXwEV4AIxlknE=; b=ujfsc3qfM4EDCs5SrDLLC988iEDoUpBGHr0e+KGwhCcpOgGb5KCYe00gnr6ZYpYjo5 +gTD8uSU4o8C1LF03QYlwHFCvY1Z75EbTQV7OVlz4H7EQWNFK0LdZO4I9NaS9/NcYcf+ D6oXlyqwm0YEQY4Dx/OSr/qQ9HidAQLamWLmw4LmTQTyN1wAGXS9my+ly7sAGhDqLMi6 tpUiqOtYahuwWzk+nf4QMA2uXSYxbisoS0639O7H2DBgZRr8zgB95HZX7VCAC6ORzRwV nA6NYzrdy5/VD29zHFG86tPSFMuD1QnUcNddx11RBfJuolAferNSfz+souc3G8Mpp+2L qBmw== X-Gm-Message-State: AOJu0YwAuIqArbXaTuxRaP1Wp3fIM7bp+oPgW94+QiJs3VAjiN8HvNIk Gdo7TkMOG/Or/uFWs2MZs4jTNjhzKyk= X-Google-Smtp-Source: AGHT+IH9XoAqMHJQeEn7VZYkYoBCUgWmUTN0Lnhg234HV+0W3yg+s9OhyNepJ4qfMPQKP3HFHGtfiw== X-Received: by 2002:a81:8002:0:b0:5a7:a959:337 with SMTP id q2-20020a818002000000b005a7a9590337mr9319925ywf.27.1699973832420; Tue, 14 Nov 2023 06:57:12 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::44? ([2600:1700:bd2c:b810::44]) by smtp.googlemail.com with ESMTPSA id i5-20020a0df805000000b005925765aa30sm2429288ywf.135.2023.11.14.06.57.11 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 14 Nov 2023 06:57:12 -0800 (PST) Content-Type: multipart/alternative; boundary="------------vI07eoo84qYwVuV0qP03I3sI" Message-ID: <0098d2d1-c8a5-42a4-9be3-3a854cb2ff17@gmail.com> Date: Tue, 14 Nov 2023 08:57:11 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Update "usename" in pg_user Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: <2143ad5560114b9c854858763b6326a3@med.uni-muenchen.de> From: Ron In-Reply-To: <2143ad5560114b9c854858763b6326a3@med.uni-muenchen.de> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------vI07eoo84qYwVuV0qP03I3sI Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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. --------------vI07eoo84qYwVuV0qP03I3sI Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------vI07eoo84qYwVuV0qP03I3sI--