[GitHub] [trafficcontrol] rawlinp opened a new pull request #5792: Provide initial groundwork implementation for PostgreSQL Traffic Vault

[GitBox <gi...@apache.org>]

rawlinp opened a new pull request #5792:
URL: https://github.com/apache/trafficcontrol/pull/5792


   ## What does this PR (Pull Request) do?
   Add a Postgres Traffic Vault backend and lay the groundwork for its
   implementation, only actually implementing the `Ping` method and leaving
   the rest unimplemented for now.
   
   ## Which Traffic Control components are affected by this PR?
   - Documentation
   - Traffic Control Client (Go)
   - Traffic Ops
   - Traffic Vault
   
   ## What is the best way to verify this PR?
   Use the `db/admin` tool with the `--trafficvault` option to set up a new PostgreSQL database for Traffic Vault. Configure `cdn.conf` using the new provided instructions in the documentation in order to use `"traffic_vault_backend": "postgres"`. Then, run the new `TrafficVaultPing` TO API test and/or request the `GET api/4.0/vault/ping` route manually.
   
   ## The following criteria are ALL met by this PR
   - [x] This PR includes tests
   - [x] This PR includes documentation
   - [x] This PR includes 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)
   


-- 
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] zrhoffman commented on a change in pull request #5792: Provide initial groundwork implementation for PostgreSQL Traffic Vault

[GitBox <gi...@apache.org>]

zrhoffman commented on a change in pull request #5792:
URL: https://github.com/apache/trafficcontrol/pull/5792#discussion_r622192793



##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: executing ping query", err, dbCtx.Err())
+		return tc.TrafficVaultPing{}, e
+	}
+	return tc.TrafficVaultPing{Status: "OK", Server: p.cfg.Hostname + ":" + strconv.Itoa(p.cfg.Port)}, nil
+}
+
+func (p *Postgres) GetBucketKey(bucket string, key string, tx *sql.Tx) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func init() {
+	trafficvault.AddBackend(postgresBackendName, postgresLoad)
+}
+
+func postgresLoad(b json.RawMessage) (trafficvault.TrafficVault, error) {
+	pgCfg := Config{}
+	if err := json.Unmarshal(b, &pgCfg); err != nil {
+		return nil, errors.New("unmarshalling Postgres config: " + err.Error())
+	}
+	if err := validateConfig(pgCfg); err != nil {
+		return nil, errors.New("validating Postgres config: " + err.Error())
+	}
+	if pgCfg.MaxIdleConnections == 0 {
+		pgCfg.MaxIdleConnections = defaultMaxIdleConnections
+	}
+	if pgCfg.ConnMaxLifetimeSeconds == 0 {
+		pgCfg.ConnMaxLifetimeSeconds = defaultConnMaxLifetimeSeconds
+	}
+	if pgCfg.QueryTimeoutSeconds == 0 {
+		pgCfg.QueryTimeoutSeconds = defaultDBQueryTimeoutSecs
+	}

Review comment:
       Instead of checking default values afterwards, can the defaults can be added to the initial value of `pgCfg` pre-unmarshal?

##########
File path: traffic_ops/traffic_ops_golang/trafficvault/trafficvault.go
##########
@@ -32,51 +33,57 @@ import (
 // TrafficVault defines the methods necessary for a struct to implement in order to
 // provide all the necessary functionality required of a Traffic Vault backend.
 type TrafficVault interface {
+	// NOTE: the ctx context.Context in these methods is for the HTTP request context in order to cancel the request
+	// if the HTTP connection is closed. If the method is called asynchronously in a goroutine that is spawned while
+	// handling the original HTTP request, you should use context.Background() so that the context isn't cancelled
+	// when the original HTTP connection is closed.
+
 	// GetDeliveryServiceSSLKeys retrieves the SSL keys of the given version for
 	// the delivery service identified by the given xmlID. If version is empty,
 	// the implementation should return the latest version.
-	GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx) (tc.DeliveryServiceSSLKeysV15, bool, error)
+	GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error)
 	// PutDeliveryServiceSSLKeys stores the given SSL keys for a delivery service.
-	PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx) error
+	PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error
 	// DeleteDeliveryServiceSSLKeys removes the SSL keys of the given version (or latest
 	// if version is empty) for the delivery service identified by the given xmlID.
-	DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx) error
+	DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error
 	// DeleteOldDeliveryServiceSSLKeys takes a set of existingXMLIDs as input and will remove
 	// all SSL keys for delivery services in the CDN identified by the given cdnName that
 	// do not contain an xmlID in the given set of existingXMLIDs. This method is called
 	// during a snapshot operation in order to delete SSL keys for delivery services that
 	// no longer exist.
-	DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx) error
+	DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error
 	// GetCDNSSLKeys retrieves all the SSL keys for delivery services in the CDN identified
 	// by the given cdnName.
-	GetCDNSSLKeys(cdnName string, tx *sql.Tx) ([]tc.CDNSSLKey, error)
+	GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error)
 	// GetDNSSECKeys retrieves all the DNSSEC keys associated with the CDN identified by the
 	// given cdnName.
-	GetDNSSECKeys(cdnName string, tx *sql.Tx) (tc.DNSSECKeysTrafficVault, bool, error)
+	GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error)
 	// PutDNSSECKeys stores all the DNSSEC keys for the CDN identified by the given cdnName.
-	PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx) error
+	PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error
 	// DeleteDNSSECKeys removes all the DNSSEC keys for the CDN identified by the given cdnName.
-	DeleteDNSSECKeys(cdnName string, tx *sql.Tx) error
+	DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error
 	// GetURLSigKeys retrieves the URL sig keys for the delivery service identified by the
 	// given xmlID.
-	GetURLSigKeys(xmlID string, tx *sql.Tx) (tc.URLSigKeys, bool, error)
+	GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error)
 	// PutURLSigKeys stores the given URL sig keys for the delivery service identified by
 	// the given xmlID.
