Files
2026-10-31 05:36:03 -05:00

20 KiB

db

import "github.com/effective-security/trustyca/internal/db"

Package db defines the database interfaces of the service and constructs the PostgreSQL provider that implements them.

OrgsReadonlyDb and OrgsDb own the access layer (users, logins, orgs, projects, memberships, invites, API keys, events); CaReadonlyDb and CaDb own the CA data plane (issuers, trust anchors, profiles, certificates, revocations, CRLs). Provider implements all of them:

p, err := db.New(cfg.SQL.DataSource, cfg.SQL.MigrationsDir, 0, 0, flake.DefaultIDGenerator)
if err != nil {
	return err
}
defer p.Close()
issuer, err := p.FindIssuer(ctx, orgID, projectID, "L2_CA")

New runs the SQL migrations of the migrations directory. The DB layer is plain CRUD: it never imports the authorization code, and the caller verifies that a row returned by ID belongs to the caller's org and project. The sentinel errors of the CA mutations (ErrAlreadyRevoked, ErrStaleCrlNumber, ErrRootConflict, ErrOrgScopeRequired) are compared with errors.Is.

Index

Constants

The unique constraints of the CA tables, as PostgreSQL names them in a violation: the label of an issuer in its scope, the key identifier of an issuer, and the prefix of the certificate constraints

const (
    UniqueIssuerLabel       = pgsql.UniqueIssuerLabel
    UniqueIssuerSkid        = pgsql.UniqueIssuerSkid
    UniqueCertificatePrefix = pgsql.UniqueCertificatePrefix
)

Variables

Errors of the CA mutations that the caller resolves from the current state

var (
    // ErrAlreadyRevoked is returned by RevokeCertificate for a certificate
    // whose status is Revoked
    ErrAlreadyRevoked = pgsql.ErrAlreadyRevoked
    // ErrStaleCrlNumber is returned by PublishCrl when the CRL number is not
    // greater than the issuer's current one
    ErrStaleCrlNumber = pgsql.ErrStaleCrlNumber
    // ErrRootConflict is returned by RegisterRoot when the certificate is
    // registered in another scope
    ErrRootConflict = pgsql.ErrRootConflict
    // ErrOrgScopeRequired is returned by a List of CA rows whose request
    // names neither an org, nor the platform, nor every org
    ErrOrgScopeRequired = query.ErrOrgScopeRequired
)

func IsUniqueViolation

func IsUniqueViolation(err error, prefix string) bool

IsUniqueViolation reports whether the error is a unique constraint violation of a constraint whose name starts with the prefix (empty for any), so that a caller maps a conflict without parsing the message

type CaDb

CaDb defines an interface for CRUD operations on the CA tables. Every mutation of an existing row names the exact org scope of the row (a zero orgID is the platform), so a row of another org is not found or not deleted. State changes that must agree are one transaction: a certificate and its issue event, a root issuer and its trust anchor, a revocation and the certificate status, a CRL and the issuer's CRL number.

