Thread: altering objects owned by other user

I want to allow a non-superuser to alter objects owned by another user.
This should be an audited operation (logging a notice of what was done
to the postgres logs is sufficient).

This is so that I can allow trusted users to perform maintenance
operations without having to give them either superuser privilege, or
the password for the object owner account.  This should allow us to
satisfy an outside auditor that no-one outside of the sysadmin group has
unrestricted (ie unaudited) superuser access.

I had hoped to implement this using set session authorization within a
security-definer plpgsql function but security-definer is inadequate for
passing on superuser status.

Does anyone have any suggestions?

My current thinking is to implement a C language function which is only
accessible to my trusted users.  This would simply call
SetSessionAuthorization with the is_superuser argument set to true.  Is
this a horrible idea?

Thanks.

__
Marc

Marc Munro <marc@bloodnok.com> writes:
> I want to allow a non-superuser to alter objects owned by another user.

Use 8.1, have the objects in question be owned by a group (role), grant
membership in the group as appropriate.

> This should be an audited operation (logging a notice of what was done
> to the postgres logs is sufficient).

Perhaps log_statement = ddl?

            regards, tom lane

Tom,
Thanks.  Good suggestions, both.  I'm going to defer this problem until
we are able to upgrade.

__
Marc

On Mon, 2006-02-13 at 14:18 -0500, Tom Lane wrote:
> Marc Munro <marc@bloodnok.com> writes:
> > I want to allow a non-superuser to alter objects owned by another user.
>
> Use 8.1, have the objects in question be owned by a group (role), grant
> membership in the group as appropriate.
>
> > This should be an audited operation (logging a notice of what was done
> > to the postgres logs is sufficient).
>
> Perhaps log_statement = ddl?
>
>             regards, tom lane