How to restore roles without changing postgres password

pg_dumpall creates an SQL file which is just a simple text file

you can then edit sql removing postgres user from the file

This can be automated in a script that searches the generated sql file for
the postgres user replacing it with a blank/empty line or adds -- to the
bringing of the line which comments it out.

On Tue, Feb 11, 2020 at 5:27 PM Andrus <kobruleht2@hot.ee> wrote:

"Andrus" <kobruleht2@hot.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

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@sss.pgh.pa.us> wrote:

Hi!

Thank you.

pg_dumpall creates an SQL file which is just a simple text file

you can then edit sql removing postgres user from the file
This can be automated in a script that searches the generated sql file for the postgres user replacing it with a blank/empty line or adds -- to the bringing of >the line which comments it out.

This script creates cluster copy in every night. So this should be done automatically.
I have little experience with Linux.
Can you provide example, how it should it be done using sed or other tool.
There is also second user named dbandmin whose password cannot changed also.

It would be best if CREATE ROLE and ALTER ROLE clauses for postgres and dbadmin users are removed for file.

Or if this is not reasonable, same passwords or different role names can used in both clusters.

Also I dont understand why GRANTED BY clauses appear in file. This looks like noice.
GRANT documentation
https://www.postgresql.org/docs/current/sql-grant.html

does not contain GRANTED BY clause. It looks like pg_dumpall generates undocumented clause.

Andrus.

Hi!

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..

Maybe it is easier to create plpgsql procedure which returns desired script as text.
Or it retrieves globals from other cluster using dblink and applies changes to new cluster.

This can be called instead of pq_dumpall and can edited for custom needs.
Editing plpgsql script is easier for postgres users than creating sed script to delete commands from sql file.

Andrus.

On 2/11/20 11:31 PM, Andrus wrote:

Hi!
Thank you.

pg_dumpall creates an SQL file which is just a simple text file
you can then edit sql removing postgres user from  the file
This can be automated in a script that searches the generated sql file

for the postgres user  replacing it with a blank/empty line or adds --
to the bringing of >the line which comments it out.
This script creates cluster copy in every night. So this should be done
automatically.
I have little experience with Linux.
Can you provide example, how it should it be done using sed or other tool.
There is also second user named dbandmin whose password  cannot changed
also.
It would be best if  CREATE ROLE and ALTER ROLE  clauses for postgres
and dbadmin users are removed for file.

Then we would get all sorts of posts about why they are not showing up
anymore. This suggestion is a non starter.

Or if this is not reasonable, same passwords or different role names can
used in both clusters.

They can be, you just have to track/manipulate that yourself. What it
comes down to is that the Postgres project is not the admin for
everyone's install.

Also I dont understand why GRANTED BY clauses appear in file. This looks
like noice.
GRANT documentation
https://www.postgresql.org/docs/current/sql-grant.html
does not contain GRANTED BY clause. It looks like pg_dumpall generates
undocumented clause.

It is not noise, see:

~/src/bin/pg_dump/pg_dumpall.cpg_dumpall.c

/*
* We don't track the grantor very carefully in the backend, so cope
* with the possibility that it has been dropped.
*/
if (!PQgetisnull(res, i, 3))
{
char *grantor = PQgetvalue(res, i, 3);

fprintf(OPF, " GRANTED BY %s", fmtId(grantor));
}
fprintf(OPF, ";\n");

Andrus.

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 2/11/20 11:31 PM, Andrus wrote:

Also I dont understand why GRANTED BY clauses appear in file. This looks
like noice.
GRANT documentation
https://www.postgresql.org/docs/current/sql-grant.html
does not contain GRANTED BY clause. It looks like pg_dumpall generates
undocumented clause.

It is not noise, see:

Indeed, but it's a fair question why it's not documented.
The clause does appear in the SQL standard:

<grant privilege statement> ::=
GRANT <privileges> TO <grantee> [ { <comma> <grantee> }... ]
[ WITH HIERARCHY OPTION ]
[ WITH GRANT OPTION ]
[ GRANTED BY <grantor> ]

so I suppose whoever added the implementation just forgot about
fixing the docs.

regards, tom lane