Re: Reset Postgresql users password - Mailing list pgsql-general

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