pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedUpdate "usename" in pg_user
10+ messages / 8 participants
[nested] [flat]
* Update "usename" in pg_user
@ 2023-11-14 12:56 Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>
0 siblings, 4 replies; 10+ messages in thread
From: Bernd Lentes @ 2023-11-14 12:56 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 INSTEAD Regel 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Update "usename" in pg_user
@ 2023-11-14 13:05 Paul Smith* <paul@pscs.co.uk>
parent: Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>
3 siblings, 1 reply; 10+ messages in thread
From: Paul Smith* @ 2023-11-14 13:05 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* RE: Update "usename" in pg_user
@ 2023-11-14 13:20 Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>
parent: Paul Smith* <paul@pscs.co.uk>
0 siblings, 2 replies; 10+ messages in thread
From: Bernd Lentes @ 2023-11-14 13:20 UTC (permalink / raw)
To: Paul Smith* <paul@pscs.co.uk>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
>>
>> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Update "usename" in pg_user
@ 2023-11-14 13:28 David G. Johnston <david.g.johnston@gmail.com>
parent: Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>
1 sibling, 0 replies; 10+ messages in thread
From: David G. Johnston @ 2023-11-14 13:28 UTC (permalink / raw)
To: Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>; +Cc: Paul Smith* <paul@pscs.co.uk>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Update "usename" in pg_user
@ 2023-11-14 14:38 Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
parent: Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>
3 siblings, 1 reply; 10+ messages in thread
From: Dischner, Anton @ 2023-11-14 14:38 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Update "usename" in pg_user
@ 2023-11-14 14:51 Holger Jakobs <holger@jakobs.com>
parent: Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>
3 siblings, 0 replies; 10+ messages in thread
From: Holger Jakobs @ 2023-11-14 14:51 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; +Cc: bernd.lentes@helmholtz-muenchen.de
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
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../6d800f7b-47e3-08e1-2a9e-0cd561af64cd@jakobs.com/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Update "usename" in pg_user
@ 2023-11-14 14:57 Ron <ronljohnsonjr@gmail.com>
parent: Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
0 siblings, 1 reply; 10+ messages in thread
From: Ron @ 2023-11-14 14:57 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Update "usename" in pg_user
@ 2023-11-14 15:04 David G. Johnston <david.g.johnston@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: David G. Johnston @ 2023-11-14 15:04 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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\"
> <%25s@example.com%5C>;', 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Update "usename" in pg_user
@ 2023-11-14 16:42 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>
1 sibling, 0 replies; 10+ messages in thread
From: Laurenz Albe @ 2023-11-14 16:42 UTC (permalink / raw)
To: Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>; Paul Smith* <paul@pscs.co.uk>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Update "usename" in pg_user
@ 2023-11-14 17:20 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>
3 siblings, 0 replies; 10+ messages in thread
From: Alvaro Herrera @ 2023-11-14 17:20 UTC (permalink / raw)
To: Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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 INSTEAD Regel 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)
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-11-14 17:20 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-14 12:56 Update "usename" in pg_user Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>
2023-11-14 13:05 ` Paul Smith* <paul@pscs.co.uk>
2023-11-14 13:20 ` Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>
2023-11-14 13:28 ` David G. Johnston <david.g.johnston@gmail.com>
2023-11-14 16:42 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-11-14 14:38 ` Dischner, Anton <Anton.Dischner@med.uni-muenchen.de>
2023-11-14 14:57 ` Ron <ronljohnsonjr@gmail.com>
2023-11-14 15:04 ` David G. Johnston <david.g.johnston@gmail.com>
2023-11-14 14:51 ` Holger Jakobs <holger@jakobs.com>
2023-11-14 17:20 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
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