type CaDb interface {
    CaReadonlyDb

    // RegisterIssuer inserts an issuer: a Pending one with its CSR, any
    // other status with its certificate. A root issuer requires its anchor,
    // its certificate as a trust anchor of its org, registered in the same
    // transaction as RegisterRoot does; any other issuer takes nil.
    RegisterIssuer(ctx context.Context, m *model.Issuer, anchor *model.RootCertificate) (*model.Issuer, error)
    // UpdateIssuer changes the lifecycle status or the runtime settings of
    // an issuer; Destroyed also clears the protected key material
    UpdateIssuer(ctx context.Context, req *query.UpdateIssuerRequest) (*model.Issuer, error)
    // ActivateIssuer sets the certificate of a Pending issuer of the model's
    // org, clears its CSR and makes it Active; an issuer that is not
    // Pending is not found
    ActivateIssuer(ctx context.Context, m *model.Issuer) (*model.Issuer, error)
    // DeleteIssuer deletes an issuer and its current CRL; the certificates
    // it issued are kept
    DeleteIssuer(ctx context.Context, orgID, id uint64) (int64, error)

    // RegisterRoot registers a trust anchor; the same certificate in the
    // same scope updates its trust, in another scope fails with
    // ErrRootConflict
    RegisterRoot(ctx context.Context, m *model.RootCertificate) (*model.RootCertificate, error)
    // DeleteRoot deletes a trust anchor
    DeleteRoot(ctx context.Context, orgID, id uint64) (int64, error)

    // RegisterProfile creates a certificate profile, or replaces the
    // profile with the same label in the same scope
    RegisterProfile(ctx context.Context, m *model.CertificateProfile) (*model.CertificateProfile, error)
    // DeleteProfile deletes a certificate profile
    DeleteProfile(ctx context.Context, orgID, id uint64) (int64, error)

    // RegisterCertificate inserts the issuance record of a certificate and,
    // when an event is given, records it in the same transaction with the
    // certificate as its reference
    RegisterCertificate(ctx context.Context, m *model.Certificate, evt *model.Event) (*model.Certificate, error)
    // UpdateCertificate replaces the label, the client metadata or the
    // published locations of a certificate
    UpdateCertificate(ctx context.Context, req *query.UpdateCertificateRequest) (*model.Certificate, error)
    // RevokeCertificate records the revocation and sets the certificate
    // status to Revoked in one transaction; a revoked certificate fails
    // with ErrAlreadyRevoked
    RevokeCertificate(ctx context.Context, req *query.RevokeCertificateRequest) (*model.RevokedCertificate, error)
    // DeleteCertificate deletes a certificate and its revocation
    DeleteCertificate(ctx context.Context, orgID, id uint64) (int64, error)
    // ExpireCertificates sets a batch of Active certificates that expired
    // before the time to Expired, lowest IDs first, and returns how many it
    // changed; the certificate status task repeats it until a batch is
    // smaller than the limit
    ExpireCertificates(ctx context.Context, req *query.ExpireCertificatesRequest) (int64, error)
    // DeleteExpiredCertificates deletes a batch of certificates of any
    // status that expired before the time, with their revocations, lowest
    // IDs first, and returns how many it deleted (retention)
    DeleteExpiredCertificates(ctx context.Context, req *query.DeleteExpiredCertificatesRequest) (int64, error)

    // PublishCrl stores the current CRL of an issuer and records its number
    // on the issuer in one transaction; the CRL's IKID must be the issuer's
    // SKID, and a number that is not greater than the issuer's fails with
    // ErrStaleCrlNumber. The tenancy and issuer name come from the issuer.
    PublishCrl(ctx context.Context, m *model.Crl) (*model.Crl, error)
    // DeleteCrl deletes the current CRL of the issuer with the key identifier
    DeleteCrl(ctx context.Context, orgID uint64, ikid string) (int64, error)
}

type CaReadonlyDb

CaReadonlyDb defines an interface for Read operations on the CA tables: issuers, trust anchors, profiles, certificates, revocations and CRLs. Rows with a zero OrgID are the platform's (org_id IS NULL). A Get by ID returns the row of any scope and the caller verifies its org and project; a List names its org scope (OrgID, Platform or AllOrgs) or fails with ErrOrgScopeRequired.

