Re: Is a SERIAL column a "black box", or not?

Bruno Wolff III <bruno@wolff.to> writes:
> I suggested a long time ago that default expressions should always be
> executed as the owner of the table. This got shot down, but I don't remember
> if it was because people thought the idea was bad in itself or if it was
> the work involved (which I wasn't in a position to do).

The more I think about it the better I like that idea.  It seems like a
natural and unsurprising semantics, whereas ideas involving implicit
GRANTs seem to me to violate the principle of least surprise.  It fixes
the problem for both serial and handmade sequences --- indeed, it fixes
related problems for functions other than nextval().  And it doesn't
require introduction of any new syntax.

One argument against it is that it'd break trying to log who-did-what
by the expedient of having a column default CURRENT_USER:blame_me text default current_user
You could still make use of session_user for this, but that's not really
the right thing if the INSERT is being done from a security-definer
function.  I don't find this objection very compelling, because such a
default is pretty fragile anyway: it could be broken just by assigning
explicitly to the column.  You'd be better off doing the logging by
having a BEFORE trigger that sets the column value.  However, I suspect
that the SQL spec demands that such a default behave as it currently
does, which means that changing this would violate spec.

A cheesy compromise would be to switch userid for default-evaluation
only if the expression contains any volatile functions.  I find this
idea pretty ugly, but it would allow us to still behave per-spec
for CURRENT_USER while getting the results we want for nextval().
(current_user() is marked "stable".)
        regards, tom lane