Thread: Reset Postgresql users password
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
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
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
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
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
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.
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.
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
> 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.
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)
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.