-	PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx) error
+	PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error
 	// GetURISigningKeys retrieves the URI signing keys (as raw JSON bytes) for the delivery
 	// service identified by the given xmlID.
-	GetURISigningKeys(xmlID string, tx *sql.Tx) ([]byte, bool, error)
+	GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error)
 	// PutURISigningKeys stores the given URI signing keys (as raw JSON bytes) for the delivery
 	// service identified by the given xmlID.
-	PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx) error
+	PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error
 	// DeleteURISigningKeys removes the URI signing keys for the delivery service identified by
 	// the given xmlID.
-	DeleteURISigningKeys(xmlID string, tx *sql.Tx) error
+	DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error
 	// Ping simply checks the health of the Traffic Vault backend, returning a status and which
 	// server hostname the status was returned by.
-	Ping(tx *sql.Tx) (tc.TrafficVaultPingResponse, error)
+	Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error)
 	// GetBucketKey returns the raw bytes identified by the given bucket and key. This may not
 	// apply to every Traffic Vault backend implementation.
+	// Deprecated: this method and associated API routes will be removed in the future.
 	GetBucketKey(bucket string, key string, tx *sql.Tx) ([]byte, bool, error)

Review comment:
       Since this PR adds this deprecation message to `TrafficVault.GetBucketKey()`, maybe this is also a good time to remove this reference to undefined label `to-api-vault-bucket-bucket-key-key-values`:
   
   https://github.com/apache/trafficcontrol/blob/6110455f75a7bd61cc22ff4a518319d1f021d03f/docs/source/api/migrating-from-v1.rst#L500-L502

