Skip to content

Hide projects created and owned by banned users

Eugie Limpin requested to merge el-hide-banned-user-projects into master

What does this MR do and why?

Partially resolves https://gitlab.com/gitlab-org/modelops/anti-abuse/team-tasks/-/issues/373+.

This MR implements hiding projects created and owned by banned users. Projects of banned users are only hidden from non-admin users if the FF (hide_projects_of_banned_users) is enabled.

Database changes

This MR is patterned after the implementation of hiding MRs of banned users. It employs an anti-join to exclude projects created and owned by banned users.

Project.without_created_and_owned_by_banned_user scope

Query 1: executed in explore projects page with filters (name="com", lang="Ruby") and set to page 10: https://gitlab.com/explore/projects?language=12&name=com&non_archived=true&page=10&sort=name_asc

Before

postgres.ai EXPLAIN

Raw query 
SELECT
    "projects".*
FROM
    "projects"
    INNER JOIN "repository_languages" ON "repository_languages"."project_id" = "projects"."id"
WHERE (EXISTS (
        SELECT
            1
        FROM
            "project_authorizations"
        WHERE
            "project_authorizations"."user_id" = 1614863
            AND (project_authorizations.project_id = projects.id))
        OR projects.visibility_level IN (10, 20))
AND "projects"."pending_delete" = FALSE
AND (("projects"."path" ILIKE '%com%'
        OR "projects"."name" ILIKE '%com%')
    OR "projects"."description" ILIKE '%com%')
AND "projects"."archived" = FALSE
AND "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE
AND "repository_languages"."programming_language_id" = 12
AND "projects"."hidden" = FALSE
ORDER BY
    LOWER("projects"."name") ASC
LIMIT 21 OFFSET 200

After

postgres.ai EXPLAIN

Raw query 
SELECT
    "projects".*
FROM
    "projects"
    INNER JOIN "repository_languages" ON "repository_languages"."project_id" = "projects"."id"
WHERE (EXISTS (
        SELECT
            1
        FROM
            "project_authorizations"
        WHERE
            "project_authorizations"."user_id" = 1614863
            AND (project_authorizations.project_id = projects.id))
        OR projects.visibility_level IN (10, 20))
AND (NOT EXISTS (
        SELECT
            1
        FROM
            "banned_users"
            INNER JOIN "project_authorizations" ON "project_authorizations"."user_id" = "banned_users"."user_id"
        WHERE (projects.creator_id = banned_users.user_id)
        AND (project_authorizations.project_id = projects.id)
        AND "project_authorizations"."access_level" = 50))
AND "projects"."pending_delete" = FALSE
AND (("projects"."path" ILIKE '%com%'
        OR "projects"."name" ILIKE '%com%')
    OR "projects"."description" ILIKE '%com%')
AND "projects"."archived" = FALSE
AND "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE
AND "repository_languages"."programming_language_id" = 12
AND "projects"."hidden" = FALSE
ORDER BY
    LOWER("projects"."name") ASC
LIMIT 21 OFFSET 200

Query 2: executed in user dashboard page with filters (name="com", lang="Ruby") and set to page 10: https://gitlab.com/dashboard/projects?language=12&name=com&non_archived=true&page=10&sort=name_asc

Before

postgres.ai EXPLAIN

Raw query 
WITH "projects_cte" AS MATERIALIZED (
    SELECT
        "projects".*
    FROM
        "projects"
        INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
    WHERE
        "project_authorizations"."user_id" = 1614863
)
SELECT
    "projects".*
FROM
    "projects_cte" AS "projects"
    INNER JOIN "repository_languages" ON "repository_languages"."project_id" = "projects"."id"
WHERE
    "projects"."pending_delete" = FALSE
    AND (("projects"."path" ILIKE '%com%'
            OR "projects"."name" ILIKE '%com%')
        OR "projects"."description" ILIKE '%com%')
    AND "projects"."archived" = FALSE
    AND "projects"."marked_for_deletion_at" IS NULL
    AND "projects"."pending_delete" = FALSE
    AND "repository_languages"."programming_language_id" = 12
    AND "projects"."hidden" = FALSE
ORDER BY
    LOWER("projects"."name") ASC
LIMIT 20 OFFSET 180

After

postgres.ai EXPLAIN

Raw query 
WITH "projects_cte" AS MATERIALIZED (
    SELECT
        "projects".*
    FROM
        "projects"
        INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
    WHERE
        "project_authorizations"."user_id" = 1614863
        AND (
            NOT EXISTS (
                SELECT
                    1
                FROM
                    "banned_users"
                    INNER JOIN "project_authorizations" ON "project_authorizations"."user_id" = "banned_users"."user_id"
                WHERE (
                    projects.creator_id = banned_users.user_id
)
                AND (
                    project_authorizations.project_id = projects.id
)
                AND "project_authorizations"."access_level" = 50
)
))
SELECT
    "projects".*
FROM
    "projects_cte" AS "projects"
    INNER JOIN "repository_languages" ON "repository_languages"."project_id" = "projects"."id"
WHERE
    "projects"."pending_delete" = FALSE
    AND (("projects"."path" ILIKE '%com%'
            OR "projects"."name" ILIKE '%com%')
        OR "projects"."description" ILIKE '%com%')
AND "projects"."archived" = FALSE
AND "projects"."marked_for_deletion_at" IS NULL
AND "projects"."pending_delete" = FALSE
AND "repository_languages"."programming_language_id" = 12
AND "projects"."hidden" = FALSE
ORDER BY
    LOWER("projects"."name") ASC
LIMIT 20 OFFSET 180

Screenshots or screen recordings

Current user: non-admin

Table
Before After
Show project Screenshot_2023-06-19_at_3.59.40_PM Screenshot_2023-06-19_at_3.51.44_PM
User dashboard project list Screenshot_2023-06-19_at_4.02.37_PM (Empty state) Screenshot_2023-06-19_at_4.02.50_PM
Explore projects Screenshot_2023-06-19_at_4.42.06_PM (Empty state) Screenshot_2023-06-19_at_4.41.53_PM

Current user: admin

Table
After
Show project Screenshot_2023-06-19_at_5.07.50_PM
User dashboard project list Screenshot_2023-06-19_at_5.09.30_PM
Explore projects Screenshot_2023-06-19_at_5.07.22_PM

How to set up and validate locally

  1. Enable feature flag 
    $ rails console
> Feature.enable(:hide_projects_of_banned_users)
  2. With a new user or an existing one, create a public project
  3. With another user, try to search for the project from http://localhost:3000/explore/projects and validate that it is returned
  4. Ban the user that owns the project 
    $ rails console
> u = User.find(<project_user_id>)
> u.ban
=> true
  5. With another user, try to search for the project from http://localhost:3000/explore/projects and validate that it is NOT returned

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Eugie Limpin

Merge request reports