Add ORDER BY clause to policy queries to ensure deterministic ordering

[cweibel]

Current behavior

Current Behavior

The policy server's SQL queries for fetching network policies do not include an ORDER BY clause, resulting in non-deterministic row ordering. This causes issues for API consumers (such as the Terraform Cloud Foundry provider) that rely on consistent ordering between API calls.

SQL Queries Without ORDER BY

In src/code.cloudfoundry.org/policy-server/store/store.go, both the ByGuids() and All() functions execute queries without ordering:

ByGuids() (lines 273-310):

select
    src_grp.guid,
    src_grp.id,
    dst_grp.guid,
    dst_grp.id,
    destinations.port,
    destinations.start_port,
    destinations.end_port,
    destinations.protocol
from policies
left outer join "groups" as src_grp on (policies.group_id = src_grp.id)
left outer join destinations on (destinations.id = policies.destination_id)
left outer join "groups" as dst_grp on (destinations.group_id = dst_grp.id)
where src_grp.guid in (?) OR dst_grp.guid in (?);
-- NO ORDER BY

All() (lines 312-326): Same query structure, also missing ORDER BY.

PostgreSQL Query Plan Analysis

The query plan shows PostgreSQL sorts by policies.group_id for the merge join, but ordering within the same group_id is undefined:

QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=3.65..4.30 rows=10 width=92)
   Hash Cond: (policies.destination_id = destinations.id)
   ->  Merge Right Join  (cost=1.59..2.12 rows=10 width=42)
         Merge Cond: (src_grp.id = policies.group_id)
         ->  Index Scan using groups_pkey on groups src_grp  (cost=0.29..1983.57 rows=61926 width=38)
         ->  Sort  (cost=1.27..1.29 rows=10 width=8)
               Sort Key: policies.group_id    <-- Only sorts by group_id, not destination
               ->  Seq Scan on policies  (cost=0.00..1.10 rows=10 width=8)
   ->  Hash  (cost=1.96..1.96 rows=8 width=58)
         ...

When multiple policies share the same source app (same group_id) but have different destination ports, their relative order depends on:

• Physical row location in the policies table
• Sequential scan order, which changes after VACUUM, ANALYZE, or table updates

Impact on Terraform Provider

The Terraform Cloud Foundry provider (https://github.com/cloudfoundry/terraform-provider-cloudfoundry) calls GetPoliciesByID() via the policy_client library. When the API returns policies in a different order than stored in Terraform state, Terraform incorrectly detects drift and attempts to update the policies on every terraform apply, even though nothing has actually changed.

Example Terraform output showing perpetual drift:

# module.project_runner.cloudfoundry_network_policy.egress_routing will be updated in-place
~ resource "cloudfoundry_network_policy" "egress_routing" {
    ~ policies = [
        ~ {
            ~ port            = "8080" -> "61443"
              # (3 unchanged attributes hidden)
          },
        ~ {
            ~ port            = "61443" -> "8080"
              # (3 unchanged attributes hidden)
          },
      ]
  }

Running terraform apply succeeds, but the next terraform plan shows the same change again with ports swapped back - an infinite loop.

Database Schema Reference

policydb=> \d policies
                                Table "public.policies"
     Column     |  Type   | Collation | Nullable |               Default
----------------+---------+-----------+----------+--------------------------------------
 id             | integer |           | not null | nextval('policies_id_seq'::regclass)
 group_id       | integer |           |          |
 destination_id | integer |           |          |
Indexes:
    "policies_pkey" PRIMARY KEY, btree (id)
    "policies_group_id_destination_id_key" UNIQUE CONSTRAINT, btree (group_id, destination_id)

Trigger

This issue became apparent after upgrading cf-deployment from v56.6.0 to v57.0.0 (cf-networking v3.113.0 → v3.115.0). The upgrade likely triggered database maintenance (VACUUM/ANALYZE) or statistics updates that changed the physical row order in the policies table.

Steps to Reproduce

1. Create two network policies with the same source app, same destination app, but different ports (e.g., 8080 and 61443)
2. Use the Terraform Cloud Foundry provider to manage these policies
3. Run terraform apply - it succeeds
4. Run terraform plan - it shows changes (ports swapped)
5. Run terraform apply again - it succeeds
6. Run terraform plan - it shows changes again (ports swapped back)
7. This cycle repeats indefinitely

Proposed Fix

Add an ORDER BY clause to the queries in store.go. A reasonable ordering would be:

ORDER BY src_grp.guid, dst_grp.guid, destinations.port, destinations.protocol

This ensures:

1. Policies are grouped by source app
2. Within source app, grouped by destination app
3. Within same source/destination pair, ordered by port
4. Protocol as final tiebreaker

Affected Code Locations

• src/code.cloudfoundry.org/policy-server/store/store.go
• ByGuids() function (line ~310)
• All() function (line ~326)

Desired behavior

Expected Behavior

The /networking/v1/external/policies API endpoint should return policies in a deterministic, stable order so that API consumers receive consistent results across calls.

Affected Version

Environment - cf-deployment: v57.0.0+ - cf-networking-release: v3.115.0 - Database: PostgreSQL