##########
File path: traffic_ops/v4-client/vault.go
##########
@@ -0,0 +1,33 @@
+/*
+
+   Licensed under the Apache License, Version 2.0 (the "License");

Review comment:
       Nit: Empty line before the license is unnecessary

##########
File path: traffic_ops/v4-client/vault.go
##########
@@ -0,0 +1,33 @@
+/*
+
+   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.
+*/
+
+package client
+
+import (
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/traffic_ops/toclientlib"
+)
+
+const (
+	// APIPing is the full path to the /ping API endpoint.
+	APIVaultPing = "/vault/ping"

Review comment:
       Godoc should reference `APIVaultPing`

##########
File path: traffic_ops/app/db/trafficvault/migrations/2021042200000000_init.sql
##########
@@ -0,0 +1,22 @@
+/*
+
+    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
+-- SQL in section 'Up' is executed when this migration is applied
+SELECT 1;
+
+-- +goose Down
+-- SQL section 'Down' is executed when this migration is rolled back
+SELECT 1;

Review comment:
       This too can be just
   ```sql
   SELECT;
   ```

##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: executing ping query", err, dbCtx.Err())
+		return tc.TrafficVaultPing{}, e
+	}
+	return tc.TrafficVaultPing{Status: "OK", Server: p.cfg.Hostname + ":" + strconv.Itoa(p.cfg.Port)}, nil
+}
+
+func (p *Postgres) GetBucketKey(bucket string, key string, tx *sql.Tx) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func init() {
+	trafficvault.AddBackend(postgresBackendName, postgresLoad)
+}
+
+func postgresLoad(b json.RawMessage) (trafficvault.TrafficVault, error) {
+	pgCfg := Config{}
+	if err := json.Unmarshal(b, &pgCfg); err != nil {
+		return nil, errors.New("unmarshalling Postgres config: " + err.Error())
+	}
+	if err := validateConfig(pgCfg); err != nil {
+		return nil, errors.New("validating Postgres config: " + err.Error())
+	}
+	if pgCfg.MaxIdleConnections == 0 {
+		pgCfg.MaxIdleConnections = defaultMaxIdleConnections
+	}
+	if pgCfg.ConnMaxLifetimeSeconds == 0 {
+		pgCfg.ConnMaxLifetimeSeconds = defaultConnMaxLifetimeSeconds
+	}
+	if pgCfg.QueryTimeoutSeconds == 0 {
+		pgCfg.QueryTimeoutSeconds = defaultDBQueryTimeoutSecs
+	}
+
+	sslStr := "require"
+	if !pgCfg.SSL {
+		sslStr = "disable"
+	}
+	db, err := sqlx.Open("postgres", fmt.Sprintf("postgres://%s:%s@%s:%d/%s?sslmode=%s&fallback_application_name=trafficvault", pgCfg.User, pgCfg.Password, pgCfg.Hostname, pgCfg.Port, pgCfg.DBName, sslStr))
+	if err != nil {
+		return nil, errors.New("opening database: " + err.Error())
+	}
+	db.SetMaxOpenConns(pgCfg.MaxConnections)
+	db.SetMaxIdleConns(pgCfg.MaxIdleConnections)
+	db.SetConnMaxLifetime(time.Duration(pgCfg.ConnMaxLifetimeSeconds) * time.Second)
+
+	ctx, cancel := context.WithTimeout(context.Background(), time.Duration(pgCfg.QueryTimeoutSeconds)*time.Second)
+	defer cancel()
+	if err := db.PingContext(ctx); err != nil {
+		// NOTE: not fatal since Traffic Vault not being available at startup shouldn't be fatal
+		log.Errorln("pinging the Traffic Vault database: " + err.Error())
+	} else {
+		log.Infoln("successfully pinged the Traffic Vault database")
+	}
+
+	return &Postgres{cfg: pgCfg, db: db}, nil
+}
+
+func validateConfig(cfg Config) error {
+	errs := tovalidate.ToErrors(validation.Errors{
+		"user":                  validation.Validate(cfg.User, validation.Required),
+		"password":              validation.Validate(cfg.Password, validation.Required),
+		"hostname":              validation.Validate(cfg.Hostname, validation.Required),
+		"dbname":                validation.Validate(cfg.DBName, validation.Required),
+		"port":                  validation.Validate(cfg.Port, validation.By(tovalidate.IsValidPortNumber)),
+		"max_connections":       validation.Validate(cfg.MaxConnections, validation.Min(0)),
+		"query_timeout_seconds": validation.Validate(cfg.QueryTimeoutSeconds, validation.Min(0)),

Review comment:
       If the defaults are added to the initial value for `pgCfg` in `postgresLoad()`: https://github.com/apache/trafficcontrol/blob/6110455f75a7bd61cc22ff4a518319d1f021d03f/traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go#L178-L193
   then the minimum accepted value can be greater than `0` for `cfg.MaxConnections` and `cfg.QueryTimeoutSeconds`.
   
   https://github.com/apache/trafficcontrol/blob/6110455f75a7bd61cc22ff4a518319d1f021d03f/traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go#L185-L193

##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: executing ping query", err, dbCtx.Err())
+		return tc.TrafficVaultPing{}, e
+	}
+	return tc.TrafficVaultPing{Status: "OK", Server: p.cfg.Hostname + ":" + strconv.Itoa(p.cfg.Port)}, nil
+}
+
+func (p *Postgres) GetBucketKey(bucket string, key string, tx *sql.Tx) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func init() {
+	trafficvault.AddBackend(postgresBackendName, postgresLoad)
+}
+
+func postgresLoad(b json.RawMessage) (trafficvault.TrafficVault, error) {
+	pgCfg := Config{}
+	if err := json.Unmarshal(b, &pgCfg); err != nil {
+		return nil, errors.New("unmarshalling Postgres config: " + err.Error())
+	}
+	if err := validateConfig(pgCfg); err != nil {
+		return nil, errors.New("validating Postgres config: " + err.Error())
+	}
+	if pgCfg.MaxIdleConnections == 0 {
+		pgCfg.MaxIdleConnections = defaultMaxIdleConnections
+	}
+	if pgCfg.ConnMaxLifetimeSeconds == 0 {
+		pgCfg.ConnMaxLifetimeSeconds = defaultConnMaxLifetimeSeconds
+	}
+	if pgCfg.QueryTimeoutSeconds == 0 {
+		pgCfg.QueryTimeoutSeconds = defaultDBQueryTimeoutSecs
+	}

Review comment:
       Missing a default value for `pgCfg.MaxConnections`

##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {

Review comment:
       What is the purpose of scanning `1` to `n` here? Would `SELECT` with no `1` be sufficient to check connectivity?
   If it does scan `1` to `n`, this should check the value of `n` post-scan.

##########
File path: traffic_ops/app/db/trafficvault/migrations/2021042200000000_init.sql
##########
@@ -0,0 +1,22 @@
+/*
+
+    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
+-- SQL in section 'Up' is executed when this migration is applied
+SELECT 1;

Review comment:
       This can be just
   ```sql
   SELECT;
   ```

##########
File path: traffic_ops/v4-client/vault.go
##########
@@ -0,0 +1,33 @@
+/*
+
+   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.
+*/
+
+package client

Review comment:
       In most of our Go sources outside the TO client, the package name precedes the Apache license.

##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: executing ping query", err, dbCtx.Err())
+		return tc.TrafficVaultPing{}, e
+	}
+	return tc.TrafficVaultPing{Status: "OK", Server: p.cfg.Hostname + ":" + strconv.Itoa(p.cfg.Port)}, nil
+}
+
+func (p *Postgres) GetBucketKey(bucket string, key string, tx *sql.Tx) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func init() {
+	trafficvault.AddBackend(postgresBackendName, postgresLoad)
+}
+
+func postgresLoad(b json.RawMessage) (trafficvault.TrafficVault, error) {
+	pgCfg := Config{}
+	if err := json.Unmarshal(b, &pgCfg); err != nil {
+		return nil, errors.New("unmarshalling Postgres config: " + err.Error())
+	}
+	if err := validateConfig(pgCfg); err != nil {
+		return nil, errors.New("validating Postgres config: " + err.Error())
+	}
+	if pgCfg.MaxIdleConnections == 0 {
+		pgCfg.MaxIdleConnections = defaultMaxIdleConnections
+	}
+	if pgCfg.ConnMaxLifetimeSeconds == 0 {
+		pgCfg.ConnMaxLifetimeSeconds = defaultConnMaxLifetimeSeconds
+	}
+	if pgCfg.QueryTimeoutSeconds == 0 {
+		pgCfg.QueryTimeoutSeconds = defaultDBQueryTimeoutSecs
+	}
+
+	sslStr := "require"
+	if !pgCfg.SSL {
+		sslStr = "disable"
+	}
+	db, err := sqlx.Open("postgres", fmt.Sprintf("postgres://%s:%s@%s:%d/%s?sslmode=%s&fallback_application_name=trafficvault", pgCfg.User, pgCfg.Password, pgCfg.Hostname, pgCfg.Port, pgCfg.DBName, sslStr))
+	if err != nil {
+		return nil, errors.New("opening database: " + err.Error())
+	}
+	db.SetMaxOpenConns(pgCfg.MaxConnections)
+	db.SetMaxIdleConns(pgCfg.MaxIdleConnections)
+	db.SetConnMaxLifetime(time.Duration(pgCfg.ConnMaxLifetimeSeconds) * time.Second)
+
+	ctx, cancel := context.WithTimeout(context.Background(), time.Duration(pgCfg.QueryTimeoutSeconds)*time.Second)
+	defer cancel()
+	if err := db.PingContext(ctx); err != nil {
+		// NOTE: not fatal since Traffic Vault not being available at startup shouldn't be fatal
+		log.Errorln("pinging the Traffic Vault database: " + err.Error())
+	} else {
+		log.Infoln("successfully pinged the Traffic Vault database")
+	}
+
+	return &Postgres{cfg: pgCfg, db: db}, nil
+}
+
+func validateConfig(cfg Config) error {
+	errs := tovalidate.ToErrors(validation.Errors{
+		"user":                  validation.Validate(cfg.User, validation.Required),
+		"password":              validation.Validate(cfg.Password, validation.Required),
+		"hostname":              validation.Validate(cfg.Hostname, validation.Required),
+		"dbname":                validation.Validate(cfg.DBName, validation.Required),
+		"port":                  validation.Validate(cfg.Port, validation.By(tovalidate.IsValidPortNumber)),
+		"max_connections":       validation.Validate(cfg.MaxConnections, validation.Min(0)),
+		"query_timeout_seconds": validation.Validate(cfg.QueryTimeoutSeconds, validation.Min(0)),

Review comment:
       Missing validation for `cfg.MaxIdleConnections` and `cfg.ConnMaxLifetimeSeconds`




-- 
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] zrhoffman commented on a change in pull request #5792: Provide initial groundwork implementation for PostgreSQL Traffic Vault

[GitBox <gi...@apache.org>]

zrhoffman commented on a change in pull request #5792:
URL: https://github.com/apache/trafficcontrol/pull/5792#discussion_r622346074



##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {

Review comment:
       > I _think_ if you're not expecting any values you could do `Scan(nil)` safely.
   
   That yields
   ```go
   sql: Scan error on column index 0, name "?column?": destination not a pointer
   ```
   but you can do `Scan()`.
   
   




-- 
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] ocket8888 commented on a change in pull request #5792: Provide initial groundwork implementation for PostgreSQL Traffic Vault

[GitBox <gi...@apache.org>]

ocket8888 commented on a change in pull request #5792:
URL: https://github.com/apache/trafficcontrol/pull/5792#discussion_r622327895



##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {

Review comment:
       I assume he did that just because errors in the query are deferred until `Scan` is called when you use `QueryRow` and `Scan` expects a pointer argument. I _think_ if you're not expecting any values you could do `Scan(nil)` safely.




-- 
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 #5792: Provide initial groundwork implementation for PostgreSQL Traffic Vault

[GitBox <gi...@apache.org>]

rawlinp commented on pull request #5792:
URL: https://github.com/apache/trafficcontrol/pull/5792#issuecomment-828027315


   There is an issue w/ my added TO API test. It works for the Postgres backend locally but not Riak in CiaB due to the fact that Riak requires servers to be created first. I'll push a fix tomorrow.


-- 
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] zrhoffman commented on a change in pull request #5792: Provide initial groundwork implementation for PostgreSQL Traffic Vault

