FK Constraint with ON DELETE SET DEFAULT cascading as table owner

On Friday, February 1, 2019, rob stone <floriparob(at)gmail(dot)com> wrote:

> Hello,
>
> On Fri, 2019-02-01 at 14:01 +0000, Brad Leupen wrote:
> > CREATE TABLE foo (
> > tenant TEXT REFERENCES tenant NOT NULL DEFAULT user,
>
> > This script winds up failing because the “user” default value on
> > foo.tenant evaluates to the table owner, not the active user role of
> > “tenant1”. Is this the expected behavior?

> If you don't specify a "default" it uses NULL.
> There is no tenant.id that is NULL.
> So, the foreign key validation fails.
>
> AFAICT, it is working as intended.
>

It working as intended but your analysis is wrong (for one, there is a
default).

Cascading triggers use the role of the table owner instead of the statement
executing role to handle various authorization configurations. You may not
be able leverage on delete/update here given your requirement.

David J.