Merge lp:~stub/launchpad/pending-db-changes into lp:launchpad/db-devel

[Stuart Bishop]

The authdb replication set has been collapsed into the lpmain replication set on production, so make the staging environment match. Also, fix new-slave.py.

This branch refactors the staging setup to match our new, simpler production environment. The authdb replication set is now gone. We also do some related cleanups, such as fixing the synchronization bug in new-slave.py so it should stop failing on production.

Some tidying was done to trusted.sql, making some lines wrap at 80 columns. Also some obsolete permissions where stripped from security.cfg.

[Michael Hudson-Doyle (mwhudson)]

This all looks good to me, although there's obviously quite a bit of trust involved in me saying that.

I noticed a typo in a comment near one of your changes:

# Turn off silencing for now so we details on staging deployments.

Please fix that and land!

review: Approve

[Stuart Bishop (stub)]

Live in Thailand long time now. Speak only en_TH. Fix comment now en_US already.

=== modified file 'database/replication/Makefile'
--- database/replication/Makefile	2010-03-26 08:43:39 +0000
+++ database/replication/Makefile	2010-04-13 06:35:59 +0000
@@ -44,14 +44,16 @@
 
 PGMASSACRE=../../utilities/pgmassacre.py
 
-# Turn off silencing for now so we details on staging deployments.
+CREATEDB_83=createdb --encoding=UTF8
+CREATEDB_84=createdb --encoding=UTF8 --locale=C --template=template0
+CREATEDB=${CREATEDB_83}
+
+# Turn off output silencing so we can see details of staging deployments.
+# Without the timestamps, we are unable to estimate production deployment
+# times.
 #SHHH=../../utilities/shhh.py
 SHHH=
 
-AUTHDB_TABLES=\
-	account accountpassword authkoken emailaddress \
-	openidassociation openidauthorization openidnonce openidrpsummary
-
 default:
 	echo Usage: make [start|stop|restart]
 
@@ -76,7 +78,7 @@
 	
 	# Replicate it again, so we can test with multiple slaves.
 	-${PGMASSACRE} launchpad_dev_slave2
-	createdb --encoding=UTF8 launchpad_dev_slave2
+	${CREATEDB} launchpad_dev_slave2
 	LPCONFIG=${DEV_CONFIG} ./slon_ctl.py start \
 		 node3_node 'dbname=launchpad_dev_slave2 user=slony'
 	LPCONFIG=${DEV_CONFIG} ./new-slave.py 3 launchpad_dev_slave2
@@ -96,23 +98,12 @@
 	    _MASTER=lpmain_staging_new _SLAVE=lpmain_staging_slave_new \
 	    LAG="0 seconds"
 	# Create the DB with the desired default tablespace.
-	createdb --encoding UTF8 --tablespace ${STAGING_TABLESPACE} \
-	    lpmain_staging_new
-	# Restore the DB schema. We need to restore permissions, despite
+	${CREATEDB} --tablespace ${STAGING_TABLESPACE} lpmain_staging_new
+	# Restore the database. We need to restore permissions, despite
 	# later running security.py, to pull in permissions granted on
 	# production to users not maintained by security.py.
 	pg_restore --dbname=lpmain_staging_new \
 	    --no-owner --exit-on-error ${STAGING_DUMP}
-	psql -q -d lpmain_staging_new -f authdb_drop.sql
-	psql -q -d lpmain_staging_new -f authdb_create.sql \
-	    2>&1 | grep -v _sl || true
-	# Restore the authdb data.
-	for table in ${AUTHDB_TABLES}; do \
-	    pg_restore --dbname=lpmain_staging_new \
-		--no-acl --no-owner --disable-triggers --data-only \
-		--table=$$table ${STAGING_DUMP}; \
-	done
-	psql -q -d lpmain_staging_new -f authdb_sequences.sql
 	# Uninstall Slony-I if it is installed - a pg_dump of a DB with
 	# Slony-I installed isn't usable without this step.
 	LPCONFIG=${NEW_STAGING_CONFIG} ./repair-restored-db.py
@@ -144,17 +135,9 @@
 	LPCONFIG=${STAGING_CONFIG} ./slon_ctl.py --lag="${LAG}" start
 
 dogfood:
-	createdb --encoding UTF8 ${DOGFOOD_DBNAME}
+	${CREATEDB} ${DOGFOOD_DBNAME}
 	pg_restore --dbname=${DOGFOOD_DBNAME} --no-acl --no-owner \
 	    --exit-on-error ${DOGFOOD_DUMP}
-	psql -q -d ${DOGFOOD_DBNAME} -f authdb_drop.sql
-	psql -q -d ${DOGFOOD_DBNAME} -f authdb_create.sql \
-	    2>&1 | grep -v _sl || true
-	for table in ${AUTHDB_TABLES}; do \
-	    pg_restore --dbname=${DOGFOOD_DBNAME} \
-		--no-acl --no-owner --disable-triggers --data-only \
-		--table=$$table ${DOGFOOD_DUMP}; \
-	done
 	./repair-restored-db.py -d ${DOGFOOD_DBNAME}
 	../schema/upgrade.py -d ${DOGFOOD_DBNAME}
 	../schema/fti.py -d ${DOGFOOD_DBNAME}
@@ -174,14 +157,15 @@
 _replicate:
 	@echo LPCONFIG currently ${LPCONFIG}
 	# Start the slon daemon for the master.
-	./slon_ctl.py start \
+	./slon_ctl.py --lag="0 seconds" start \
 		 node1_node "dbname=${_MASTER} user=slony"
 	# Initialize the cluster and create replication sets.
 	./initialize.py
 	# Create the soon-to-be-slave database, empty at this point.
-	createdb --encoding=UTF8 --tablespace=${_SLAVE_TABLESPACE} ${_SLAVE}
+	${CREATEDB} --tablespace=${_SLAVE_TABLESPACE} ${_SLAVE}
 	# Start the slon daemon for the slave
-	./slon_ctl.py start node2_node "dbname=${_SLAVE} user=slony"
+	./slon_ctl.py --lag="0 seconds" start \
+	    node2_node "dbname=${_SLAVE} user=slony"
 	# Setup the slave
 	./new-slave.py 2 "dbname=${_SLAVE}"
 	# Upgrade all databases in the cluster and reset security.
@@ -192,12 +176,7 @@
 	@echo Running security.py `date`
 	./slon_ctl.py stop # security.py can deadlock with slony
 	${SHHH} ../schema/security.py --cluster -U slony
-	./slon_ctl.py --lag="0 seconds" start
-	# Migrate tables to the authdb replication set, creating the set
-	# and subscribing nodes to it as necessary.
-	./populate_auth_replication_set.py -U slony
 	# Restart slon daemons with default lag setting.
-	./slon_ctl.py stop
 	./slon_ctl.py --lag="${LAG}" start
 	# Generate a preamble for manual slonik(1) usage.
 	./preamble.py > preamble.sk

