Re: 8.0.0 pg_restore -L doesn't restore ACLs

PostgreSQL 8.0.0

Running "pg_restore -l" to create a list file and then restoring
using "pg_restore -L" fails to restore ACLs. See the "Privileges
where [sic] not restored" thread in pgsql-admin:

http://archives.postgresql.org/pgsql-admin/2005-01/msg00197.php

As I mention in a followup in that thread, the following restores
objects with their ACLs:

pg_restore -d testdb db.dump

The following doesn't restore ACLs:

pg_restore -l db.dump > db.list
pg_restore -d testdb -L db.list db.dump

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> PostgreSQL 8.0.0
> Running "pg_restore -l" to create a list file and then restoring
> using "pg_restore -L" fails to restore ACLs. See the "Privileges
> where [sic] not restored" thread in pgsql-admin:
> http://archives.postgresql.org/pgsql-admin/2005-01/msg00197.php

No doubt this is because the -l option doesn't list ACL entries in the
first place :-(.

A quick fix for the immediate issue would be to pass acl_pass as true
instead of false in PrintTOCSummary (pg_backup_archiver.c line 712 in
current sources). I wonder however why PrintTOCSummary is filtering the
TOC entries at all, and whether there are any other cases that it really
shouldn't be filtering out. Philip, do you remember why it's like that?

regards, tom lane

On Sat, Jan 22, 2005 at 07:19:28PM -0500, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
> > PostgreSQL 8.0.0
> > Running "pg_restore -l" to create a list file and then restoring
> > using "pg_restore -L" fails to restore ACLs. See the "Privileges
> > where [sic] not restored" thread in pgsql-admin:
> > http://archives.postgresql.org/pgsql-admin/2005-01/msg00197.php
>
> No doubt this is because the -l option doesn't list ACL entries in the
> first place :-(.

Right, but it did in 7.4.6. I'm not sure if you're saying, "It
doesn't but it used to" or "It doesn't and it never did."

If I dump a 7.4.6 database with pg_dump 7.4.6 and do a pg_restore -l
with pg_restore 7.4.6, I get the following:

;
; Archive created at Sat Jan 22 20:40:14 2005
; dbname: testdb
; TOC Entries: 7
; Compression: -1
; Dump Version: 1.7-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
;
;
; Selected TOC Entries:
;
4; 2200 ACL public pgsql
5; 2561677 TABLE foo mfuhr
6; 2561677 ACL foo mfuhr
7; 2561677 TABLE DATA foo mfuhr
3; 2200 COMMENT SCHEMA public pgsql

If I use pg_restore 8.0.0 on the same file, I get this instead:

;
; Archive created at Sat Jan 22 20:40:14 2005
; dbname: testdb
; TOC Entries: 7
; Compression: -1
; Dump Version: 1.7-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
;
;
; Selected TOC Entries:
;
5; 0 2561677 TABLE public foo mfuhr
7; 0 2561677 TABLE DATA public foo mfuhr
3; 0 2200 COMMENT - SCHEMA public pgsql

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike(at)fuhr(dot)org> writes:
>> PostgreSQL 8.0.0
>> Running "pg_restore -l" to create a list file and then restoring
>> using "pg_restore -L" fails to restore ACLs. See the "Privileges
>> where [sic] not restored" thread in pgsql-admin:
>> http://archives.postgresql.org/pgsql-admin/2005-01/msg00197.php

> No doubt this is because the -l option doesn't list ACL entries in the
> first place :-(.

Fixed for 8.0.1.

regards, tom lane