Thread: has_schema_privilege function
Hi, I have created below function. I am checking return value of has_schema_privilege by using flag="f". I think this is not strong way to make a check because if in future "f" becomes "false" my stored procedure will work improper.
Could you tell me is there any other robust way to make sure that user1 doesn't have CREATE permissions on mydb schema?
CREATE OR REPLACE FUNCTION schema_perm_test()
RETURNS void AS
$BODY$
$rv = spi_exec_query("SELECT has_schema_privilege('user1', 'mydb', 'CREATE') AS flag;");
if(lc($rv->{rows}->[0]->{flag}) eq "f") {
# Do tasks
}
$BODY$
LANGUAGE 'plperl' VOLATILE SECURITY DEFINER
RETURNS void AS
$BODY$
$rv = spi_exec_query("SELECT has_schema_privilege('user1', 'mydb', 'CREATE') AS flag;");
if(lc($rv->{rows}->[0]->{flag}) eq "f") {
# Do tasks
}
$BODY$
LANGUAGE 'plperl' VOLATILE SECURITY DEFINER
Thanks.
On Tue, Mar 9, 2010 at 10:28 AM, Jignesh Shah <jignesh.shah1980@gmail.com> wrote: > Could you tell me is there any other robust way to make sure that user1 > doesn't have CREATE permissions on mydb schema? It depends what you're worried about. If you're worried that plperl will begin mapping booleans to perl variables differently or Postgres will change the text representation then you could alter the SQL to say something like CASE WHEN has_schema_privilege() THEN 1 ELSE 0 END or whatever constant you prefer like 'yes' and 'no' or 'ok' and ''. -- greg