Обсуждение: pgsql: Update for roles: < * Prevent default re-use of sysids for

Log Message:
-----------
Update for roles:

< * Prevent default re-use of sysids for dropped users and groups
> * Prevent default re-use of sysids for dropped users and roles
450c450
< * Add COMMENT ON for all cluster global objects (users, groups, databases
> * Add COMMENT ON for all cluster global objects (users, roles, databases
609c609
<       users and groups with separate DROP commands
>       users and roles with separate DROP commands

Modified Files:
--------------
    pgsql/doc:
        TODO (r1.1583 -> r1.1584)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/TODO.diff?r1=1.1583&r2=1.1584)
    pgsql/doc/src/FAQ:
        TODO.html (r1.90 -> r1.91)
        (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/FAQ/TODO.html.diff?r1=1.90&r2=1.91)

* Bruce Momjian (momjian@svr1.postgresql.org) wrote:
> < * Prevent default re-use of sysids for dropped users and groups
> > * Prevent default re-use of sysids for dropped users and roles

sysids are gone, roles have Oids, so I don't think this is an issue
anymore...  Users are gone too...

> 450c450
> < * Add COMMENT ON for all cluster global objects (users, groups, databases
> > * Add COMMENT ON for all cluster global objects (users, roles, databases

Users are gone, so this would just apply to roles..

> <       users and groups with separate DROP commands
> >       users and roles with separate DROP commands

users are gone, there are only roles, so I don't know that they'd need
separate DROP commands, if that's what this means?  We do have DROP ROLE
and REVOKE ROLE...

    Thanks,

        Stephen

Stephen Frost wrote:
-- Start of PGP signed section.
> * Bruce Momjian (momjian@svr1.postgresql.org) wrote:
> > < * Prevent default re-use of sysids for dropped users and groups
> > > * Prevent default re-use of sysids for dropped users and roles
>
> sysids are gone, roles have Oids, so I don't think this is an issue
> anymore...  Users are gone too...
>
> > 450c450
> > < * Add COMMENT ON for all cluster global objects (users, groups, databases
> > > * Add COMMENT ON for all cluster global objects (users, roles, databases
>
> Users are gone, so this would just apply to roles..

Updated.

> > <       users and groups with separate DROP commands
> > >       users and roles with separate DROP commands
>
> users are gone, there are only roles, so I don't know that they'd need
> separate DROP commands, if that's what this means?  We do have DROP ROLE
> and REVOKE ROLE...

The problem is this used in pg_dumpall --clean:

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

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

* Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> The problem is this used in pg_dumpall --clean:
>
>     DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');

Oh, wow.  Hrmm.  That certainly won't work now.  It seems to me that the
correct thing to do here would be to loop through the roles and delete
them one-by-one, with cascade?  I think that would clean things out
correctly.  Alternatively I suppose you could delete from pg_authid and
pg_auth_members..  That seems a little ugly to me.

    Thanks,

        Stephen

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> The problem is this used in pg_dumpall --clean:

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

Hmm.  I haven't gotten around to looking at pg_dumpall issues, but we
might have to add an ON DELETE rule to the pg_shadow view to allow old
dump files to continue working.

IIRC, there are *really* old dump files that try to do COPY TO
pg_shadow, but I'm assuming we don't care about supporting stuff
that far back.

            regards, tom lane