Re: domain access privilege

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
>> You can drop the table whether they want you to or not; the foreign key
>> constraint goes away by CASCADE.

> I tried this as the user owning the referenced table (using 7.2.1) and
> I was unable to do the drop.

Sorry, I was speaking of the 7.3 implementation. In current CVS:

regression=# \c - tgl
You are now connected as new user tgl.
cregression=> create table mytable(f1 int primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'mytable_pkey' for table 'mytable'
CREATE TABLE
regression=> \c - postgres
You are now connected as new user postgres.
regression=# create table bar (f1 int references mytable);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE TABLE
regression=# \c - tgl
You are now connected as new user tgl.
regression=> drop table mytable;
NOTICE: constraint $1 on table bar depends on table mytable
ERROR: Cannot drop table mytable because other objects depend on it
Use DROP ... CASCADE to drop the dependent objects too
regression=> drop table mytable cascade;
NOTICE: Drop cascades to constraint $1 on table bar
DROP TABLE

With debugging message level cranked up a little, it is possible to see
that the triggers implementing the constraint go away too:

regression=> drop table mytable cascade;
DEBUG: StartTransactionCommand
DEBUG: Drop auto-cascades to type mytable
NOTICE: Drop cascades to constraint $1 on table bar
DEBUG: Drop auto-cascades to trigger RI_ConstraintTrigger_290343 on table mytable
DEBUG: Drop auto-cascades to trigger RI_ConstraintTrigger_290341 on table mytable
DEBUG: Drop auto-cascades to trigger RI_ConstraintTrigger_290339 on table bar
DEBUG: Drop auto-cascades to constraint mytable_pkey on table mytable
DEBUG: Drop auto-cascades to index mytable_pkey
DEBUG: CommitTransactionCommand
DROP TABLE

regards, tom lane