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 2020/11/24 17:21:07 UTC
[GitHub] [trafficcontrol] MylesBock opened a new pull request #5327: 4954/psql to pgx admin
MylesBock opened a new pull request #5327:
URL: https://github.com/apache/trafficcontrol/pull/5327
<!--
************ 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?
Replaces use of `psql` and `dropuser` with `dburl` and `lib/pq`.
Test coverage is unknown
- [x] This PR fixes #REPLACE_ME OR is not related to any Issue <!-- You can check for an issue here: https://github.com/apache/trafficcontrol/issues -->
## Which Traffic Control components are affected by this PR?
- Traffic Ops
## What is the best way to verify this PR?
Run the `traffic_ops/db/admin` utility against a postgres instance, ensure the multiline SQL queries do not break anything
## 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).
-->
## 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!
-->
- [ ] This PR includes tests OR I have explained why tests are unnecessary
- [ ] This PR includes documentation OR I have explained why documentation is unnecessary
- [ ] This PR includes an update to CHANGELOG.md OR such an update is not necessary
- [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] MylesBock removed a comment on pull request #5327: Remove use of `psql` from `traffic_ops/db/admin`
Posted by GitBox <gi...@apache.org>.
MylesBock removed a comment on pull request #5327:
URL: https://github.com/apache/trafficcontrol/pull/5327#issuecomment-748575360
Drafting.
https://github.com/MylesBock/trafficcontrol/tree/experimental/restructure
Will return when this project is more conducive of outside help, cya
----------------------------------------------------------------
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 a change in pull request #5327: Remove use of `psql` from `traffic_ops/db/admin`
Posted by GitBox <gi...@apache.org>.
rawlinp commented on a change in pull request #5327:
URL: https://github.com/apache/trafficcontrol/pull/5327#discussion_r529776694
##########
File path: traffic_ops/app/db/admin.go
##########
@@ -171,68 +173,73 @@ func parseDBConfig() error {
return nil
}
-func createDB() {
- dbExistsCmd := exec.Command("psql", "-h", HostIP, "-U", DBSuperUser, "-p", HostPort, "-tAc", "SELECT 1 FROM pg_database WHERE datname='"+DBName+"'")
- out, err := dbExistsCmd.Output()
- if err != nil {
- die("unable to check if DB already exists: " + err.Error())
- }
- if len(out) > 0 {
- fmt.Println("Database " + DBName + " already exists")
- return
- }
- createDBCmd := exec.Command("createdb", "-h", HostIP, "-p", HostPort, "-U", DBSuperUser, "-e", "--owner", DBUser, DBName)
- out, err = createDBCmd.CombinedOutput()
- fmt.Printf("%s", out)
- if err != nil {
- die("Can't create db " + DBName)
+func connectAndExecute(query string, queryErrPrefix string, returnRows bool) *sql.Rows {
+ //todo prepared statements
+ //todo inspect results of executing the above query for all possible cases (db exists, db dne, db inaccessible)
+ //todo determine test coverage of this portion
+ if db, err := dburl.Open(fmt.Sprintf("pg://%s:%s@%s:%s/", DBSuperUser, DBPassword, HostIP, HostPort)); err == nil {
+ if !returnRows {
+ if _, err := db.Exec(query); err != nil {
Review comment:
So some of our files like `seeds.sql` contain multiple sql queries. Doesn't this only execute a single sql query?
----------------------------------------------------------------
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 a change in pull request #5327: Remove use of `psql` from `traffic_ops/db/admin`
Posted by GitBox <gi...@apache.org>.
rawlinp commented on a change in pull request #5327:
URL: https://github.com/apache/trafficcontrol/pull/5327#discussion_r529882162
##########
File path: traffic_ops/app/db/admin.go
##########
@@ -171,68 +173,73 @@ func parseDBConfig() error {
return nil
}
-func createDB() {
- dbExistsCmd := exec.Command("psql", "-h", HostIP, "-U", DBSuperUser, "-p", HostPort, "-tAc", "SELECT 1 FROM pg_database WHERE datname='"+DBName+"'")
- out, err := dbExistsCmd.Output()
- if err != nil {
- die("unable to check if DB already exists: " + err.Error())
- }
- if len(out) > 0 {
- fmt.Println("Database " + DBName + " already exists")
- return
- }
- createDBCmd := exec.Command("createdb", "-h", HostIP, "-p", HostPort, "-U", DBSuperUser, "-e", "--owner", DBUser, DBName)
- out, err = createDBCmd.CombinedOutput()
- fmt.Printf("%s", out)
- if err != nil {
- die("Can't create db " + DBName)
+func connectAndExecute(query string, queryErrPrefix string, returnRows bool) *sql.Rows {
+ //todo prepared statements
+ //todo inspect results of executing the above query for all possible cases (db exists, db dne, db inaccessible)
+ //todo determine test coverage of this portion
+ if db, err := dburl.Open(fmt.Sprintf("pg://%s:%s@%s:%s/", DBSuperUser, DBPassword, HostIP, HostPort)); err == nil {
+ if !returnRows {
+ if _, err := db.Exec(query); err != nil {
Review comment:
Wow, I thought `db.Exec` would return after running only a single query, hence making it not work for files like `seeds.sql` which have multiple queries. If that works, I'm fine with that -- no need to break them down into individual queries.
----------------------------------------------------------------
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] MylesBock commented on a change in pull request #5327: Remove use of `psql` from `traffic_ops/db/admin`
Posted by GitBox <gi...@apache.org>.
MylesBock commented on a change in pull request #5327:
URL: https://github.com/apache/trafficcontrol/pull/5327#discussion_r529987581
##########
File path: traffic_ops/app/db/admin.go
##########
@@ -171,68 +173,73 @@ func parseDBConfig() error {
return nil
}
-func createDB() {
- dbExistsCmd := exec.Command("psql", "-h", HostIP, "-U", DBSuperUser, "-p", HostPort, "-tAc", "SELECT 1 FROM pg_database WHERE datname='"+DBName+"'")
- out, err := dbExistsCmd.Output()
- if err != nil {
- die("unable to check if DB already exists: " + err.Error())
- }
- if len(out) > 0 {
- fmt.Println("Database " + DBName + " already exists")
- return
- }
- createDBCmd := exec.Command("createdb", "-h", HostIP, "-p", HostPort, "-U", DBSuperUser, "-e", "--owner", DBUser, DBName)
- out, err = createDBCmd.CombinedOutput()
- fmt.Printf("%s", out)
- if err != nil {
- die("Can't create db " + DBName)
+func connectAndExecute(query string, queryErrPrefix string, returnRows bool) *sql.Rows {
+ //todo prepared statements
+ //todo inspect results of executing the above query for all possible cases (db exists, db dne, db inaccessible)
+ //todo determine test coverage of this portion
+ if db, err := dburl.Open(fmt.Sprintf("pg://%s:%s@%s:%s/", DBSuperUser, DBPassword, HostIP, HostPort)); err == nil {
+ if !returnRows {
+ if _, err := db.Exec(query); err != nil {
Review comment:
I'm 99% sure this is why ciab doesn't come up in the pipeline though, so I'll break it down into individual queries or use dotsql
----------------------------------------------------------------
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] MylesBock commented on pull request #5327: Remove use of `psql` from `traffic_ops/db/admin`
Posted by GitBox <gi...@apache.org>.
MylesBock commented on pull request #5327:
URL: https://github.com/apache/trafficcontrol/pull/5327#issuecomment-748575360
Drafting.
https://github.com/MylesBock/trafficcontrol/tree/experimental/restructure
Will return when this project is more conducive of outside help, cya
----------------------------------------------------------------
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] MylesBock commented on a change in pull request #5327: Remove use of `psql` from `traffic_ops/db/admin`
Posted by GitBox <gi...@apache.org>.
MylesBock commented on a change in pull request #5327:
URL: https://github.com/apache/trafficcontrol/pull/5327#discussion_r529786414
##########
File path: traffic_ops/app/db/admin.go
##########
@@ -171,68 +173,73 @@ func parseDBConfig() error {
return nil
}
-func createDB() {
- dbExistsCmd := exec.Command("psql", "-h", HostIP, "-U", DBSuperUser, "-p", HostPort, "-tAc", "SELECT 1 FROM pg_database WHERE datname='"+DBName+"'")
- out, err := dbExistsCmd.Output()
- if err != nil {
- die("unable to check if DB already exists: " + err.Error())
- }
- if len(out) > 0 {
- fmt.Println("Database " + DBName + " already exists")
- return
- }
- createDBCmd := exec.Command("createdb", "-h", HostIP, "-p", HostPort, "-U", DBSuperUser, "-e", "--owner", DBUser, DBName)
- out, err = createDBCmd.CombinedOutput()
- fmt.Printf("%s", out)
- if err != nil {
- die("Can't create db " + DBName)
+func connectAndExecute(query string, queryErrPrefix string, returnRows bool) *sql.Rows {
+ //todo prepared statements
+ //todo inspect results of executing the above query for all possible cases (db exists, db dne, db inaccessible)
+ //todo determine test coverage of this portion
+ if db, err := dburl.Open(fmt.Sprintf("pg://%s:%s@%s:%s/", DBSuperUser, DBPassword, HostIP, HostPort)); err == nil {
+ if !returnRows {
+ if _, err := db.Exec(query); err != nil {
Review comment:
If it's preferred I could parse in the DML/DDL files and break them down into individual queries
----------------------------------------------------------------
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] MylesBock commented on a change in pull request #5327: Remove use of `psql` from `traffic_ops/db/admin`
Posted by GitBox <gi...@apache.org>.
MylesBock commented on a change in pull request #5327:
URL: https://github.com/apache/trafficcontrol/pull/5327#discussion_r529784550
##########
File path: traffic_ops/app/db/admin.go
##########
@@ -171,68 +173,73 @@ func parseDBConfig() error {
return nil
}
-func createDB() {
- dbExistsCmd := exec.Command("psql", "-h", HostIP, "-U", DBSuperUser, "-p", HostPort, "-tAc", "SELECT 1 FROM pg_database WHERE datname='"+DBName+"'")
- out, err := dbExistsCmd.Output()
- if err != nil {
- die("unable to check if DB already exists: " + err.Error())
- }
- if len(out) > 0 {
- fmt.Println("Database " + DBName + " already exists")
- return
- }
- createDBCmd := exec.Command("createdb", "-h", HostIP, "-p", HostPort, "-U", DBSuperUser, "-e", "--owner", DBUser, DBName)
- out, err = createDBCmd.CombinedOutput()
- fmt.Printf("%s", out)
- if err != nil {
- die("Can't create db " + DBName)
+func connectAndExecute(query string, queryErrPrefix string, returnRows bool) *sql.Rows {
+ //todo prepared statements
+ //todo inspect results of executing the above query for all possible cases (db exists, db dne, db inaccessible)
+ //todo determine test coverage of this portion
+ if db, err := dburl.Open(fmt.Sprintf("pg://%s:%s@%s:%s/", DBSuperUser, DBPassword, HostIP, HostPort)); err == nil {
+ if !returnRows {
+ if _, err := db.Exec(query); err != nil {
Review comment:
Correct, but in this case it's reading an entire file in for things like patch, seed, create and executing it all at once
This has come up apparently in a few discussions on `lib/pq` and other repositories related to golang and postgres, and one project offers a solution (https://github.com/gchaincl/dotsql) but it requires more effort
----------------------------------------------------------------
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] MylesBock commented on pull request #5327: Remove use of `psql` from `traffic_ops/db/admin`
Posted by GitBox <gi...@apache.org>.
MylesBock commented on pull request #5327:
URL: https://github.com/apache/trafficcontrol/pull/5327#issuecomment-733139985
Converted to draft, need to figure out what's up with CIAB starting up
----------------------------------------------------------------
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