[GitBox <gi...@apache.org>]

zrhoffman commented on a change in pull request #5792:
URL: https://github.com/apache/trafficcontrol/pull/5792#discussion_r622206878



##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: executing ping query", err, dbCtx.Err())
+		return tc.TrafficVaultPing{}, e
+	}
+	return tc.TrafficVaultPing{Status: "OK", Server: p.cfg.Hostname + ":" + strconv.Itoa(p.cfg.Port)}, nil
+}
+
+func (p *Postgres) GetBucketKey(bucket string, key string, tx *sql.Tx) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func init() {
+	trafficvault.AddBackend(postgresBackendName, postgresLoad)
+}
+
+func postgresLoad(b json.RawMessage) (trafficvault.TrafficVault, error) {
+	pgCfg := Config{}
+	if err := json.Unmarshal(b, &pgCfg); err != nil {
+		return nil, errors.New("unmarshalling Postgres config: " + err.Error())
+	}
+	if err := validateConfig(pgCfg); err != nil {
+		return nil, errors.New("validating Postgres config: " + err.Error())
+	}
+	if pgCfg.MaxIdleConnections == 0 {
+		pgCfg.MaxIdleConnections = defaultMaxIdleConnections
+	}
+	if pgCfg.ConnMaxLifetimeSeconds == 0 {
+		pgCfg.ConnMaxLifetimeSeconds = defaultConnMaxLifetimeSeconds
+	}
+	if pgCfg.QueryTimeoutSeconds == 0 {
+		pgCfg.QueryTimeoutSeconds = defaultDBQueryTimeoutSecs
+	}
+
+	sslStr := "require"
+	if !pgCfg.SSL {
+		sslStr = "disable"
+	}
+	db, err := sqlx.Open("postgres", fmt.Sprintf("postgres://%s:%s@%s:%d/%s?sslmode=%s&fallback_application_name=trafficvault", pgCfg.User, pgCfg.Password, pgCfg.Hostname, pgCfg.Port, pgCfg.DBName, sslStr))
+	if err != nil {
+		return nil, errors.New("opening database: " + err.Error())
+	}
+	db.SetMaxOpenConns(pgCfg.MaxConnections)
+	db.SetMaxIdleConns(pgCfg.MaxIdleConnections)
+	db.SetConnMaxLifetime(time.Duration(pgCfg.ConnMaxLifetimeSeconds) * time.Second)
+
+	ctx, cancel := context.WithTimeout(context.Background(), time.Duration(pgCfg.QueryTimeoutSeconds)*time.Second)
+	defer cancel()
+	if err := db.PingContext(ctx); err != nil {
+		// NOTE: not fatal since Traffic Vault not being available at startup shouldn't be fatal
+		log.Errorln("pinging the Traffic Vault database: " + err.Error())
+	} else {
+		log.Infoln("successfully pinged the Traffic Vault database")
+	}
+
+	return &Postgres{cfg: pgCfg, db: db}, nil
+}
+
+func validateConfig(cfg Config) error {
+	errs := tovalidate.ToErrors(validation.Errors{
+		"user":                  validation.Validate(cfg.User, validation.Required),
+		"password":              validation.Validate(cfg.Password, validation.Required),
+		"hostname":              validation.Validate(cfg.Hostname, validation.Required),
+		"dbname":                validation.Validate(cfg.DBName, validation.Required),
+		"port":                  validation.Validate(cfg.Port, validation.By(tovalidate.IsValidPortNumber)),
+		"max_connections":       validation.Validate(cfg.MaxConnections, validation.Min(0)),
+		"query_timeout_seconds": validation.Validate(cfg.QueryTimeoutSeconds, validation.Min(0)),

Review comment:
       If the defaults are added to the initial value for `pgCfg` in `postgresLoad()`: https://github.com/apache/trafficcontrol/blob/6110455f75a7bd61cc22ff4a518319d1f021d03f/traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go#L178-L193
   then the minimum accepted value can be greater than `0` for `cfg.MaxConnections` and `cfg.QueryTimeoutSeconds`.




-- 
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] ocket8888 commented on a change in pull request #5792: Provide initial groundwork implementation for PostgreSQL Traffic Vault

[GitBox <gi...@apache.org>]

ocket8888 commented on a change in pull request #5792:
URL: https://github.com/apache/trafficcontrol/pull/5792#discussion_r622325932



##########
File path: traffic_ops/v4-client/vault.go
##########
@@ -0,0 +1,33 @@
+/*
+
+   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.
+*/
+
+package client

Review comment:
       And fwiw I'm changing the client to be consistent with that as I'm going through each file in #5747 




-- 
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 edited a comment on pull request #5792: Provide initial groundwork implementation for PostgreSQL Traffic Vault

[GitBox <gi...@apache.org>]

rawlinp edited a comment on pull request #5792:
URL: https://github.com/apache/trafficcontrol/pull/5792#issuecomment-828027315


   There is an issue w/ my added TO API test. It works for the Postgres backend locally but not Riak in CiaB due to the fact that Riak requires servers to be created first. I'll push a fix ~tomorrow~.


-- 
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] zrhoffman merged pull request #5792: Provide initial groundwork implementation for PostgreSQL Traffic Vault

[GitBox <gi...@apache.org>]

zrhoffman merged pull request #5792:
URL: https://github.com/apache/trafficcontrol/pull/5792


   


-- 
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 #5792: Provide initial groundwork implementation for PostgreSQL Traffic Vault

[GitBox <gi...@apache.org>]

rawlinp commented on a change in pull request #5792:
URL: https://github.com/apache/trafficcontrol/pull/5792#discussion_r622553992



##########
File path: traffic_ops/v4-client/vault.go
##########
@@ -0,0 +1,33 @@
+/*
+
+   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.
+*/
+
+package client

Review comment:
       moved it

##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {

Review comment:
       I'll check the value of `1` just for sanity

##########
File path: traffic_ops/traffic_ops_golang/trafficvault/trafficvault.go
##########
@@ -32,51 +33,57 @@ import (
 // TrafficVault defines the methods necessary for a struct to implement in order to
 // provide all the necessary functionality required of a Traffic Vault backend.
 type TrafficVault interface {
+	// NOTE: the ctx context.Context in these methods is for the HTTP request context in order to cancel the request
+	// if the HTTP connection is closed. If the method is called asynchronously in a goroutine that is spawned while
+	// handling the original HTTP request, you should use context.Background() so that the context isn't cancelled
+	// when the original HTTP connection is closed.
+
 	// GetDeliveryServiceSSLKeys retrieves the SSL keys of the given version for
 	// the delivery service identified by the given xmlID. If version is empty,
 	// the implementation should return the latest version.
-	GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx) (tc.DeliveryServiceSSLKeysV15, bool, error)
+	GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error)
 	// PutDeliveryServiceSSLKeys stores the given SSL keys for a delivery service.
-	PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx) error
+	PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error
 	// DeleteDeliveryServiceSSLKeys removes the SSL keys of the given version (or latest
 	// if version is empty) for the delivery service identified by the given xmlID.