=== removed file 'database/replication/authdb_create.sql'
--- database/replication/authdb_create.sql	2010-03-30 05:51:30 +0000
+++ database/replication/authdb_create.sql	1970-01-01 00:00:00 +0000
@@ -1,885 +0,0 @@
--- Copyright 2009 Canonical Ltd.  This software is licensed under the
--- GNU Affero General Public License version 3 (see the file LICENSE).
-
--- Generated by:
--- pg_dump --format=p --schema-only --no-owner --no-privileges \
---     --table=Account --table=AccountPassword --table=AuthToken \
---     --table=EmailAddress --table=OpenIDAssociation \
---     --table=OpenIDAuthorization --table=OpenIDNonce \
---     --table=OpenIDRPSummary --table=ValidPersonCache \
---     --table=ValidPersonOrTeamCache launchpad_prod_4
-
---
--- PostgreSQL database dump
---
-
-SET client_encoding = 'UTF8';
-SET standard_conforming_strings = off;
-SET check_function_bodies = false;
-SET client_min_messages = warning;
-SET escape_string_warning = off;
-
-SET search_path = public, pg_catalog;
-
-SET default_tablespace = '';
-
-SET default_with_oids = false;
-
---
--- Name: account; Type: TABLE; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE TABLE account (
-    id integer NOT NULL,
-    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
-    creation_rationale integer NOT NULL,
-    status integer NOT NULL,
-    date_status_set timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
-    displayname text NOT NULL,
-    openid_identifier text DEFAULT generate_openid_identifier() NOT NULL,
-    status_comment text,
-    old_openid_identifier text
-);
-
-
---
--- Name: TABLE account; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON TABLE account IS 'An account that may be used for authenticating to Canonical or other systems.';
-
-
---
--- Name: COLUMN account.status; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN account.status IS 'The status of the account.';
-
-
---
--- Name: COLUMN account.date_status_set; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN account.date_status_set IS 'When the status was last changed.';
-
-
---
--- Name: COLUMN account.displayname; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN account.displayname IS 'Name to display when rendering information about this account.';
-
-
---
--- Name: COLUMN account.openid_identifier; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN account.openid_identifier IS 'The key used to construct an OpenID identity URL for this account.';
-
-
---
--- Name: COLUMN account.status_comment; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN account.status_comment IS 'The comment on the status of the account.';
-
-
---
--- Name: COLUMN account.old_openid_identifier; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN account.old_openid_identifier IS 'The previous openid_identifier, used for transitions to the current openid_identifier.';
-
-
---
--- Name: account_id_seq; Type: SEQUENCE; Schema: public; Owner: -
---
-
-CREATE SEQUENCE account_id_seq
-    INCREMENT BY 1
-    NO MAXVALUE
-    NO MINVALUE
-    CACHE 1;
-
-
---
--- Name: account_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
---
-
-ALTER SEQUENCE account_id_seq OWNED BY account.id;
-
-
---
--- Name: accountpassword; Type: TABLE; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE TABLE accountpassword (
-    id integer NOT NULL,
-    account integer NOT NULL,
-    password text NOT NULL
-);
-
-
---
--- Name: TABLE accountpassword; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON TABLE accountpassword IS 'A password used to authenticate an Account.';
-
-
---
--- Name: COLUMN accountpassword.password; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN accountpassword.password IS 'SSHA digest encrypted password.';
-
-
---
--- Name: accountpassword_id_seq; Type: SEQUENCE; Schema: public; Owner: -
---
-
-CREATE SEQUENCE accountpassword_id_seq
-    INCREMENT BY 1
-    NO MAXVALUE
-    NO MINVALUE
-    CACHE 1;
-
-
---
--- Name: accountpassword_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
---
-
-ALTER SEQUENCE accountpassword_id_seq OWNED BY accountpassword.id;
-
-
---
--- Name: authtoken; Type: TABLE; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE TABLE authtoken (
-    id integer NOT NULL,
-    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
-    date_consumed timestamp without time zone,
-    token_type integer NOT NULL,
-    token text NOT NULL,
-    requester integer,
-    requester_email text,
-    email text NOT NULL,
-    redirection_url text
-);
-
-
---
--- Name: TABLE authtoken; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON TABLE authtoken IS 'AuthToken stores one time tokens used by the authentication service for validating email addresses and other tasks that require verifying an email address is valid such as password recovery and account merging. This table will be cleaned occasionally to remove expired tokens. Expiry time is not yet defined.';
-
-
---
--- Name: COLUMN authtoken.date_created; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN authtoken.date_created IS 'The timestamp that this request was made.';
-
-
---
--- Name: COLUMN authtoken.date_consumed; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN authtoken.date_consumed IS 'The date and time when this token was consumed. It''s NULL if it hasn''t been consumed yet.';
-
-
---
--- Name: COLUMN authtoken.token_type; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN authtoken.token_type IS 'The type of request, as per dbschema.TokenType.';
-
-
---
--- Name: COLUMN authtoken.token; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN authtoken.token IS 'The token (not the URL) emailed used to uniquely identify this request. This token will be used to generate a URL that when clicked on will continue a workflow.';
-
-
---
--- Name: COLUMN authtoken.requester; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN authtoken.requester IS 'The Account that made this request. This will be null for password recovery requests.';
-
-
---
--- Name: COLUMN authtoken.requester_email; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN authtoken.requester_email IS 'The email address that was used to login when making this request. This provides an audit trail to help the end user confirm that this is a valid request. It is not a link to the EmailAddress table as this may be changed after the request is made. This field will be null for password recovery requests.';
-
-
---
--- Name: COLUMN authtoken.email; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN authtoken.email IS 'The email address that this request was sent to.';
-
-
---
--- Name: authtoken_id_seq; Type: SEQUENCE; Schema: public; Owner: -
---
-
-CREATE SEQUENCE authtoken_id_seq
-    INCREMENT BY 1
-    NO MAXVALUE
-    NO MINVALUE
-    CACHE 1;
-
-
---
--- Name: authtoken_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
---
-
-ALTER SEQUENCE authtoken_id_seq OWNED BY authtoken.id;
-
-
---
--- Name: emailaddress; Type: TABLE; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE TABLE emailaddress (
-    id integer NOT NULL,
-    email text NOT NULL,
-    person integer,
-    status integer NOT NULL,
-    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
-    account integer,
-    CONSTRAINT emailaddress__is_linked__chk CHECK (((person IS NOT NULL) OR (account IS NOT NULL)))
-);
-
-
---
--- Name: COLUMN emailaddress.email; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN emailaddress.email IS 'An email address used by a Person. The email address is stored in a casesensitive way, but must be case insensitivly unique.';
-
-
---
--- Name: emailaddress_id_seq; Type: SEQUENCE; Schema: public; Owner: -
---
-
-CREATE SEQUENCE emailaddress_id_seq
-    INCREMENT BY 1
-    NO MAXVALUE
-    NO MINVALUE
-    CACHE 1;
-
-
---
--- Name: emailaddress_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
---
-
-ALTER SEQUENCE emailaddress_id_seq OWNED BY emailaddress.id;
-
-
---
--- Name: openidassociation; Type: TABLE; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE TABLE openidassociation (
-    server_url character varying(2047) NOT NULL,
-    handle character varying(255) NOT NULL,
-    secret bytea,
-    issued integer,
-    lifetime integer,
-    assoc_type character varying(64),
-    CONSTRAINT secret_length_constraint CHECK ((length(secret) <= 128))
-);
-
-
---
--- Name: openidauthorization; Type: TABLE; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE TABLE openidauthorization (
-    id integer NOT NULL,
-    account integer NOT NULL,
-    client_id text,
-    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
-    date_expires timestamp without time zone NOT NULL,
-    trust_root text NOT NULL
-);
-
-
---
--- Name: openidauthorization_id_seq; Type: SEQUENCE; Schema: public; Owner: -
---
-
-CREATE SEQUENCE openidauthorization_id_seq
-    INCREMENT BY 1
-    NO MAXVALUE
-    NO MINVALUE
-    CACHE 1;
-
-
---
--- Name: openidauthorization_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
---
-
-ALTER SEQUENCE openidauthorization_id_seq OWNED BY openidauthorization.id;
-
-
---
--- Name: openidnonce; Type: TABLE; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE TABLE openidnonce (
-    server_url character varying(2047) NOT NULL,
-    "timestamp" integer NOT NULL,
-    salt character(40) NOT NULL
-);
-
-
---
--- Name: TABLE openidnonce; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON TABLE openidnonce IS 'Nonces for our OpenID consumer.';
-
-
---
--- Name: openidrpsummary; Type: TABLE; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE TABLE openidrpsummary (
-    id integer NOT NULL,
-    account integer NOT NULL,
-    openid_identifier text NOT NULL,
-    trust_root text NOT NULL,
-    date_created timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
-    date_last_used timestamp without time zone DEFAULT timezone('UTC'::text, now()) NOT NULL,
-    total_logins integer DEFAULT 1 NOT NULL
-);
-
-
---
--- Name: TABLE openidrpsummary; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON TABLE openidrpsummary IS 'The summary of the activity between a person and an RP.';
-
-
---
--- Name: COLUMN openidrpsummary.account; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN openidrpsummary.account IS 'The account who used the RP.';
-
-
---
--- Name: COLUMN openidrpsummary.openid_identifier; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN openidrpsummary.openid_identifier IS 'The OpenID identifier used to login.';
-
-
---
--- Name: COLUMN openidrpsummary.trust_root; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN openidrpsummary.trust_root IS 'The trust root for the RP';
-
-
---
--- Name: COLUMN openidrpsummary.date_created; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN openidrpsummary.date_created IS 'The creation date of this summary; the first time the person used the RP.';
-
-
---
--- Name: COLUMN openidrpsummary.date_last_used; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN openidrpsummary.date_last_used IS 'The date the RP was last used.';
-
-
---
--- Name: COLUMN openidrpsummary.total_logins; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON COLUMN openidrpsummary.total_logins IS 'The total number of times the RP was used by the person.';
-
-
---
--- Name: openidrpsummary_id_seq; Type: SEQUENCE; Schema: public; Owner: -
---
-
-CREATE SEQUENCE openidrpsummary_id_seq
-    INCREMENT BY 1
-    NO MAXVALUE
-    NO MINVALUE
-    CACHE 1;
-
-
---
--- Name: openidrpsummary_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
---
-
-ALTER SEQUENCE openidrpsummary_id_seq OWNED BY openidrpsummary.id;
-
-
---
--- Name: validpersoncache; Type: VIEW; Schema: public; Owner: -
---
-
-CREATE VIEW validpersoncache AS
-    SELECT emailaddress.person AS id FROM emailaddress, account WHERE ((((emailaddress.account = account.id) AND (emailaddress.person IS NOT NULL)) AND (emailaddress.status = 4)) AND (account.status = 20));
-
-
---
--- Name: VIEW validpersoncache; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON VIEW validpersoncache IS 'A materialized view listing the Person.ids of all valid people (but not teams).';
-
-
---
--- Name: validpersonorteamcache; Type: VIEW; Schema: public; Owner: -
---
-
-CREATE VIEW validpersonorteamcache AS
-    SELECT person.id FROM ((person LEFT JOIN emailaddress ON ((person.id = emailaddress.person))) LEFT JOIN account ON ((emailaddress.account = account.id))) WHERE ((person.teamowner IS NOT NULL) OR ((account.status = 20) AND (emailaddress.status = 4)));
-
-
---
--- Name: id; Type: DEFAULT; Schema: public; Owner: -
---
-
-ALTER TABLE account ALTER COLUMN id SET DEFAULT nextval('account_id_seq'::regclass);
-
-
---
--- Name: id; Type: DEFAULT; Schema: public; Owner: -
---
-
-ALTER TABLE accountpassword ALTER COLUMN id SET DEFAULT nextval('accountpassword_id_seq'::regclass);
-
-
---
--- Name: id; Type: DEFAULT; Schema: public; Owner: -
---
-
-ALTER TABLE authtoken ALTER COLUMN id SET DEFAULT nextval('authtoken_id_seq'::regclass);
-
-
---
--- Name: id; Type: DEFAULT; Schema: public; Owner: -
---
-
-ALTER TABLE emailaddress ALTER COLUMN id SET DEFAULT nextval('emailaddress_id_seq'::regclass);
-
-
---
--- Name: id; Type: DEFAULT; Schema: public; Owner: -
---
-
-ALTER TABLE openidauthorization ALTER COLUMN id SET DEFAULT nextval('openidauthorization_id_seq'::regclass);
-
-
---
--- Name: id; Type: DEFAULT; Schema: public; Owner: -
---
-
-ALTER TABLE openidrpsummary ALTER COLUMN id SET DEFAULT nextval('openidrpsummary_id_seq'::regclass);
-
-
---
--- Name: account_openid_identifier_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY account
-    ADD CONSTRAINT account_openid_identifier_key UNIQUE (openid_identifier);
-
-
---
--- Name: account_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY account
-    ADD CONSTRAINT account_pkey PRIMARY KEY (id);
-
-
---
--- Name: accountpassword_account_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY accountpassword
-    ADD CONSTRAINT accountpassword_account_key UNIQUE (account);
-
-
---
--- Name: accountpassword_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY accountpassword
-    ADD CONSTRAINT accountpassword_pkey PRIMARY KEY (id);
-
-
---
--- Name: authtoken__token__key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY authtoken
-    ADD CONSTRAINT authtoken__token__key UNIQUE (token);
-
-
---
--- Name: authtoken_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY authtoken
-    ADD CONSTRAINT authtoken_pkey PRIMARY KEY (id);
-
-
---
--- Name: emailaddress_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY emailaddress
-    ADD CONSTRAINT emailaddress_pkey PRIMARY KEY (id);
-
-
---
--- Name: openidassociation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY openidassociation
-    ADD CONSTRAINT openidassociation_pkey PRIMARY KEY (server_url, handle);
-
-
---
--- Name: openidauthorization_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY openidauthorization
-    ADD CONSTRAINT openidauthorization_pkey PRIMARY KEY (id);
-
-
---
--- Name: openidnonce_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY openidnonce
-    ADD CONSTRAINT openidnonce_pkey PRIMARY KEY (server_url, "timestamp", salt);
-
-
---
--- Name: openidrpsummary__account__trust_root__openid_identifier__key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY openidrpsummary
-    ADD CONSTRAINT openidrpsummary__account__trust_root__openid_identifier__key UNIQUE (account, trust_root, openid_identifier);
-
-
---
--- Name: openidrpsummary_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
---
-
-ALTER TABLE ONLY openidrpsummary
-    ADD CONSTRAINT openidrpsummary_pkey PRIMARY KEY (id);
-
-
---
--- Name: account__old_openid_identifier__idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE INDEX account__old_openid_identifier__idx ON account USING btree (old_openid_identifier);
-
-
---
--- Name: authtoken__date_consumed__idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE INDEX authtoken__date_consumed__idx ON authtoken USING btree (date_consumed);
-
-
---
--- Name: authtoken__date_created__idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE INDEX authtoken__date_created__idx ON authtoken USING btree (date_created);
-
-
---
--- Name: authtoken__requester__idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE INDEX authtoken__requester__idx ON authtoken USING btree (requester);
-
-
---
--- Name: emailaddress__account__key; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE UNIQUE INDEX emailaddress__account__key ON emailaddress USING btree (account) WHERE ((status = 4) AND (account IS NOT NULL));
-
-
---
--- Name: INDEX emailaddress__account__key; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON INDEX emailaddress__account__key IS 'Ensures that an Account only has one preferred email address';
-
-
---
--- Name: emailaddress__lower_email__key; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE INDEX emailaddress__lower_email__key ON emailaddress USING btree (lower(email));
-
-
---
--- Name: emailaddress__person__key; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE UNIQUE INDEX emailaddress__person__key ON emailaddress USING btree (person) WHERE ((status = 4) AND (person IS NOT NULL));
-
-
---
--- Name: INDEX emailaddress__person__key; Type: COMMENT; Schema: public; Owner: -
---
-
-COMMENT ON INDEX emailaddress__person__key IS 'Ensures that a Person only has one preferred email address';
-
-
---
--- Name: emailaddress__person__status__idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE INDEX emailaddress__person__status__idx ON emailaddress USING btree (person, status);
-
-
---
--- Name: openidauthorixation__account__troot__expires__client_id__idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE INDEX openidauthorixation__account__troot__expires__client_id__idx ON openidauthorization USING btree (account, trust_root, date_expires, client_id);
-
-
---
--- Name: openidauthorixation__account__trust_root__key; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE UNIQUE INDEX openidauthorixation__account__trust_root__key ON openidauthorization USING btree (account, trust_root) WHERE (client_id IS NULL);
-
-
---
--- Name: openidauthorization__account__client_id__trust_root__key; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE UNIQUE INDEX openidauthorization__account__client_id__trust_root__key ON openidauthorization USING btree (account, client_id, trust_root) WHERE (client_id IS NOT NULL);
-
-
---
--- Name: openidrpsummary__openid_identifier__idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE INDEX openidrpsummary__openid_identifier__idx ON openidrpsummary USING btree (openid_identifier);
-
-
---
--- Name: openidrpsummary__trust_root__idx; Type: INDEX; Schema: public; Owner: -; Tablespace: 
---
-
-CREATE INDEX openidrpsummary__trust_root__idx ON openidrpsummary USING btree (trust_root);
-
-
---
--- Name: _sl_logtrigger_200; Type: TRIGGER; Schema: public; Owner: -
---
-
-CREATE TRIGGER _sl_logtrigger_200
-    AFTER INSERT OR DELETE OR UPDATE ON account
-    FOR EACH ROW
-    EXECUTE PROCEDURE _sl.logtrigger('_sl', '200', 'kvvvvvvvv');
-
-
---
--- Name: _sl_logtrigger_201; Type: TRIGGER; Schema: public; Owner: -
---
-
-CREATE TRIGGER _sl_logtrigger_201
-    AFTER INSERT OR DELETE OR UPDATE ON accountpassword
-    FOR EACH ROW
-    EXECUTE PROCEDURE _sl.logtrigger('_sl', '201', 'kvv');
-
-
---
--- Name: _sl_logtrigger_274; Type: TRIGGER; Schema: public; Owner: -
---
-
-CREATE TRIGGER _sl_logtrigger_274
-    AFTER INSERT OR DELETE OR UPDATE ON emailaddress
-    FOR EACH ROW
-    EXECUTE PROCEDURE _sl.logtrigger('_sl', '274', 'kvvvvv');
-
-
---
--- Name: _sl_logtrigger_335; Type: TRIGGER; Schema: public; Owner: -
---
-
-CREATE TRIGGER _sl_logtrigger_335
-    AFTER INSERT OR DELETE OR UPDATE ON openidauthorization
-    FOR EACH ROW
-    EXECUTE PROCEDURE _sl.logtrigger('_sl', '335', 'kvvvvv');
-
-
---
--- Name: _sl_logtrigger_337; Type: TRIGGER; Schema: public; Owner: -
---
-
-CREATE TRIGGER _sl_logtrigger_337
-    AFTER INSERT OR DELETE OR UPDATE ON openidrpsummary
-    FOR EACH ROW
-    EXECUTE PROCEDURE _sl.logtrigger('_sl', '337', 'kvvvvvv');
-
-
---
--- Name: _sl_logtrigger_438; Type: TRIGGER; Schema: public; Owner: -
---
-
-CREATE TRIGGER _sl_logtrigger_438
-    AFTER INSERT OR DELETE OR UPDATE ON authtoken
-    FOR EACH ROW
-    EXECUTE PROCEDURE _sl.logtrigger('_sl', '438', 'kvvvvvvvv');
-
-
---
--- Name: _sl_logtrigger_439; Type: TRIGGER; Schema: public; Owner: -
---
-
-CREATE TRIGGER _sl_logtrigger_439
-    AFTER INSERT OR DELETE OR UPDATE ON openidassociation
-    FOR EACH ROW
-    EXECUTE PROCEDURE _sl.logtrigger('_sl', '439', 'kkvvvv');
-
-
---
--- Name: _sl_logtrigger_445; Type: TRIGGER; Schema: public; Owner: -
---
-
-CREATE TRIGGER _sl_logtrigger_445
-    AFTER INSERT OR DELETE OR UPDATE ON openidnonce
-    FOR EACH ROW
-    EXECUTE PROCEDURE _sl.logtrigger('_sl', '445', 'kkk');
-
-
---
--- Name: set_date_status_set_t; Type: TRIGGER; Schema: public; Owner: -
---
-
-CREATE TRIGGER set_date_status_set_t
-    BEFORE UPDATE ON account
-    FOR EACH ROW
-    EXECUTE PROCEDURE set_date_status_set();
-
-
---
--- Name: accountpassword_account_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
---
-
-ALTER TABLE ONLY accountpassword
-    ADD CONSTRAINT accountpassword_account_fkey FOREIGN KEY (account) REFERENCES account(id) ON DELETE CASCADE;
-
-
---
--- Name: authtoken__requester__fk; Type: FK CONSTRAINT; Schema: public; Owner: -
---
-
-ALTER TABLE ONLY authtoken
-    ADD CONSTRAINT authtoken__requester__fk FOREIGN KEY (requester) REFERENCES account(id);
-
-
---
--- Name: emailaddress__account__fk; Type: FK CONSTRAINT; Schema: public; Owner: -
---
-
-ALTER TABLE ONLY emailaddress
-    ADD CONSTRAINT emailaddress__account__fk FOREIGN KEY (account) REFERENCES account(id) ON DELETE SET NULL;
-
-
---
--- Name: openidauthorization__account__fk; Type: FK CONSTRAINT; Schema: public; Owner: -
---
-
-ALTER TABLE ONLY openidauthorization
-    ADD CONSTRAINT openidauthorization__account__fk FOREIGN KEY (account) REFERENCES account(id);
-
-
---
--- Name: openidrpsummary_account_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
---
-
-ALTER TABLE ONLY openidrpsummary
-    ADD CONSTRAINT openidrpsummary_account_fkey FOREIGN KEY (account) REFERENCES account(id);
-
-
---
--- PostgreSQL database dump complete
---
-
-CREATE INDEX emailaddress__account__status__idx
-    ON EmailAddress(account, status);
-
-
--- Permissions for Ubuntu SSO server testing on staging.
-
--- Mirrored from sso_auth user 2010-01-12.
--- These tables will eventually not be available.
---
-GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE account TO ubuntu_sso;
-GRANT USAGE ON SEQUENCE account_id_seq TO ubuntu_sso;
-GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE accountpassword TO ubuntu_sso;
-GRANT USAGE ON SEQUENCE accountpassword_id_seq TO ubuntu_sso;
-GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE authtoken TO ubuntu_sso;
-GRANT USAGE ON SEQUENCE authtoken_id_seq TO ubuntu_sso;
-GRANT SELECT ON TABLE person TO ubuntu_sso;
-GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE emailaddress TO ubuntu_sso;
-GRANT USAGE ON SEQUENCE emailaddress_id_seq TO ubuntu_sso;
-GRANT SELECT,INSERT,DELETE ON TABLE openidassociation TO ubuntu_sso;
-GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE openidauthorization TO ubuntu_sso;
-GRANT USAGE ON SEQUENCE openidauthorization_id_seq TO ubuntu_sso;
-GRANT SELECT,INSERT,DELETE ON TABLE openidnonce TO ubuntu_sso;
-GRANT SELECT,INSERT,UPDATE ON TABLE openidrpsummary TO ubuntu_sso;
-GRANT USAGE ON SEQUENCE openidrpsummary_id_seq TO ubuntu_sso;
-GRANT SELECT ON SEQUENCE person_id_seq TO ubuntu_sso;
-GRANT SELECT ON TABLE personlocation TO ubuntu_sso;
-GRANT SELECT ON SEQUENCE personlocation_id_seq TO ubuntu_sso;
-GRANT SELECT ON TABLE teamparticipation TO ubuntu_sso;
-GRANT SELECT ON SEQUENCE teamparticipation_id_seq TO ubuntu_sso;
-
--- Permissions on the Ubuntu SSO tables.
---
-GRANT SELECT, INSERT, DELETE, UPDATE ON TABLE auth_permission,
-auth_group_permissions, auth_group, auth_user, auth_user_groups,
-auth_user_user_permissions, auth_message, django_content_type,
-django_session, django_site, django_admin_log,
-ssoopenidrpconfig TO ubuntu_sso;
-
-GRANT USAGE ON SEQUENCE auth_group_id_seq,
-auth_group_permissions_id_seq, auth_message_id_seq,
-auth_permission_id_seq, auth_user_groups_id_seq, auth_user_id_seq,
-auth_user_user_permissions_id_seq, django_admin_log_id_seq,
-django_content_type_id_seq, django_site_id_seq,
-ssoopenidrpconfig_id_seq TO ubuntu_sso;
-
--- Permissions on the lpmirror tables (mirrors of relevant Launchpad
--- information, available even when Launchpad database upgrades are in
--- progress).
-GRANT SELECT
-ON TABLE
-    lp_person, lp_personlocation, lp_teamparticipation, lp_account
-TO ubuntu_sso;
-

