Merge lp:~benji/launchpad/bug-877195-db-3 into lp:launchpad

[Benji York]

add some security declarations for template statistics jobs

[Robert Collins (lifeless)]

Heh, actually this doens't need DB review per the DB review process - this is just a normal code review.

[Robert Collins (lifeless)]

review: Approve

[Stuart Bishop (stub)]

yer

review: Approve

[Benji York (benji)]

Robert Collins wrote:
> Heh, actually this doens't need DB review per the DB review process -
> this is just a normal code review.

My mistake. Retargeting to devel.

[Benji York (benji)]

Well, since this was a branch of db-devel, that (retargeting won't work). I'll make a new branch with this content and self-approve with a comment pointing here.

[Stuart Bishop (stub)]

Normally this should land on db-devel. If you land it on devel, technically you would need someone to manually apply the permissions to production before landing. Otherwise, code landings may depend on the new permissions that have not been applied to the production db yet.

Unmerged revisions

11171. By Benji York on 2011-11-25

add some security declarations for template statistics jobs

=== modified file 'database/replication/helpers.py'
--- database/replication/helpers.py	2011-10-13 03:52:48 +0000
+++ database/replication/helpers.py	2011-11-28 15:43:50 +0000
@@ -44,7 +44,6 @@
     ('public', 'openidnonce'),
     ('public', 'openidassociation'),
     ('public', 'person'),
-    ('public', 'launchpaddatabaserevision'),
     ('public', 'databasereplicationlag'),
     ('public', 'fticache'),
     ('public', 'nameblacklist'),
@@ -63,6 +62,16 @@
     # suggestivepotemplate.potemplate foreign key constraint exists on
     # production.
     ('public', 'suggestivepotemplate'),
+    # These are odd. They are updated via slonik & EXECUTE SCRIPT, and
+    # the contents of these tables will be different on each node
+    # because we store timestamps when the patches were applied.
+    # However, we want the tables listed as replicated so that, when
+    # building a new replica, the data that documents the schema patch
+    # level matches the schema patch level and upgrade.py does the right
+    # thing. This is a bad thing to do, but we are safe in this
+    # particular case.
+    ('public', 'launchpaddatabaserevision'),
+    ('public', 'launchpaddatabaseupdatelog'),
     ])
 
 # Explicitly list tables that should not be replicated. This includes the
 
=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2011-11-16 01:19:54 +0000
+++ database/schema/comments.sql	2011-11-28 15:43:50 +0000
@@ -1223,6 +1223,9 @@
 COMMENT ON COLUMN LaunchpadDatabaseRevision.minor IS 'Minor number. Patches made during development each increment the minor number.';
 COMMENT ON COLUMN LaunchpadDatabaseRevision.patch IS 'The patch number will hopefully always be ''0'', as it exists to support emergency patches made to the production server. eg. If production is running ''4.0.0'' and needs to have a patch applied ASAP, we would create a ''4.0.1'' patch and roll it out. We then may need to refactor all the existing ''4.x.0'' patches.';
 
+-- LaunchpadDatabaseUpdateLog
+COMMENT ON TABLE LaunchpadDatabaseUpdateLog IS 'Record of Launchpad database schema updates. When and what update.py was run.';
+
 -- Karma
 COMMENT ON TABLE Karma IS 'Used to quantify all the ''operations'' a user performs inside the system, which maybe reporting and fixing bugs, uploading packages, end-user support, wiki editting, etc.';
 COMMENT ON COLUMN Karma.action IS 'A foreign key to the KarmaAction table.';
 
=== added file 'database/schema/patch-2208-01-3.sql'
--- database/schema/patch-2208-01-3.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2208-01-3.sql	2011-11-28 15:43:50 +0000
@@ -0,0 +1,17 @@
+SET client_min_messages=ERROR;
+
+ALTER TABLE LaunchpadDatabaseRevision
+    ADD branch_nick text,
+    ADD revno integer,
+    ADD revid text;
+
+CREATE TABLE LaunchpadDatabaseUpdateLog (
+    id serial primary key,
+    start_time timestamp without time zone NOT NULL DEFAULT (
+        CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
+    end_time timestamp without time zone,
+    branch_nick text,
+    revno integer,
+    revid text);
+   
+INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 01, 3);
 
=== added file 'database/schema/patch-2208-93-2.sql'
--- database/schema/patch-2208-93-2.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2208-93-2.sql	2011-11-28 15:43:50 +0000
@@ -0,0 +1,13 @@
+-- Copyright 2011 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 AccessPolicyGrant ADD CONSTRAINT
+    "accesspolicygrant_grantee_fkey" FOREIGN KEY (grantee) REFERENCES Person;
+ALTER TABLE AccessPolicyGrant ADD CONSTRAINT
+    "accesspolicygrant_grantor_fkey" FOREIGN KEY (grantor) REFERENCES Person;
+
+ALTER TABLE AccessPolicyArtifact ADD CONSTRAINT
+    "accesspolicyartifact_branch_fkey" FOREIGN KEY (branch) REFERENCES Branch;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 93, 2);
 
=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2011-11-22 14:12:40 +0000
+++ database/schema/security.cfg	2011-11-28 15:43:50 +0000
@@ -445,6 +445,9 @@
 public.job                              = SELECT, UPDATE, DELETE
 public.pofilestatsjob                   = SELECT, UPDATE, DELETE
 public.potmsgset                        = SELECT
+public.distroseries                     = SELECT
+public.distribution                     = SELECT
+public.sourcepackagename                = SELECT
 public.translationmessage               = SELECT
 public.translationtemplateitem          = SELECT
 type=user
 
=== modified file 'lib/lp/code/doc/branch.txt'
--- lib/lp/code/doc/branch.txt	2011-05-27 19:53:20 +0000
+++ lib/lp/code/doc/branch.txt	2011-11-28 15:43:50 +0000
@@ -389,6 +389,7 @@
     ...     ])
     >>> for name in listing:
     ...     print name
+    accesspolicyartifact.branch
     branch.stacked_on
     branchjob.branch
     branchmergeproposal.dependent_branch