-	DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx) error
+	DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error
 	// DeleteOldDeliveryServiceSSLKeys takes a set of existingXMLIDs as input and will remove
 	// all SSL keys for delivery services in the CDN identified by the given cdnName that
 	// do not contain an xmlID in the given set of existingXMLIDs. This method is called
 	// during a snapshot operation in order to delete SSL keys for delivery services that
 	// no longer exist.
-	DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx) error
+	DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error
 	// GetCDNSSLKeys retrieves all the SSL keys for delivery services in the CDN identified
 	// by the given cdnName.
-	GetCDNSSLKeys(cdnName string, tx *sql.Tx) ([]tc.CDNSSLKey, error)
+	GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error)
 	// GetDNSSECKeys retrieves all the DNSSEC keys associated with the CDN identified by the
 	// given cdnName.
-	GetDNSSECKeys(cdnName string, tx *sql.Tx) (tc.DNSSECKeysTrafficVault, bool, error)
+	GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error)
 	// PutDNSSECKeys stores all the DNSSEC keys for the CDN identified by the given cdnName.
-	PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx) error
+	PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error
 	// DeleteDNSSECKeys removes all the DNSSEC keys for the CDN identified by the given cdnName.
-	DeleteDNSSECKeys(cdnName string, tx *sql.Tx) error
+	DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error
 	// GetURLSigKeys retrieves the URL sig keys for the delivery service identified by the
 	// given xmlID.
-	GetURLSigKeys(xmlID string, tx *sql.Tx) (tc.URLSigKeys, bool, error)
+	GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error)
 	// PutURLSigKeys stores the given URL sig keys for the delivery service identified by
 	// the given xmlID.
-	PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx) error
+	PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error
 	// GetURISigningKeys retrieves the URI signing keys (as raw JSON bytes) for the delivery
 	// service identified by the given xmlID.
-	GetURISigningKeys(xmlID string, tx *sql.Tx) ([]byte, bool, error)
+	GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error)
 	// PutURISigningKeys stores the given URI signing keys (as raw JSON bytes) for the delivery
 	// service identified by the given xmlID.
-	PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx) error
+	PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error
 	// DeleteURISigningKeys removes the URI signing keys for the delivery service identified by
 	// the given xmlID.
