This is a simplified version of what we have for in-Sourcegraph db
connections as well. They're not directly compatible because we use
`pgx` hooks.
## Test plan
n/a
During manual testing for https://github.com/sourcegraph/sourcegraph/pull/62934, I started realizing that I would run into database errors:
```
failed to deallocate cached statement(s): conn busy
```
Turns out, `pgx.Conn` is meant for non-concurrent use. What we really want is a connection pool, with an off-the-shelf offering from `pgxpool`.
## Test plan
Integration tests pass, now with more cases using `t.Parallel()`. Also ran a quick sanity check by hand:
```
sg start
```
```
for i in {1..10}; do curl --header "Content-Type: application/json" --header 'authorization: bearer $SAMS_TOKEN' --data '{"filters":[{"filter":{"is_archived":false}}]}' \
http://localhost:6081/enterpriseportal.subscriptions.v1.SubscriptionsService/ListEnterpriseSubscriptionLicenses & ; done
```
## Changelog
- The MSP runtime `lib/managedservicesplatform/contract.Contract`'s `ConnectToDatabase(...)` has been renamed to `GetConnectionPool(...)`, and now returns a `*pgxpool.Pool` instead of a `*pgx.Conn`
- The MSP runtime `lib/managedservicesplatform/cloudsql` helper library's `Connect(...)` has been renamed to `GetConnectionPool(...)`, and now returns a `*pgxpool.Pool` instead of a `*pgx.Conn`
Part of CORE-112. We need to implement the `CodyAccess` service proposed in https://github.com/sourcegraph/sourcegraph/pull/62263, so that Cody Gateway can depend on it as we start a transition over to Enterprise Portal as the source-or-truth for Cody Gateway access; see the [Linear project](https://linear.app/sourcegraph/project/kr-launch-enterprise-portal-for-cody-gateway-and-cody-analytics-ee5d9ea105c2/overview). This PR implements the data layer by reading directly from the Sourcegraph.com Cloud SQL database, and a subsequent PR https://github.com/sourcegraph/sourcegraph/pull/62771 will expose this via the API and also implement auth; nothing in this PR is used yet.
Most things in this PR will be undone by the end of a [follow-up project](https://linear.app/sourcegraph/project/kr-enterprise-portal-manages-all-enterprise-subscriptions-12f1d5047bd2/overview) tentatively slated for completion by end-of-August.
### Query
I've opted to write a new query specifically to fetch the data required to fulfill the proposed `CodyAccess` RPCs; the existing queries fetch a lot more than is strictly needed, and often make multiple round trips to the database. The new query fetches everything it needs for get/list in a single round trip.
`EXPLAIN ANALYZE` of the new list-all query against the Sourcegraph.com production database indicates this is likely performant enough for our internal-only use cases, especially as this will only be around for a few months.
```
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=1610.56..1629.45 rows=1511 width=121) (actual time=23.358..24.921 rows=1512 loops=1)
Group Key: ps.id
-> Hash Left Join (cost=958.18..1585.58 rows=1999 width=1094) (actual time=8.258..12.255 rows=2748 loops=1)
Hash Cond: (ps.id = active_license.product_subscription_id)
-> Hash Right Join (cost=67.00..689.14 rows=1999 width=956) (actual time=1.098..3.970 rows=2748 loops=1)
Hash Cond: (product_licenses.product_subscription_id = ps.id)
-> Seq Scan on product_licenses (cost=0.00..616.88 rows=1999 width=919) (actual time=0.015..1.769 rows=2002 loops=1)
Filter: (access_token_enabled IS TRUE)
Rows Removed by Filter: 1789
-> Hash (cost=48.11..48.11 rows=1511 width=53) (actual time=1.055..1.056 rows=1512 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 93kB
-> Seq Scan on product_subscriptions ps (cost=0.00..48.11 rows=1511 width=53) (actual time=0.016..0.552 rows=1512 loops=1)
-> Hash (cost=874.39..874.39 rows=1343 width=154) (actual time=7.123..7.125 rows=1343 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 248kB
-> Subquery Scan on active_license (cost=842.02..874.39 rows=1343 width=154) (actual time=5.425..6.461 rows=1343 loops=1)
-> Unique (cost=842.02..860.96 rows=1343 width=162) (actual time=5.422..6.268 rows=1343 loops=1)
-> Sort (cost=842.02..851.49 rows=3788 width=162) (actual time=5.421..5.719 rows=3791 loops=1)
Sort Key: product_licenses_1.product_subscription_id, product_licenses_1.created_at DESC
Sort Method: quicksort Memory: 1059kB
-> Seq Scan on product_licenses product_licenses_1 (cost=0.00..616.88 rows=3788 width=162) (actual time=0.003..1.872 rows=3791 loops=1)
Planning Time: 2.266 ms
Execution Time: 28.568 ms
```
We noted the lack of index on `product_livenses.subscription_id`, but it doesn't seem to be an issue at this scale, so I've left it as is.
### Pagination
After discussing with Erik, we decided there is no need to implement pagination for the list-all RPC yet; a rough upper bound of 1kb per subscription * 1511 rows (see `EXPLAIN ANALYZE` above) is 1.5MB, which is well below the per-message limits we have set for Sourcegraph-internal traffic (40MB), and below the [default 4MB limit](https://pkg.go.dev/google.golang.org/grpc#MaxRecvMsgSize) as well. In https://github.com/sourcegraph/sourcegraph/pull/62771 providing pagination parameters will result in a `CodeUnimplemented` error.
We can figure out how we want to implement pagination as part of the [follow-up project](https://linear.app/sourcegraph/project/kr-enterprise-portal-manages-all-enterprise-subscriptions-12f1d5047bd2/overview) to migrate the data to an Enterprise-Portal-owned database.
### Testing
A good chunk of this PR's changes are exposing a small set of `cmd/frontend` internals **for testing** via the new `cmd/frontend/dotcomproductsubscriptiontest`:
- seeding test databases with subscriptions and licenses
- for "regression testing" the new read queries by validating what the new read queries get, against what the existing GraphQL resolvers resolve to. This is important because the GraphQL resolvers has a lot of the override logic
See `TestGetCodyGatewayAccessAttributes` for how all this is used.
<img width="799" alt="image" src="https://github.com/sourcegraph/sourcegraph/assets/23356519/af4d0c1e-c9a9-448a-9b8e-0f328688a75a">
There is also some hackery involved in setting up a `pgx/v5` connection used in MSP from the `sql.DB` + `pgx/v4` stuff used by `dbtest`; see `newTestDotcomReader` docstrings for details.
## Test plan
```
go test -v ./cmd/enterprise-portal/internal/dotcomdb
```
---
Co-authored-by: Chris Smith <chrsmith@users.noreply.github.com>
Co-authored-by: Joe Chen <joe@sourcegraph.com>
Part of https://linear.app/sourcegraph/issue/CORE-96 - we want to be able to use the same Cloud SQL connection mechanism we use for MSP-provisioned databases to be able to connect to a database in another project (in this case, the database of interest is the Sourcegraph.com database).
Also see #62525
## Test plan
CI - this is a refactor only
