On Friday, February 1, 2019, rob stone <
floriparob@gmail.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.