-	DeleteURISigningKeys(xmlID string, tx *sql.Tx) error
+	DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error
 	// Ping simply checks the health of the Traffic Vault backend, returning a status and which
 	// server hostname the status was returned by.
-	Ping(tx *sql.Tx) (tc.TrafficVaultPingResponse, error)
+	Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error)
 	// GetBucketKey returns the raw bytes identified by the given bucket and key. This may not
 	// apply to every Traffic Vault backend implementation.
+	// Deprecated: this method and associated API routes will be removed in the future.
 	GetBucketKey(bucket string, key string, tx *sql.Tx) ([]byte, bool, error)

Review comment:
       Sure, I can do that

##########
File path: traffic_ops/v4-client/vault.go
##########
@@ -0,0 +1,33 @@
+/*
+
+   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.
+*/
+
+package client
+
+import (
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/traffic_ops/toclientlib"
+)
+
+const (
+	// APIPing is the full path to the /ping API endpoint.
+	APIVaultPing = "/vault/ping"

Review comment:
       copy pasta got me

##########
File path: traffic_ops/app/db/trafficvault/migrations/2021042200000000_init.sql
##########
@@ -0,0 +1,22 @@
+/*
+
+    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
+-- SQL in section 'Up' is executed when this migration is applied
+SELECT 1;

Review comment:
       Done

##########
File path: traffic_ops/app/db/trafficvault/migrations/2021042200000000_init.sql
##########
@@ -0,0 +1,22 @@
+/*
+
+    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
+-- SQL in section 'Up' is executed when this migration is applied
+SELECT 1;
+
+-- +goose Down
+-- SQL section 'Down' is executed when this migration is rolled back
+SELECT 1;

Review comment:
       Done

##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: executing ping query", err, dbCtx.Err())
+		return tc.TrafficVaultPing{}, e
+	}
+	return tc.TrafficVaultPing{Status: "OK", Server: p.cfg.Hostname + ":" + strconv.Itoa(p.cfg.Port)}, nil
+}
+
+func (p *Postgres) GetBucketKey(bucket string, key string, tx *sql.Tx) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func init() {
+	trafficvault.AddBackend(postgresBackendName, postgresLoad)
+}
+
+func postgresLoad(b json.RawMessage) (trafficvault.TrafficVault, error) {
+	pgCfg := Config{}
+	if err := json.Unmarshal(b, &pgCfg); err != nil {
+		return nil, errors.New("unmarshalling Postgres config: " + err.Error())
+	}
+	if err := validateConfig(pgCfg); err != nil {
+		return nil, errors.New("validating Postgres config: " + err.Error())
+	}
+	if pgCfg.MaxIdleConnections == 0 {
+		pgCfg.MaxIdleConnections = defaultMaxIdleConnections
+	}
+	if pgCfg.ConnMaxLifetimeSeconds == 0 {
+		pgCfg.ConnMaxLifetimeSeconds = defaultConnMaxLifetimeSeconds
+	}
+	if pgCfg.QueryTimeoutSeconds == 0 {
+		pgCfg.QueryTimeoutSeconds = defaultDBQueryTimeoutSecs
+	}

Review comment:
       If we did that, we'd still have to check and set the defaults after, because unset or _explicitly set to 0_ values would still set it back to 0 at which point we'd need to set it back to the defaults.

##########
File path: traffic_ops/v4-client/vault.go
##########
@@ -0,0 +1,33 @@
+/*
+
+   Licensed under the Apache License, Version 2.0 (the "License");

Review comment:
       removed

##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: executing ping query", err, dbCtx.Err())
+		return tc.TrafficVaultPing{}, e
+	}
+	return tc.TrafficVaultPing{Status: "OK", Server: p.cfg.Hostname + ":" + strconv.Itoa(p.cfg.Port)}, nil
+}
+
+func (p *Postgres) GetBucketKey(bucket string, key string, tx *sql.Tx) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func init() {
+	trafficvault.AddBackend(postgresBackendName, postgresLoad)
+}
+
+func postgresLoad(b json.RawMessage) (trafficvault.TrafficVault, error) {
+	pgCfg := Config{}
+	if err := json.Unmarshal(b, &pgCfg); err != nil {
+		return nil, errors.New("unmarshalling Postgres config: " + err.Error())
+	}
+	if err := validateConfig(pgCfg); err != nil {
+		return nil, errors.New("validating Postgres config: " + err.Error())
+	}
+	if pgCfg.MaxIdleConnections == 0 {
+		pgCfg.MaxIdleConnections = defaultMaxIdleConnections
+	}
+	if pgCfg.ConnMaxLifetimeSeconds == 0 {
+		pgCfg.ConnMaxLifetimeSeconds = defaultConnMaxLifetimeSeconds
+	}
+	if pgCfg.QueryTimeoutSeconds == 0 {
+		pgCfg.QueryTimeoutSeconds = defaultDBQueryTimeoutSecs
+	}
+
+	sslStr := "require"
+	if !pgCfg.SSL {
+		sslStr = "disable"
+	}
+	db, err := sqlx.Open("postgres", fmt.Sprintf("postgres://%s:%s@%s:%d/%s?sslmode=%s&fallback_application_name=trafficvault", pgCfg.User, pgCfg.Password, pgCfg.Hostname, pgCfg.Port, pgCfg.DBName, sslStr))
+	if err != nil {
+		return nil, errors.New("opening database: " + err.Error())
+	}
+	db.SetMaxOpenConns(pgCfg.MaxConnections)
+	db.SetMaxIdleConns(pgCfg.MaxIdleConnections)
+	db.SetConnMaxLifetime(time.Duration(pgCfg.ConnMaxLifetimeSeconds) * time.Second)
+
+	ctx, cancel := context.WithTimeout(context.Background(), time.Duration(pgCfg.QueryTimeoutSeconds)*time.Second)
+	defer cancel()
+	if err := db.PingContext(ctx); err != nil {
+		// NOTE: not fatal since Traffic Vault not being available at startup shouldn't be fatal
+		log.Errorln("pinging the Traffic Vault database: " + err.Error())
+	} else {
+		log.Infoln("successfully pinged the Traffic Vault database")
+	}
+
+	return &Postgres{cfg: pgCfg, db: db}, nil
+}
+
+func validateConfig(cfg Config) error {
+	errs := tovalidate.ToErrors(validation.Errors{
+		"user":                  validation.Validate(cfg.User, validation.Required),
+		"password":              validation.Validate(cfg.Password, validation.Required),
+		"hostname":              validation.Validate(cfg.Hostname, validation.Required),
+		"dbname":                validation.Validate(cfg.DBName, validation.Required),
+		"port":                  validation.Validate(cfg.Port, validation.By(tovalidate.IsValidPortNumber)),
+		"max_connections":       validation.Validate(cfg.MaxConnections, validation.Min(0)),
+		"query_timeout_seconds": validation.Validate(cfg.QueryTimeoutSeconds, validation.Min(0)),

Review comment:
       This is on purpose, because any integer is valid for those fields. N <= 0 means no idle connections are retained and connections are not closed due to a connection's age, respectively. N > 0 are all valid.

##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: executing ping query", err, dbCtx.Err())
+		return tc.TrafficVaultPing{}, e
+	}
+	return tc.TrafficVaultPing{Status: "OK", Server: p.cfg.Hostname + ":" + strconv.Itoa(p.cfg.Port)}, nil
+}
+
+func (p *Postgres) GetBucketKey(bucket string, key string, tx *sql.Tx) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func init() {
+	trafficvault.AddBackend(postgresBackendName, postgresLoad)
+}
+
+func postgresLoad(b json.RawMessage) (trafficvault.TrafficVault, error) {
+	pgCfg := Config{}
+	if err := json.Unmarshal(b, &pgCfg); err != nil {
+		return nil, errors.New("unmarshalling Postgres config: " + err.Error())
+	}
+	if err := validateConfig(pgCfg); err != nil {
+		return nil, errors.New("validating Postgres config: " + err.Error())
+	}
+	if pgCfg.MaxIdleConnections == 0 {
+		pgCfg.MaxIdleConnections = defaultMaxIdleConnections
+	}
+	if pgCfg.ConnMaxLifetimeSeconds == 0 {
+		pgCfg.ConnMaxLifetimeSeconds = defaultConnMaxLifetimeSeconds
+	}
+	if pgCfg.QueryTimeoutSeconds == 0 {
+		pgCfg.QueryTimeoutSeconds = defaultDBQueryTimeoutSecs
+	}

Review comment:
       This is on purpose -- the default is `0`, which means unlimited.

##########
File path: traffic_ops/traffic_ops_golang/trafficvault/backends/postgres/postgres.go
##########
@@ -0,0 +1,233 @@
+// Package postgres provides a TrafficVault implementation which uses PostgreSQL as the backend.
+package postgres
+
+/*
+ * 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.
+ */
+
+import (
+	"context"
+	"database/sql"
+	"encoding/json"
+	"errors"
+	"fmt"
+	"strconv"
+	"time"
+
+	"github.com/apache/trafficcontrol/lib/go-log"
+	"github.com/apache/trafficcontrol/lib/go-tc"
+	"github.com/apache/trafficcontrol/lib/go-tc/tovalidate"
+	"github.com/apache/trafficcontrol/lib/go-util"
+	"github.com/apache/trafficcontrol/traffic_ops/traffic_ops_golang/trafficvault"
+
+	validation "github.com/go-ozzo/ozzo-validation"
+	"github.com/jmoiron/sqlx"
+)
+
+type Error string
+
+func (e Error) Error() string {
+	return string(e)
+}
+
+const (
+	notImplementedErr = Error("this Traffic Vault functionality is not implemented for the postgres backend")
+
+	postgresBackendName = "postgres"
+
+	defaultMaxIdleConnections     = 10 // if this is higher than MaxDBConnections it will be automatically adjusted below it by the db/sql library
+	defaultConnMaxLifetimeSeconds = 60
+	defaultDBQueryTimeoutSecs     = 30
+)
+
+type Config struct {
+	DBName                 string `json:"dbname"`
+	Hostname               string `json:"hostname"`
+	User                   string `json:"user"`
+	Password               string `json:"password"`
+	Port                   int    `json:"port"`
+	SSL                    bool   `json:"ssl"`
+	MaxConnections         int    `json:"max_connections"`
+	MaxIdleConnections     int    `json:"max_idle_connections"`
+	ConnMaxLifetimeSeconds int    `json:"conn_max_lifetime_seconds"`
+	QueryTimeoutSeconds    int    `json:"query_timeout_seconds"`
+}
+
+type Postgres struct {
+	cfg Config
+	db  *sqlx.DB
+}
+
+func checkErrWithContext(prefix string, err error, ctxErr error) error {
+	e := prefix + err.Error()
+	if ctxErr != nil {
+		e = fmt.Sprintf("%s: %s: %s", prefix, ctxErr.Error(), err.Error())
+	}
+	return errors.New(e)
+}
+
+func (p *Postgres) beginTransaction(ctx context.Context) (*sqlx.Tx, context.Context, context.CancelFunc, error) {
+	dbCtx, cancelFunc := context.WithTimeout(ctx, time.Duration(p.cfg.QueryTimeoutSeconds)*time.Second)
+	tx, err := p.db.BeginTxx(dbCtx, nil)
+	if err != nil {
+		e := checkErrWithContext("could not begin Traffic Vault PostgreSQL transaction", err, ctx.Err())
+		cancelFunc()
+		return nil, nil, nil, e
+	}
+	return tx, dbCtx, cancelFunc, nil
+}
+
+func (p *Postgres) commitTransaction(tx *sqlx.Tx, ctx context.Context, cancelFunc context.CancelFunc) {
+	if err := tx.Commit(); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: committing transaction", err, ctx.Err())
+		log.Errorln(e)
+	}
+	cancelFunc()
+}
+
+func (p *Postgres) GetDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) (tc.DeliveryServiceSSLKeysV15, bool, error) {
+	return tc.DeliveryServiceSSLKeysV15{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDeliveryServiceSSLKeys(key tc.DeliveryServiceSSLKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDeliveryServiceSSLKeys(xmlID string, version string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteOldDeliveryServiceSSLKeys(existingXMLIDs map[string]struct{}, cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetCDNSSLKeys(cdnName string, tx *sql.Tx, ctx context.Context) ([]tc.CDNSSLKey, error) {
+	return nil, notImplementedErr
+}
+
+func (p *Postgres) GetDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) (tc.DNSSECKeysTrafficVault, bool, error) {
+	return tc.DNSSECKeysTrafficVault{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutDNSSECKeys(cdnName string, keys tc.DNSSECKeysTrafficVault, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteDNSSECKeys(cdnName string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURLSigKeys(xmlID string, tx *sql.Tx, ctx context.Context) (tc.URLSigKeys, bool, error) {
+	return tc.URLSigKeys{}, false, notImplementedErr
+}
+
+func (p *Postgres) PutURLSigKeys(xmlID string, keys tc.URLSigKeys, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) GetURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func (p *Postgres) PutURISigningKeys(xmlID string, keysJson []byte, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) DeleteURISigningKeys(xmlID string, tx *sql.Tx, ctx context.Context) error {
+	return notImplementedErr
+}
+
+func (p *Postgres) Ping(tx *sql.Tx, ctx context.Context) (tc.TrafficVaultPing, error) {
+	tvTx, dbCtx, cancelFunc, err := p.beginTransaction(ctx)
+	if err != nil {
+		return tc.TrafficVaultPing{}, err
+	}
+	defer p.commitTransaction(tvTx, dbCtx, cancelFunc)
+	n := 0
+	if err := tvTx.QueryRow("SELECT 1").Scan(&n); err != nil {
+		e := checkErrWithContext("Traffic Vault PostgreSQL: executing ping query", err, dbCtx.Err())
+		return tc.TrafficVaultPing{}, e
+	}
+	return tc.TrafficVaultPing{Status: "OK", Server: p.cfg.Hostname + ":" + strconv.Itoa(p.cfg.Port)}, nil
+}
+
+func (p *Postgres) GetBucketKey(bucket string, key string, tx *sql.Tx) ([]byte, bool, error) {
+	return nil, false, notImplementedErr
+}
+
+func init() {
+	trafficvault.AddBackend(postgresBackendName, postgresLoad)
+}
+
+func postgresLoad(b json.RawMessage) (trafficvault.TrafficVault, error) {
+	pgCfg := Config{}
+	if err := json.Unmarshal(b, &pgCfg); err != nil {
+		return nil, errors.New("unmarshalling Postgres config: " + err.Error())
+	}
+	if err := validateConfig(pgCfg); err != nil {
+		return nil, errors.New("validating Postgres config: " + err.Error())
+	}
+	if pgCfg.MaxIdleConnections == 0 {
+		pgCfg.MaxIdleConnections = defaultMaxIdleConnections
+	}
+	if pgCfg.ConnMaxLifetimeSeconds == 0 {
+		pgCfg.ConnMaxLifetimeSeconds = defaultConnMaxLifetimeSeconds
+	}
+	if pgCfg.QueryTimeoutSeconds == 0 {
+		pgCfg.QueryTimeoutSeconds = defaultDBQueryTimeoutSecs
+	}
+
+	sslStr := "require"
+	if !pgCfg.SSL {
+		sslStr = "disable"
+	}
+	db, err := sqlx.Open("postgres", fmt.Sprintf("postgres://%s:%s@%s:%d/%s?sslmode=%s&fallback_application_name=trafficvault", pgCfg.User, pgCfg.Password, pgCfg.Hostname, pgCfg.Port, pgCfg.DBName, sslStr))
+	if err != nil {
+		return nil, errors.New("opening database: " + err.Error())
+	}
+	db.SetMaxOpenConns(pgCfg.MaxConnections)
+	db.SetMaxIdleConns(pgCfg.MaxIdleConnections)
+	db.SetConnMaxLifetime(time.Duration(pgCfg.ConnMaxLifetimeSeconds) * time.Second)
+
+	ctx, cancel := context.WithTimeout(context.Background(), time.Duration(pgCfg.QueryTimeoutSeconds)*time.Second)
+	defer cancel()
+	if err := db.PingContext(ctx); err != nil {
+		// NOTE: not fatal since Traffic Vault not being available at startup shouldn't be fatal
+		log.Errorln("pinging the Traffic Vault database: " + err.Error())
+	} else {
+		log.Infoln("successfully pinged the Traffic Vault database")
+	}
+
+	return &Postgres{cfg: pgCfg, db: db}, nil
+}
+
+func validateConfig(cfg Config) error {
+	errs := tovalidate.ToErrors(validation.Errors{
+		"user":                  validation.Validate(cfg.User, validation.Required),
+		"password":              validation.Validate(cfg.Password, validation.Required),
+		"hostname":              validation.Validate(cfg.Hostname, validation.Required),
+		"dbname":                validation.Validate(cfg.DBName, validation.Required),
+		"port":                  validation.Validate(cfg.Port, validation.By(tovalidate.IsValidPortNumber)),
+		"max_connections":       validation.Validate(cfg.MaxConnections, validation.Min(0)),
+		"query_timeout_seconds": validation.Validate(cfg.QueryTimeoutSeconds, validation.Min(0)),

Review comment:
       > If the defaults are added to the initial value for pgCfg in postgresLoad():
   
   Per my earlier comment, I don't think we should do that because we'd still want to set the defaults `if 0` after




-- 
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