Обсуждение: Update "usename" in pg_user
Hi, the last time i wrote a SQL command is about 20 years ago. So I'm asking for your advice. I have a DB which is accessed by ~ 40 users. The users are in pg_user and authenticated with LDAP. Due to change of the LDAP server I need to modify the username now. From firstname.lastname to firstname.lastname@helmholtz-munich.de. I tried the following (just for one user to test it): update pg_user set usename = 'usename@helmholtz-munich.de' where usename = 'dorota.germann'; It didn't work. I got this message (unfortunately in german): postgres=# update pg_user set usename = 'usename@helmholtz-munich.de' where usename = 'dorota.germann'; FEHLER: kann Sicht »pg_shadow« nicht aktualisieren DETAIL: Sichten, die nicht aus einer einzigen Tabelle oder Sicht lesen, sind nicht automatisch aktualisierbar. HINT: Um Aktualisieren der Sicht zu ermöglichen, richten Sie einen INSTEAD OF UPDATE Trigger oder eine ON UPDATE DO INSTEADRegel ohne Bedingung ein. Any idea ? Bernd -- Bernd Lentes System Administrator MCD Helmholtzzentrum München +49 89 3187 1241 bernd.lentes@helmholtz-munich.de https://www.helmholtz-munich.de/en/mcd Helmholtz Zentrum München – Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH) Ingolstädter Landstraße 1, D-85764 Neuherberg, https://www.helmholtz-munich.de Geschäftsführung: Prof. Dr. med. Dr. h.c. Matthias H. Tschöp, Prof. Dr. Dr. h.c. mult. Martin Hrabě de Angelis (komm.) |Aufsichtsratsvorsitzende: MinDir’in Prof. Dr. Veronika von Messling Registergericht: Amtsgericht München HRB 6466 | USt-IdNr. DE 129521671
On 14/11/2023 12:56, Bernd Lentes wrote: > Due to change of the LDAP server I need to modify the username now. > From firstname.lastname to firstname.lastname@helmholtz-munich.de. > I tried the following (just for one user to test it): > > update pg_user set usename = 'usename@helmholtz-munich.de' where usename = 'dorota.germann'; 1) don't update system tables directly unless you absolutely must ALTER USER <username> RENAME TO <new_username> is what you want 2) To use characters other than alphanumeric or _ then you need to use double quotes So: ALTER USER dorota.germann RENAME TO "dorota.germann@helmholtz-munich.de"; should do the job Paul
>> >> update pg_user set usename = 'usename@helmholtz-munich.de' where >> usename = 'dorota.germann'; > >1) don't update system tables directly unless you absolutely must OK. >ALTER USER <username> RENAME TO <new_username> is what you want > >2) To use characters other than alphanumeric or _ then you need to use >double quotes > > >So: > >ALTER USER dorota.germann RENAME TO "dorota.germann@helmholtz- >munich.de"; > >should do the job > >Paul Hi Paul, thanks. Can I do this for all users in one turn ? Bernd Helmholtz Zentrum München – Deutsches Forschungszentrum für Gesundheit und Umwelt (GmbH) Ingolstädter Landstraße 1, D-85764 Neuherberg, https://www.helmholtz-munich.de Geschäftsführung: Prof. Dr. med. Dr. h.c. Matthias H. Tschöp, Prof. Dr. Dr. h.c. mult. Martin Hrabě de Angelis (komm.) |Aufsichtsratsvorsitzende: MinDir’in Prof. Dr. Veronika von Messling Registergericht: Amtsgericht München HRB 6466 | USt-IdNr. DE 129521671
thanks. Can I do this for all users in one turn ?
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
# 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
Hi, the last time i wrote a SQL command is about 20 years ago. So I'm asking for your advice. I have a DB which is accessed by ~ 40 users. The users are in pg_user and authenticated with LDAP.
There have been several warnings on this list about using LDAP for authentication.
I quote one of them:
PostgreSQL supports GSSAPI / Kerberos, which is a much better approach in Active Directory and other environments where Kerberos is deployed.
Using LDAP exposes the user's password to the database server and therefore isn't secure and should be strongly discouraged.
You can certainly have applications authenticate to PostgreSQL using Kerberos too with very little overhead (unlike PG's LDAP authentication option, which makes a synchronous call out to the LDAP server on each and every login).
If you insist on using LDAP, you might use the regexp feature to extract the username from the complete mail address. Only if the same user name with different server parts (after the @) exist, you'll have to change your user names.
Quoted user names are very much unloved.
Regards,
Holgr
-- Holger Jakobs, PostgreSQL Expert and Trainer, Bergisch Gladbach, Tel. +49-178-9759012
Вложения
@font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4;}p.MsoNormal, li.MsoNormal, div.MsoNormal {margin:0cm; margin-bottom:.0001pt; font-size:12.0pt; font-family:"Times New Roman",serif;}a:link, span.MsoHyperlink {mso-style-priority:99; color:#0563C1; text-decoration:underline;}a:visited, span.MsoHyperlinkFollowed {mso-style-priority:99; color:#954F72; text-decoration:underline;}p.msonormal0, li.msonormal0, div.msonormal0 {mso-style-name:msonormal; mso-margin-top-alt:auto; margin-right:0cm; mso-margin-bottom-alt:auto; margin-left:0cm; font-size:12.0pt; font-family:"Times New Roman",serif;}span.E-MailFormatvorlage18 {mso-style-type:personal-compose; font-family:"Times New Roman",serif;}.MsoChpDefault {mso-style-type:export-only; font-size:10.0pt;}div.WordSection1 {page:WordSection1;} 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.
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.
On Tue, 2023-11-14 at 13:20 +0000, Bernd Lentes wrote:
> >
> > ALTER USER <username> RENAME TO <new_username> is what you want
>
> thanks. Can I do this for all users in one turn ?
You can use the psql client and run
SELECT format('ALTER ROLE %I rename to %I',
usename,
usename || 'helmholtz-munich.de')
FROM pg_user
WHERE usename <> 'postgres' \gexec
\gexec will automatically execute each result line as an SQL statement.
Yours,
Laurenz Albe
On 2023-Nov-14, Bernd Lentes wrote: > It didn't work. I got this message (unfortunately in german): > postgres=# update pg_user set usename = 'usename@helmholtz-munich.de' where usename = 'dorota.germann'; > FEHLER: kann Sicht »pg_shadow« nicht aktualisieren > DETAIL: Sichten, die nicht aus einer einzigen Tabelle oder Sicht lesen, sind nicht automatisch aktualisierbar. > HINT: Um Aktualisieren der Sicht zu ermöglichen, richten Sie einen INSTEAD OF UPDATE Trigger oder eine ON UPDATE DO INSTEADRegel ohne Bedingung ein. This error says that you're trying to update a view (Sicht), and suggesting to update the underlying table instead. So you would update pg_authid, which is the table that the pg_user and pg_shadow views are based on (and the column would be "rolname", not "usename"). Also, you probably wanted the SET clause as SET usename = usename || '@helmholtz-munich.de' otherwise all users would end up with the same username (or actually got an error that the second user would get a duplicate name). However, using UPDATE (or any DML) on system catalogs is not a great move. Using \gexec as already suggested by Laurenz is probably your best bet. BTW, you can use SET lc_messages to "C"; before the UPDATE to get these error messages in English. -- Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/ "The Postgresql hackers have what I call a "NASA space shot" mentality. Quite refreshing in a world of "weekend drag racer" developers." (Scott Marlowe)