type CaReadonlyDb interface {
    xdb.IDGenerator

    // GetIssuer returns an issuer by ID
    GetIssuer(ctx context.Context, id uint64) (*model.Issuer, error)
    // FindIssuer returns the issuer of the exact scope and label: a zero
    // orgID is the platform scope and a zero projectID the org scope
    FindIssuer(ctx context.Context, orgID, projectID uint64, label string) (*model.Issuer, error)
    // GetIssuerBySKID returns the issuer by its Subject Key Identifier, which
    // is the IKID of the certificates and CRLs it issued
    GetIssuerBySKID(ctx context.Context, skid string) (*model.Issuer, error)
    // ListIssuers lists issuers, newest first
    ListIssuers(ctx context.Context, req *query.ListIssuersRequest) (*model.IssuerResult, error)
    // CountIssuers counts the issuers of an org by lifecycle status, for the
    // org, one of its projects or the visible projects of the request; a
    // status without issuers has no entry
    CountIssuers(ctx context.Context, req *query.CountIssuersRequest) (model.IssuerCounts, error)

    // GetRoot returns a trust anchor by ID
    GetRoot(ctx context.Context, id uint64) (*model.RootCertificate, error)
    // ListRoots lists trust anchors, newest first
    ListRoots(ctx context.Context, req *query.ListRootsRequest) (*model.RootCertificateResult, error)

    // GetProfile returns a certificate profile by ID
    GetProfile(ctx context.Context, id uint64) (*model.CertificateProfile, error)
    // FindProfile returns the profile of the exact scope and label: a zero
    // orgID is the platform scope and a zero projectID the org scope
    FindProfile(ctx context.Context, orgID, projectID uint64, label string) (*model.CertificateProfile, error)
    // ListProfiles lists certificate profiles, newest first
    ListProfiles(ctx context.Context, req *query.ListProfilesRequest) (*model.CertificateProfileResult, error)

    // GetCertificate returns a certificate by ID
    GetCertificate(ctx context.Context, id uint64) (*model.Certificate, error)
    // GetCertificateBySha256 returns a certificate by its hex encoded
    // SHA-256 thumbprint
    GetCertificateBySha256(ctx context.Context, sha256 string) (*model.Certificate, error)
    // GetCertificateByIssuerSerial returns a certificate by the key
    // identifier of its issuer and its serial number
    GetCertificateByIssuerSerial(ctx context.Context, ikid, serial string) (*model.Certificate, error)
    // ListCertificates lists certificates, newest first
    ListCertificates(ctx context.Context, req *query.ListCertificatesRequest) (*model.CertificateResult, error)
    // CountCertificates counts the certificates of an org by status, and the
    // Active ones whose validity ends within the periods of the request,
    // for the org, one of its projects or the visible projects of the
    // request
    CountCertificates(ctx context.Context, req *query.CountCertificatesRequest) (*model.CertificateCounts, error)

    // GetRevoked returns the revocation of a certificate by its ID, or by the
    // key identifier of its issuer and its serial number
    GetRevoked(ctx context.Context, req *query.GetRevokedRequest) (*model.Revoked, error)
    // ListRevoked lists revocations, newest first; a CRL is built from the
    // revocations of an issuer that are still valid
    ListRevoked(ctx context.Context, req *query.ListRevokedRequest) (*model.RevokedResult, error)
    // ListRevokedCertificates lists revoked certificates with their
    // certificate columns, newest revocation first
    ListRevokedCertificates(ctx context.Context, req *query.ListRevokedCertificatesRequest) (*model.RevokedCertificateResult, error)

    // GetCrl returns the current CRL of the issuer with the key identifier
    GetCrl(ctx context.Context, ikid string) (*model.Crl, error)
    // GetCrls returns the current CRLs of the issuers with the key
    // identifiers, in any order; an issuer without a CRL has none in the
    // result. The list is bounded by the caller: a page of issuers.
    GetCrls(ctx context.Context, ikids []string) ([]*model.Crl, error)
    // ListCrls lists the current CRLs, newest first
    ListCrls(ctx context.Context, req *query.ListCrlsRequest) (*model.CrlResult, error)
    // ListCrlStates lists the issuers of an org with the lifecycle statuses
    // of the request, newest first, with the state of their current CRL:
    // the key identifier, the end of the validity, the settings and the
    // next update of the CRL, never a PEM (the tenant summary)
    ListCrlStates(ctx context.Context, req *query.ListCrlStatesRequest) (*model.CrlStateResult, error)

    // GetSummaryCounts reads the counts of the tenant summary at one
    // snapshot: a read-only REPEATABLE READ transaction runs the issuer
    // counts, the certificate counts and the pages of the CRL states, so
    // they agree with each other
    GetSummaryCounts(ctx context.Context, req *query.SummaryRequest) (*model.SummaryCounts, error)
}

type OrgsDb

OrgsDb defines an interface for CRUD operations on Orgs

type OrgsDb interface {
    OrgsReadonlyDb

    // LoginUser registers the login and its user. Only the first login of a
    // new user returns the login and the user with the same ID.
    LoginUser(ctx context.Context, login *model.Login) (*model.Login, *model.User, error)
    // DeleteUserByEmail deletes user and logins
    DeleteUserByEmail(ctx context.Context, email string) (map[string]int64, error)
    // DeleteUser deletes user and logins by user ID
    DeleteUser(ctx context.Context, id uint64) (map[string]int64, error)
    // UpdateUser updates a user's profile
    UpdateUser(ctx context.Context, id uint64, name string, emailVerified bool) (*model.User, error)
    // DeleteLogin deletes a single login by ID
    DeleteLogin(ctx context.Context, id uint64) (int64, error)

    // RegisterOrg registers a new org
    RegisterOrg(ctx context.Context, org *model.Org, ownerID uint64) (*model.Org, error)
    // UpdateOrg updates an org
    UpdateOrg(ctx context.Context, req *query.UpdateOrgRequest) (*model.Org, error)
    // DeleteOrg deletes an org and all its projects and members
    DeleteOrg(ctx context.Context, id uint64) (map[string]int64, error)

    // RegisterProject creates a project in an org
    RegisterProject(ctx context.Context, project *model.Project) (*model.Project, error)
    // UpdateProject updates a project's name, description or status
    UpdateProject(ctx context.Context, req *query.UpdateProjectRequest) (*model.Project, error)

    // AddMember adds a member to an org (empty ProjectID) or to a project
    AddMember(ctx context.Context, member *model.Membership) (*model.Membership, error)
    // UpdateMemberRole updates the role of a membership at the given scope
    UpdateMemberRole(ctx context.Context, req *query.UpdateMemberRoleRequest) (*model.Membership, error)
    // DeleteMember deletes memberships at the requested scope
    DeleteMember(ctx context.Context, r *query.DeleteMemberRequest) (int64, error)

    // CreateInvite creates or updates an invite for a user to join an org
    // (empty ProjectID) or a project
    CreateInvite(ctx context.Context, invite *model.Invite) (*model.Invite, error)
    // DeleteInviteByID deletes an invite by ID
    DeleteInviteByID(ctx context.Context, id uint64) (int64, error)
    // DeleteInvite deletes invites at the requested scope
    DeleteInvite(ctx context.Context, r *query.DeleteInviteRequest) (int64, error)
    // AcceptInvite accepts an org invite for a user
    AcceptInvite(ctx context.Context, inviteID, userID uint64) (*model.Membership, error)
    // AcceptInvites moves the pending invites for a user's email to grants
    // and returns the grants it created; each invite is accepted in a
    // transaction of its own
    AcceptInvites(ctx context.Context, user *model.User) (model.MembershipSlice, error)

    // CreateEvent returns new Event
    CreateEvent(ctx context.Context, m *model.Event) (*model.Event, error)
    // TryCreateEvent creates the event asynchronously and logs failures
    TryCreateEvent(evt *model.Event)

    // UseAPIKey increments the used count and sets the used at time
    UseAPIKey(ctx context.Context, orgID, id uint64) (*model.APIKey, error)
    // RegisterAPIKey inserts a new API key into the database
    RegisterAPIKey(ctx context.Context, m *model.APIKey) (*model.APIKey, error)
    // UpdateAPIKey updates an API key
    // DeleteAPIKey deletes an API key
    DeleteAPIKey(ctx context.Context, orgID, id uint64) (int64, error)
}

