I propose small modification to your script. I remove the admin_select_plan since it gave unchecked power to admins. I added a guard for user_id to set the value to zero if it is NULL so that your revised regexp_select_plan always works.
CREATE OR REPLACE FUNCTION name_blacklist_match(text, integer) RETURNS int4
LANGUAGE plpythonu STABLE RETURNS NULL ON NULL INPUT
EXTERNAL SECURITY DEFINER SET search_path TO public AS
$$
import re
name = args[0].decode("UTF-8")
user_id = args[1]
if user_id is None:
# Ids cannot be NULL. Zero will force every repexp rule to apply.
user_id = 0
# Initialize shared storage, shared between invocations.
if not SD.has_key("regexp_select_plan"):
# All the blacklist regexps except the ones we are an admin
# for. These we do not check since they are not blacklisted to us. SD["regexp_select_plan"] = plpy.prepare("""
SELECT id, regexp FROM NameBlacklist
WHERE admin IS NULL OR admin NOT IN ( SELECT team FROM TeamParticipation WHERE person = $1)
ORDER BY id
""", ["integer"])
# Storage for compiled regexps SD["compiled"] = {}
compiled = SD["compiled"]
for row in plpy.execute(SD["regexp_select_plan"], [user_id]):
regexp_id = row["id"]
regexp_txt = row["regexp"]
if (compiled.get(regexp_id) is None
or compiled[regexp_id][0] != regexp_txt):
regexp = re.compile( regexp_txt, re.IGNORECASE | re.UNICODE | re.VERBOSE
) compiled[regexp_id] = (regexp_txt, regexp)
else:
regexp = compiled[regexp_id][1]
if regexp.search(name) is not None:
return regexp_id
return None
$$;
Hi Stuart.
I propose small modification to your script. I remove the admin_select_plan since it gave unchecked power to admins. I added a guard for user_id to set the value to zero if it is NULL so that your revised regexp_select_plan always works.
CREATE OR REPLACE FUNCTION name_blacklist_ match(text, integer) RETURNS int4 .decode( "UTF-8" )
LANGUAGE plpythonu STABLE RETURNS NULL ON NULL INPUT
EXTERNAL SECURITY DEFINER SET search_path TO public AS
$$
import re
name = args[0]
user_id = args[1]
if user_id is None:
# Ids cannot be NULL. Zero will force every repexp rule to apply.
user_id = 0
# Initialize shared storage, shared between invocations. key("regexp_ select_ plan"):
if not SD.has_
# All the blacklist regexps except the ones we are an admin
SD["regexp_ select_ plan"] = plpy.prepare("""
SELECT team FROM TeamParticipation
WHERE person = $1)
# for. These we do not check since they are not blacklisted to us.
SELECT id, regexp FROM NameBlacklist
WHERE admin IS NULL OR admin NOT IN (
ORDER BY id
""", ["integer"])
# Storage for compiled regexps
SD["compiled" ] = {}
compiled = SD["compiled"]
for row in plpy.execute( SD["regexp_ select_ plan"], [user_id]): get(regexp_ id) is None regexp_ id][0] != regexp_txt):
regexp_ txt, re.IGNORECASE | re.UNICODE | re.VERBOSE
compiled[ regexp_ id] = (regexp_txt, regexp) regexp_ id][1]
regexp_id = row["id"]
regexp_txt = row["regexp"]
if (compiled.
or compiled[
regexp = re.compile(
)
else:
regexp = compiled[
if regexp.search(name) is not None:
return regexp_id
return None
$$;