Re: ADD FOREIGN KEY (was Re: [GENERAL] 7.4Beta)

Stephan Szabo wrote:
> On Tue, 30 Sep 2003, Bruce Momjian wrote:
> 
> > Stephan Szabo wrote:
> > > > If we go that direction, why don't we just make a GUC variable to
> > > > disable constraint checking.  Is that what this will do, or is it more
> > > > limited.  I know it breaks referential integrity, but we have had many
> > > > folks as for it, it is on the TODO list, and there are tons of server
> > > > functions flying around that do just this by fiddling with pg_class.  I
> > > > would rather just have it be a GUC for that particular backend.  People
> > > > are going to need to turn it off anyway, so why not give them a clean
> > > > way to do it.
> > >
> > > But such a GUC wouldn't affect just one backend.  It'd potentially affect
> > > all backends that were doing concurrent modifications that would be
> > > involved since the locks aren't taken.  In addition, who would be allowed
> > > to set this value and what constraints would it affect? If it's only
> > > superusers, then it doesn't help for non-superuser restores.  If it's
> > > settable by anyone and affects only constraints on tables that user owns
> > > and that refer to tables that user owns it might be okay.  If it's
> > > settable by anyone and affects all tables it renders the constraints
> > > meaningless since anyone could break them.
> >
> > I assume it would be only setable by the super-user.  They are mucking
> > around with pg_class anyway (and have permission to do so), so let them
> > do it cleanly at least.  Allowing non-supers to do it for tables they
> > own would be OK, I guess.  Is there a problem if some of the primary
> > table is owned by someone else?  Not sure.
> 
> The problem I have with a super-user only solution is that it doesn't
> solve the problem for restores in general.  I think we need a mechanism
> that works for any user that wants to restore a table (or tables) from
> dump(s), so for the dump/restore mechanism I think we should be looking in
> that direction.

OK.  Let's explore that.  What does ownership mean?  If I grant all
permissions on an object I own to you, what can you not do?  I think
GRANT/REVOKE and ALTER TABLE are the only two ones, right?

So, if I own it, I am the only one who can ALTER the table to add/remove
the foreign key constraint.  So, if I already have a foreign key
constraint on a table, I can easily remove it if I am the owner and do
whatever I want with the table.

Now, the big question is, is there harm in my saying in the system
catalogs that I have a foreign key constraint on a table, when I might
have turned off the constraint via GUC and modified the table so the
foreign key constraint isn't valid?  I think that is the big question
--- is there harm to others in saying something I own has a foreign key,
when it might not?

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073