Search Postgresql Archives

Re: intentional or oversight? pg_dump -c does not restore default priviliges on schema public

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



* Tom Lane (tgl@xxxxxxxxxxxxx) wrote:
> Stephen Frost <sfrost@xxxxxxxxxxx> writes:
> > I'm not seeing a very simple answer for this, unfortunately.
> 
> I'm inclined to argue that it was a mistake to include any non-pinned
> objects in pg_init_privs.  The reason initdb leaves some objects unpinned
> is exactly because they can be dropped and recreated, and that means
> that their "initial" privileges are not static system properties.
> 
> We might need to fix pg_dump too, but I think these entries in
> pg_init_privs should simply not be there.

Wouldn't that mean we then need to drop and recreate all of those
non-pinned objects, with their ACLs, on a -c?

Or are you thinking we would just dump out whatever ACLs they have that
are different from default object creation, meaning that we'd have ACLs
for nearly all the views in pg_catalog and information_schema included
in regular pg_dump output?  Just dumping out the ACLs wouldn't
necessairly get us back to the same state as the database which was
dumped out though as the existing objects might already have some GRANTs
performed on them.

pg_init_privs also handles the initial privileges for extension objects,
though I'm guessing you aren't including those.  The initdb-time objects
are certainly similar though, in some ways, to extensions, the main
difference being that you can't really drop/recreate all of the initdb
time objects.

It seems to me that the oddball here is the public schema, and
specifically, the public schema when the user has been using the
from-initdb public schema (which has initdb-time privileges which are
recorded into pg_init_privs).  Had the user dropped and recreated the
public schema, the initdb-time privs for the original public schema in
pg_init_privs would have been removed, and the new public schema
wouldn't have had any entries in pg_init_privs.  The reason it's an
oddball is that it's the only from-initdb time object which is dropped
and recreated as part of pg_dump -c.  If we dropped all non-pinned
objects and recreated them when running pg_dump -c then perhaps what
you're suggesting would work, but we don't and I don't think that's
really what you were thinking we would do.

While it's a bit grotty, I tend to think the best approach is to
continue to assume that the only from-initdb-time object that the user
actually wants us to drop/recreate on a pg_dump -c is the public schema,
which means we need to handle things a bit differently when working in
-c mode for the public schema when pulling ACLs.

Thanks!

Stephen

Attachment: signature.asc
Description: Digital signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux