Thread: pg_dump/all doesn't output database ACLs (v7.3.4)

I haven't tried the 7.4 beta, so it may be fixed there - but in
7.3.4, pg_dumpall doesn't output global database ACL's.  Try this
in a fresh database after an initdb:

template1=# create user test nocreatedb nocreateuser;
CREATE USER
template1=# create database testdb;
CREATE DATABASE
template1=# grant create on database testdb to test;
GRANT
template1=# select datname, datacl from pg_database ;
datname  |         datacl
-----------+-------------------------
testdb    | {=T,postgres=CT,test=C}
template1 | {=,postgres=CT}
template0 | {=,postgres=CT}
(3 rows)
template1=# \q

$ pg_dumpall -U postgres
--
-- PostgreSQL database cluster dump
--

\connect "template1"

--
-- Users
--

DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');

CREATE USER test WITH SYSID 100 NOCREATEDB NOCREATEUSER;


--
-- Groups
--

DELETE FROM pg_group;



--
-- Database creation
--

CREATE DATABASE testdb WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'SQL_ASCII';


\connect template1
--
-- PostgreSQL database dump
--

--
-- TOC entry 2 (OID 1)
-- Name: DATABASE template1; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON DATABASE template1 IS 'Default template database';


\connect testdb
--
-- PostgreSQL database dump
--

Ben Grimm <ben@zaeon.com> writes:
> I haven't tried the 7.4 beta, so it may be fixed there - but in
> 7.3.4, pg_dumpall doesn't output global database ACL's.

This is fixed for 7.4.

            regards, tom lane

Ben Grimm <ben@zaeon.com> writes:
> On Thu, 04 Sep 2003, Tom Lane wrote:
>> Ben Grimm <ben@zaeon.com> writes:
>>> I haven't tried the 7.4 beta, so it may be fixed there - but in
>>> 7.3.4, pg_dumpall doesn't output global database ACL's.
>>
>> This is fixed for 7.4.

> Is there a patch to backport this to the 7.3 series?

No, but you can use 7.4's pg_dumpall against your 7.3 database.  This is
generally the recommended way to do an upgrade, btw --- the newer
version's pg_dump is supposed to be able to compensate for cross-version
compatibility issues, but we lack the crystal ball needed to make the
older version's pg_dump do it :-(

            regards, tom lane

On Mon, 08 Sep 2003, Tom Lane wrote:

> Ben Grimm <ben@zaeon.com> writes:
> > On Thu, 04 Sep 2003, Tom Lane wrote:
> >> Ben Grimm <ben@zaeon.com> writes:
> >>> I haven't tried the 7.4 beta, so it may be fixed there - but in
> >>> 7.3.4, pg_dumpall doesn't output global database ACL's.
> >>
> >> This is fixed for 7.4.
>
> > Is there a patch to backport this to the 7.3 series?
>
> No, but you can use 7.4's pg_dumpall against your 7.3 database.

Sure, I tried it right away when you mentioned that it was fixed for 7.4.
So doing an upgrade won't give any problems relating to database acl's,
but we'll still have issues with user owned schemas.

So.. what about all the people that are starting to use schemas now that
they're available?  For example, we went from about 1500 logical users
using a few dozen tables to 1500 real users using schemas.  Now, all of
the data that is owned by a given user - rather than being mixed with all
of the other users data - is consolidated into inherited versions of tables
from the public schema.  It all works quite well, and I'm not sure we'd
have been able to do it this way with any other dbms.  Of course, now we
have 86,000 rows in pg_class and pg_dump takes 3x longer to run than it
used to.  What I'm getting at is that we use a development server that is
loaded from a copy of the production server, and after we designed and
tested the migration, redesigned the application to fit the new model, and
finally did our application/schema upgrade a couple of weeks back we went
to copy the new production database back to the development server.  I was
really surprised to find the bugs I did - and worried to know that the
nightly backups I take are not going to be as simple to restore as just
running 'bzcat backup.bz2 | psql'.  I can make it work so it's not tragic
for me; but I wonder about all the other people who are using schemas
and/or database level permissions in the 7.3 series.

On Thu, 04 Sep 2003, Tom Lane wrote:

> Ben Grimm <ben@zaeon.com> writes:
> > I haven't tried the 7.4 beta, so it may be fixed there - but in
> > 7.3.4, pg_dumpall doesn't output global database ACL's.
>
> This is fixed for 7.4.

Is there a patch to backport this to the 7.3 series?  Without it there
is no reliable way to backup a database with more complicated permissions.

I'm sure I'd just muddle through with a sed script to insert the grants
at the right place should the need arise.  But likewise - with the other
bug I reported - pg_dump is generating sql that can't be executed because
the users don't have permissions.  The resulting SQL just won't work as
is... and without intervention users can't restore/upgrade databases that
have these types of permissions.