pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Reset Postgresql users password
9+ messages / 8 participants
[nested] [flat]

* Reset Postgresql users password
@ 2023-07-12 19:28 Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-12 19:34 ` Re: Reset Postgresql users password Gianni Ceccarelli <dakkar@thenautilus.net>
  2023-07-17 02:28 ` Re: Reset Postgresql users password Ron <ronljohnsonjr@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: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   ` Re: Reset Postgresql users password Mateusz Henicz <mateuszhenicz@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:28 Reset Postgresql users password Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-12 19:34 ` Re: Reset Postgresql users password Gianni Ceccarelli <dakkar@thenautilus.net>
@ 2023-07-12 19:42   ` Mateusz Henicz <mateuszhenicz@gmail.com>
  2023-07-12 20:16     ` Re: Reset Postgresql users password Gurjeet Singh <gurjeet@singh.im>
  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 19:28 Reset Postgresql users password Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-12 19:34 ` Re: Reset Postgresql users password Gianni Ceccarelli <dakkar@thenautilus.net>
  2023-07-12 19:42   ` Re: Reset Postgresql users password Mateusz Henicz <mateuszhenicz@gmail.com>
@ 2023-07-12 20:16     ` Gurjeet Singh <gurjeet@singh.im>
  2023-07-12 20:25       ` Re: Reset Postgresql users password Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-12 20:35       ` Re: Reset Postgresql users password David G. Johnston <david.g.johnston@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 19:28 Reset Postgresql users password Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-12 19:34 ` Re: Reset Postgresql users password Gianni Ceccarelli <dakkar@thenautilus.net>
  2023-07-12 19:42   ` Re: Reset Postgresql users password Mateusz Henicz <mateuszhenicz@gmail.com>
  2023-07-12 20:16     ` Re: Reset Postgresql users password Gurjeet Singh <gurjeet@singh.im>
@ 2023-07-12 20:25       ` Johnathan Tiamoh <johnathantiamoh@gmail.com>
  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 19:28 Reset Postgresql users password Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-12 19:34 ` Re: Reset Postgresql users password Gianni Ceccarelli <dakkar@thenautilus.net>
  2023-07-12 19:42   ` Re: Reset Postgresql users password Mateusz Henicz <mateuszhenicz@gmail.com>
  2023-07-12 20:16     ` Re: Reset Postgresql users password Gurjeet Singh <gurjeet@singh.im>
@ 2023-07-12 20:35       ` David G. Johnston <david.g.johnston@gmail.com>
  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-12 19:28 Reset Postgresql users password Johnathan Tiamoh <johnathantiamoh@gmail.com>
@ 2023-07-17 02:28 ` Ron <ronljohnsonjr@gmail.com>
  2023-07-17 03:27   ` Re: Reset Postgresql users password Wen Yi <wen-yi@qq.com>
  2023-07-17 07:52   ` Re: Reset Postgresql users password Chris Travers <chris.travers@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-12 19:28 Reset Postgresql users password Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-17 02:28 ` Re: Reset Postgresql users password Ron <ronljohnsonjr@gmail.com>
@ 2023-07-17 03:27   ` Wen Yi <wen-yi@qq.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-12 19:28 Reset Postgresql users password Johnathan Tiamoh <johnathantiamoh@gmail.com>
  2023-07-17 02:28 ` Re: Reset Postgresql users password Ron <ronljohnsonjr@gmail.com>
@ 2023-07-17 07:52   ` Chris Travers <chris.travers@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