type OrgsReadonlyDb

OrgsReadonlyDb defines an interface for Read operations on Orgs

type OrgsReadonlyDb interface {
    xdb.IDGenerator

    // GetUser returns User
    GetUser(ctx context.Context, id uint64) (*model.User, error)
    // GetUserByEmail returns User
    GetUserByEmail(ctx context.Context, email string) (*model.User, error)
    // GetLogin returns a login by ID
    GetLogin(ctx context.Context, id uint64) (*model.Login, error)
    // GetLoginsByEmail returns Logins
    GetLoginsByEmail(ctx context.Context, email string) (model.LoginSlice, error)
    // ListLogins returns Logins
    ListLogins(ctx context.Context, limit, offset uint32) (model.LoginSlice, uint32, error)

    // GetOrg returns Org
    GetOrg(ctx context.Context, id uint64) (*model.Org, error)
    // FindOrg returns Org by alias
    FindOrg(ctx context.Context, alias string) (*model.Org, error)
    // ListOrgs lists orgs
    ListOrgs(ctx context.Context, req *query.ListOrgsRequest) (*model.OrgResult, error)
    // GetUserOrgs gets the orgs for a user
    GetUserOrgs(ctx context.Context, userID uint64) (model.OrgSlice, error)

    // GetProject returns Project by ID
    GetProject(ctx context.Context, id uint64) (*model.Project, error)
    // FindProject returns Project by org and alias
    FindProject(ctx context.Context, orgID uint64, alias string) (*model.Project, error)
    // ListProjects lists projects of an org
    ListProjects(ctx context.Context, req *query.ListProjectsRequest) (*model.ProjectResult, error)

    // GetUserMemberships returns all grants of a user in active orgs
    GetUserMemberships(ctx context.Context, userID uint64) (model.MembershipInfoSlice, error)
    // ListMemberships lists memberships of the requested scope
    ListMemberships(ctx context.Context, req *query.ListMembershipsRequest) (model.MembershipInfoSlice, error)

    // GetInvite returns an invite by ID
    GetInvite(ctx context.Context, id uint64) (*model.Invite, error)
    // GetInviteByOrgAndEmail returns an invite by org, scope and email;
    // projectID 0 returns the org-wide invite
    GetInviteByOrgAndEmail(ctx context.Context, orgID, projectID uint64, email string) (*model.Invite, error)
    // GetOrgInvites returns invites of all scopes for an org
    GetOrgInvites(ctx context.Context, orgID uint64) (model.InviteSlice, error)
    // GetUserInvites returns invites for a user's email
    GetUserInvites(ctx context.Context, email string) (model.InviteSlice, error)

    // ListEvents returns events
    ListEvents(ctx context.Context, r *query.ListEventsRequest) (*model.EventResult, error)
    // GetEvent returns Event
    GetEvent(ctx context.Context, id uint64) (*model.Event, error)

    // ListAPIKeys lists API keys
    ListAPIKeys(ctx context.Context, req *query.ListAPIKeysRequest) (*model.APIKeyResult, error)
    // GetAPIKey returns API key by ID
    GetAPIKey(ctx context.Context, orgID, id uint64) (*model.APIKey, error)
}

type Provider

Provider provides complete DB access

type Provider interface {
    xdb.Provider

    OrgsDb
    CaDb
}

func New

func New(dataSourceName, migrationsDir string, forceVersion, migrateVersion int, idGen flake.IDGenerator) (Provider, error)

New creates a Provider instance

Generated by gomarkdoc