On my 7.3 server:

australia=# \dp exercise_activities
                       Access privileges for database "australia"
 Schema |        Table        |                    Access privileges
--------+---------------------+---------------------------------------------
------------
 public | exercise_activities |
{=,chriskl=arwdRxt,auadmin=arwdRxt,au-diary=r,au-php=r}
(1 row)

is dumped as:

REVOKE ALL ON TABLE exercise_activities FROM PUBLIC;
GRANT ALL ON TABLE exercise_activities TO chriskl;
GRANT SELECT ON TABLE exercise_activities TO "au-diary";
GRANT SELECT ON TABLE exercise_activities TO "au-php";

Now if you load that into 7.4CVS, you get:

australia=# \dp exercise_activities
                                               Access privileges for
database "australia"
 Schema |        Table        |
Access privileges
--------+---------------------+---------------------------------------------
-------------------------------------------------------------
 public | exercise_activities |
{auadmin=a*r*w*d*R*x*t*/auadmin,chriskl=arwdRxt/auadmin,"\"au-diary\"=r/auad
min","\"au-php\"=r/auadmin"}
(1 row)

Which is dumped as:

REVOKE ALL ON TABLE exercise_activities FROM PUBLIC;
GRANT ALL ON TABLE exercise_activities TO chriskl;
GRANT SELECT ON TABLE exercise_activities TO "\""au-diary\""";
GRANT SELECT ON TABLE exercise_activities TO "\""au-php\""";

ie. 7.4 considers the double quotes around a username to be part of the
username...

Chris


Wed, 9 Jul 2003 11:06:17 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Another nasty pg_dump problem
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> On my 7.3 server:
> REVOKE ALL ON TABLE exercise_activities FROM PUBLIC;
> GRANT ALL ON TABLE exercise_activities TO chriskl;
> GRANT SELECT ON TABLE exercise_activities TO "au-diary";
> GRANT SELECT ON TABLE exercise_activities TO "au-php";

> Now if you load that into 7.4CVS, you get:

> REVOKE ALL ON TABLE exercise_activities FROM PUBLIC;
> GRANT ALL ON TABLE exercise_activities TO chriskl;
> GRANT SELECT ON TABLE exercise_activities TO "\""au-diary\""";
> GRANT SELECT ON TABLE exercise_activities TO "\""au-php\""";

I've repaired this in CVS tip.  While testing it, though, I notice that
CVS-tip pg_dump puts out useless commands

	REVOKE ALL ON SCHEMA public FROM PUBLIC;
	GRANT ALL ON SCHEMA public TO PUBLIC;

which are not generated when dumping from 7.3.  The reason evidently is
that this check in pg_dump.c no longer works:

        /*
         * If it's the PUBLIC namespace, don't emit a CREATE SCHEMA record
         * for it, since we expect PUBLIC to exist already in the
         * destination database.  And emit ACL info only if the ACL isn't
         * the standard value for PUBLIC.
         */
        if (strcmp(nspinfo->nspname, "public") == 0)
        {
            if (!aclsSkip && strcmp(nspinfo->nspacl, "{=UC}") != 0)
                dumpACL(fout, "SCHEMA", qnspname, nspinfo->nspname, NULL,
                        nspinfo->usename, nspinfo->nspacl,
                        nspinfo->oid);
        }

since the default ACL for public no longer looks like that.  Can we fix
this?

			regards, tom lane

Thu, 31 Jul 2003 13:28:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Another nasty pg_dump problem 
Tom Lane writes:

> I've repaired this in CVS tip.  While testing it, though, I notice that
> CVS-tip pg_dump puts out useless commands
>
> 	REVOKE ALL ON SCHEMA public FROM PUBLIC;
> 	GRANT ALL ON SCHEMA public TO PUBLIC;
>
> which are not generated when dumping from 7.3.  The reason evidently is
> that this check in pg_dump.c no longer works:

This could be fixed, but note that elsewhere we use

    /*
     * Always start with REVOKE ALL FROM PUBLIC, so that we don't have to
     * wire-in knowledge about the default public privileges for different
     * kinds of objects.
     */
    appendPQExpBuffer(firstsql, "REVOKE ALL ON %s %s FROM PUBLIC;\n",
                      type, name);

So maybe this isn't such a bad state after all.

-- 
Peter Eisentraut   peter_e@gmx.net

Fri, 1 Aug 2003 12:25:18 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: Another nasty pg_dump problem 
Peter Eisentraut <peter_e@gmx.net> writes:
> This could be fixed, but note that elsewhere we use

>     /*
>      * Always start with REVOKE ALL FROM PUBLIC, so that we don't have to
>      * wire-in knowledge about the default public privileges for different
>      * kinds of objects.
>      */
>     appendPQExpBuffer(firstsql, "REVOKE ALL ON %s %s FROM PUBLIC;\n",
>                       type, name);

> So maybe this isn't such a bad state after all.

Well, if you want to take that position then the test for "{=UC}" ought
to be ripped out, so that we are consistent about it across backend
versions.

			regards, tom lane

Fri, 01 Aug 2003 09:31:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Another nasty pg_dump problem 