database: add `filtered_repos` function to return repos
Created by: unknwon
This PR extracts the current authzQuery
to become a Postgres function (vs an application-level query condition) named filtered_repos
.
Problem
Indexes seems are not being picked up by Postgres planner as a function (because this function returns the as many rows as possible without first consult WHERE
conditions, which makes sense?):
Query with the function:
SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT * FROM filtered_repos(TRUE, FALSE, 0, 'read') WHERE id = 1 AND deleted_at IS NULL;
Query as a where condition:
SET enable_seqscan = OFF;
EXPLAIN ANALYZE SELECT * FROM repo WHERE id = 1 AND deleted_at IS NULL AND (
TRUE -- TRUE or FALSE to indicate whether to bypass the check
OR (
NOT FALSE -- Disregard unrestricted state when permissions user mapping is enabled
AND (
NOT repo.private -- Happy path of non-private repositories
OR EXISTS ( -- Each external service defines if repositories are unrestricted
SELECT
FROM external_services AS es
JOIN external_service_repos AS esr ON (
esr.external_service_id = es.id
AND esr.repo_id = repo.id
AND es.unrestricted = TRUE
AND es.deleted_at IS NULL
)
LIMIT 1
)
)
)
OR EXISTS ( -- We assume that all repos added by the authenticated user should be shown
SELECT 1
FROM external_service_repos
WHERE repo_id = repo.id
AND user_id = 0
)
OR ( -- Restricted repositories require checking permissions
SELECT object_ids_ints @> INTSET(repo.id)
FROM user_permissions
WHERE
user_id = 0
AND permission = 'read'
AND object_type = 'repos'
)
);
This makes me wonder if we should start thinking alternative strategies (which I don't have any yet).