"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Has anyone looked at this problem? I have delved into the source code, but
> I can't for the life of me see where to make the change. I think there are
> actually a few possible solutions:
> * Dump all foreign key constraints as a superuser
I don't like that solution --- pg_dump should not operate on the
assumption that it has access to a superuser account, at least not
when dumping single-owner databases.
> * Prevent changing ownership of tables that have foreign keys where the new
> owner does not have REFERENCE privs for all referenced tables.
> * Grant REFERENCE to new owner when changing ownership of table.
Neither of these would really prevent the problem AFAICS, since you
could easily create the same situation by revoking the REFERENCE priv
afterwards.
The generic problem is that you can get into states where references
exist that should not be allowed under the current privilege setup.
It doesn't only affect foreign keys, either --- consider for example
a view that references a table in another schema, and suppose USAGE
rights on that other schema are revoked from the view owner.
Probably the only real solution is to implement DROP-CASCADE-like
checking when a privilege is revoked. Seems like rather a lot of
work :-(
regards, tom lane