Обсуждение: Dump & restore in directory format and permissions are largely lost?

Поиск
Список
Период
Сортировка

Dump & restore in directory format and permissions are largely lost?

От
Wells Oliver
Дата:
Source and target are 13.2. Source dump command:

time pg_dump \
        --verbose \
        --dbname=boxscore \
        --blobs \
        --jobs=${JOBS} \
        --format=directory \
        --file=db.full.dump > ~/db.full.dump.log 2>&1

Restore excludes mat views, so two steps:

time pg_restore \
        --list \
        --format=directory \
        db.full.dump | sed '/MATERIALIZED VIEW DATA/d' > restore.lst

And then

time pg_restore \
        --verbose \
        --host=${HOST} \
        --user=postgres \
        --format=directory \
        --use-list restore.lst \
        --dbname=boxscore \
        --jobs 16 \
        db.full.dump > ~/restore.log 2>&1

After creation, none of my schemes or relations really have the right permissions, they seem totally absent. The same exact roles exist in source and target.

Am I missing something obvious here?


--

Re: Dump & restore in directory format and permissions are largely lost?

От
"David G. Johnston"
Дата:
On Tue, Jun 15, 2021 at 6:55 PM Wells Oliver <wells.oliver@gmail.com> wrote:

Am I missing something obvious here?


You specified a host in the restore command - since pg_dump doesn't dump global objects like roles are you sure the restore cluster has the necessary roles present?

David J.

Re: Dump & restore in directory format and permissions are largely lost?

От
Wells Oliver
Дата:
Yes. The target and source servers have the same users and roles, which is why their permissions being absent from the same objects on the target where they exist in the source is very confusing to me.

I also do a pg_restore -l -Fd on the dump file, and I don't see any GRANT or REVOKE statements: should I expect that?

On Tue, Jun 15, 2021 at 5:58 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jun 15, 2021 at 6:55 PM Wells Oliver <wells.oliver@gmail.com> wrote:

Am I missing something obvious here?


You specified a host in the restore command - since pg_dump doesn't dump global objects like roles are you sure the restore cluster has the necessary roles present?

David J.


--

Re: Dump & restore in directory format and permissions are largely lost?

От
Tom Lane
Дата:
Wells Oliver <wells.oliver@gmail.com> writes:
> Yes. The target and source servers have the same users and roles, which is
> why their permissions being absent from the same objects on the target
> where they exist in the source is very confusing to me.

Did you look for errors in the log output of both pg_dump and pg_restore?

> I also do a pg_restore -l -Fd on the dump file, and I don't see any GRANT
> or REVOKE statements: should I expect that?

They'd show up in "-l" output as ACL items.

            regards, tom lane



Re: Dump & restore in directory format and permissions are largely lost?

От
Wells Oliver
Дата:
It looks like the dump, then is creating an insufficient number of ACL statements. Running 

pg_restore -Fd -l db.full.dump/ | grep ACL | grep webaccess

Shows nothing, though I use this role on many schemas and relations.

Is there anything about this dump statement that would prevent some ACL lines from being generated?

time pg_dump \
        --verbose \
        --dbname=db \
        --blobs \
        --jobs=8 \
        --format=directory \
        --file=db.full.dump

Is there a way to run pg_dump to spit out all ACL lines w/o having to do the full dump?



On Tue, Jun 15, 2021 at 6:19 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> Yes. The target and source servers have the same users and roles, which is
> why their permissions being absent from the same objects on the target
> where they exist in the source is very confusing to me.

Did you look for errors in the log output of both pg_dump and pg_restore?

> I also do a pg_restore -l -Fd on the dump file, and I don't see any GRANT
> or REVOKE statements: should I expect that?

They'd show up in "-l" output as ACL items.

                        regards, tom lane


--

Re: Dump & restore in directory format and permissions are largely lost?

От
Tom Lane
Дата:
Wells Oliver <wells.oliver@gmail.com> writes:
> pg_restore -Fd -l db.full.dump/ | grep ACL | grep webaccess
> Shows nothing, though I use this role on many schemas and relations.

What do you think you're filtering for there?  The -l output for ACL entries
will show the names of the objects and their owners, but not the grantees.

> Is there anything about this dump statement that would prevent some ACL
> lines from being generated?

Not that I see.

            regards, tom lane



Re: Dump & restore in directory format and permissions are largely lost?

От
Wells Oliver
Дата:
Ah, I'm sorry, is there a way to see grants contained in a dump?

On Tue, Jun 15, 2021 at 6:30 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> pg_restore -Fd -l db.full.dump/ | grep ACL | grep webaccess
> Shows nothing, though I use this role on many schemas and relations.

What do you think you're filtering for there?  The -l output for ACL entries
will show the names of the objects and their owners, but not the grantees.

> Is there anything about this dump statement that would prevent some ACL
> lines from being generated?

Not that I see.

                        regards, tom lane


--

Re: Dump & restore in directory format and permissions are largely lost?

От
Tom Lane
Дата:
Wells Oliver <wells.oliver@gmail.com> writes:
> Ah, I'm sorry, is there a way to see grants contained in a dump?

I'd try "pg_restore -s -f - archive.file | grep GRANT" or so.

            regards, tom lane



Re: Dump & restore in directory format and permissions are largely lost?

От
Wells Oliver
Дата:
That worked, I piped all the GRANTs into a file and ran it against the DB and permissions have been restored, but it remains puzzling to me why they were absent after the restore.

On Tue, Jun 15, 2021 at 6:45 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> Ah, I'm sorry, is there a way to see grants contained in a dump?

I'd try "pg_restore -s -f - archive.file | grep GRANT" or so.

                        regards, tom lane


--