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