Обсуждение: How to restore roles without changing postgres password

Hi!

How to create backup script which restores all roles and role memberships 
from other server without changing postgres user password.

I tried shell script

PGHOST=example.com
PGUSER=postgres
PGPASSWORD=mypass
export PGHOST  PGPASSWORD  PGUSER
pg_dumpall --roles-only --file=globals.sql
psql -f globals.sql postgres

but this changes user postgres  password also.
How to restore roles so that postgres user password is not changed on 
restore.

Script runs on Debian 10 with Postgres 12
Server from where it reads users runs on Debian Squeeze with Postgres 9.1

Andrus 

"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

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

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