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

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > My idea is to create a new SECURITY DEFINER function called
> > serial_nextval(), and use that for SERIAL defaults.
>
> You haven't thought about this at all. Who will own that function?
> Surely we don't want to create a new one for every SERIAL column.
> And even if we did, what magic will cause its ownership to change
> when the table's owner is changed?

It would have to be a function that somehow grabbed the table owner and
internally did the permission checks based on that, but since CHECK
needs something similar, I think AS OWNER is probably best. Does that
solve all the SERIAL "black box" problems? TODO shows these SERIAL
issues:

* %Disallow changing default expression of a SERIAL column?
* %Disallow ALTER SEQUENCE changes for SERIAL sequences because pg_dump
does not dump the changes
* %Have ALTER TABLE RENAME rename SERIAL sequence names

> I'm leaning towards the idea that we need special syntax, along the
> lines of
> DEFAULT nextval('some_seq') AS OWNER
> which would result in generating a special expression node type at
> the time the DEFAULT expression is inserted into a query plan (and
> no earlier). At runtime this node would temporarily switch
> current_user, just as we do for SECURITY_DEFINER functions --- but by
> postponing the determination of which user to switch to until the plan
> is built, we avoid trouble with ALTER TABLE OWNER.
>
> Per Bruno's earlier comments, we probably need the same feature for
> table CHECK constraints. Might be interesting to think about it for
> domain check constraints too, though that's getting a bit far afield
> unless someone has a convincing use-case.

Added to TODO:

* Add DEFAULT .. AS OWNER so permission checks are done as the table
owner

This would be useful for SERIAL nextval() calls and CHECK constraints.

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +