Re: How to restore roles without changing postgres password

HI Tom

Not a bad idea, would want to extend this to all the roles on the server
not just postgres

I've edited the global dump many times removing/editing table spaces,
comment old users, etc..

On Tue, Feb 11, 2020 at 5:46 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Andrus" <kobruleht2(at)hot(dot)ee> writes:
> > How to create backup script which restores all roles and role
> memberships
> > from other server without changing postgres user password.
>
> [ shrug... ] Edit the command(s) you don't want out of the script.
> This seems like a mighty random requirement to expect pg_dump to
> support out-of-the-box.
>
> I wonder though if there's a case for making that easier by breaking
> up the output into multiple ALTER commands. Right now you get
> something like
>
> CREATE ROLE postgres;
> ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN
> REPLICATION BYPASSRLS PASSWORD 'md5128f0d64bfb424d132c3305b3057281c';
>
> but perhaps we could make it print
>
> CREATE ROLE postgres;
> ALTER ROLE postgres WITH SUPERUSER;
> ALTER ROLE postgres WITH INHERIT;
> ALTER ROLE postgres WITH CREATEROLE;
> ALTER ROLE postgres WITH CREATEDB;
> ALTER ROLE postgres WITH LOGIN;
> ALTER ROLE postgres WITH REPLICATION;
> ALTER ROLE postgres WITH BYPASSRLS;
> ALTER ROLE postgres WITH PASSWORD 'md5128f0d64bfb424d132c3305b3057281c';
>
> That would make scripted edits a bit easier, and it'd also make the
> output a bit more cross-version portable, eg if you try to load the
> latter into a version without BYPASSRLS, the rest of the commands
> would still work.
>
> regards, tom lane
>
>
>