Thread: How to restore roles without changing postgres password

How to restore roles without changing postgres password

From
"Andrus"
Date:
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 




Re: How to restore roles without changing postgres password

From
Justin
Date:
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:
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



Re: How to restore roles without changing postgres password

From
Tom Lane
Date:
"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



Re: How to restore roles without changing postgres password

From
Justin
Date:
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:
"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


Re: How to restore roles without changing postgres password

From
"Andrus"
Date:
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
 
does not contain GRANTED BY clause. It looks like pg_dumpall generates undocumented clause.
 
Andrus.
 

Re: How to restore roles without changing postgres password

From
"Andrus"
Date:
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.

Re: How to restore roles without changing postgres password

From
Adrian Klaver
Date:
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



Re: How to restore roles without changing postgres password

From
Tom Lane
Date:
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



Re: How to restore roles without changing postgres password

From
Tom Lane
Date:
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