> The problem here is not with pg_dump; the problem is that dropping
> privileges doesn't cascade to dropping objects that are dependent on
> those privileges. AFAICS the SQL spec requires us to be able to do
> the latter.
The spec really requires that?? So basically we have RESTRICT and
CASCADE on REVOKE?
That seems pretty odd to me. What's so wrong about allowing someone to
create tables for a while and then revoking their permission to do it
from now on??
That's exactly what we do for databases at the moment, we have an
'OWNER' clause. And that's how I coded tablespaces to be dumped as well.
Either way, our concept of a superuser surely isn't in the spec, so can
we at least fix that problem? ie. we dump lanugages as default
session_authorization and then ALTER LANGUAGE it to change it to the
correct user? Same for CREATE OPERATOR CLASS and ALTER OP CLASS, and
CREATE CAST commands for binary-compatible casts. (I do note that
neither of those ALTER forms allows changing owner and there is no ALTER
CAST at all - we'd need to add them).
> If we're gonna invest work on fixing this, we ought to do
> what the spec tells us to, not invent warts on the security model.
Sure. Let's be honest though and admit that there are a lot of broken
dumps out there at the moment. For me, I have to change all my users to
superusers before dumping, then change them all back after a restore.
This is because we did a security crackdown and tightened up on
everyone's privileges...
Chris