launchpad-reviewers team mailing list archive
-
launchpad-reviewers team
-
Mailing list archive
-
Message #28810
[Merge] ~cjwatson/launchpad:db-name-blocklist into launchpad:db-devel
Colin Watson has proposed merging ~cjwatson/launchpad:db-name-blocklist into launchpad:db-devel.
Commit message:
Rename NameBlacklist table to NameBlocklist
Requested reviews:
William Grant (wgrant): db
Launchpad code reviewers (launchpad-reviewers): db
For more details, see:
https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/426843
I've left behind aliases (in the form of a view that meets the conditions in https://www.postgresql.org/docs/10/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS and a couple of simple functions) to keep the old names working until they're no longer referenced in code. I've also only changed the table names in sampledata and not the data itself for now, for the same reason.
The replaced functions are just the result of replacing "blacklist" with "blocklist" in the function bodies.
--
Your team Launchpad code reviewers is requested to review the proposed merge of ~cjwatson/launchpad:db-name-blocklist into launchpad:db-devel.
diff --git a/database/sampledata/current-dev.sql b/database/sampledata/current-dev.sql
index ae1f035..77df7a4 100644
--- a/database/sampledata/current-dev.sql
+++ b/database/sampledata/current-dev.sql
@@ -5420,13 +5420,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 a637af4..0ef39cc 100644
--- a/database/sampledata/current.sql
+++ b/database/sampledata/current.sql
@@ -5334,13 +5334,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 463936b..52f890c 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-2210-49-0.sql b/database/schema/patch-2210-49-0.sql
new file mode 100644
index 0000000..03dcbcf
--- /dev/null
+++ b/database/schema/patch-2210-49-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 (2210, 49, 0);
diff --git a/database/schema/security.cfg b/database/schema/security.cfg
index 56eb404..63cd65e 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
@@ -251,6 +253,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
@@ -2389,6 +2392,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
