role_members_ids_migration.go

// Copyright 2022 The Cockroach Authors.
//
// Use of this software is governed by the Business Source License
// included in the file licenses/BSL.txt.
//
// As of the Change Date specified in that file, in accordance with
// the Business Source License, use of this software will be governed
// by the Apache License, Version 2.0, included in the file
// licenses/APL.txt.

package upgrades

import (
	"context"

	"github.com/cockroachdb/cockroach/pkg/clusterversion"
	"github.com/cockroachdb/cockroach/pkg/keys"
	"github.com/cockroachdb/cockroach/pkg/sql/catalog/systemschema"
	"github.com/cockroachdb/cockroach/pkg/sql/sessiondata"
	"github.com/cockroachdb/cockroach/pkg/upgrade"
)

const addIDColumnsToRoleMembersStmt = `
ALTER TABLE system.role_members
ADD COLUMN IF NOT EXISTS role_id OID,
ADD COLUMN IF NOT EXISTS member_id OID
`

func alterSystemRoleMembersAddIDColumns(
	ctx context.Context, cs clusterversion.ClusterVersion, d upgrade.TenantDeps,
) error {
	op := operation{
		name:           "add-id-columns-system-role-members",
		schemaList:     []string{"role_id", "member_id"},
		query:          addIDColumnsToRoleMembersStmt,
		schemaExistsFn: columnExists,
	}
	return migrateTable(ctx, cs, d, op, keys.RoleMembersTableID, systemschema.RoleMembersTable)
}

const addIndexOnRoleIDToRoleMembersStmt = `
CREATE INDEX role_members_role_id_idx ON system.role_members (role_id ASC)
`

const addIndexOnMemberIDToRoleMembersStmt = `
CREATE INDEX role_members_member_id_idx ON system.role_members (member_id ASC)
`

const addUniqueIndexOnIDsToRoleMembersStmt = `
CREATE UNIQUE INDEX role_members_role_id_member_id_key ON system.role_members (role_id ASC, member_id ASC)
`

func alterSystemRoleMembersAddIndexesForIDColumns(
	ctx context.Context, cs clusterversion.ClusterVersion, d upgrade.TenantDeps,
) error {
	for _, op := range []operation{
		{
			name:           "add-role-id-index-system-role-members",
			schemaList:     []string{"role_members_role_id_idx"},
			query:          addIndexOnRoleIDToRoleMembersStmt,
			schemaExistsFn: hasIndex,
		},
		{
			name:           "add-member-id-index-system-role-members",
			schemaList:     []string{"role_members_member_id_idx"},
			query:          addIndexOnMemberIDToRoleMembersStmt,
			schemaExistsFn: hasIndex,
		},
		{
			name:           "add-id-unique-index-system-role-members",
			schemaList:     []string{"role_members_role_id_member_id_key"},
			query:          addUniqueIndexOnIDsToRoleMembersStmt,
			schemaExistsFn: hasIndex,
		},
	} {
		if err := migrateTable(ctx, cs, d, op, keys.RoleMembersTableID, systemschema.RoleMembersTable); err != nil {
			return err
		}
	}

	return nil
}

const backfillRoleIDColumnRoleMemberStmt = `
UPDATE system.role_members
SET role_id = u.user_id
FROM system.users AS u
WHERE role_id IS NULL AND role = u.username
LIMIT 1000
`

const backfillMemberIDColumnRoleMembersStmt = `
UPDATE system.role_members
SET member_id = u.user_id
FROM system.users AS u
WHERE member_id IS NULL AND member = u.username
LIMIT 1000
`

func backfillSystemRoleMembersIDColumns(
	ctx context.Context, _ clusterversion.ClusterVersion, d upgrade.TenantDeps,
) error {
	ie := d.InternalExecutorFactory.MakeInternalExecutorWithoutTxn()
	for _, backfillStmt := range []string{backfillRoleIDColumnRoleMemberStmt, backfillMemberIDColumnRoleMembersStmt} {
		for {
			rowsAffected, err := ie.ExecEx(ctx, "backfill-id-columns-system-role-members", nil, /* txn */
				sessiondata.NodeUserSessionDataOverride,
				backfillStmt,
			)
			if err != nil {
				return err
			}
			if rowsAffected == 0 {
				break
			}
		}
	}

	return nil
}

// TODO(yang): Add a migration for making the ID columns not-null. Choosing to
// put this in a separate migration so that we can handle any BACKUP/RESTORE
// changes in a separate PR.