Обсуждение: Update "usename" in pg_user

Поиск
Список
Период
Сортировка

Update "usename" in pg_user

От
Bernd Lentes
Дата:
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

Re: Update "usename" in pg_user

От
Paul Smith*
Дата:
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




RE: Update "usename" in pg_user

От
Bernd Lentes
Дата:
>>
>> 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

Re: Update "usename" in pg_user

От
"David G. Johnston"
Дата:
On Tue, Nov 14, 2023 at 6:20 AM Bernd Lentes <bernd.lentes@helmholtz-muenchen.de> wrote:

thanks. Can I do this for all users in one turn ?

Define "turn".  Each rename is its own command.  But you can put them in a transaction.

Use whatever tooling you use to write and execute SQL to produce dynamic SQL.  psql and its "\gexec" command is nice for the purpose.

David J.

Re: Update "usename" in pg_user

От
"Dischner, Anton"
Дата:

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

Re: Update "usename" in pg_user

От
Holger Jakobs
Дата:
Am 14.11.23 um 13:56 schrieb Bernd Lentes:
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
Вложения

Re: Update "usename" in pg_user

От
Ron
Дата:
On 11/14/23 08:38, Dischner, Anton wrote:
@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.

Re: Update "usename" in pg_user

От
"David G. Johnston"
Дата:
On Tue, Nov 14, 2023 at 7:57 AM Ron <ronljohnsonjr@gmail.com> wrote:

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.


Yeah, it's positional.  I think (motivated individuals can check the docs) %1$s

It would be nice to have an hstore and/or json variant of format so one can use names instead of positions.

format('%greeting$s, my name is %name$s', jsonb_build_object('name', 'Dave', 'greeting', 'Yo!')))

David J.

Re: Update "usename" in pg_user

От
Laurenz Albe
Дата:
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



Re: Update "usename" in pg_user

От
Alvaro Herrera
Дата:
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)