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@xxxxxxxxxxxxx> wrote:
"Andrus" <kobruleht2@xxxxxx> 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
