"Oliver Elphick" <olly@lfix.co.uk> writes:
> Restoring a full dump from 6.4.2 into 6.5:
> When the owner of a database does not have usesuper privilege in pg_shadow,
> it is not possible to recreate items that were created with superuser
> privilege, even if the superuser is running the script:
> QUERY: CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS
> '/usr/lib/postgresql/lib/plpgsql.so' LANGUAGE 'C';
> ERROR: Only users with Postgres superuser privilege are permitted to create
> a function in the 'C' language. Others may use the 'sql' language or the
> created procedural languages.
Hmm. This seems wrong; if the function was created by the superuser
then it should have proowner set to the superuser, and pg_dump looks
like it does the right thing about reconnecting as the function owner
(assuming you used -z, which is now default but wasn't in 6.4.2...).
Is it possible that "plpgsql_call_handler" was somehow marked as being
owned by the database owner rather than the superuser? If so, I'd think
that that is the real bug.
> It would seem that there should be some command that operates with
> superuser privilege, whatever the nominal state indicated by the data.
I'd be worried about security holes if it's not designed very carefully...
> Since the above script was being run by postgres, it should all have
> been capable of being executed.
I wonder whether we need a notion of "effective" and "real" user ID,
such as most Unix systems have. Then it'd be possible for the system
to know "I may be creating objects on behalf of user X, but I really
am the superuser" and apply protection checks appropriately. This'd
be a much more elegant solution than \connect for pg_dump scripts,
since the whole script would run in a single superuser session and just
do a SET VARIABLE or something to indicate which user would be the owner
of created objects.
However, that's not going to happen for 6.5. For a short-term fix, we
need to look at why pg_dump didn't reconnect as superuser before trying
to create that C function.
regards, tom lane