backupccl: Add option to RESTORE to preserve grants to specified users

[dt]

Currently all BACKUPs include the privilege grant information on individual tables and databases, even if those backups do not include the actual users/roles to which those privileges are granted.

During restore, these grants are wiped and replaced with default privileges as if the restore had created new tables, since it isn't obvious that RESTORE can assume that a grant to user "jsmith" on the old cluster should still apply to user "jsmith" on the new cluster -- they may not be the same person, they may have different access, or may not even exist, etc.

However if can operator is restoring into the same cluster or another cluster that does have the same users configured, it would be nice if they could choose to preserve that grant information instead of being forced to re-grant everything from scratch.

An option like RESTORE mytable ... WITH preserve_grants_for = 'jsmith,dtaylor,...' would allow operators to express when a grant to a given pre-backup user should be preserved and apply to a post-restore user. As a shorthand, preserve_grants_for = '*' could mean for all users (and error if any table in the backup has a grant to a username that does not exist on the restoring cluster).

Epic: CRDB-9127

Jira issue: CRDB-5156

gz#11757

[pbardea]

A first step here could be to just add a flag preserve_grants to restore that doesn't reset the grants on the descriptors. It would also perform additional validation:

• The user performing the restore should be able to DROP all of the descriptors being restored
• All of the users/roles referenced by the grants on the descriptors should exist. This check should be done transactionally with the creation of the descriptors to avoid races with concurrent user changes while the restore is running.

[pbardea]

cc @livlobo @mwang1026 for visibility

[mwang1026]

All of the users/roles referenced by the grants on the descriptors should exist. This check should be done transactionally with the creation of the descriptors to avoid races with concurrent user changes while the restore is running.

Would it throw an error with the invalid user name? And would the UX be that they would have to create that user before the RESTORE would succeed? Alternatives could be

• Exclude grants for certain individuals (so WITH preserve_grants,exclude_grant_for=(liv,michael) or something like that)
• The above -- they have to create that user
• Create that user (I think we should just not consider this without customer pushback, but here for completeness)
• Something else?

Also, re: restoring of system.users we don't allow that, right? So you'd have to restore into a temp table, then insert into the users table? Would it preserve the passwords as well?

[dt]

@mwang1026 @livlobo and I think @vy-ton: Another question is what we should do with the default grants: do we replace them with the backed up grants (so you get back exactly what was backed up, which is generally the expectation) or do we add the backed up grants to the default grants on the newly created restoring tables, so that root/admin on the new cluster are assured to have the usual grants?

[mwang1026]

Are the default grants inherited or are they explicit? Like could you revoke select from admin on a table?

[RichardJCai]

Are the default grants inherited or are they explicit? Like could you revoke select from admin on a table?

What does "default grants" mean here?

Like could you revoke select from admin on a table?
You can't do this at all in CockroachDB. It errors if you try.

[mwang1026]

That that's what I meant by "default" -- that root and admin have grants to everything. I wasn't sure if that's explicit (i.e. the descriptor explicitly says root has access) or whether it's inferred/inherited (i.e. we don't need the descriptor to say so because it is just fact that admin and root have access to everything)

[livlobo]

Would it throw an error with the invalid user name? And would the UX be that they would have to create that user before the RESTORE would succeed?

• If the user referenced by the grant on a descriptor does not exist, we could throw an error indicating the invalid usernames. Is it feasible to detect all invalid usernames and message that to the user? @dt
• The error/notice could include a suggestion that they can either use the exclude_grants_for=(liv, michael) option or create user to continue with restore.

Also, re: restoring of system.users we don't allow that, right? So you'd have to restore into a temp table, then insert into the users table? Would it preserve the passwords as well?

• Is it feasible to do this automatically under-the-hood? @dt

Another question is what we should do with the default grants: do we replace them with the backed up grants (so you get back exactly what was backed up, which is generally the expectation) or do we add the backed up grants to the default grants on the newly created restoring tables, so that root/admin on the new cluster are assured to have the usual grants?

• Re: "default grants" on the admin role: The admin role will always have ALL privileges since they cannot be revoked, if I understand correctly. So,admin (and thereby root) will have the usual grants, whether we restore them or not.
• Re: "default grants" on the public role: It makes sense to replace the privileges on the public role with the backed up privileges, so you get exactly what was backed up, since in my opinion, that would be most intuitive for the user.

[mwang1026]

IIRC in the demo that we saw from Casper

• It would error if the user you're trying to restore grants for did not exist in the cluster -- I believe this is desired
• It would inherit the grants from the database (e.g. if you did RESTORE DATABASE d1 ... WITH preserve_grants=user1), and if user1 had select on d1 but only insert on t1, the user would inherit the select on d1 for table t1 so grants on the restored t1 would be select, insert when on the backup they only had insert. I think we want it so that they only have insert.

[livlobo]

@gh-casper - Could you comment on the status of this PR?