Update of organisation policy by Digger API failing

[chvima]

When the organisation policy already exists and I am using the API call again to update it, it fails on SQL error

API command used (both first time and update):

curl -X PUT -H "Authorization: Bearer XXX"  https://domain/orgs/digger/access-policy --data-binary @- << EOF    package digger
default allow = true
EOF

Log of first time succesfull insert:

2024/01/10 10:42:38 /go/src/github.com/diggerhq/digger/backend/controllers/policies.go:156 record not found
[0.935ms] [rows:0] SELECT * FROM "policies" WHERE (organisation_id = 1 AND (repo_id IS NULL AND project_id IS NULL) AND type = 'access') AND "policies"."deleted_at" IS NULL LIMIT 1

2024/01/10 10:42:38 /go/src/github.com/diggerhq/digger/backend/controllers/policies.go:159
[5.824ms] [rows:1] INSERT INTO "policies" ("created_at","updated_at","deleted_at","project_id","policy","type","created_by_id","organisation_id","repo_id") VALUES ('2024-01-10 10:42:38.104','2024-01-10 10:42:38.104',NULL,NULL,'package digger
default allow = true
','access',NULL,1,NULL) RETURNING "id"
[GIN] 2024/01/10 - 10:42:38 | 200 |    14.79003ms |    37.48.10.103 | PUT      "/orgs/digger/access-policy"

Log of update:

2024/01/10 10:43:41 /go/src/github.com/diggerhq/digger/backend/controllers/policies.go:156
[1.761ms] [rows:1] SELECT * FROM "policies" WHERE (organisation_id = 1 AND (repo_id IS NULL AND project_id IS NULL) AND type = 'access') AND "policies"."deleted_at" IS NULL LIMIT 1

2024/01/10 10:43:41 /go/src/github.com/diggerhq/digger/backend/controllers/policies.go:171 ERROR: table name "policies" specified more than once (SQLSTATE 42712)
[3.683ms] [rows:1] UPDATE "policies" SET "policy"='package digger
default allow = true
',"updated_at"='2024-01-10 10:43:41.073' FROM "policies" WHERE (organisation_id = 1 AND (repo_id IS NULL AND project_id IS NULL) AND type = 'access') AND "policies"."deleted_at" IS NULL AND "id" = 7
2024/01/10 10:43:41 policies.go:173: Error updating policy: ERROR: table name "policies" specified more than once (SQLSTATE 42712)
[GIN] 2024/01/10 - 10:43:41 | 500 |    8.985476ms |    37.48.10.103 | PUT      "/orgs/digger/access-policy"

Using Digger v 0.3.11, ad backend DB Postgres 16

[chvima]

Right now I have to do the updates by deleting the row in DB and using API call or editing in DB.

[chvima]

Still valid as of v0.4.9.

[chvima]

Still an issue as of 0.4.13.

I think the issue is in the generated SQL command

UPDATE "policies" SET "policy"='package digger
default allow = true
',"updated_at"='2024-01-10 10:43:41.073' FROM "policies" WHERE (organisation_id = 1 AND (repo_id IS NULL AND project_id IS NULL) AND type = 'access') AND "policies"."deleted_at" IS NULL AND "id" = 7

This part of code makes it invalid:

FROM "policies"