=== removed file 'database/replication/authdb_drop.sql'
--- database/replication/authdb_drop.sql	2009-11-11 10:32:35 +0000
+++ database/replication/authdb_drop.sql	1970-01-01 00:00:00 +0000
@@ -1,14 +0,0 @@
--- Copyright 2009 Canonical Ltd.  This software is licensed under the
--- GNU Affero General Public License version 3 (see the file LICENSE).
-
-SET client_min_messages=ERROR;
-
--- Drop everything in the authdb replication set.
-DROP TABLE IF EXISTS Account CASCADE;
-DROP TABLE IF EXISTS AccountPassword CASCADE;
-DROP TABLE IF EXISTS AuthToken CASCADE;
-DROP TABLE IF EXISTS EmailAddress CASCADE;
-DROP TABLE IF EXISTS OpenIDAssociation CASCADE;
-DROP TABLE IF EXISTS OpenIDAuthorization CASCADE;
-DROP TABLE IF EXISTS OpenIDNonce CASCADE;
-DROP TABLE IF EXISTS OpenIDRPSummary;

=== removed file 'database/replication/authdb_sequences.sql'
--- database/replication/authdb_sequences.sql	2010-01-13 06:54:32 +0000
+++ database/replication/authdb_sequences.sql	1970-01-01 00:00:00 +0000
@@ -1,22 +0,0 @@
--- Repair sequences in the authdb replication set. We need to do this because
--- we cannot restore the sequence values from the dump when restoring the
--- data using pg_restore --data-only.
-
-SELECT setval('account_id_seq', max(id)) AS Account
-FROM Account;
-
-SELECT setval('accountpassword_id_seq', max(id)) AS AccountPassword
-FROM AccountPassword;
-
-SELECT setval('authtoken_id_seq', max(id)) AS AuthToken
-FROM AuthToken;
-
-SELECT setval('emailaddress_id_seq', max(id)) AS EmailAddress
-FROM EmailAddress;
-
-SELECT setval('openidauthorization_id_seq', max(id)) AS OpenIDAuthorization
-FROM OpenIDAuthorization;
-
-SELECT setval('openidrpsummary_id_seq', max(id)) AS OpenIDRPSummary
-FROM OpenIDRPSummary;
-

