Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

3.5 KB
Raw Blame
package store_test

import (
	"testing"

	"github.com/stretchr/testify/require"
)

// saved_query_star is keyed (saved_query, principal) and its parent key is
// globally unique, so it carries no project-scoped id-reuse risk. What it does
// carry is the purge: DeleteProjects is the one writer whose star predicate
// reaches outside the project being purged, because a project principal may
// have starred another project's saved query. This locks down that reach —
// rows belonging to a surviving project must not be collateral.

func TestDeleteProjectPurgeKeepsOtherProjectSavedQueryStars(t *testing.T) {
	t.Parallel()
	const seedSQL = `
		INSERT INTO project (resource_id, workspace, name) VALUES ('project-b', 'default', 'Project B');
		INSERT INTO service_account (name, email, workspace, service_key_hash, project)
			VALUES ('SA A', '[email protected]', 'default', '', 'project-a'),
			       ('SA B', '[email protected]', 'default', '', 'project-b');
		INSERT INTO saved_query (resource_id, creator, project, name, statement) VALUES
			('sq-a-sa',   '[email protected]',   'project-a', 'A by SA A',   ''),
			('sq-a-user', '[email protected]',   'project-a', 'A by user',   ''),
			('sq-b-sa',   '[email protected]',   'project-b', 'B by SA B',   ''),
			('sq-b-user', '[email protected]',   'project-b', 'B by user',   '');
		INSERT INTO saved_query_star (saved_query, principal) VALUES
			('sq-a-sa',   '[email protected]'),
			('sq-a-user', '[email protected]'),
			('sq-b-sa',   '[email protected]'),
			('sq-b-sa',   '[email protected]'),
			('sq-b-user', '[email protected]');
	`

	fixture := newStorePostgresFixture(t, seedSQL)
	require.NoError(t, fixture.store.DeleteProjects(fixture.ctx, "default", "project-a"))

	savedQueryExists := func(resourceID string) bool {
		var exists bool
		require.NoError(t, fixture.db.QueryRowContext(fixture.ctx,
			"SELECT EXISTS (SELECT 1 FROM saved_query WHERE resource_id = $1)", resourceID).Scan(&exists))
		return exists
	}
	starExists := func(savedQuery, principal string) bool {
		var exists bool
		require.NoError(t, fixture.db.QueryRowContext(fixture.ctx,
			"SELECT EXISTS (SELECT 1 FROM saved_query_star WHERE saved_query = $1 AND principal = $2)",
			savedQuery, principal).Scan(&exists))
		return exists
	}

	// Project B is untouched: its saved queries, and the stars its own
	// principals hold on them, all survive.
	require.True(t, savedQueryExists("sq-b-sa"), "project B saved query was purged with project A")
	require.True(t, savedQueryExists("sq-b-user"))
	require.True(t, starExists("sq-b-sa", "[email protected]"), "project B principal's star was purged with project A")
	require.True(t, starExists("sq-b-user", "[email protected]"), "a user's star on project B was purged with project A")

	// Project A's own rows go: the SA-created query with it, and the
	// user-created one is re-parented to the default project rather than
	// deleted, so its star has to survive with it.
	require.False(t, savedQueryExists("sq-a-sa"))
	require.False(t, starExists("sq-a-sa", "[email protected]"))
	require.True(t, savedQueryExists("sq-a-user"))
	require.True(t, starExists("sq-a-user", "[email protected]"))

	var reparented string
	require.NoError(t, fixture.db.QueryRowContext(fixture.ctx,
		"SELECT project FROM saved_query WHERE resource_id = 'sq-a-user'").Scan(&reparented))
	require.Equal(t, "default", reparented)

	// The purged project's service account is gone, so the star it held on
	// project B's saved query goes with it — that is the one cross-project
	// deletion the purge is allowed to make.
	require.False(t, starExists("sq-b-sa", "[email protected]"))
}