You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafficcontrol.apache.org by GitBox <gi...@apache.org> on 2021/01/07 22:00:58 UTC
[GitHub] [trafficcontrol] srijeet0406 opened a new pull request #5415: Adding check to make sure that you cannot add two servers with identical content
srijeet0406 opened a new pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415
<!--
************ STOP!! ************
If this Pull Request is intended to fix a security vulnerability, DO NOT submit it! Instead, contact
the Apache Software Foundation Security Team at security@trafficcontrol.apache.org and follow the
guidelines at https://www.apache.org/security/ regarding vulnerability disclosure.
-->
## What does this PR (Pull Request) do?
<!-- Explain the changes you made here. If this fixes an Issue, identify it by
replacing the text in the checkbox item with the Issue number e.g.
- [x] This PR fixes #9001 OR is not related to any Issue
^ This will automatically close Issue number 9001 when the Pull Request is
merged (The '#' is important).
Be sure you check the box properly, see the "The following criteria are ALL
met by this PR" section for details.
-->
- [x] This PR fixes #5407 <!-- You can check for an issue here: https://github.com/apache/trafficcontrol/issues -->
## Which Traffic Control components are affected by this PR?
<!-- Please delete all components from this list that are NOT affected by this
Pull Request. Also, feel free to add the name of a tool or script that is
affected but not on the list.
Additionally, if this Pull Request does NOT affect documentation, please
explain why documentation is not required. -->
- Traffic Ops
- CI tests
## What is the best way to verify this PR?
<!-- Please include here ALL the steps necessary to test your Pull Request. If
it includes tests (and most should), outline here the steps needed to run the
tests. If not, lay out the manual testing procedure and please explain why
tests are unnecessary for this Pull Request. -->
Using API 1.x, create a server using the POST API call.
Now, try to add the same server again using the same POST API call -> You should see an error saying that another server exists with the same profile/ IP address combination.
## If this is a bug fix, what versions of Traffic Control are affected?
<!-- If this PR fixes a bug, please list here all of the affected versions - to
the best of your knowledge. It's also pretty helpful to include a commit hash
of where 'master' is at the time this PR is opened (if it affects master),
because what 'master' means will change over time. For example, if this PR
fixes a bug that's present in master (at commit hash '1df853c8'), in v4.0.0,
and in the current 4.0.1 Release candidate (e.g. RC1), then this list would
look like:
- master (1df853c8)
- 4.0.0
- 4.0.1 (RC1)
If you don't know what other versions might have this bug, AND don't know how
to find the commit hash of 'master', then feel free to leave this section
blank (or, preferably, delete it entirely).
-->
- master
## The following criteria are ALL met by this PR
<!-- Check the boxes to signify that the associated statement is true. To
"check a box", replace the space inside of the square brackets with an 'x'.
e.g.
- [ x] <- Wrong
- [x ] <- Wrong
- [] <- Wrong
- [*] <- Wrong
- [x] <- Correct!
-->
- [x] This PR includes tests
- [x] This PR does not include documentation
- [x] This PR does not include an update to CHANGELOG.md
- [x] This PR includes any and all required license headers
- [x] This PR **DOES NOT FIX A SERIOUS SECURITY VULNERABILITY** (see [the Apache Software Foundation's security guidelines](https://www.apache.org/security/) for details)
## Additional Information
<!-- If you would like to include any additional information on the PR for
potential reviewers please put it here.
Some examples of this would be:
- Before and after screenshots/gifs of the Traffic Portal if it is affected
- Links to other dependent Pull Requests
- References to relevant context (e.g. new/updates to dependent libraries,
mailing list records, blueprints)
Feel free to leave this section blank (or, preferably, delete it entirely).
-->
<!--
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements. See the NOTICE file
distributed with this work for additional information
regarding copyright ownership. The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied. See the License for the
specific language governing permissions and limitations
under the License.
-->
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] rawlinp commented on pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
rawlinp commented on pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#issuecomment-756982139
No, there was definitely a constraint before API 3.x, but then 3.x moved things around so the constraint was replaced with these triggers: `traffic_ops/app/db/migrations/2020081108261100_add_server_ip_profile_trigger.sql`. So, I'm curious why those triggers aren't preventing this and if they can be fixed instead of adding the check to the API.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] srijeet0406 commented on a change in pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
srijeet0406 commented on a change in pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#discussion_r556753387
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+DROP TRIGGER IF EXISTS before_update_ip_address_trigger ON ip_address;
+DROP TRIGGER IF EXISTS before_create_ip_address_trigger ON ip_address;
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION before_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+BEGIN
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND (SELECT SPLIT_PART(sip.address::varchar,'/',1)) = (SELECT SPLIT_PART(NEW.address::varchar,'/',1)) AND sip.profile = (SELECT profile from server s WHERE s.id = NEW.server))
Review comment:
ah, did not know that. I'll use 'host'
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] mattjackson220 commented on a change in pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
mattjackson220 commented on a change in pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#discussion_r555918047
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND sip.address = NEW.address AND sip.profile = new_profile)
+ GROUP BY sip.sid, sip.profile;
+
+ IF server_count > 0 THEN
+ RAISE EXCEPTION 'ip_address is not unique across the server [id:%] profile [id:%], [%] conflicts',
+ server_id,
+ server_profile,
+ server_count;
+ END IF;
+ RETURN NEW;
+END;
+$$ LANGUAGE PLPGSQL;
+-- +goose StatementEnd
+
+CREATE TRIGGER after_create_ip_address_trigger
+ AFTER INSERT
+ ON ip_address
+ FOR EACH ROW
+EXECUTE PROCEDURE after_ip_address_table();
+
+CREATE TRIGGER after_update_ip_address_trigger
+ BEFORE UPDATE
Review comment:
should be `AFTER`
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND sip.address = NEW.address AND sip.profile = new_profile)
Review comment:
looks like this does allow the same ip/profile combo if the netmask on the ip address is different. i dont think we want to allow that either so we will have to strip the netmask off before checking equality here
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND sip.address = NEW.address AND sip.profile = new_profile)
+ GROUP BY sip.sid, sip.profile;
+
+ IF server_count > 0 THEN
+ RAISE EXCEPTION 'ip_address is not unique across the server [id:%] profile [id:%], [%] conflicts',
+ server_id,
+ server_profile,
+ server_count;
+ END IF;
+ RETURN NEW;
+END;
+$$ LANGUAGE PLPGSQL;
+-- +goose StatementEnd
+
+CREATE TRIGGER after_create_ip_address_trigger
+ AFTER INSERT
+ ON ip_address
+ FOR EACH ROW
+EXECUTE PROCEDURE after_ip_address_table();
+
+CREATE TRIGGER after_update_ip_address_trigger
+ BEFORE UPDATE
+ ON ip_address
+ FOR EACH ROW
+ WHEN (NEW.address <> OLD.address)
+EXECUTE PROCEDURE after_ip_address_table();
+
Review comment:
should we drop the old before_ip_address triggers?
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND sip.address = NEW.address AND sip.profile = new_profile)
Review comment:
the `new_profile` variable could be deleted and have the `SELECT` just moved in here instead
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
Review comment:
if we limit it to only blocking when the ip address is a service address and the monitoring is true, then we probably need to add more triggers to check this again right? if the service address is changed then its covered by these since its in the ip_address table but we will need to add triggers for the interface table for if monitor is changed. might be worth a discussion if we want to allow duplicates if they are unmonitored (id say we probably want to block that case too)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] srijeet0406 commented on pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
srijeet0406 commented on pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#issuecomment-756872019
> it's weird that you could do this before because I _thought_ there was a database constraint to enforce unique IP/profile so at the very least you'd get a 500.
I think that was for API 3.x onwards.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] mitchell852 edited a comment on pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
mitchell852 edited a comment on pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#issuecomment-756847700
it's weird that you could do this before because I _thought_ there was a database constraint to enforce unique IP/profile so at the very least you'd get a 500.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] mitchell852 commented on pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
mitchell852 commented on pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#issuecomment-756847700
it's weird that you could do this before because I _thought_ there was a database constraint to enforce unique IP/profile...
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] srijeet0406 commented on a change in pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
srijeet0406 commented on a change in pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#discussion_r556154475
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND sip.address = NEW.address AND sip.profile = new_profile)
Review comment:
Great catch, fixed now.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] srijeet0406 commented on pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
srijeet0406 commented on pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#issuecomment-756872019
> it's weird that you could do this before because I _thought_ there was a database constraint to enforce unique IP/profile so at the very least you'd get a 500.
I think that was for API 3.x onwards.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] srijeet0406 commented on a change in pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
srijeet0406 commented on a change in pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#discussion_r556154376
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
Review comment:
Talked with the team and turns out we dont need the `monitor = true` condition anymore.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] mitchell852 edited a comment on pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
mitchell852 edited a comment on pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#issuecomment-756847700
it's weird that you could do this before because I _thought_ there was a database constraint to enforce unique IP/profile so at the very least you'd get a 500.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] mattjackson220 commented on a change in pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
mattjackson220 commented on a change in pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#discussion_r556610539
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+DROP TRIGGER IF EXISTS before_update_ip_address_trigger ON ip_address;
+DROP TRIGGER IF EXISTS before_create_ip_address_trigger ON ip_address;
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION before_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+BEGIN
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND (SELECT SPLIT_PART(sip.address::varchar,'/',1)) = (SELECT SPLIT_PART(NEW.address::varchar,'/',1)) AND sip.profile = (SELECT profile from server s WHERE s.id = NEW.server))
+ GROUP BY sip.sid, sip.profile;
+
+ IF server_count > 0 THEN
+ RAISE EXCEPTION 'ip_address is not unique across the server [id:%] profile [id:%], [%] conflicts',
+ server_id,
+ server_profile,
+ server_count;
+ END IF;
+ RETURN NEW;
+END;
+$$ LANGUAGE PLPGSQL;
+-- +goose StatementEnd
+
+CREATE TRIGGER before_create_ip_address_trigger
+ BEFORE INSERT
+ ON ip_address
+ FOR EACH ROW
+EXECUTE PROCEDURE before_ip_address_table();
+
+CREATE TRIGGER before_update_ip_address_trigger
+ BEFORE UPDATE
+ ON ip_address
+ FOR EACH ROW
+ WHEN (NEW.address <> OLD.address)
+EXECUTE PROCEDURE before_ip_address_table();
+
+-- +goose Down
+-- SQL section 'Down' is executed when this migration is rolled back
+DROP TRIGGER IF EXISTS before_update_ip_address_trigger ON ip_address;
+DROP TRIGGER IF EXISTS before_create_ip_address_trigger ON ip_address;
Review comment:
with dropping the old ones, on goose down we'll want to add the old ones back in
##########
File path: traffic_ops/testing/api/v3/deliveryserviceservers_test.go
##########
@@ -157,6 +157,25 @@ func TryToRemoveLastServerInDeliveryService(t *testing.T) {
server.HostName = util.StrPtr(dssaTestingXMLID + "-quest")
server.ID = nil
+ if len(server.Interfaces) == 0 {
+ t.Fatal("no interfaces in this server, quitting")
+ }
+ interfaces := make([]tc.ServerInterfaceInfo, 0)
+ ipAddresses := make([]tc.ServerIPAddress, 0)
+ gateway := "255.255.255.255"
Review comment:
super nitpicky but this is a netmask. a gateway would look more like "1.1.1.2"
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+DROP TRIGGER IF EXISTS before_update_ip_address_trigger ON ip_address;
+DROP TRIGGER IF EXISTS before_create_ip_address_trigger ON ip_address;
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION before_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+BEGIN
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND (SELECT SPLIT_PART(sip.address::varchar,'/',1)) = (SELECT SPLIT_PART(NEW.address::varchar,'/',1)) AND sip.profile = (SELECT profile from server s WHERE s.id = NEW.server))
Review comment:
`SPLIT_PART` also works here but postgres has `host` that allows you to get the IP address separate from the netmask without using varchar and splitting it. so you could just do `SELECT host(sip.address)` instead.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] mitchell852 commented on pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
mitchell852 commented on pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#issuecomment-756847700
it's weird that you could do this before because I _thought_ there was a database constraint to enforce unique IP/profile...
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] mattjackson220 merged pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
mattjackson220 merged pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] rawlinp commented on pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
rawlinp commented on pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#issuecomment-756982139
No, there was definitely a constraint before API 3.x, but then 3.x moved things around so the constraint was replaced with these triggers: `traffic_ops/app/db/migrations/2020081108261100_add_server_ip_profile_trigger.sql`. So, I'm curious why those triggers aren't preventing this and if they can be fixed instead of adding the check to the API.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] srijeet0406 commented on a change in pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
srijeet0406 commented on a change in pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#discussion_r556154541
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND sip.address = NEW.address AND sip.profile = new_profile)
Review comment:
done
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND sip.address = NEW.address AND sip.profile = new_profile)
+ GROUP BY sip.sid, sip.profile;
+
+ IF server_count > 0 THEN
+ RAISE EXCEPTION 'ip_address is not unique across the server [id:%] profile [id:%], [%] conflicts',
+ server_id,
+ server_profile,
+ server_count;
+ END IF;
+ RETURN NEW;
+END;
+$$ LANGUAGE PLPGSQL;
+-- +goose StatementEnd
+
+CREATE TRIGGER after_create_ip_address_trigger
+ AFTER INSERT
+ ON ip_address
+ FOR EACH ROW
+EXECUTE PROCEDURE after_ip_address_table();
+
+CREATE TRIGGER after_update_ip_address_trigger
+ BEFORE UPDATE
+ ON ip_address
+ FOR EACH ROW
+ WHEN (NEW.address <> OLD.address)
+EXECUTE PROCEDURE after_ip_address_table();
+
Review comment:
done
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] srijeet0406 commented on a change in pull request #5415: Adding check to make sure that you cannot add two servers with identical content
Posted by GitBox <gi...@apache.org>.
srijeet0406 commented on a change in pull request #5415:
URL: https://github.com/apache/trafficcontrol/pull/5415#discussion_r556154714
##########
File path: traffic_ops/app/db/migrations/2021010900000000_server_ip_profile_trigger_update.sql
##########
@@ -0,0 +1,73 @@
+/*
+ Licensed under the Apache License, Version 2.0 (the "License");
+ you may not use this file except in compliance with the License.
+ You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+*/
+
+-- +goose Up
+-- +goose StatementBegin
+CREATE OR REPLACE FUNCTION after_ip_address_table()
+ RETURNS TRIGGER
+AS
+$$
+DECLARE
+ server_count BIGINT;
+ server_id BIGINT;
+ server_profile BIGINT;
+ new_profile BIGINT;
+BEGIN
+ SELECT profile INTO new_profile from server s WHERE s.id = NEW.server;
+ WITH server_ips AS (
+ SELECT s.id as sid, ip.interface, i.name, ip.address, s.profile, ip.server
+ FROM server s
+ JOIN interface i
+ on i.server = s.ID
+ JOIN ip_address ip
+ on ip.Server = s.ID and ip.interface = i.name
+ WHERE i.monitor = true AND ip.service_address = true
+ )
+ SELECT count(distinct(sip.sid)), sip.sid, sip.profile
+ INTO server_count, server_id, server_profile
+ FROM server_ips sip
+ WHERE (sip.server <> NEW.server AND sip.address = NEW.address AND sip.profile = new_profile)
+ GROUP BY sip.sid, sip.profile;
+
+ IF server_count > 0 THEN
+ RAISE EXCEPTION 'ip_address is not unique across the server [id:%] profile [id:%], [%] conflicts',
+ server_id,
+ server_profile,
+ server_count;
+ END IF;
+ RETURN NEW;
+END;
+$$ LANGUAGE PLPGSQL;
+-- +goose StatementEnd
+
+CREATE TRIGGER after_create_ip_address_trigger
+ AFTER INSERT
+ ON ip_address
+ FOR EACH ROW
+EXECUTE PROCEDURE after_ip_address_table();
+
+CREATE TRIGGER after_update_ip_address_trigger
+ BEFORE UPDATE
Review comment:
Changed he trigger to a `BEFORE` instead.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org