Horton

Thread

Commits

  1. Doc: fix old oversights in GRANT/REVOKE documentation.

  1. How to restore roles without changing postgres password

    Andrus <kobruleht2@hot.ee> — 2020-02-11T22:26:50Z

    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

  2. Re: How to restore roles without changing postgres password

    Justin <zzzzz.graf@gmail.com> — 2020-02-11T22:37:55Z

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

  3. Re: How to restore roles without changing postgres password

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-02-11T22:46:20Z

    "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

  4. Re: How to restore roles without changing postgres password

    Justin <zzzzz.graf@gmail.com> — 2020-02-11T23:04:02Z

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

  5. Re: How to restore roles without changing postgres password

    Andrus <kobruleht2@hot.ee> — 2020-02-12T07:31:48Z

    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.

  6. Re: How to restore roles without changing postgres password

    Andrus <kobruleht2@hot.ee> — 2020-02-12T10:06:56Z

    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.

  7. Re: How to restore roles without changing postgres password

    Adrian Klaver <adrian.klaver@aklaver.com> — 2020-02-12T16:39:27Z

    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

  8. Re: How to restore roles without changing postgres password

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-02-12T16:59:46Z

    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