Thread: Reset Postgresql users password

Reset Postgresql users password

From
Johnathan Tiamoh
Date:
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

Re: Reset Postgresql users password

From
Gianni Ceccarelli
Date:
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




Re: Reset Postgresql users password

From
Mateusz Henicz
Date:
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



Re: Reset Postgresql users password

From
Gurjeet Singh
Date:
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



Re: Reset Postgresql users password

From
Johnathan Tiamoh
Date:
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


Re: Reset Postgresql users password

From
"David G. Johnston"
Date:
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.

Re: Reset Postgresql users password

From
Ron
Date:
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.



Re: Reset Postgresql users password

From
"Wen Yi"
Date:
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.

Re: Reset Postgresql users password

From
Chris Travers
Date:
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.