launchpad-reviewers team mailing list archive
-
launchpad-reviewers team
-
Mailing list archive
-
Message #29425
[Merge] ~cjwatson/launchpad:merge-db-stable into launchpad:master
Colin Watson has proposed merging ~cjwatson/launchpad:merge-db-stable into launchpad:master.
Commit message:
Merge db-stable 4ab4dc9a08 (Rename NameBlacklist table to NameBlocklist)
Requested reviews:
Launchpad code reviewers (launchpad-reviewers)
For more details, see:
https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/433580
--
Your team Launchpad code reviewers is requested to review the proposed merge of ~cjwatson/launchpad:merge-db-stable into launchpad:master.
diff --git a/database/sampledata/current-dev.sql b/database/sampledata/current-dev.sql
index 1a12d10..43507f2 100644
--- a/database/sampledata/current-dev.sql
+++ b/database/sampledata/current-dev.sql
@@ -5427,13 +5427,13 @@ INSERT INTO public.mirrorproberecord (id, distribution_mirror, log_file, date_cr
ALTER TABLE public.mirrorproberecord ENABLE TRIGGER ALL;
-ALTER TABLE public.nameblacklist DISABLE TRIGGER ALL;
+ALTER TABLE public.nameblocklist DISABLE TRIGGER ALL;
-INSERT INTO public.nameblacklist (id, regexp, comment, admin) VALUES (1, 'blacklist', 'For testing purposes', NULL);
-INSERT INTO public.nameblacklist (id, regexp, comment, admin) VALUES (2, '^admin', NULL, NULL);
+INSERT INTO public.nameblocklist (id, regexp, comment, admin) VALUES (1, 'blacklist', 'For testing purposes', NULL);
+INSERT INTO public.nameblocklist (id, regexp, comment, admin) VALUES (2, '^admin', NULL, NULL);
-ALTER TABLE public.nameblacklist ENABLE TRIGGER ALL;
+ALTER TABLE public.nameblocklist ENABLE TRIGGER ALL;
ALTER TABLE public.oauthconsumer DISABLE TRIGGER ALL;
diff --git a/database/sampledata/current.sql b/database/sampledata/current.sql
index f1c680d..bf2b999 100644
--- a/database/sampledata/current.sql
+++ b/database/sampledata/current.sql
@@ -5341,13 +5341,13 @@ INSERT INTO public.mirrorproberecord (id, distribution_mirror, log_file, date_cr
ALTER TABLE public.mirrorproberecord ENABLE TRIGGER ALL;
-ALTER TABLE public.nameblacklist DISABLE TRIGGER ALL;
+ALTER TABLE public.nameblocklist DISABLE TRIGGER ALL;
-INSERT INTO public.nameblacklist (id, regexp, comment, admin) VALUES (1, 'blacklist', 'For testing purposes', NULL);
-INSERT INTO public.nameblacklist (id, regexp, comment, admin) VALUES (2, '^admin', NULL, NULL);
+INSERT INTO public.nameblocklist (id, regexp, comment, admin) VALUES (1, 'blacklist', 'For testing purposes', NULL);
+INSERT INTO public.nameblocklist (id, regexp, comment, admin) VALUES (2, '^admin', NULL, NULL);
-ALTER TABLE public.nameblacklist ENABLE TRIGGER ALL;
+ALTER TABLE public.nameblocklist ENABLE TRIGGER ALL;
ALTER TABLE public.oauthconsumer DISABLE TRIGGER ALL;
diff --git a/database/schema/comments.sql b/database/schema/comments.sql
index fb8dfcc..9c5a162 100644
--- a/database/schema/comments.sql
+++ b/database/schema/comments.sql
@@ -2022,11 +2022,11 @@ COMMENT ON COLUMN POFileTranslator.pofile IS 'The pofile the translation was sub
COMMENT ON COLUMN POFileTranslator.date_last_touched IS 'When was added latest
translation message.';
--- NameBlacklist
-COMMENT ON TABLE NameBlacklist IS 'A list of regular expressions used to blacklist names.';
-COMMENT ON COLUMN NameBlacklist.regexp IS 'A Python regular expression. It will be compiled with the IGNORECASE, UNICODE and VERBOSE flags. The Python search method will be used rather than match, so ^ markers should be used to indicate the start of a string.';
-COMMENT ON COLUMN NameBlacklist.comment IS 'An optional comment on why this regexp was entered. It should not be displayed to non-admins and its only purpose is documentation.';
-COMMENT ON COLUMN NameBlacklist.admin IS 'The person who can override the blacklisted name.';
+-- NameBlocklist
+COMMENT ON TABLE NameBlocklist IS 'A list of regular expressions used to block names.';
+COMMENT ON COLUMN NameBlocklist.regexp IS 'A Python regular expression. It will be compiled with the IGNORECASE, UNICODE and VERBOSE flags. The Python search method will be used rather than match, so ^ markers should be used to indicate the start of a string.';
+COMMENT ON COLUMN NameBlocklist.comment IS 'An optional comment on why this regexp was entered. It should not be displayed to non-admins and its only purpose is documentation.';
+COMMENT ON COLUMN NameBlocklist.admin IS 'The person who can override the blocked name.';
-- ScriptActivity
COMMENT ON TABLE ScriptActivity IS 'Records of successful runs of scripts ';
diff --git a/database/schema/patch-2211-01-0.sql b/database/schema/patch-2211-01-0.sql
new file mode 100644
index 0000000..c55e838
--- /dev/null
+++ b/database/schema/patch-2211-01-0.sql
@@ -0,0 +1,112 @@
+-- Copyright 2022 Canonical Ltd. This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+ALTER TABLE NameBlacklist RENAME TO NameBlocklist;
+COMMENT ON TABLE NameBlocklist IS 'A list of regular expressions used to block names.';
+COMMENT ON COLUMN NameBlocklist.admin IS 'The person who can override the blocked name.';
+
+ALTER SEQUENCE nameblacklist_id_seq RENAME TO nameblocklist_id_seq;
+ALTER INDEX nameblacklist_pkey RENAME TO nameblocklist_pkey;
+ALTER INDEX nameblacklist__regexp__key RENAME TO nameblocklist__regexp__key;
+
+CREATE OR REPLACE FUNCTION name_blocklist_match(text, integer) RETURNS integer
+ LANGUAGE plpython3u STABLE STRICT SECURITY DEFINER
+ SET search_path TO 'public'
+ AS $_$
+ import re
+ name = args[0]
+ user_id = args[1]
+
+ # Initialize shared storage, shared between invocations.
+ if "regexp_select_plan" not in SD:
+
+ # All the blocklist regexps except the ones we are an admin
+ # for. These we do not check since they are not blocklisted to us.
+ SD["regexp_select_plan"] = plpy.prepare("""
+ SELECT id, regexp FROM NameBlocklist
+ 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"] = {}
+
+ # admins is a celebrity and its id is immutable.
+ admins_id = plpy.execute(
+ "SELECT id FROM Person WHERE name='admins'")[0]["id"]
+
+ SD["admin_select_plan"] = plpy.prepare("""
+ SELECT TRUE FROM TeamParticipation
+ WHERE
+ TeamParticipation.team = %d
+ AND TeamParticipation.person = $1
+ LIMIT 1
+ """ % admins_id, ["integer"])
+
+ # All the blocklist regexps except those that have an admin because
+ # members of ~admin can use any name that any other admin can use.
+ SD["admin_regexp_select_plan"] = plpy.prepare("""
+ SELECT id, regexp FROM NameBlocklist
+ WHERE admin IS NULL
+ ORDER BY id
+ """, ["integer"])
+
+
+ compiled = SD["compiled"]
+
+ # Names are never blocklisted for Lauchpad admins.
+ if user_id is not None and plpy.execute(
+ SD["admin_select_plan"], [user_id]).nrows() > 0:
+ blocklist_plan = "admin_regexp_select_plan"
+ else:
+ blocklist_plan = "regexp_select_plan"
+
+ for row in plpy.execute(SD[blocklist_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.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
+$_$;
+
+COMMENT ON FUNCTION public.name_blocklist_match(text, integer) IS 'Return the id of the row in the NameBlocklist table that matches the given name, or NULL if no regexps in the NameBlocklist table match.';
+
+CREATE OR REPLACE FUNCTION is_blocklisted_name(text, integer) RETURNS boolean
+ LANGUAGE sql STABLE STRICT SECURITY DEFINER
+ SET search_path TO 'public'
+ AS $_$
+ SELECT COALESCE(name_blocklist_match($1, $2)::boolean, FALSE);
+$_$;
+
+COMMENT ON FUNCTION public.is_blocklisted_name(text, integer) IS 'Return TRUE if any regular expressions stored in the NameBlocklist table match the given name, otherwise return FALSE.';
+
+-- Temporary aliases for old names, needed until they're no longer
+-- referenced in code.
+
+CREATE VIEW NameBlacklist AS SELECT * FROM NameBlocklist;
+
+CREATE OR REPLACE FUNCTION name_blacklist_match(text, integer) RETURNS integer
+ LANGUAGE sql STABLE STRICT SECURITY DEFINER
+ SET search_path TO 'public'
+ AS $_$
+ SELECT name_blocklist_match($1, $2);
+$_$;
+
+CREATE OR REPLACE FUNCTION is_blacklisted_name(text, integer) RETURNS boolean
+ LANGUAGE sql STABLE STRICT SECURITY DEFINER
+ SET search_path TO 'public'
+ AS $_$
+ SELECT is_blocklisted_name($1, $2);
+$_$;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 01, 0);
diff --git a/database/schema/security.cfg b/database/schema/security.cfg
index a7f801b..d5578d7 100644
--- a/database/schema/security.cfg
+++ b/database/schema/security.cfg
@@ -48,6 +48,7 @@ public._ftq(text) = EXECUTE
public.getlocalnodeid() = EXECUTE
public.gitrepository_denorm_access(integer) = EXECUTE
public.is_blacklisted_name(text, integer) = EXECUTE
+public.is_blocklisted_name(text, integer) = EXECUTE
public.latestdatabasediskutilization =
public.launchpaddatabaserevision = SELECT
public.launchpaddatabaseupdatelog = SELECT
@@ -60,6 +61,7 @@ public.max(debversion) = EXECUTE
public.milestone_sort_key(timestamp without time zone, text) = EXECUTE
public.min(debversion) = EXECUTE
public.name_blacklist_match(text, integer) = EXECUTE
+public.name_blocklist_match(text, integer) = EXECUTE
public.null_count(anyarray) = EXECUTE
public.ocirecipe_denorm_access(integer) = EXECUTE
public.person_sort_key(text, text) = EXECUTE
@@ -252,6 +254,7 @@ public.mirrordistroarchseries = SELECT, INSERT, DELETE, UPDATE
public.mirrordistroseriessource = SELECT, INSERT, UPDATE, DELETE
public.mirrorproberecord = SELECT, INSERT, DELETE
public.nameblacklist = SELECT, INSERT, UPDATE, DELETE
+public.nameblocklist = SELECT, INSERT, UPDATE, DELETE
public.oauthaccesstoken = SELECT, INSERT, UPDATE, DELETE
public.oauthconsumer = SELECT, INSERT
public.oauthrequesttoken = SELECT, INSERT, UPDATE, DELETE
@@ -2392,6 +2395,7 @@ public.messageapproval = SELECT, UPDATE
public.milestone = SELECT, UPDATE
public.milestonetag = SELECT, INSERT, UPDATE, DELETE
public.nameblacklist = SELECT, UPDATE
+public.nameblocklist = SELECT, UPDATE
public.oauthaccesstoken = SELECT, UPDATE
public.oauthrequesttoken = SELECT, UPDATE
public.ociproject = SELECT, UPDATE
