Skip to content

database: add `filtered_repos` function to return repos

Administrator requested to merge jc/filtered_repos-function into main

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;
CleanShot 2021-06-16 at 19 02 41@2x

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'
		)
	);
CleanShot 2021-06-16 at 19 04 01@2x

This makes me wonder if we should start thinking alternative strategies (which I don't have any yet).

Merge request reports

Loading