=== modified file 'database/replication/helpers.py'
--- database/replication/helpers.py	2010-04-01 09:29:27 +0000
+++ database/replication/helpers.py	2010-04-13 06:35:59 +0000
@@ -28,20 +28,15 @@
 
 # Replication set id constants. Don't change these without DBA help.
 LPMAIN_SET_ID = 1
-AUTHDB_SET_ID = 2
 HOLDING_SET_ID = 666
+LPMIRROR_SET_ID = 4
 
-# Seed tables for the authdb replication set to be passed to
+# Seed tables for the lpmain replication set to be passed to
 # calculate_replication_set().
-AUTHDB_SEED = frozenset([
+LPMAIN_SEED = frozenset([
     ('public', 'account'),
+    ('public', 'openidnonce'),
     ('public', 'openidassociation'),
-    ('public', 'openidnonce'),
-    ])
-
-# Seed tables for the lpmain replication set to be passed to
-# calculate_replication_set().
-LPMAIN_SEED = frozenset([
     ('public', 'person'),
     ('public', 'launchpaddatabaserevision'),
     ('public', 'databasereplicationlag'),
@@ -57,7 +52,6 @@
     ('public', 'launchpadstatistic'),
     ('public', 'parsedapachelog'),
     ('public', 'shipitsurvey'),
-    ('public', 'openidassociations'), # Remove this in April 2009 or later.
     ('public', 'databasereplicationlag'),
     ])
 
@@ -177,12 +171,13 @@
         script = preamble() + script
 
     if sync is not None:
-        script = script + dedent("""\
+        sync_script = dedent("""\
             sync (id = @master_node);
             wait for event (
-                origin = ALL, confirmed = ALL,
+                origin = @master_node, confirmed = ALL,
                 wait on = @master_node, timeout = %d);
             """ % sync)
+        script = script + sync_script
 
     # Copy the script to a NamedTemporaryFile rather than just pumping it
     # to slonik via stdin. This way it can be examined if slonik appears
@@ -193,7 +188,7 @@
 
     # Run slonik
     log.debug("Executing slonik script %s" % script_on_disk.name)
-    log.log(DEBUG2, script)
+    log.log(DEBUG2, 'Running script:\n%s' % script)
     returncode = subprocess.call(['slonik', script_on_disk.name])
 
     if returncode != 0:
@@ -324,10 +319,10 @@
         cluster name = sl;
 
         # Symbolic ids for replication sets.
-        define lpmain_set  %d;
-        define authdb_set  %d;
-        define holding_set %d;
-        """ % (LPMAIN_SET_ID, AUTHDB_SET_ID, HOLDING_SET_ID))]
+        define lpmain_set   %d;
+        define holding_set  %d;
+        define lpmirror_set %d;
+        """ % (LPMAIN_SET_ID, HOLDING_SET_ID, LPMIRROR_SET_ID))]
 
     if master_node is not None:
         preamble.append(dedent("""\
@@ -504,19 +499,6 @@
         raise ReplicationConfigError(
             "Unreplicated sequences: %s" % repr(unrepl_sequences))
 
-    authdb_tables, authdb_sequences = calculate_replication_set(
-        cur, AUTHDB_SEED)
     lpmain_tables, lpmain_sequences = calculate_replication_set(
         cur, LPMAIN_SEED)
 
-    confused_tables = authdb_tables.intersection(lpmain_tables)
-    if confused_tables:
-        raise ReplicationConfigError(
-            "Tables exist in multiple replication sets: %s"
-            % repr(confused_tables))
-    confused_sequences = authdb_sequences.intersection(lpmain_sequences)
-    if confused_sequences:
-        raise ReplicationConfigError(
-            "Sequences exist in multiple replication sets: %s"
-            % repr(confused_sequences))
-

=== modified file 'database/replication/initialize.py'
--- database/replication/initialize.py	2010-04-07 21:26:08 +0000
+++ database/replication/initialize.py	2010-04-13 06:35:59 +0000
@@ -88,54 +88,13 @@
     helpers.sync(120) # Will exit on failure.
 
 
-def create_replication_sets(
-    authdb_tables, authdb_sequences, lpmain_tables, lpmain_sequences):
+def create_replication_sets(lpmain_tables, lpmain_sequences):
     """Create the replication sets."""
     log.info('Creating Slony-I replication sets.')
 
-    # Instead of creating both the authdb and lpmain replication sets,
-    # we just create the lpmain replication set containing everything.
-    # This way, we can then test the populate_auth_replication_set.py
-    # migration script that moves the relevant tables from the lpmain
-    # replication set to the authdb replication set.
-    # We will turn this behavior off once we are running two
-    # replication sets in production and remove the migration script.
-    lpmain_tables = lpmain_tables.union(authdb_tables)
-    lpmain_sequences = lpmain_sequences.union(authdb_sequences)
-
     script = ["try {"]
-    # script,append("""
-    #     echo 'Creating AuthDB replication set (@authdb_set)';
-    #     create set (
-    #         id=@authdb_set, origin=@master_node,
-    #         comment='AuthDB tables and sequences');
-    #     """)
 
-    # entry_id = 1
-    # for table in sorted(authdb_tables):
-    #     script.append("""
-    #         echo 'Adding %(table)s to replication set @authdb_set';
-    #         set add table (
-    #             set id=@authdb_set,
-    #             origin=@master_node,
-    #             id=%(entry_id)d,
-    #             fully qualified name='%(table)s');
-    #         """ % vars())
-    #     entry_id += 1
-    # entry_id = 1
-    # for sequence in sorted(authdb_sequences):
-    #     script.append("""
-    #         echo 'Adding %(sequence)s to replication set @authdb_set';
-    #         set add sequence (
-    #             set id=@authdb_set,
-    #             origin=@master_node,
-    #             id=%(entry_id)d,
-    #             fully qualified name='%(sequence)s');
-    #         """ % vars())
-    #     entry_id += 1
-    #
-    # assert entry_id < 200, 'authdb replcation set has > 200 objects???'
-    entry_id = 200
+    entry_id = 1
 
     script.append("""
         echo 'Creating LPMain replication set (@lpmain_set)';
@@ -157,7 +116,7 @@
             """ % vars())
         entry_id += 1
 
-    entry_id = 200
+    entry_id = 1
     script.append(
         "echo 'Adding %d sequences to replication set @lpmain_set';"
         % len(lpmain_sequences))
@@ -199,9 +158,6 @@
     con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
     global cur
     cur = con.cursor()
-    log.debug("Calculating authdb replication set.")
-    authdb_tables, authdb_sequences = helpers.calculate_replication_set(
-        cur, helpers.AUTHDB_SEED)
     log.debug("Calculating lpmain replication set.")
     lpmain_tables, lpmain_sequences = helpers.calculate_replication_set(
         cur, helpers.LPMAIN_SEED)
@@ -212,8 +168,7 @@
     fails = 0
     for table in all_tables_in_schema(cur, 'public'):
         times_seen = 0
-        for table_set in [
-            authdb_tables, lpmain_tables, helpers.IGNORED_TABLES]:
+        for table_set in [lpmain_tables, helpers.IGNORED_TABLES]:
             if table in table_set:
                 times_seen += 1
         if times_seen == 0:
@@ -224,8 +179,7 @@
             fails += 1
     for sequence in all_sequences_in_schema(cur, 'public'):
         times_seen = 0
-        for sequence_set in [
-            authdb_sequences, lpmain_sequences, helpers.IGNORED_SEQUENCES]:
+        for sequence_set in [lpmain_sequences, helpers.IGNORED_SEQUENCES]:
             if sequence in sequence_set:
                 times_seen += 1
         if times_seen == 0:
@@ -242,8 +196,7 @@
 
     ensure_live()
 
-    create_replication_sets(
-        authdb_tables, authdb_sequences, lpmain_tables, lpmain_sequences)
+    create_replication_sets(lpmain_tables, lpmain_sequences)
 
     helpers.sync(0)
 

=== modified file 'database/replication/new-slave.py'
--- database/replication/new-slave.py	2010-04-07 21:26:08 +0000
+++ database/replication/new-slave.py	2010-04-13 06:35:59 +0000
@@ -22,10 +22,9 @@
 from canonical.database.sqlbase import (
     connect_string, ISOLATION_LEVEL_AUTOCOMMIT)
 from canonical.launchpad.scripts import db_options, logger_options, logger
-from canonical.launchpad.webapp.adapter import _auth_store_tables
 
 import replication.helpers
-from replication.helpers import AUTHDB_SET_ID, LPMAIN_SET_ID
+from replication.helpers import LPMAIN_SET_ID
 
 def main():
     parser = OptionParser(
@@ -77,8 +76,6 @@
     # Get the connection string for masters.
     lpmain_connection_string = get_master_connection_string(
         source_connection, parser, LPMAIN_SET_ID) or source_connection_string
-    authdb_connection_string = get_master_connection_string(
-        source_connection, parser, AUTHDB_SET_ID) or source_connection_string
 
     # Sanity check the target connection string.
     target_connection_string = ConnectionString(raw_target_connection_string)
@@ -130,31 +127,6 @@
         log.error("Failed to duplicate database schema.")
         return 1
 
-    # Drop the authdb replication set tables we just restored, as they
-    # will be broken if the authdb master is a seperate database to the
-    # lpmain master.
-    log.debug("Dropping (possibly corrupt) authdb tables.")
-    cur = target_con.cursor()
-    for table_name in _auth_store_tables:
-        cur.execute("DROP TABLE IF EXISTS %s CASCADE" % table_name)
-    target_con.commit()
-
-    # Duplicate the authdb schema.
-    log.info("Duplicating authdb schema from '%s' to '%s'" % (
-        authdb_connection_string, target_connection_string))
-    table_args = ["--table=%s" % table for table in _auth_store_tables]
-    # We need to restore the two cross-replication-set views that where
-    # dropped as a side effect of dropping the auth store tables.
-    table_args.append("--table=ValidPersonCache")
-    table_args.append("--table=ValidPersonOrTeamCache")
-    cmd = "pg_dump --schema-only --no-privileges %s %s | psql -1 -q %s" % (
-        ' '.join(table_args),
-        source_connection_string.asPGCommandLineArgs(),
-        target_connection_string.asPGCommandLineArgs())
-    if subprocess.call(cmd, shell=True) != 0:
-        log.error("Failed to duplicate database schema.")
-        return 1
-
     # Trash the broken Slony tables we just duplicated.
     log.debug("Removing slony cruft.")
     cur = target_con.cursor()
@@ -163,21 +135,30 @@
     del target_con
 
     # Get a list of existing set ids that can be subscribed too. This
-    # is all sets where the origin is the master_node, and set 2 if
-    # the master happens to be configured as a forwarding slave. We
+    # is all sets where the origin is the master_node. We
     # don't allow other sets where the master is configured as a
     # forwarding slave as we have to special case rebuilding the database
-    # schema (such as we do for the authdb replication set 2).
+    # schema, and we want to avoid cascading slave configurations anyway
+    # since we are running an antique Slony-I at the moment - keep it
+    # simple!
+    # We order the sets smallest to largest by number of tables.
+    # This should let us subscribe the quickest sets first for more
+    # immediate feedback.
     source_connection.rollback()
     master_node = replication.helpers.get_master_node(source_connection)
     cur = source_connection.cursor()
     cur.execute("""
-        SELECT set_id FROM _sl.sl_set WHERE set_origin=%d
-        UNION
-        SELECT sub_set AS set_id FROM _sl.sl_subscribe
-        WHERE sub_receiver=%d AND sub_forward IS TRUE AND sub_active IS TRUE
-            AND sub_set=2
-        """ % (master_node.node_id, master_node.node_id))
+        SELECT set_id
+        FROM _sl.sl_set, (
+            SELECT tab_set, count(*) AS tab_count
+            FROM _sl.sl_table GROUP BY tab_set
+            ) AS TableCounts
+        WHERE
+            set_origin=%d
+            AND tab_set = set_id
+        ORDER BY tab_count
+        """
+        % (master_node.node_id,))
     set_ids = [set_id for set_id, in cur.fetchall()]
     log.debug("Discovered set ids %s" % repr(list(set_ids)))
 
@@ -209,19 +190,32 @@
         } on error { echo 'Failed.'; exit 1; }
         """)
 
+    full_sync = []
+    sync_nicknames = [node.nickname for node in existing_nodes]
+    sync_nicknames.append('new_node');
+    for nickname in sync_nicknames:
+        full_sync.append(dedent("""\
+            echo 'Waiting for %(nickname)s sync.';
+            sync (id=@%(nickname)s);
+            wait for event (
+                origin = @%(nickname)s, confirmed=ALL,
+                wait on = @%(nickname)s, timeout=0);
+            """ % {'nickname': nickname}))
+    full_sync = '\n'.join(full_sync)
+    script += full_sync
+
     for set_id in set_ids:
-
         script += dedent("""\
         echo 'Subscribing new node to set %d.';
         subscribe set (
             id=%d, provider=@master_node, receiver=@new_node, forward=yes);
-
-        echo 'Waiting for sync... this might take a while...';
+        echo 'Waiting for subscribe to start processing.';
         sync (id = @master_node);
         wait for event (
-            origin = ALL, confirmed = ALL,
+            origin = @master_node, confirmed = ALL,
             wait on = @master_node, timeout = 0);
         """ % (set_id, set_id))
+        script += full_sync
 
     replication.helpers.execute_slonik(script)
 

=== removed file 'database/replication/populate_auth_replication_set.py'
--- database/replication/populate_auth_replication_set.py	2010-04-07 21:26:08 +0000
+++ database/replication/populate_auth_replication_set.py	1970-01-01 00:00:00 +0000
@@ -1,177 +0,0 @@
-#!/usr/bin/python2.5 -S
-#
-# Copyright 2009 Canonical Ltd.  This software is licensed under the
-# GNU Affero General Public License version 3 (see the file LICENSE).
-
-"""Populate the auth replication set.
-
-This script moves the the SSO tables from the main replication set to
-the auth replication set.
-
-Once it has been run on production, these tables can no longer be
-maintained using the Launchpad database maintenance scripts
-(upgrade.py, security.py etc.).
-
-We do this so Launchpad database upgrades do not lock the SSO tables,
-allowing the SSO service to continue to operate.
-
-This is a single shot script.
-"""
-
-__metaclass__ = type
-__all__ = []
-
-import _pythonpath
-
-import sys
-from textwrap import dedent
-from optparse import OptionParser
-
-from canonical.database.sqlbase import (
-    connect, ISOLATION_LEVEL_AUTOCOMMIT, sqlvalues)
-from canonical.launchpad.scripts import db_options, logger_options, logger
-
-import replication.helpers
-
-def create_auth_set(cur):
-    """Create the auth replication set if it doesn't already exist."""
-    cur.execute("SELECT TRUE FROM _sl.sl_set WHERE set_id=2")
-    if cur.fetchone() is not None:
-        log.info("Auth set already exists.")
-        return
-    slonik_script = dedent("""\
-        create set (
-            id=@authdb_set, origin=@master_node,
-            comment='SSO service tables');
-        """)
-    log.info("Creating authdb replication set.")
-    replication.helpers.execute_slonik(slonik_script, sync=0)
-
-
-def subscribe_auth_set(cur):
-    """The authdb set subscription much match the lpmain set subscription.
-
-    This is a requirement to move stuff between replication sets. It
-    is also what we want (all nodes replicating everything).
-    """
-    cur.execute("""
-        SELECT sub_receiver FROM _sl.sl_subscribe WHERE sub_set = 1
-        EXCEPT
-        SELECT sub_receiver FROM _sl.sl_subscribe WHERE sub_set = 2
-        """)
-    for node_id in (node_id for node_id, in cur.fetchall()):
-        log.info("Subscribing Node #%d to authdb replication set" % node_id)
-        success = replication.helpers.execute_slonik(dedent("""\
-            subscribe set (
-                id = @authdb_set, provider = @master_node,
-                receiver = %d, forward = yes);
-            """ % node_id), sync=0)
-        if not success:
-            log.error("Slonik failed. Exiting.")
-            sys.exit(1)
-
-
-def migrate_tables_and_sequences(cur):
-    auth_tables, auth_sequences = (
-        replication.helpers.calculate_replication_set(
-            cur, replication.helpers.AUTHDB_SEED))
-
-    slonik_script = ["try {"]
-    for table_fqn in auth_tables:
-        namespace, table_name = table_fqn.split('.')
-        cur.execute("""
-            SELECT tab_id, tab_set
-            FROM _sl.sl_table
-            WHERE tab_nspname = %s AND tab_relname = %s
-            """ % sqlvalues(namespace, table_name))
-        try:
-            table_id, set_id = cur.fetchone()
-        except IndexError:
-            log.error("Table %s not found in _sl.sl_tables" % table_fqn)
-            sys.exit(1)
-        if set_id == 1:
-            slonik_script.append("echo 'Moving table %s';" % table_fqn)
-            slonik_script.append(
-                "set move table "
-                "(origin=@master_node, id=%d, new set=@authdb_set);"
-                % table_id)
-        elif set_id == 2:
-            log.warn(
-                "Table %s already in authdb replication set"
-                % table_fqn)
-        else:
-            log.error("Unknown replication set %s" % set_id)
-            sys.exit(1)
-
-    for sequence_fqn in auth_sequences:
-        namespace, sequence_name = sequence_fqn.split('.')
-        cur.execute("""
-            SELECT seq_id, seq_set
-            FROM _sl.sl_sequence
-            WHERE seq_nspname = %s AND seq_relname = %s
-            """ % sqlvalues(namespace, sequence_name))
-        try:
-            sequence_id, set_id = cur.fetchone()
-        except IndexError:
-            log.error(
-                "Sequence %s not found in _sl.sl_sequences" % sequence_fqn)
-            sys.exit(1)
-        if set_id == 1:
-            slonik_script.append("echo 'Moving sequence %s';" % sequence_fqn)
-            slonik_script.append(
-                "set move sequence "
-                "(origin=@master_node, id=%d, new set=@authdb_set);"
-                % sequence_id)
-        elif set_id ==2:
-            log.warn(
-                "Sequence %s already in authdb replication set."
-                % sequence_fqn)
-        else:
-            log.error("Unknown replication set %s" % set_id)
-            sys.exit(1)
-
-    if len(slonik_script) == 1:
-        log.warn("No tables or sequences to migrate.")
-        return
-
-    slonik_script.append(dedent("""\
-        } on error {
-            echo 'Failed to move one or more tables or sequences.';
-            exit 1;
-        }
-        """))
-
-    slonik_script = "\n".join(slonik_script)
-
-    log.info("Running migration script...")
-    if not replication.helpers.execute_slonik(slonik_script, sync=0):
-        log.error("Slonik failed. Exiting.")
-        sys.exit(1)
-
-
-def main():
-    parser = OptionParser()
-    db_options(parser)
-    logger_options(parser)
-    options, args = parser.parse_args()
-
-    global log
-    log = logger(options)
-
-    con = connect('slony', isolation=ISOLATION_LEVEL_AUTOCOMMIT)
-    cur = con.cursor()
-
-    # Don't start until cluster is synced.
-    log.info("Waiting for sync.")
-    replication.helpers.sync(0)
-
-    create_auth_set(cur)
-    subscribe_auth_set(cur)
-    migrate_tables_and_sequences(cur)
-
-
-log = None # Global log
-
-
-if __name__ == '__main__':
-    main()

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2010-04-12 17:02:16 +0000
+++ database/schema/security.cfg	2010-04-13 06:35:59 +0000
@@ -35,20 +35,27 @@
 public.is_printable_ascii(text)            = EXECUTE
 public.launchpaddatabaserevision           = SELECT
 public.name_blacklist_match(text)          = EXECUTE
-public.fticache                            =
 public.pillarname                          = SELECT
 public.ulower(text)                        = EXECUTE
-public._killall_backends(text)             =
 public.generate_openid_identifier()        = EXECUTE
 public.getlocalnodeid()                    = EXECUTE
 public.replication_lag()                   = EXECUTE
 public.replication_lag(integer)            = EXECUTE
 public.assert_patch_applied(integer, integer, integer) = EXECUTE
+# Explicitly state 'no permissions on these objects' to silence
+# security.py warnings.
+public.fticache                            =
+public._killall_backends(text)             =
 public.exclusivelocks                      =
 public.alllocks                            =
 public.pgstattuple(oid)                    =
 public.pgstattuple(text)                   =
 public.bugnotificationarchive              =
+public.lp_account                          =
+public.lp_personlocation                   =
+public.lp_person                           =
+public.lp_teamparticipation                =
+public.bug_update_latest_patch_uploaded(integer) =
 
 [ro]
 # A user with full readonly access to the database. Generally used for
@@ -61,59 +68,6 @@
 type=user
 groups=admin
 
-[sso_auth]
-# authdb replication set write access from the SSO service.
-type=user
-public.account                          = SELECT, INSERT, UPDATE, DELETE
-public.accountpassword                  = SELECT, INSERT, UPDATE, DELETE
-public.authtoken                        = SELECT, INSERT, UPDATE, DELETE
-public.emailaddress                     = SELECT, INSERT, UPDATE, DELETE
-public.openidrpsummary                  = SELECT, INSERT, UPDATE
-public.openidassociation                = SELECT, INSERT, DELETE
-public.openidnonce                      = SELECT, INSERT, DELETE
-public.openidauthorization              = SELECT, INSERT, UPDATE, DELETE
-public.person                           = SELECT
-public.personlocation                   = SELECT
-public.teamparticipation                = SELECT
-
-[sso_main]
-# main replication set access from the SSO service login.launchpad.net
-type=user
-public.language                         = SELECT
-public.openidrpconfig                   = SELECT
-public.person                           = SELECT
-public.personlanguage                   = SELECT
-public.personlocation                   = SELECT
-public.shippingrequest                  = SELECT
-public.teammembership                   = SELECT
-public.teamparticipation                = SELECT
-public.validpersoncache                 = SELECT
-# Needed for person.preferredemail to work.
-public.emailaddress                     = SELECT
-# Needed for OpenID login to work - Bug #352727
-public.country                          = SELECT
-# Needed for display of OpenID consumer logo per Bug #353926
-public.libraryfilealias                 = SELECT
-public.libraryfilecontent               = SELECT
-
-[launchpad_auth]
-# authdb replication set access from the main Z3 application.
-type=user
-public.account                          = SELECT, INSERT, UPDATE, DELETE
-public.accountpassword                  = SELECT, INSERT, UPDATE, DELETE
-public.authtoken                        = SELECT, INSERT, UPDATE
-public.emailaddress                     = SELECT, INSERT, UPDATE, DELETE
-public.language                         = SELECT
-public.openidrpconfig                   = SELECT
-public.openidrpsummary                  = SELECT
-public.person                           = SELECT
-public.personlanguage                   = SELECT
-public.teammembership                   = SELECT
-public.teamparticipation                = SELECT
-# XXX 2009-05-07 stub bug=373252: SELECT and DELETE permissions required
-# for garbo.py. INSERT permission needed for the tests.
-public.openidassociation                = SELECT, INSERT, DELETE
-
 [launchpad_main]
 # lpmain replication set access from the main Z3 application.
 type=user

=== modified file 'database/schema/trusted.sql'
--- database/schema/trusted.sql	2010-03-26 08:25:24 +0000
+++ database/schema/trusted.sql	2010-04-13 06:35:59 +0000
@@ -1470,36 +1470,36 @@
 
 -- Update the (redundant) column bug.latest_patch_uploaded when a
 -- a bug attachment is added or removed or if its type is changed.
-CREATE OR REPLACE FUNCTION bug_update_latest_patch_uploaded(integer) RETURNS VOID
-    SECURITY DEFINER LANGUAGE plpgsql AS
-    $$
-    BEGIN
-        UPDATE bug SET latest_patch_uploaded =
-            (SELECT max(message.datecreated)
-                FROM message, bugattachment
-                WHERE bugattachment.message=message.id AND
-                    bugattachment.bug=$1 AND
-                    bugattachment.type=1)
-            WHERE bug.id=$1;
-    END;
-    $$;
-
-
-CREATE OR REPLACE FUNCTION bug_update_latest_patch_uploaded_on_insert_update() RETURNS trigger
-    SECURITY DEFINER LANGUAGE plpgsql AS
-    $$
-    BEGIN
-        PERFORM bug_update_latest_patch_uploaded(NEW.bug);
-        RETURN NULL; -- Ignored - this is an AFTER trigger
-    END;
-    $$;
-
-
-CREATE OR REPLACE FUNCTION bug_update_latest_patch_uploaded_on_delete() RETURNS trigger
-    SECURITY DEFINER LANGUAGE plpgsql AS
-    $$
-    BEGIN
-        PERFORM bug_update_latest_patch_uploaded(OLD.bug);
-        RETURN NULL; -- Ignored - this is an AFTER trigger
-    END;
-    $$;
+CREATE OR REPLACE FUNCTION bug_update_latest_patch_uploaded(integer)
+RETURNS VOID SECURITY DEFINER LANGUAGE plpgsql AS
+$$
+BEGIN
+    UPDATE bug SET latest_patch_uploaded =
+        (SELECT max(message.datecreated)
+            FROM message, bugattachment
+            WHERE bugattachment.message=message.id AND
+                bugattachment.bug=$1 AND
+                bugattachment.type=1)
+        WHERE bug.id=$1;
+END;
+$$;
+
+
+CREATE OR REPLACE FUNCTION bug_update_latest_patch_uploaded_on_insert_update()
+RETURNS trigger SECURITY DEFINER LANGUAGE plpgsql AS
+$$
+BEGIN
+    PERFORM bug_update_latest_patch_uploaded(NEW.bug);
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+
+CREATE OR REPLACE FUNCTION bug_update_latest_patch_uploaded_on_delete()
+RETURNS trigger SECURITY DEFINER LANGUAGE plpgsql AS
+$$
+BEGIN
+    PERFORM bug_update_latest_patch_uploaded(OLD.bug);
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;