Re: Fixing broken permissions for deleted user

> -----Original Message-----
> From: Richard Huxton [mailto:dev@archonet.com]
> Sent: Wednesday, May 16, 2007 4:56 AM
> To: Justin Pasher
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Fixing broken permissions for deleted user
>
> Justin Pasher wrote:
> > I have a PostgreSQL 7.4.14 database that is being backed up nightly
> > using pg_dump. Some time back, we deleted a user from the server that
> > was no longer employed. This in turn caused some problems with ownership
> > of some of the tables (since the user didn't exist, the database could
> > only go by the user ID the the catalog, which made the dumped confused).
> > We are able to fix those tables relatively easily using ALTER TABLE
> > table_name OWNER TO new_owner.
> >
> > The problem we face are with the permissions on some tables. There are a
> > few tables that were originally created by this deleted user which  in
> > turn also granted some additional permissions to others. Here is an
> > example what what \dp shows now (hopefully word wrap is nice to me):
> >
> > |   Table    |                    Access privileges
> > +------------+----------------------------------------------------
> > | menu_items | {101=a*r*w*d*R*x*t*/101,justinp=a*r*w*d*R*x*t*/101}
> >
> > The deleted user had ID 101 (obviously).
>
> Something like:
>    CREATE USER temp SYSID 101
> Then fix ownership etc, then drop the user.
>

OK. After playing around with this extensively I FINALLY got the permissions
remove (from anything I can see).

|   Table    |                    Access privileges
+------------+----------------------------------------------------
| menu_items | {justinp=a*r*w*d*R*x*t*/justinp,"group
dbs_readwrite=arwd/justinp"}

The table owner is also a different user from user id 101. However, it still
gives me the same complaint.

pg_dump: WARNING: owner of data type "menu_items" appears to be invalid
pg_dump: WARNING: owner of data type "pg_toast_47831338" appears to be
invalid

I do notice the error says "owner of data type", so perhaps it is referring
to something else besides the table? I'm also trying to figure out an easy
way to find the other entity (pg_toast_47831338).

Justin Pasher