Re: pg_dump broken for non-super user - Mailing list pgsql-hackers

From Robert Haas
Subject Re: pg_dump broken for non-super user
Date
Msg-id CA+TgmobioyPLea640kFBwkuAriwPv8DuvvMZWHztX_K9zzhD9A@mail.gmail.com
Whole thread Raw
In response to Re: pg_dump broken for non-super user  (Stephen Frost <sfrost@snowman.net>)
List pgsql-hackers
On Wed, May 4, 2016 at 9:35 AM, Stephen Frost <sfrost@snowman.net> wrote:
> * Robert Haas (robertmhaas@gmail.com) wrote:
>> On Wed, May 4, 2016 at 5:29 AM, Stephen Frost <sfrost@snowman.net> wrote:
>> > There is no such limitation on using pg_dump as a non-superuser.  It's
>> > always been the case that you need to be able to LOCK the table that
>> > you're dumping.  If you don't have rights to LOCK a certain table then
>> > pg_dump is going to throw an error just like the one you saw.
>>
>> But in Rushabh's example, he's not doing that.  He's trying to do a
>> full-database dump of a database that contains one object which the
>> dump user has rights to access.  Previously, that worked.  Now, it
>> fails with an error about a system catalog.  How is that not broken?
>
> As I mentioned up-thread, the optimization to skip tables which are not
> "interesting" has been improved in the patch-set posted this morning to
> skip over tables whose ACLs haven't changed from the defaults.  With
> that patch, we will skip over catalog tables whose ACLs haven't been
> changed and Rushabh's command will work as a non-superuser, assuming
> none of the ACLs on tables in pg_catalog have been changed.

OK.

> However, if any of the ACLs have been changed on tables in pg_catalog,
> we'll attempt to lock those tables and include those ACLs.  That will
> still work in many cases as you only need SELECT access to be able to
> lock a table in access share mode, but if the permissions on pg_authid
> are changed, the same failure will occur.

Hmm, I guess that's reasonable.  It might cause problems for some
users, but then again it might not.  I guess that's what a beta is
for.

> I wouldn't recommend depending on those tables being excluded in backup
> scripts.  If you wish to dump everything except pg_catalog, the -N
> switch can be used to exclude that schema.

Maybe that recommendation should be added to the documentation and/or
release notes someplace.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



pgsql-hackers by date:

Previous
From: Stas Kelvich
Date:
Subject: Re: Naming of new tsvector functions
Next
From: Tom Lane
Date:
Subject: Re: Naming of new tsvector functions