Re: 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.

--
Richard Huxton
Archonet Ltd