Store explicit positions for session macaroon caveats and permissions
in the SQL schema and read them back in position order. Also remove the
migration-time sorting workaround in session comparison, so migration
validation now checks the actual persisted order instead of masking it.
This is needed because session caveat order is not just presentation
data. LiT adds caveats to the baked macaroon in slice order, and the
macaroon library updates the signature hash chain for each added caveat.
Reordering caveats can therefore change the resulting macaroon bytes and
signature.
The previous schema split caveats and permissions into child tables
without any position column, and the SQL reads had no ORDER BY. The KV
store preserves slice order, but SQL had no explicit way to reproduce
that order after migration or on later reads. The migration code’s old
sorting step was only making validation deterministic; it did not
preserve the original recipe order.
Permissions are canonicalized by lnd when baking, so their order is less
semantically important for the final macaroon. They still get positions
here so the stored recipe remains faithful to the original session data
and both child tables behave consistently.
Why it was needed:
- caveats needed explicit order preservation because they are appended
and signed in order.
- The old SQL schema did not store order, and the read queries did not
request one.
- Adding position makes the SQL representation faithful to the KV/TLV
recipe instead of relying on incidental row order.
- Adding it to permissions too keeps the stored recipe lossless and
consistent, even though lnd. canonicalizes permissions before baking.
NOTE: This commit explicitly edits the previous migration instead of
adding a new one. This is ok as SQL dbs are not yet supported in
production, so there are no live deployments to worry about.
Migrate KV firewall actions by traversing the global actions-index
instead of iterating per-session buckets, so SQL action IDs follow the
same global creation order as the legacy KV store.
Also stabilize SQL action listing by ordering on created_at and id,
using id as a deterministic tie-breaker when multiple actions share the
same timestamp.
This is needed because KV actions have a real global sequence in
actions-index, while the old migration assigned SQL IDs based on bucket
traversal order. That could reorder legacy actions during migration.
Separately, ordering by created_at alone was not stable for equal
timestamps, so action queries could return different orders for the
same data.
NOTE: This commit explicitly edits the previous migration instead of
adding a new one. This is ok as SQL dbs are not yet supported in
production, so there are no live deployments to worry about.
This commits ensures that the `CountActions` function in the `sqlc` &
`sqlcmig6` packages utilizes the `fillPlaceHolders` function to properly
handle SQL query placeholders.
This ensures that the function works correctly on `Postgres` backends
when it contains ? placeholders instead of positional ones (e.g., $1).
Commit 24d7307a in `master` added a a new `UpdateAccountLabel` method to
the `Querier` interface. This commit also updates to `sqlcmig6` package
to support that query.
Add the ListAllAccountInvoices & ListAllAccountPayments queries to the
sqlcmig6 package, to mimic the sqlc queries present before the kvdb to
sql migration is added to prod.
In order to be able to use the custom queries with the
`sqlcmig6.Queries`, we assert at compile time that `sqlcmig6.Queries`
implements the `CustomQueries` interface.
This commit introduces the `sqlcmig6` package, which at the time of this
commit contains the same queries and models as `sqlc` package.
Importantly though, once the kvdb to sql migration is made available in
production, the `sqlcmig6` package will not change, as it is intended
to represent the sql db as it was at the time of the migration.
The sqlcmig6 package is therefore intended to be used in the kvdb to sql
migration code, as it is will always be compatible with the sql database
when all sql migrations prior to the kvdb to sql migration are applied.
When additional sql migrations are added in the future, they may effect
the `sqlc` package in such a way that the standard `sqlc` queries and
models aren't compatible with kvdb to sql migration code any longer.
By preserving the `sqlcmig6` package, we ensure that the kvdb to sql
migration code can always use the same queries and models that were
available at the time of the migration, even if the `sqlc` package
changes in the future.
Note that the `sqlcmig6` package have not been generated by `sqlc` (the
queries and models are copied from the `sqlc` package), as it is not
intended to be changed in the future.
