sql: database owner should be allowed to run ALTER DATABASE ... SET

[rafiss]

Describe the problem

Database owners should be allowed to run ALTER DATABASE ... SET ... commands.

This is the Postgres behavior, so we should be compatible: https://www.postgresql.org/docs/current/sql-alterdatabase.html

To Reproduce

As root

root@localhost:26257/defaultdb> create database d;
CREATE DATABASE

root@localhost:26257/defaultdb> create user testuser;
CREATE ROLE

root@localhost:26257/defaultdb> alter database d owner to testuser;
ALTER DATABASE

root@localhost:26257/defaultdb> \q

As testuser

testuser@localhost:26257/defaultdb> alter database d set application_name = 'abc';
ERROR: only users with the admin role are allowed to ALTER ROLE ALL ... SET
SQLSTATE: 42501

Expected behavior

The last command should succeed.

Jira issue: CRDB-38275

[kaustubhbabar5]

Hii, I would like to work on this

[kaustubhbabar5]

as far as I can understand

1. For both Postgres and Cockroach ALTER DATABASE ... SET ... and ALTER ROLE ALL IN DATABASE SET ... change the session defaults for a database and should effetively be same.
2. Only the database owner or a superuser (admin in case of cockroach) can change the session defaults for a database
3. database owner cannot set certain variables using ALTER DATABASE ... SET ..., only a superuser can

the issues here are

• database owners are not allowed to either ALTER DATABASE ... SET ... or ALTER ROLE ALL IN DATABASE SET ...
• we don't any checks for point 3

[kaustubhbabar5]

Hii @rafiss, need your inputs here.