Thread: domain access privilege

I didn't see anything in the 7.3 docs on what privilege will be needed
to use a domain. Is there going to be a way to control references
to domains?

Bruno Wolff III <bruno@wolff.to> writes:
> I didn't see anything in the 7.3 docs on what privilege will be needed
> to use a domain.

It's documented, but I think only on the GRANT reference page at
present:
http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-grant.html

            regards, tom lane

On Sat, Jul 20, 2002 at 11:59:07 -0400,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> > I didn't see anything in the 7.3 docs on what privilege will be needed
> > to use a domain.
>
> It's documented, but I think only on the GRANT reference page at
> present:
> http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-grant.html
>
>             regards, tom lane

There isn't much. "domain" is only referred to in the compatibility section.
It says "usage" is the standard keyword used for granting permission
to use domains. It doesn't indicate that this will be something usable
in 7.3. I haven't downloaded the 7.3 code, so I can't tell if this is
just a case of the development documentation not having been finished or
if there isn't going to be a way to do it.

Its not a big deal keeping domains secret. But if anyone can reference your
domain, it looks like they can keep you from dropping it. (Assuming it
works like the references privilege currently does in 7.2.1.) This might
be a pain in the rear in some cases. (The admin can always help you out
though.)

There can be a similar problem if you temporarily grant someone references
to a table to do something and they either create other references you
don't want or they refuse to drop the reference later to allow you to
drop the table. Revoke of references doesn't affect references that have
already been made (which can be good for some purposes). I don't think there
is a better solution to this than appealing to the local superuser, since
letting someone drop somebody else's tables unexpectedly is a worse solution
and there isn't a lot of other options.

Bruno Wolff III <bruno@wolff.to> writes:
> There isn't much. "domain" is only referred to in the compatibility section.
> It says "usage" is the standard keyword used for granting permission
> to use domains.

Oh, duh.  I read your question as asking about schemas, not domains.
Sorry.

Domains are types and there are no access permissions for types (except
for the rather indirect approach of disallowing USAGE on the schema
containing the type name, which will not help you to revoke existing
usages of a type...).  It seems to me that access permissions for the
associated functions are equivalent if not superior.  What would it mean
to, say, revoke usage permission on a type?  If someone else already has
a table that has a column of that type, what happens?  What about
functions or domains that reference the type?

> Its not a big deal keeping domains secret. But if anyone can reference your
> domain, it looks like they can keep you from dropping it.

We have DROP CASCADE now, and assuming that Chris gets DROP COLUMN done,
this is not an issue.  I suspect that the above complaint is a red
herring anyway; the average user is probably going to be much more
unhappy about the reverse direction of denial of service, namely
"I'll lose my data if the type owner drops the type of my column!!
How can I prevent him from doing that?"

> There can be a similar problem if you temporarily grant someone references
> to a table to do something and they either create other references you
> don't want or they refuse to drop the reference later to allow you to
> drop the table.

You can drop the table whether they want you to or not; the foreign key
constraint goes away by CASCADE.

            regards, tom lane

On Sat, Jul 20, 2002 at 14:06:30 -0400,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
>
> > There can be a similar problem if you temporarily grant someone references
> > to a table to do something and they either create other references you
> > don't want or they refuse to drop the reference later to allow you to
> > drop the table.
>
> 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. When I did it as a superuser the referencing
constraint was dropped and then the table.

The message was:
NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "test1"
ERROR:  test1: Must be table owner.

Normally you aren't going to give someone references access unless you trust
them, so it isn't a big deal if you need to cooperate with them to drop
a table they are referencing.

I guess using a restricted schema is reasonable for domains you don't want
everyone to use. I can't think of a reason you would want people to see it,
but not be able to use it.

Bruno Wolff III <bruno@wolff.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