pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedReset Postgresql users password
9+ messages / 8 participants
[nested] [flat]
* Reset Postgresql users password
@ 2023-07-12 19:28 Johnathan Tiamoh <johnathantiamoh@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Johnathan Tiamoh @ 2023-07-12 19:28 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello,
I wish to find out if there is a way to reset all users in Postgresql
password to the same password at once.
Thank you
Tiamoh J
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Reset Postgresql users password
@ 2023-07-12 19:34 Gianni Ceccarelli <dakkar@thenautilus.net>
parent: Johnathan Tiamoh <johnathantiamoh@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Gianni Ceccarelli @ 2023-07-12 19:34 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2023-07-12 Johnathan Tiamoh <johnathantiamoh@gmail.com> wrote:
> I wish to find out if there is a way to reset all users in Postgresql
> password to the same password at once.
I guess you could update the `pg_catalog.pg_authid` table, see
https://www.postgresql.org/docs/15/catalog-pg-authid.html
--
Dakkar - <Mobilis in mobile>
GPG public key fingerprint = A071 E618 DD2C 5901 9574
6FE2 40EA 9883 7519 3F88
key id = 0x75193F88
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Reset Postgresql users password
@ 2023-07-12 19:42 Mateusz Henicz <mateuszhenicz@gmail.com>
parent: Gianni Ceccarelli <dakkar@thenautilus.net>
0 siblings, 1 reply; 9+ messages in thread
From: Mateusz Henicz @ 2023-07-12 19:42 UTC (permalink / raw)
To: Gianni Ceccarelli <dakkar@thenautilus.net>; +Cc: pgsql-general@lists.postgresql.org
You can also just write an sql and execute it, like:
select 'alter user '|| usename ||' with password ''newpassword'';' from
pg_user;
\gexec
Cheers,
Mateusz
śr., 12 lip 2023 o 21:35 Gianni Ceccarelli <dakkar@thenautilus.net>
napisał(a):
> On 2023-07-12 Johnathan Tiamoh <johnathantiamoh@gmail.com> wrote:
> > I wish to find out if there is a way to reset all users in Postgresql
> > password to the same password at once.
>
> I guess you could update the `pg_catalog.pg_authid` table, see
> https://www.postgresql.org/docs/15/catalog-pg-authid.html
>
> --
> Dakkar - <Mobilis in mobile>
> GPG public key fingerprint = A071 E618 DD2C 5901 9574
> 6FE2 40EA 9883 7519 3F88
> key id = 0x75193F88
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Reset Postgresql users password
@ 2023-07-12 20:16 Gurjeet Singh <gurjeet@singh.im>
parent: Mateusz Henicz <mateuszhenicz@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Gurjeet Singh @ 2023-07-12 20:16 UTC (permalink / raw)
To: Mateusz Henicz <mateuszhenicz@gmail.com>; +Cc: Gianni Ceccarelli <dakkar@thenautilus.net>; pgsql-general@lists.postgresql.org
On Wed, Jul 12, 2023 at 12:42 PM Mateusz Henicz <mateuszhenicz@gmail.com> wrote:
>
> You can also just write an sql and execute it, like:
>
> select 'alter user '|| usename ||' with password ''newpassword'';' from pg_user;
> \gexec
Note that the above assumes you're using psql.
For tools other than psql, you'd have to use dynamic SQL something
like the DO block below. It will work on psql, just as well.
DO $$
declare
rec record;
begin
for rec in select
'alter user '|| quote_ident(usename)
||' with password '|| quote_literal('newpassword')
as c from pg_user
loop
raise notice 'Executing command: %', rec.c;
execute rec.c;
end loop;
end;
$$;
Best regards,
Gurjeet
http://Gurje.et
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Reset Postgresql users password
@ 2023-07-12 20:25 Johnathan Tiamoh <johnathantiamoh@gmail.com>
parent: Gurjeet Singh <gurjeet@singh.im>
1 sibling, 0 replies; 9+ messages in thread
From: Johnathan Tiamoh @ 2023-07-12 20:25 UTC (permalink / raw)
To: Gurjeet Singh <gurjeet@singh.im>; +Cc: Gianni Ceccarelli <dakkar@thenautilus.net>; Mateusz Henicz <mateuszhenicz@gmail.com>; pgsql-general@lists.postgresql.org
Thank you all very much.
I really appreciate !!
On Wed, Jul 12, 2023 at 4:17 PM Gurjeet Singh <gurjeet@singh.im> wrote:
> On Wed, Jul 12, 2023 at 12:42 PM Mateusz Henicz <mateuszhenicz@gmail.com>
> wrote:
> >
> > You can also just write an sql and execute it, like:
> >
> > select 'alter user '|| usename ||' with password ''newpassword'';' from
> pg_user;
> > \gexec
>
> Note that the above assumes you're using psql.
>
> For tools other than psql, you'd have to use dynamic SQL something
> like the DO block below. It will work on psql, just as well.
>
> DO $$
> declare
> rec record;
> begin
> for rec in select
> 'alter user '|| quote_ident(usename)
> ||' with password '|| quote_literal('newpassword')
> as c from pg_user
> loop
> raise notice 'Executing command: %', rec.c;
> execute rec.c;
> end loop;
> end;
> $$;
>
> Best regards,
> Gurjeet
> http://Gurje.et
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Reset Postgresql users password
@ 2023-07-12 20:35 David G. Johnston <david.g.johnston@gmail.com>
parent: Gurjeet Singh <gurjeet@singh.im>
1 sibling, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2023-07-12 20:35 UTC (permalink / raw)
To: Gurjeet Singh <gurjeet@singh.im>; +Cc: Mateusz Henicz <mateuszhenicz@gmail.com>; Gianni Ceccarelli <dakkar@thenautilus.net>; pgsql-general@lists.postgresql.org
On Wed, Jul 12, 2023 at 1:17 PM Gurjeet Singh <gurjeet@singh.im> wrote:
> for rec in select
> 'alter user '|| quote_ident(usename)
> ||' with password '|| quote_literal('newpassword')
>
>
Which is more clearly written using the format function:
...
for rec in select
format('alter user %I with password %L', usename, 'newpassword')
from pg_user
loop
...
David J.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Reset Postgresql users password
@ 2023-07-17 02:28 Ron <ronljohnsonjr@gmail.com>
parent: Johnathan Tiamoh <johnathantiamoh@gmail.com>
1 sibling, 2 replies; 9+ messages in thread
From: Ron @ 2023-07-17 02:28 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 7/12/23 14:28, Johnathan Tiamoh wrote:
> Hello,
>
> I wish to find out if there is a way to reset all users in Postgresql
> password to the same password at once.
To the same value??
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Reset Postgresql users password
@ 2023-07-17 03:27 Wen Yi <wen-yi@qq.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Wen Yi @ 2023-07-17 03:27 UTC (permalink / raw)
To: pgsql-general <pgsql-general@lists.postgresql.org>
I think maybe you can check the pg_hba.conf, change the method to the 'trust'.
(Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication. See Section 21.4 for details.)
And then login as the root user, after that do the things you wish.
https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
------------------ Original ------------------
From: "Ron" <ronljohnsonjr@gmail.com>;
Date: Mon, Jul 17, 2023 10:28 AM
To: "pgsql-general"<pgsql-general@lists.postgresql.org>;
Subject: Re: Reset Postgresql users password
On 7/12/23 14:28, Johnathan Tiamoh wrote:
> Hello,
>
> I wish to find out if there is a way to reset all users in Postgresql
> password to the same password at once.
To the same value??
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Reset Postgresql users password
@ 2023-07-17 07:52 Chris Travers <chris.travers@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Chris Travers @ 2023-07-17 07:52 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
You can use a DO block or write a function do to this.
It takes some practice (and you need to use EXECUTE FORMAT())
If users need to be able to change their own users, something like this
works:
CREATE FUNCTION change_my_password(in_password, text)
returns void language plpgsql as
$$
begin
EXECUTE FORMAT($F$ALTER USER $I WITH PASSWORD %L$F$, session_user,
in_password);
end;
$$ SECURITY DEFINER;
On Mon, Jul 17, 2023 at 9:28 AM Ron <ronljohnsonjr@gmail.com> wrote:
> On 7/12/23 14:28, Johnathan Tiamoh wrote:
> > Hello,
> >
> > I wish to find out if there is a way to reset all users in Postgresql
> > password to the same password at once.
>
> To the same value??
>
> --
> Born in Arizona, moved to Babylonia.
>
>
>
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor
lock-in.
http://www.efficito.com/learn_more
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2023-07-17 07:52 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-12 19:28 Reset Postgresql users password Johnathan Tiamoh <johnathantiamoh@gmail.com>
2023-07-12 19:34 ` Gianni Ceccarelli <dakkar@thenautilus.net>
2023-07-12 19:42 ` Mateusz Henicz <mateuszhenicz@gmail.com>
2023-07-12 20:16 ` Gurjeet Singh <gurjeet@singh.im>
2023-07-12 20:25 ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
2023-07-12 20:35 ` David G. Johnston <david.g.johnston@gmail.com>
2023-07-17 02:28 ` Ron <ronljohnsonjr@gmail.com>
2023-07-17 03:27 ` Wen Yi <wen-yi@qq.com>
2023-07-17 07:52 ` Chris Travers <chris.travers@gmail.com>
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