Description

Adds a new SQLite extension function aws_policy_equal(policy1, policy2) to the StackQL embedded SQL backend. The function performs semantic comparison of AWS IAM policy JSON documents and AWS tags arrays, returning 1 if they are equivalent and 0 otherwise.

Key behaviours:

• Treats Action, NotAction, Resource, NotResource, Principal, NotPrincipal, AWS, Service, Tags, and tags fields as unordered sets — element order is ignored when comparing.
• Handles Action expressed as either a JSON string or array ("s3:GetObject" vs ["s3:GetObject"]) transparently.
• When both top-level arguments are JSON arrays (e.g. raw AWS tags arrays returned by the API), they are also compared as unordered sets.
• Performs case-insensitive service name comparison for ARNs.

This is implemented in sqlite3-binding.c as a registered custom SQLite extension (sqlite3_awspolicyequal_init), consistent with other StackQL extension functions (json_equal, split_part, regexp, etc.).

Type of change

• Feature (non-breaking change to add functionality).

Issues referenced

Evidence

The function correctly handles the following cases (verified via inline test queries):

-- Identical policies
SELECT aws_policy_equal(
  '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Action":"s3:*","Resource":"*"}]}',
  '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Action":"s3:*","Resource":"*"}]}'
); -- 1

-- Action array vs string equivalence
SELECT aws_policy_equal(
  '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Action":["s3:GetObject"],"Resource":"*"}]}',
  '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Action":"s3:GetObject","Resource":"*"}]}'
); -- 1

-- Unordered Action arrays
SELECT aws_policy_equal(
  '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Action":["s3:GetObject","s3:PutObject"],"Resource":"*"}]}',
  '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Action":["s3:PutObject","s3:GetObject"],"Resource":"*"}]}'
); -- 1

-- Top-level tags arrays (order-insensitive)
SELECT aws_policy_equal(
  '[{"Key":"env","Value":"prod"},{"Key":"team","Value":"platform"}]',
  '[{"Key":"team","Value":"platform"},{"Key":"env","Value":"prod"}]'
); -- 1

-- Nested Tags field (order-insensitive)
SELECT aws_policy_equal(
  '{"BucketName":"my-bucket","Tags":[{"Key":"env","Value":"prod"},{"Key":"team","Value":"platform"}]}',
  '{"BucketName":"my-bucket","Tags":[{"Key":"team","Value":"platform"},{"Key":"env","Value":"prod"}]}'
); -- 1

-- Non-equivalent policies
SELECT aws_policy_equal(
  '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Action":"s3:*","Resource":"*"}]}',
  '{"Version":"2012-10-17","Statement":[{"Effect":"Deny","Action":"s3:*","Resource":"*"}]}'
); -- 0

Checklist:

• A full round of testing has been completed, and there are no test failures as a result of these changes.
• The changes are covered with functional and/or integration robot testing.
• The changes work on all supported platforms.
• Unit tests pass locally, as per the developer guide.
• Robot tests pass locally, as per the developer guide.
• Linter passes locally, as per the developer guide.

Variations

Robot tests for aws_policy_equal are not yet included in this PR. The function is a net-new addition with no changes to existing behaviour, so regression risk is minimal. Follow-up robot test coverage should be tracked as a separate issue.

Tech Debt

No technical debt results from this change. The implementation follows the established pattern for StackQL custom SQLite extensions (consistent with json_equal, split_part, etc.) and introduces no workarounds or deferred work beyond the robot test coverage noted above.