mirror of
https://github.com/hanzoai/ca.git
synced 2026-11-03 18:08:27 +00:00
194 lines
6.9 KiB
Go
194 lines
6.9 KiB
Go
package query_test
|
|
|
|
import (
|
|
"testing"
|
|
|
|
"github.com/effective-security/trustyca/api/pb"
|
|
"github.com/effective-security/trustyca/internal/db/pgsql/query"
|
|
"github.com/effective-security/trustyca/internal/db/schema"
|
|
"github.com/effective-security/xdb"
|
|
"github.com/stretchr/testify/require"
|
|
)
|
|
|
|
func Test_IssuerQuery(t *testing.T) {
|
|
t.Parallel()
|
|
|
|
cursor, err := query.ParseCursor(query.EncodeCursor(xdb.NewID(5)))
|
|
require.NoError(t, err)
|
|
|
|
check(t, []tcase{
|
|
{
|
|
// the counts of the summary: the org, a project, the visible projects
|
|
query.CountIssuers, []any{&query.CountIssuersRequest{OrgID: 1}},
|
|
`SELECT status, COUNT(*)
|
|
FROM trustyca.issuer
|
|
WHERE org_id = $1
|
|
GROUP BY status`,
|
|
},
|
|
{
|
|
query.CountIssuers, []any{&query.CountIssuersRequest{OrgID: 1, ProjectID: 2, ProjectIDs: []uint64{3}}},
|
|
`SELECT status, COUNT(*)
|
|
FROM trustyca.issuer
|
|
WHERE org_id = $1 AND project_id = $2
|
|
GROUP BY status`,
|
|
},
|
|
{
|
|
query.CountIssuers, []any{&query.CountIssuersRequest{OrgID: 1, ProjectIDs: []uint64{}}},
|
|
`SELECT status, COUNT(*)
|
|
FROM trustyca.issuer
|
|
WHERE org_id = $1 AND (project_id IS NULL OR project_id = ANY($2))
|
|
GROUP BY status`,
|
|
},
|
|
{
|
|
query.RegisterIssuer, nil,
|
|
`INSERT INTO trustyca.issuer
|
|
( id, org_id, project_id, label, type, status, parent_id, skid, ikid, serial_number, subject, issuer, sha256, not_before, not_after, pem, chain_pem, root_pem, csr_pem, key_provider, key_id, key_protected, config, crl_number, created_at, updated_at
|
|
) VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, Now(), Now()
|
|
)
|
|
RETURNING ` + schema.IssuerTableInfo.AllColumns(),
|
|
},
|
|
{
|
|
query.UpdateIssuer, []any{&query.UpdateIssuerRequest{ID: 1, Status: pb.IssuerStatus_Archived}},
|
|
`UPDATE trustyca.issuer
|
|
SET status=$1, updated_at=Now()
|
|
WHERE id = $2 AND org_id IS NOT DISTINCT FROM $3
|
|
RETURNING ` + schema.IssuerTableInfo.AllColumns(),
|
|
},
|
|
{
|
|
// Destroyed clears the protected key
|
|
query.UpdateIssuer, []any{&query.UpdateIssuerRequest{ID: 1, Status: pb.IssuerStatus_Destroyed, Config: []byte(`{}`)}},
|
|
`UPDATE trustyca.issuer
|
|
SET status=$1, key_protected=NULL, config=$2, updated_at=Now()
|
|
WHERE id = $3 AND org_id IS NOT DISTINCT FROM $4
|
|
RETURNING ` + schema.IssuerTableInfo.AllColumns(),
|
|
},
|
|
{
|
|
query.UpdateIssuer, []any{&query.UpdateIssuerRequest{ID: 1, Config: []byte(`{}`)}},
|
|
`UPDATE trustyca.issuer
|
|
SET config=$1, updated_at=Now()
|
|
WHERE id = $2 AND org_id IS NOT DISTINCT FROM $3
|
|
RETURNING ` + schema.IssuerTableInfo.AllColumns(),
|
|
},
|
|
{
|
|
query.ActivateIssuer, nil,
|
|
`UPDATE trustyca.issuer
|
|
SET skid=$1, ikid=$2, serial_number=$3, subject=$4, issuer=$5, sha256=$6, not_before=$7, not_after=$8, pem=$9, chain_pem=$10, root_pem=$11, csr_pem='', status=$12, updated_at=Now()
|
|
WHERE id = $13 AND status = $14 AND org_id IS NOT DISTINCT FROM $15
|
|
RETURNING ` + schema.IssuerTableInfo.AllColumns(),
|
|
},
|
|
{
|
|
// the number only grows for the issuer whose SKID is the CRL's IKID;
|
|
// updated_at is kept so issuer caches stay valid
|
|
query.SetIssuerCrlNumber, nil,
|
|
`UPDATE trustyca.issuer
|
|
SET crl_number=$1
|
|
WHERE id = $2 AND skid = $3 AND crl_number < $4
|
|
RETURNING ` + schema.IssuerTableInfo.AllColumns(),
|
|
},
|
|
{
|
|
query.FindIssuer, []any{&query.FindIssuerRequest{OrgID: 1, ProjectID: 2, Label: "l2"}},
|
|
`SELECT ` + schema.IssuerTableInfo.AllColumns() + `
|
|
FROM trustyca.issuer
|
|
WHERE org_id = $1 AND project_id = $2 AND label = $3`,
|
|
},
|
|
{
|
|
// org scope
|
|
query.FindIssuer, []any{&query.FindIssuerRequest{OrgID: 1, Label: "l2"}},
|
|
`SELECT ` + schema.IssuerTableInfo.AllColumns() + `
|
|
FROM trustyca.issuer
|
|
WHERE org_id = $1 AND project_id IS NULL AND label = $2`,
|
|
},
|
|
{
|
|
// platform scope
|
|
query.FindIssuer, []any{&query.FindIssuerRequest{Label: "L2_CA"}},
|
|
`SELECT ` + schema.IssuerTableInfo.AllColumns() + `
|
|
FROM trustyca.issuer
|
|
WHERE org_id IS NULL AND project_id IS NULL AND label = $1`,
|
|
},
|
|
{
|
|
query.GetIssuerBySKID, nil,
|
|
`SELECT ` + schema.IssuerTableInfo.AllColumns() + `
|
|
FROM trustyca.issuer
|
|
WHERE skid = $1`,
|
|
},
|
|
{
|
|
// every org, for the service's own tasks
|
|
query.ListIssuers, []any{&query.ListIssuersRequest{AllOrgs: true, Status: pb.IssuerStatus_Active, Limit: 10}},
|
|
`SELECT ` + schema.IssuerTableInfo.AllColumns() + `
|
|
FROM trustyca.issuer
|
|
WHERE status = $1
|
|
ORDER BY id DESC
|
|
LIMIT $2
|
|
OFFSET $3`,
|
|
},
|
|
{
|
|
query.ListIssuers, []any{&query.ListIssuersRequest{OrgID: 1, ProjectID: 2, ParentID: 3, Type: pb.AuthorityType_Issuing, Limit: 10, Offset: 20}},
|
|
`SELECT ` + schema.IssuerTableInfo.AllColumns() + `
|
|
FROM trustyca.issuer
|
|
WHERE org_id = $1 AND project_id = $2 AND parent_id = $3 AND type = $4
|
|
ORDER BY id DESC
|
|
LIMIT $5
|
|
OFFSET $6`,
|
|
},
|
|
{
|
|
// a platform issuer by label, for the admin
|
|
query.ListIssuers, []any{&query.ListIssuersRequest{Platform: true, Label: "trusty.l1", Limit: 10}},
|
|
`SELECT ` + schema.IssuerTableInfo.AllColumns() + `
|
|
FROM trustyca.issuer
|
|
WHERE org_id IS NULL AND label = $1
|
|
ORDER BY id DESC
|
|
LIMIT $2
|
|
OFFSET $3`,
|
|
},
|
|
{
|
|
// platform rows of project scope, cursor page
|
|
query.ListIssuers, []any{&query.ListIssuersRequest{Platform: true, Scope: pb.Scope_Project, Status: pb.IssuerStatus_Active, Limit: 10, Cursor: cursor}},
|
|
`SELECT ` + schema.IssuerTableInfo.AllColumns() + `
|
|
FROM trustyca.issuer
|
|
WHERE org_id IS NULL AND project_id IS NOT NULL AND status = $1 AND id < $2
|
|
ORDER BY id DESC
|
|
LIMIT $3`,
|
|
},
|
|
{
|
|
// the org-wide rows and the rows of the caller's projects, none here
|
|
query.ListIssuers, []any{&query.ListIssuersRequest{OrgID: 1, ProjectIDs: []uint64{}, Status: pb.IssuerStatus_Active, Limit: 10, Cursor: cursor}},
|
|
`SELECT ` + schema.IssuerTableInfo.AllColumns() + `
|
|
FROM trustyca.issuer
|
|
WHERE org_id = $1 AND (project_id IS NULL OR project_id = ANY($2)) AND status = $3 AND id < $4
|
|
ORDER BY id DESC
|
|
LIMIT $5`,
|
|
},
|
|
{
|
|
// the issuers of a page of certificates: by ID, and by the key
|
|
// identifier the certificates carry
|
|
query.ListIssuers, []any{&query.ListIssuersRequest{OrgID: 1, IDs: []uint64{4, 5}, IKIDs: []string{"k1", "k2"}, Limit: 10}},
|
|
`SELECT ` + schema.IssuerTableInfo.AllColumns() + `
|
|
FROM trustyca.issuer
|
|
WHERE org_id = $1 AND id = ANY($2) AND skid = ANY($3)
|
|
ORDER BY id DESC
|
|
LIMIT $4
|
|
OFFSET $5`,
|
|
},
|
|
{
|
|
// an exact project takes precedence over the visible projects
|
|
query.ListIssuers, []any{&query.ListIssuersRequest{OrgID: 1, ProjectID: 2, ProjectIDs: []uint64{3}, Limit: 10}},
|
|
`SELECT ` + schema.IssuerTableInfo.AllColumns() + `
|
|
FROM trustyca.issuer
|
|
WHERE org_id = $1 AND project_id = $2
|
|
ORDER BY id DESC
|
|
LIMIT $3
|
|
OFFSET $4`,
|
|
},
|
|
{
|
|
// the first cursor page has no cursor predicate
|
|
query.ListIssuers, []any{&query.ListIssuersRequest{OrgID: 1, Scope: pb.Scope_Org, Limit: 10, Cursor: query.NewCursor(0)}},
|
|
`SELECT ` + schema.IssuerTableInfo.AllColumns() + `
|
|
FROM trustyca.issuer
|
|
WHERE org_id = $1 AND project_id IS NULL
|
|
ORDER BY id DESC
|
|
LIMIT $2
|
|
OFFSET $3`,
|
|
},
|
|
})
|
|
}
|