Thread: STABLE functions
Hi All,
May be someone can help me with the following problem:
1. I need to extend 'featurs' of database user account.
2. I did that by creating a table:
CREATE TABLE users (username text, -- key matching 'current_user'
freaturs text -- thing I need
);
3. I allow acces to that table through VIEWs:
CREATE VIEW my_users AS SELECT * FROM users WHERE ....
4. one of the 'featurs' I need is a 'controlled' addition/deletion of
database uses - by 'controlled' I mean "with a little help from stored
procedures"
5. one of the procedures is:
CREATE FUNCTION kill(text) RETURNS boolean as $$ SET ROLE
MASTER_OF_THE_UNIVERSE; EXECUTE 'DROP USER ' || quote_ident($1); reset
role; return true; END $$ LANGUAGE plpgsql STABLE;
6. which is used within:
CREATE RULE goaway AS ON DELETE TO my_users DO INSTEAD DELETE .... WHEN
kill(old.username) = true AND ....;
Now, the problem is:
----------------an attempt execution-------------
ERROR: SET is not allowed in a non-volatile function
CONTEXT: SQL statement "set role MASTER_OF_THE_UNIVERSE"
PL/pgSQL function kill line 1 at execute statement
-----------------------------------------------
I've defined the function as STABLE, since it's *meant* to be called
just once-per-statement (depends just on it's argument, not on any data
within AND clause that follows not on any other actions happening within
the statement).
In other words, I wouldn't like this function to be called again and
again (as with NONE-STABLE functions), for every row to be deleted. Just
once per statement to retrieve the value it would have for this
statement, which depends solely on "old.username" which is supposed to
remain stable throuout the statement.
So I fell into 'semantical ambiquity case':
1. I used the STABLE keyword to tell executor to evaluate the function
just once per statement.
2. while it looks, that the STABLE keyword is there, to tell the
executor, that 'whatever it does' - the outcome of the function remains
the same within a statement.
Which is not exactly the same meaning. Any one knows which one is
'according to standard'?
But. Is there any implementable solution to my design?
BTW: signifficant part of my stored procedures code is there for the
sole reason, that manipulation of USERS (addition/deletion/etc) is not
accessible for a member of priviledged groups.... until the role is set
explicitly (as in the case of KILL function above). May be there is a
configuration switch to change this default?
BTW-2: My design would be much easier if only I counld:
CREATE TABLE users(
id int references pg_authid(oid) on delete cascade,
.....
);
Which I cannot, apparently. Are there technical reasons for this
restriction?
--
-R
Rafal Pietrak <rafal@zorro.isa-geek.com> writes:
> 5. one of the procedures is:
> CREATE FUNCTION kill(text) RETURNS boolean as $$ SET ROLE
> MASTER_OF_THE_UNIVERSE; EXECUTE 'DROP USER ' || quote_ident($1); reset
> role; return true; END $$ LANGUAGE plpgsql STABLE;
You should use the SECURITY DEFINER property instead of explicitly
fooling with role settings. It's easy to think of cases where that
RESET will select the *wrong* setting leading to a security hole.
Also, if it has side effects, it IS NOT STABLE. Period. So you can't
mark anything doing DROP USER as stable.
> 1. I used the STABLE keyword to tell executor to evaluate the function
> just once per statement.
Wrong. STABLE is not a directive to the system, it is a promise about
the behavior of your function ... and you're trying to break the
promise.
Your best bet for this is probably to put the DROP in an AFTER DELETE
trigger on the my_users table, instead of trying to use a rule.
> BTW-2: My design would be much easier if only I counld:
> CREATE TABLE users(
> id int references pg_authid(oid) on delete cascade,
> .....
> );
> Which I cannot, apparently. Are there technical reasons for this
> restriction?
We don't support triggers on system catalogs.
regards, tom lane
On Wed, 2006-10-11 at 10:30 -0400, Tom Lane wrote: > > 1. I used the STABLE keyword to tell executor to evaluate the function > > just once per statement. > > Wrong. STABLE is not a directive to the system, it is a promise about > the behavior of your function ... and you're trying to break the > promise. Ok. so it's semantics version.2. > Your best bet for this is probably to put the DROP in an AFTER DELETE > trigger on the my_users table, instead of trying to use a rule. :) yes! Thenx Tom. The solution was right in front of my eyes and I couldn't have seen it!! > > BTW-2: My design would be much easier if only I counld: > > CREATE TABLE users( > > id int references pg_authid(oid) on delete cascade, > > ..... > > ); > > Which I cannot, apparently. Are there technical reasons for this > > restriction? > > We don't support triggers on system catalogs. Apparently there is also no way to "foreign key reference" those, even without installing any on-delete actions/triggers. Pity. Are there any plans for anything like that (referencing keys in system tables from public schemas)? Or may be inharitance of system tables in public schemas? (So one could have extentions?) -- -R
On Wed, Oct 11, 2006 at 08:56:23PM +0200, Rafal Pietrak wrote: > Apparently there is also no way to "foreign key reference" those, even > without installing any on-delete actions/triggers. > > Pity. > > Are there any plans for anything like that (referencing keys in system > tables from public schemas)? Not really. The primary objection is that every lookup in the backend on the system catalogs (of which there are many) would have to be modified to check for triggers. This would be bad for performence against a handful of cases where it would be useful. What may have a better chance is assigning triggers to commands (like ON CREATE USER) which trigger on specific situations. No-one serious considered implementing this though, at it's unclear what the use-case would be anyway... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On Thu, 2006-10-12 at 11:47 +0200, Martijn van Oosterhout wrote: > What may have a better chance is assigning triggers to commands (like ON > CREATE USER) which trigger on specific situations. No-one serious > considered implementing this though, at it's unclear what the use-case > would be anyway... I see. From MHO, the use-case would come from the same need, that made me rise the questions. Namely: need to extend 'user' definition by application speciffic 'things'. My particular need was to have the ability to block the account until its password is changed. Which I believe might (at certain point) come into the main sources. But other applicatoins may have unforseen requirements - It would be desirable to allow for those, event to see just what those applications actually need. Still. It certeinly does not justyfy entering performance panelties for every Postgres DB aplication out there. And yet, may be there are workarounds? Like allowing application extentions on session SET-able variables? With troggers on update? -- -R
On Thu, Oct 12, 2006 at 12:38:08PM +0200, Rafal Pietrak wrote: > My particular need was to have the ability to block the account until > its password is changed. Which I believe might (at certain point) come > into the main sources. But other applicatoins may have unforseen > requirements - It would be desirable to allow for those, event to see > just what those applications actually need. <snip> > And yet, may be there are workarounds? Like allowing application > extentions on session SET-able variables? With troggers on update? One workaround is: since you can do the create user inside a function, write functions to do what you want and then only use them to manipulate users. It requires a bit of discipline, but it's doable. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Wed, Oct 11, 2006 at 08:56:23PM +0200, Rafal Pietrak wrote:
>> Are there any plans for anything like that (referencing keys in system
>> tables from public schemas)?
> Not really. The primary objection is that every lookup in the backend
> on the system catalogs (of which there are many) would have to be
> modified to check for triggers. This would be bad for performence
> against a handful of cases where it would be useful.
There are also some interesting issues of circularity --- eg, you will
never be able to have triggers on pg_proc or pg_trigger, because that
would create an infinite recursion in relcache load. Not to mention
risks such as a broken trigger on pg_trigger keeping you from removing
it...
It's possible that we could support AFTER triggers on certain non-core
catalogs. I can only recall people ever asking for this feature in
connection with the user/group catalogs, so covering those might be
enough in practice; that'd certainly be lots less invasive than trying
to make it work everywhere.
regards, tom lane
On Thu, Oct 12, 2006 at 10:34:30AM -0400, Tom Lane wrote:
> catalogs. I can only recall people ever asking for this feature in
> connection with the user/group catalogs, so covering those might be
> enough in practice; that'd certainly be lots less invasive than trying
> to make it work everywhere.
The Slony-I project has heard a lot of agitating for automatic
support of DDL. As near as I can tell, that either requires triggers
on system catalogs or else triggers on statements like CREATE TABLE,
ALTER TABLE, &c.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton