Fix cyclic-dependency between oss services
Pushkin uses postgresql, and creates dbs
using alembic migrations, but we are using
some INSERTS into new tables. This is the
reason for running 'postgresql.client' state twice.
With this patch we are create all required
tables on postgresql side, not on pushkin.
Related-Prod: PROD-14525
Change-Id: I35cacc33f66b71783a6086957a04e798a65d3987
diff --git a/postgresql/client/pushkin.yml b/postgresql/client/pushkin.yml
deleted file mode 100644
index c6ec567..0000000
--- a/postgresql/client/pushkin.yml
+++ /dev/null
@@ -1,26 +0,0 @@
-classes:
- - system.postgresql.client
-parameters:
- _param:
- pushkin_db_host: ${_param:haproxy_postgresql_bind_host}
- pushkin_db_user: pushkin
- pushkin_db_user_password: pushkin
- postgresql:
- client:
- server:
- server01:
- database:
- pushkin:
- enabled: true
- encoding: 'UTF8'
- locale: 'en_US'
- users:
- - name: ${_param:pushkin_db_user}
- password: ${_param:pushkin_db_user_password}
- host: ${_param:pushkin_db_host}
- createdb: true
- rights: all privileges
- extension:
- hstore:
- enabled: true
-
diff --git a/postgresql/client/alertmanager.yml b/postgresql/client/pushkin/alertmanager.yml
similarity index 68%
rename from postgresql/client/alertmanager.yml
rename to postgresql/client/pushkin/alertmanager.yml
index 8bd272a..8e413da 100644
--- a/postgresql/client/alertmanager.yml
+++ b/postgresql/client/pushkin/alertmanager.yml
@@ -12,16 +12,7 @@
server:
server01:
database:
- alertmanager:
- enabled: true
- encoding: 'UTF8'
- locale: 'en_US'
- users:
- - name: ${_param:alertmanager_db_user}
- password: ${_param:alertmanager_db_user_password}
- host: ${_param:alertmanager_db_host}
- createdb: true
- rights: all privileges
+ pushkin:
init:
maintenance_db: pushkin
force: true
diff --git a/postgresql/client/pushkin/init.yml b/postgresql/client/pushkin/init.yml
new file mode 100644
index 0000000..5677646
--- /dev/null
+++ b/postgresql/client/pushkin/init.yml
@@ -0,0 +1,54 @@
+classes:
+ - system.postgresql.client
+parameters:
+ _param:
+ pushkin_db_host: ${_param:haproxy_postgresql_bind_host}
+ pushkin_db_user: pushkin
+ pushkin_db_user_password: pushkin
+ postgresql:
+ client:
+ server:
+ server01:
+ database:
+ pushkin:
+ enabled: true
+ encoding: 'UTF8'
+ locale: 'en_US'
+ users:
+ - name: ${_param:pushkin_db_user}
+ password: ${_param:pushkin_db_user_password}
+ host: ${_param:pushkin_db_host}
+ createdb: true
+ rights: all privileges
+ init:
+ maintenance_db: pushkin
+ queries:
+ - ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO public;
+ - CREATE TABLE IF NOT EXISTS login (id int8 NOT NULL, language_id int2, PRIMARY KEY (id));
+ - CREATE TABLE IF NOT EXISTS device (id serial NOT NULL, login_id int8 NOT NULL, platform_id int2 NOT NULL, device_token text NOT NULL, device_token_new text, application_version int4, unregistered_ts timestamp, device_id text, PRIMARY KEY(id));
+ - CREATE INDEX IF NOT EXISTS idx_device_login_id ON device (login_id);
+ - ALTER TABLE device DROP CONSTRAINT IF EXISTS Ref_device_to_login;
+ - ALTER TABLE device ADD CONSTRAINT Ref_device_to_login FOREIGN KEY (login_id) REFERENCES login(id) MATCH SIMPLE ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
+ - CREATE TABLE IF NOT EXISTS message (id serial NOT NULL, name text NOT NULL, cooldown_ts int8, trigger_event_id int4, screen text NOT NULL DEFAULT '', PRIMARY KEY (id), CONSTRAINT c_message_unique_name UNIQUE(name));
+ - CREATE TABLE IF NOT EXISTS message_localization (id serial NOT NULL, message_id int4 NOT NULL, language_id int2 NOT NULL, message_title text NOT NULL, message_text text NOT NULL, PRIMARY KEY(id), CONSTRAINT c_message_loc_unique_message_language UNIQUE(message_id, language_id));
+ - ALTER TABLE message_localization DROP CONSTRAINT IF EXISTS ref_message_id_to_message;
+ - ALTER TABLE message_localization ADD CONSTRAINT ref_message_id_to_message FOREIGN KEY (message_id) REFERENCES message(id) MATCH SIMPLE ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
+ - CREATE TABLE IF NOT EXISTS user_message_last_time_sent (id serial NOT NULL, login_id int8 NOT NULL, message_id int4 NOT NULL, last_time_sent_ts_bigint int8 NOT NULL, PRIMARY KEY (id), CONSTRAINT c_user_unique_message UNIQUE(login_id, message_id));
+ - ALTER TABLE user_message_last_time_sent DROP CONSTRAINT IF EXISTS ref_login_id_to_login
+ - ALTER TABLE user_message_last_time_sent ADD CONSTRAINT ref_login_id_to_login FOREIGN KEY (login_id) REFERENCES login(id) MATCH SIMPLE ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
+ - ALTER TABLE user_message_last_time_sent DROP CONSTRAINT IF EXISTS ref_message_id_to_message
+ - ALTER TABLE user_message_last_time_sent ADD CONSTRAINT ref_message_id_to_message FOREIGN KEY (message_id) REFERENCES message(id) MATCH SIMPLE ON DELETE CASCADE ON UPDATE NO ACTION NOT DEFERRABLE;
+ - ALTER TABLE login OWNER TO ${_param:pushkin_db_user};
+ - ALTER TABLE device OWNER TO ${_param:pushkin_db_user};
+ - ALTER TABLE message OWNER TO ${_param:pushkin_db_user};
+ - ALTER TABLE message_localization OWNER TO ${_param:pushkin_db_user};
+ - ALTER TABLE user_message_last_time_sent OWNER TO ${_param:pushkin_db_user};
+ - GRANT ALL PRIVILEGES ON TABLE login TO ${_param:pushkin_db_user};
+ - GRANT ALL PRIVILEGES ON TABLE device TO ${_param:pushkin_db_user};
+ - GRANT ALL PRIVILEGES ON TABLE message TO ${_param:pushkin_db_user};
+ - GRANT ALL PRIVILEGES ON TABLE message_localization TO ${_param:pushkin_db_user};
+ - GRANT ALL PRIVILEGES ON TABLE user_message_last_time_sent TO ${_param:pushkin_db_user};
+ extension:
+ hstore:
+ enabled: true
+
diff --git a/postgresql/client/janitor_monkey.yml b/postgresql/client/pushkin/janitor_monkey.yml
similarity index 68%
rename from postgresql/client/janitor_monkey.yml
rename to postgresql/client/pushkin/janitor_monkey.yml
index def9a06..b56d098 100644
--- a/postgresql/client/janitor_monkey.yml
+++ b/postgresql/client/pushkin/janitor_monkey.yml
@@ -12,16 +12,7 @@
server:
server01:
database:
- janmonkey:
- enabled: true
- encoding: 'UTF8'
- locale: 'en_US'
- users:
- - name: ${_param:janmonkey_db_user}
- password: ${_param:janmonkey_db_user_password}
- host: ${_param:janmonkey_db_host}
- createdb: true
- rights: all privileges
+ pushkin:
init:
maintenance_db: pushkin
force: true
diff --git a/postgresql/client/pushkin/security_monkey.yml b/postgresql/client/pushkin/security_monkey.yml
new file mode 100644
index 0000000..18154cd
--- /dev/null
+++ b/postgresql/client/pushkin/security_monkey.yml
@@ -0,0 +1,19 @@
+classes:
+ - system.postgresql.client
+parameters:
+ _param:
+ secmonkey_db_host: ${_param:haproxy_postgresql_bind_host}
+ secmonkey_db_user: secmonkey
+ secmonkey_db_user_password: secmonkey
+ postgresql:
+ client:
+ server:
+ server01:
+ database:
+ pushkin:
+ init:
+ maintenance_db: pushkin
+ force: true
+ queries:
+ - INSERT INTO login VALUES (${_param:secmonkey_login_id}, ${_param:secmonkey_application_id}) ON CONFLICT (id) DO UPDATE SET id = excluded.id;
+ - INSERT INTO device VALUES (${_param:secmonkey_application_id}, ${_param:secmonkey_login_id}, 42, 'security_audit_service', NULL, 1, NULL) ON CONFLICT (id) DO UPDATE SET id = excluded.id;
diff --git a/postgresql/client/sfdc.yml b/postgresql/client/pushkin/sfdc.yml
similarity index 68%
rename from postgresql/client/sfdc.yml
rename to postgresql/client/pushkin/sfdc.yml
index bafd9c9..57af7fe 100644
--- a/postgresql/client/sfdc.yml
+++ b/postgresql/client/pushkin/sfdc.yml
@@ -12,16 +12,7 @@
server:
server01:
database:
- sfdc:
- enabled: true
- encoding: 'UTF8'
- locale: 'en_US'
- users:
- - name: ${_param:sfdc_db_user}
- password: ${_param:sfdc_db_user_password}
- host: ${_param:sfdc_db_host}
- createdb: true
- rights: all privileges
+ pushkin:
init:
maintenance_db: pushkin
force: true
diff --git a/postgresql/client/security_monkey.yml b/postgresql/client/security_monkey.yml
index c47e241..ab7a4c8 100644
--- a/postgresql/client/security_monkey.yml
+++ b/postgresql/client/security_monkey.yml
@@ -20,9 +20,3 @@
host: ${_param:secmonkey_db_host}
createdb: true
rights: all privileges
- init:
- maintenance_db: pushkin
- force: true
- queries:
- - INSERT INTO login VALUES (${_param:secmonkey_login_id}, ${_param:secmonkey_application_id}) ON CONFLICT (id) DO UPDATE SET id = excluded.id;
- - INSERT INTO device VALUES (${_param:secmonkey_application_id}, ${_param:secmonkey_login_id}, 42, 'security_audit_service', NULL, 1, NULL) ON CONFLICT (id) DO UPDATE SET id = excluded.id;