role

# LogicTest: local

statement error a role/user named admin already exists
CREATE ROLE admin

statement error a role/user named root already exists
CREATE ROLE root

statement ok
CREATE ROLE IF NOT EXISTS root

statement ok
CREATE ROLE IF NOT EXISTS admin

statement error pq: cannot drop role/user admin: grants still exist on .*
DROP ROLE admin

statement error pq: cannot drop role/user root: grants still exist on .*
DROP ROLE root

statement error pq: cannot drop roles/users admin, root: grants still exist on .*
DROP ROLE admin, root

statement ok
CREATE ROLE myrole

query TTT colnames
SHOW ROLES
----
username  options                  member_of
admin     CREATEROLE, SETPASSWORD  {}
myrole    NOLOGIN                  {}
root      CREATEROLE, SETPASSWORD  {admin}
testuser  ·                        {}

statement error a role/user named myrole already exists
CREATE ROLE myrole

statement ok
CREATE ROLE IF NOT EXISTS myrole

statement error a role/user named myrole already exists
CREATE USER myrole

statement ok
DROP USER myrole

statement ok
CREATE ROLE myrole

statement ok
CREATE USER IF NOT EXISTS myrole

statement error pq: cannot drop roles/users admin, myrole: grants still exist on .*
DROP ROLE admin, myrole

query TTT colnames
SHOW ROLES
----
username  options                  member_of
admin     CREATEROLE, SETPASSWORD  {}
myrole    NOLOGIN                  {}
root      CREATEROLE, SETPASSWORD  {admin}
testuser  ·                        {}

statement ok
DROP ROLE myrole

query TTT colnames
SHOW ROLES
----
username  options                  member_of
admin     CREATEROLE, SETPASSWORD  {}
root      CREATEROLE, SETPASSWORD  {admin}
testuser  ·                        {}

statement error pq: role/user myrole does not exist
DROP ROLE myrole

statement ok
DROP ROLE IF EXISTS myrole

statement ok
CREATE ROLE rolea

statement ok
CREATE ROLE roleb

statement ok
CREATE ROLE rolec

statement ok
CREATE ROLE roled

statement error pq: role/user rolee does not exist
DROP ROLE rolea, roleb, rolec, roled, rolee

statement ok
DROP ROLE IF EXISTS rolec, roled, rolee

statement ok
DROP ROLE rolea, roleb

query TTT colnames
SHOW ROLES
----
username  options                  member_of
admin     CREATEROLE, SETPASSWORD  {}
root      CREATEROLE, SETPASSWORD  {admin}
testuser  ·                        {}

statement ok
CREATE USER testuser2

statement ok
CREATE ROLE testrole

query TTB colnames
SHOW GRANTS ON ROLE
----
role_name  member  is_admin
admin      root    true

query TTT colnames,rowsort
SELECT * FROM information_schema.administrable_role_authorizations
----
grantee  role_name  is_grantable
root     admin      YES

query TTT colnames,rowsort
SELECT * FROM information_schema.applicable_roles
----
grantee  role_name  is_grantable
root     admin      YES

query T colnames,rowsort
SELECT * FROM information_schema.enabled_roles
----
role_name
admin
root

statement error pq: role/user unknownuser does not exist
GRANT testrole TO unknownuser

statement error pq: role/user unknownrole does not exist
GRANT unknownrole TO testuser

# Test role "grant" and WITH ADMIN option.
user testuser

statement error pq: testuser is not a superuser or role admin for role testrole
GRANT testrole TO testuser2

user root

statement ok
GRANT testrole TO testuser

query TTB colnames
SELECT * FROM system.role_members
----
role      member    isAdmin
admin     root      true
testrole  testuser  false

query TTB colnames
SHOW GRANTS ON ROLE
----
role_name  member    is_admin
admin      root      true
testrole   testuser  false

user testuser

statement error pq: testuser is not a superuser or role admin for role testrole
GRANT testrole TO testuser2

user root

statement ok
GRANT testrole TO testuser WITH ADMIN OPTION

query TTB colnames
SELECT * FROM system.role_members
----
role      member    isAdmin
admin     root      true
testrole  testuser  true

user testuser

statement ok
GRANT testrole TO testuser2 WITH ADMIN OPTION

query TTT colnames,rowsort
SELECT * FROM information_schema.administrable_role_authorizations
----
grantee   role_name  is_grantable
testuser  testrole   YES

query TTT colnames,rowsort
SELECT * FROM information_schema.applicable_roles
----
grantee   role_name  is_grantable
testuser  testrole   YES

query T colnames,rowsort
SELECT * FROM information_schema.enabled_roles
----
role_name
testrole
testuser

user root

statement ok
GRANT admin TO testuser

# Verify that is_admin reports the right value.
query B
SELECT crdb_internal.is_admin()
----
true

# Dropping users/roles deletes all their memberships.
query TTB colnames
SELECT * FROM system.role_members
----
role      member     isAdmin
admin     root       true
admin     testuser   false
testrole  testuser   true
testrole  testuser2  true

query TTB colnames
SHOW GRANTS ON ROLE
----
role_name  member     is_admin
admin      root       true
admin      testuser   false
testrole   testuser   true
testrole   testuser2  true

query TTB colnames
SHOW GRANTS ON ROLE admin
----
role_name  member    is_admin
admin      root      true
admin      testuser  false

query TTB colnames
SHOW GRANTS ON ROLE FOR testuser
----
role_name  member    is_admin
admin      testuser  false
testrole   testuser  true

query TTB colnames
SHOW GRANTS ON ROLE testrole FOR testuser2
----
role_name  member     is_admin
testrole   testuser2  true

query TTB colnames
SHOW GRANTS ON ROLE foo,testrole
----
role_name  member     is_admin
testrole   testuser   true
testrole   testuser2  true

query TTB colnames
SHOW GRANTS ON ROLE FOR testuser, testuser2
----
role_name  member     is_admin
admin      testuser   false
testrole   testuser   true
testrole   testuser2  true

query TTB colnames
SHOW GRANTS ON ROLE admin, testrole FOR root, testuser2
----
role_name  member     is_admin
admin      root       true
testrole   testuser2  true

statement ok
DROP USER testuser

statement ok
CREATE USER testuser

query TTB colnames
SELECT * FROM system.role_members
----
role      member     isAdmin
admin     root       true
testrole  testuser2  true

statement ok
DROP ROLE testrole

query TTB colnames
SELECT * FROM system.role_members
----
role      member     isAdmin
admin     root       true

# Test cycle detection.
statement error pq: admin cannot be a member of itself
GRANT admin TO admin

statement ok
CREATE ROLE rolea

statement ok
CREATE ROLE roleb

statement ok
CREATE ROLE rolec

statement ok
CREATE ROLE roled

statement ok
GRANT rolea TO roleb

statement error pq: making rolea a member of roleb would create a cycle
GRANT roleb TO rolea

statement ok
GRANT roleb TO rolec

statement ok
GRANT rolec TO roled

statement error pq: rolea cannot be a member of itself
GRANT rolea TO rolea

statement error pq: making rolea a member of roleb would create a cycle
GRANT roleb TO rolea

statement error pq: making rolea a member of rolec would create a cycle
GRANT rolec TO rolea

statement error pq: making rolea a member of roled would create a cycle
GRANT roled TO rolea

statement ok
CREATE ROLE rolee

# Test inherited ADMIN OPTION.
statement ok
GRANT roled TO testuser

statement ok
GRANT rolea TO roleb WITH ADMIN OPTION

user testuser

query TTT colnames,rowsort
SELECT * FROM information_schema.administrable_role_authorizations
----
grantee   role_name  is_grantable
testuser  rolea      YES

query TTT colnames,rowsort
SELECT * FROM information_schema.applicable_roles
----
grantee   role_name  is_grantable
testuser  roled      NO
testuser  rolec      NO
testuser  roleb      NO
testuser  rolea      YES

query T colnames,rowsort
SELECT * FROM information_schema.enabled_roles
----
role_name
rolea
roleb
rolec
roled
testuser

statement error pq: testuser is not a superuser or role admin for role roled
GRANT roled TO rolee

statement error pq: testuser is not a superuser or role admin for role rolec
GRANT rolec TO rolee

statement error pq: testuser is not a superuser or role admin for role roleb
GRANT roleb TO rolee

statement ok
GRANT rolea TO rolee

query TTT colnames,rowsort
SELECT * FROM information_schema.administrable_role_authorizations
----
grantee   role_name  is_grantable
testuser  rolea      YES

query TTT colnames,rowsort
SELECT * FROM information_schema.applicable_roles
----
grantee   role_name  is_grantable
testuser  rolec      NO
testuser  roleb      NO
testuser  rolea      YES
testuser  roled      NO

query T colnames,rowsort
SELECT * FROM information_schema.enabled_roles
----
role_name
rolea
roleb
rolec
roled
testuser

user root

query TTB colnames
SELECT * FROM system.role_members
----
role   member    isAdmin
admin  root      true
rolea  roleb     true
rolea  rolee     false
roleb  rolec     false
rolec  roled     false
roled  testuser  false

statement ok
DROP ROLE rolea

statement ok
DROP ROLE rolec

query TTB colnames
SELECT * FROM system.role_members
----
role   member    isAdmin
admin  root      true
roled  testuser false

query TTT
SHOW ROLES
----
admin      CREATEROLE, SETPASSWORD  {}
roleb      NOLOGIN                  {}
roled      NOLOGIN                  {}
rolee      NOLOGIN                  {}
root       CREATEROLE, SETPASSWORD  {admin}
testuser   ·                        {roled}
testuser2  ·                        {}

statement ok
DROP ROLE roleb

statement ok
DROP ROLE roled

statement ok
DROP ROLE rolee

statement error pq: role/user root cannot be removed from role admin or lose the ADMIN OPTION
REVOKE admin FROM root

statement error pq: role/user root cannot be removed from role admin or lose the ADMIN OPTION
REVOKE ADMIN OPTION FOR admin FROM root

statement error pq: role/user unknownuser does not exist
REVOKE ADMIN OPTION FOR admin FROM unknownuser

statement error pq: role/user unknownrole does not exist
REVOKE ADMIN OPTION FOR unknownrole FROM root

statement ok
CREATE ROLE rolea

statement ok
CREATE ROLE roleb

statement ok
GRANT rolea,roleb TO testuser WITH ADMIN OPTION

query TTB colnames
SELECT * FROM system.role_members
----
role   member    isAdmin
admin  root      true
rolea  testuser  true
roleb  testuser  true

user testuser

statement ok
GRANT rolea,roleb TO root WITH ADMIN OPTION

user root

query TTB colnames
SELECT * FROM system.role_members
----
role   member    isAdmin
admin  root      true
rolea  root      true
rolea  testuser  true
roleb  root      true
roleb  testuser  true

query TTT colnames,rowsort
SELECT * FROM information_schema.administrable_role_authorizations
----
grantee  role_name  is_grantable
root     admin      YES
root     rolea      YES
root     roleb      YES

query TTT colnames,rowsort
SELECT * FROM information_schema.applicable_roles
----
grantee  role_name  is_grantable
root     admin      YES
root     rolea      YES
root     roleb      YES

query T colnames,rowsort
SELECT * FROM information_schema.enabled_roles
----
role_name
admin
rolea
roleb
root

user testuser

query TTT colnames,rowsort
SELECT * FROM information_schema.administrable_role_authorizations
----
grantee   role_name  is_grantable
testuser  rolea      YES
testuser  roleb      YES

query TTT colnames,rowsort
SELECT * FROM information_schema.applicable_roles
----
grantee   role_name  is_grantable
testuser  rolea      YES
testuser  roleb      YES

query T colnames,rowsort
SELECT * FROM information_schema.enabled_roles
----
role_name
rolea
roleb
testuser

statement ok
REVOKE ADMIN OPTION FOR rolea FROM testuser

statement error pq: testuser is not a superuser or role admin for role rolea
REVOKE ADMIN OPTION FOR rolea FROM root

statement ok
REVOKE roleb FROM root

user root

query TTB colnames
SELECT * FROM system.role_members
----
role   member    isAdmin
admin  root      true
rolea  root      true
rolea  testuser  false
roleb  testuser  true

statement ok
REVOKE rolea, roleb FROM testuser, root

query TTB colnames
SELECT * FROM system.role_members
----
role   member    isAdmin
admin  root      true

# Test privilege checks.

statement ok
CREATE DATABASE db1

user testuser

statement error only users with the admin role are allowed to CREATE DATABASE
CREATE DATABASE db2

statement error user testuser does not have DROP privilege on database db1
DROP DATABASE db1

statement error testuser is not a role admin for role admin
GRANT admin TO testuser

user root

statement ok
CREATE ROLE newgroup

statement ok
GRANT newgroup TO testuser

statement ok
GRANT admin TO newgroup

user testuser

query TTB colnames
SELECT * FROM system.role_members
----
role      member    isAdmin
admin     newgroup  false
admin     root      true
newgroup  testuser  false

statement ok
CREATE DATABASE db2

statement ok
DROP DATABASE db1

# Revoke admin privileges. 'newgroup' does not have any privileges.
user root

statement ok
REVOKE admin FROM newgroup

user testuser

statement error user testuser does not have SELECT privilege on relation role_members
SELECT * FROM system.role_members

statement error user testuser does not have CREATE privilege on database db2
CREATE TABLE db2.foo (k int);

user root

query TTB colnames
SELECT * FROM system.role_members
----
role      member    isAdmin
admin     root      true
newgroup  testuser  false

statement ok
GRANT ALL ON DATABASE db2 TO newgroup

user testuser

query TTTT colnames
SHOW GRANTS ON DATABASE db2
----
database_name  schema_name         grantee   privilege_type
db2            crdb_internal       admin     ALL
db2            crdb_internal       newgroup  ALL
db2            crdb_internal       root      ALL
db2            information_schema  admin     ALL
db2            information_schema  newgroup  ALL
db2            information_schema  root      ALL
db2            pg_catalog          admin     ALL
db2            pg_catalog          newgroup  ALL
db2            pg_catalog          root      ALL
db2            pg_extension        admin     ALL
db2            pg_extension        newgroup  ALL
db2            pg_extension        root      ALL
db2            public              admin     ALL
db2            public              newgroup  ALL
db2            public              root      ALL

statement ok
CREATE TABLE db2.foo (k int);

statement ok
INSERT INTO db2.foo VALUES (1),(2),(3);

statement ok
SELECT * FROM db2.foo

# We may be in the 'newgroup', but we don't have the admin option.
statement error testuser is not a superuser or role admin for role newgroup
GRANT newgroup TO testuser2

statement error testuser is not a superuser or role admin for role newgroup
REVOKE newgroup FROM testuser

statement error testuser is not a superuser or role admin for role newgroup
GRANT newgroup TO testuser WITH ADMIN OPTION

# Regression for #31784
user root

# grant admin to testuser without ADMIN OPTION
statement ok
CREATE USER user1;
GRANT admin TO testuser

user testuser

statement error pq: testuser is not a role admin for role admin
GRANT admin TO user1

statement error pq: testuser is not a role admin for role admin
REVOKE admin FROM user1

user root

# WITH ADMIN OPTION means that testuser now has permission to add to the admin role
statement ok
GRANT admin TO testuser WITH ADMIN OPTION

user testuser

statement ok
GRANT admin TO user1

statement ok
REVOKE admin FROM user1

user root

statement ok
DROP USER user1

user root

# The user does not have direct privileges on anything, so we can drop it.
statement ok
DROP USER testuser

query TTB colnames
SELECT * FROM system.role_members
----
role      member    isAdmin
admin     root      true

statement error cannot drop role/user newgroup: grants still exist on db2, db2.public.foo
DROP ROLE newgroup

statement ok
REVOKE ALL ON db2.* FROM newgroup

statement ok
REVOKE ALL ON DATABASE db2 FROM newgroup

statement ok
DROP ROLE newgroup

# Test the "public" pseudo-role.

statement error role name "public" is reserved
CREATE USER public

statement error role name "public" is reserved
CREATE ROLE public

statement error cannot drop role/user public: grants still exist on system.public.comments
DROP USER public

statement error cannot drop role/user public: grants still exist on system.public.comments
DROP ROLE public

statement error role/user public does not exist
GRANT public TO testuser

statement error role/user public does not exist
GRANT admin TO public

statement error role/user public does not exist
REVOKE public FROM testuser

statement error role/user public does not exist
REVOKE admin FROM public

# Test "WITH CREATEROLE" option

statement ok
CREATE USER testuser

query TTB colnames
SELECT * FROM system.role_members
----
role        member     isAdmin
admin       root       true


user testuser

statement error pq: user testuser does not have CREATEROLE privilege
CREATE ROLE rolef

user root

statement ok
ALTER ROLE testuser CREATEROLE

user testuser

statement ok
CREATE ROLE rolef

statement ok
ALTER ROLE rolef LOGIN

statement ok
DROP ROLE rolef

# Testing invalid CREATEROLE combinations
user root

statement ok
ALTER ROLE testuser NOCREATEROLE

statement error pq: conflicting role options
CREATE ROLE rolewithcreate WITH NOCREATEROLE CREATEROLE

statement error pq: conflicting role options
CREATE ROLE rolewithcreate NOCREATEROLE CREATEROLE

statement error pq: conflicting role options
ALTER ROLE testrole WITH CREATEROLE NOCREATEROLE

statement error pq: conflicting role options
ALTER ROLE testrole CREATEROLE NOCREATEROLE

statement error pq: redundant role options
CREATE ROLE rolewithcreate WITH CREATEROLE CREATEROLE

statement error pq: redundant role options
CREATE ROLE rolewithcreate WITH NOCREATEROLE NOCREATEROLE

statement error pq: redundant role options
ALTER ROLE testrole WITH CREATEROLE CREATEROLE

statement error pq: redundant role options
ALTER ROLE testrole WITH NOCREATEROLE NOCREATEROLE

statement ok
CREATE ROLE rolewithcreate WITH CREATEROLE

statement ok
CREATE ROLE anotherrolewithcreate CREATEROLE

statement ok
CREATE ROLE rolewithoutcreate WITH NOCREATEROLE

statement ok
CREATE ROLE IF NOT EXISTS rolewithcreate2 WITH CREATEROLE

statement ok
CREATE ROLE IF NOT EXISTS anotherrolewithcreate2 CREATEROLE

statement ok
CREATE ROLE IF NOT EXISTS rolewithoutcreate2 WITH NOCREATEROLE

query TTB colnames
SELECT * FROM system.role_members
----
role      member    isAdmin
admin     root      true

user testuser

# User should not have permissions to CREATE / ALTER role without CREATEROLE privilege
statement error pq: user testuser does not have CREATEROLE privilege
CREATE ROLE rolewithcreate3 WITH CREATEROLE

statement error pq: user testuser does not have CREATEROLE privilege
ALTER ROLE rolewithcreate WITH NOCREATEROLE

user root

statement ok
GRANT rolewithcreate TO testuser

user testuser

statement ok
CREATE ROLE roleg WITH CREATEROLE

statement ok
ALTER ROLE roleg WITH NOCREATEROLE

statement ok
DROP ROLE roleg

statement ok
CREATE ROLE IF NOT EXISTS roleg

statement ok
CREATE ROLE IF NOT EXISTS roleg

# Need Admin option to GRANT role, CREATEROLE should not give GRANT role privilege for other roles
statement ok
CREATE USER testuser3

statement error pq: testuser is not a role admin for role admin
GRANT admin to testuser3

statement error pq: testuser is not a superuser or role admin for role roleg
GRANT roleg to testuser3

user root

statement ok
ALTER ROLE rolewithcreate WITH NOCREATEROLE

statement ok
ALTER ROLE rolewithcreate NOCREATEROLE

statement error pq: cannot edit admin role
ALTER ROLE admin with NOCREATEROLE

query TTB colnames
SELECT * FROM system.role_members
----
role      member    isAdmin
admin     root      true
rolewithcreate  testuser  false

# testuser should no longer have CREATEROLE privileges

user testuser

statement error pq: user testuser does not have CREATEROLE privilege
CREATE ROLE roleh WITH CREATEROLE

statement error pq: user testuser does not have CREATEROLE privilege
ALTER ROLE roleg with NOCREATEROLE

statement error pq: user testuser does not have CREATEROLE privilege
DROP ROLE roleg

statement error pq: user testuser does not have CREATEROLE privilege
CREATE ROLE IF NOT EXISTS rolewithcreate WITH CREATEROLE

statement error pq: user testuser does not have CREATEROLE privilege
CREATE USER testuser4

statement error pq: user testuser does not have CREATEROLE privilege
ALTER USER testuser3 WITH PASSWORD 'ilov3beefjerky'

user root

statement error pq: role/user rolek does not exist
ALTER ROLE rolek CREATEROLE

statement ok
ALTER ROLE IF EXISTS rolek CREATEROLE

statement ok
ALTER USER IF EXISTS rolek NOCREATEROLE

statement ok
ALTER USER rolewithcreate WITH NOCREATEROLE

statement ok
ALTER ROLE rolewithcreate CREATEROLE

user testuser

statement ok
CREATE ROLE IF NOT EXISTS rolei WITH NOCREATEROLE

statement ok
DROP ROLE rolewithcreate

statement error pq: user testuser does not have CREATEROLE privilege
CREATE ROLE rolewithcreate

statement error pq: user testuser does not have CREATEROLE privilege
CREATE ROLE IF NOT EXISTS roleh WITH CREATEROLE

# Testing nested role privilege
user root

statement ok
CREATE USER childrole WITH NOCREATEROLE

statement ok
CREATE ROLE parentrole WITH CREATEROLE

statement ok
GRANT parentrole TO childrole

statement ok
GRANT childrole to testuser

user testuser

statement ok
CREATE ROLE rolej

# Testing LOGIN and VALID UNTIL role privilege
user root

statement ok
DELETE FROM system.role_options WHERE NOT username in ('root', 'admin')

statement ok
CREATE ROLE rolewithlogin LOGIN

query TTT
SELECT * FROM system.role_options
----
admin  CREATEROLE   NULL
admin  SETPASSWORD  NULL
root   CREATEROLE   NULL
root   SETPASSWORD  NULL

statement ok
CREATE ROLE rolewithnologin NOLOGIN

query TTT
SELECT * FROM system.role_options
----
admin            CREATEROLE   NULL
admin            SETPASSWORD  NULL
rolewithnologin  NOLOGIN      NULL
root             CREATEROLE   NULL
root             SETPASSWORD  NULL

statement ok
ALTER ROLE rolewithlogin VALID UNTIL '2020-01-01'

query TTT
SELECT * FROM system.role_options
----
admin            CREATEROLE   NULL
admin            SETPASSWORD  NULL
rolewithlogin    VALID UNTIL  2020-01-01 00:00:00+00:00
rolewithnologin  NOLOGIN      NULL
root             CREATEROLE   NULL
root             SETPASSWORD  NULL

statement ok
ALTER ROLE rolewithlogin VALID UNTIL NULL

query TTT
SELECT * FROM system.role_options
----
admin            CREATEROLE   NULL
admin            SETPASSWORD  NULL
rolewithlogin    VALID UNTIL  NULL
rolewithnologin  NOLOGIN      NULL
root             CREATEROLE   NULL
root             SETPASSWORD  NULL

statement ok
DROP ROLE rolewithlogin

query TTT
SELECT * FROM system.role_options
----
admin            CREATEROLE   NULL
admin            SETPASSWORD  NULL
rolewithnologin  NOLOGIN      NULL
root             CREATEROLE   NULL
root             SETPASSWORD  NULL

statement error pq: conflicting role options
CREATE ROLE thisshouldntwork LOGIN NOLOGIN

statement error pq: redundant role options
CREATE ROLE thisshouldntwork LOGIN LOGIN

statement ok
DROP ROLE childrole

statement ok
DROP ROLE parentrole

query TTB colnames
SHOW GRANTS ON ROLE
----
role_name  member  is_admin
admin      root    true

query TTB colnames
SHOW GRANTS ON ROLE admin
----
role_name  member  is_admin
admin      root    true

query TTB colnames
SHOW GRANTS ON ROLE FOR root
----
role_name  member  is_admin
admin      root    true

query TTB colnames
SHOW GRANTS ON ROLE admin FOR root
----
role_name  member  is_admin
admin      root    true

query TTB colnames
SHOW GRANTS ON ROLE FOR testuser
----
role_name  member  is_admin

query TTB colnames
SHOW GRANTS ON ROLE testuser,admin FOR testuser,admin
----
role_name  member  is_admin

# Test the "public" pseudo-role.

statement error role name "public" is reserved
CREATE USER public

statement error cannot drop role/user public: grants still exist on system.public.comments
DROP USER public

statement ok
CREATE DATABASE publicdb;

statement ok
CREATE DATABASE privatedb;

statement ok
CREATE TABLE publicdb.publictable (k int)

statement ok
CREATE TABLE publicdb.privatetable (k int)

statement ok
CREATE TABLE privatedb.publictable (k int)

statement ok
CREATE TABLE privatedb.privatetable (k int)

statement ok
GRANT GRANT,SELECT ON DATABASE publicdb TO public

statement ok
GRANT GRANT,SELECT ON publicdb.publictable TO public

statement ok
GRANT GRANT,SELECT ON privatedb.publictable TO public

user testuser

query T
SHOW DATABASES
----
publicdb

query TTT
SHOW TABLES FROM publicdb
----
public  publictable  table

query TTT
SHOW TABLES FROM privatedb
----

statement ok
SELECT * FROM publicdb.publictable

statement error user testuser does not have SELECT privilege on relation privatetable
SELECT * FROM publicdb.privatetable

statement ok
SELECT * FROM privatedb.publictable

statement error user testuser does not have SELECT privilege on relation privatetable
SELECT * FROM privatedb.privatetable

statement error user testuser does not have INSERT privilege on relation publictable
INSERT INTO publicdb.publictable VALUES (1)

user root

statement ok
GRANT INSERT ON publicdb.publictable TO public

user testuser

statement ok
INSERT INTO publicdb.publictable VALUES (1)

user root

# Revoke public access.
statement ok
REVOKE ALL ON publicdb.publictable FROM public

user testuser

statement error user testuser does not have SELECT privilege on relation publictable
SELECT * FROM publicdb.publictable

statement error user testuser does not have INSERT privilege on relation publictable
INSERT INTO publicdb.publictable VALUES (1)

query TTT
SHOW TABLES FROM publicdb
----

subtest setpassword_privilege

user root

statement ok
CREATE ROLE pseudo_admin;
  ALTER ROLE pseudo_admin CREATEROLE;
  DROP USER testuser2;
  DROP USER testuser3;
  GRANT pseudo_admin TO testuser

user testuser

# By default, a new role does not have privilege SETPASSWORD.

statement ok
CREATE USER testuser2

statement error user testuser does not have SETPASSWORD privilege
CREATE USER testuser3 WITH PASSWORD 'abc'

statement error user testuser does not have SETPASSWORD privilege
ALTER USER testuser2 WITH PASSWORD 'abc'

statement error user testuser does not have SETPASSWORD privilege
ALTER USER testuser2 VALID UNTIL '2021-01-01'

statement ok
CREATE ROLE otherrole

statement error user testuser does not have SETPASSWORD privilege
ALTER ROLE otherrole SETPASSWORD

statement error user testuser does not have SETPASSWORD privilege
ALTER ROLE otherrole NOSETPASSWORD

statement error user testuser does not have SETPASSWORD privilege
CREATE ROLE otherrole2 SETPASSWORD

statement error user testuser does not have SETPASSWORD privilege
CREATE ROLE otherrole2 NOSETPASSWORD

user root

statement ok
ALTER ROLE pseudo_admin SETPASSWORD

user testuser

statement ok
CREATE USER testuser3 WITH PASSWORD 'abc'

statement ok
ALTER USER testuser3 WITH PASSWORD 'xyz'

statement ok
ALTER USER testuser3 VALID UNTIL '2021-01-01'

statement ok
ALTER ROLE otherrole SETPASSWORD

statement ok
ALTER ROLE otherrole NOSETPASSWORD

statement ok
CREATE ROLE otherrole2 SETPASSWORD

statement ok
CREATE ROLE otherrole3 NOSETPASSWORD

# If SETPASSWORD is revoked, the changes are prevented again.

user root

statement ok
ALTER ROLE pseudo_admin NOSETPASSWORD

user testuser

statement error user testuser does not have SETPASSWORD privilege
CREATE USER testuser4 WITH PASSWORD 'abc'

statement error user testuser does not have SETPASSWORD privilege
ALTER USER testuser2 WITH PASSWORD 'abc'

statement error user testuser does not have SETPASSWORD privilege
ALTER USER testuser2 VALID UNTIL '2021-01-01'

statement error user testuser does not have SETPASSWORD privilege
ALTER ROLE otherrole SETPASSWORD

statement error user testuser does not have SETPASSWORD privilege
ALTER ROLE otherrole NOSETPASSWORD

statement error user testuser does not have SETPASSWORD privilege
CREATE ROLE otherrole4 SETPASSWORD

statement error user testuser does not have SETPASSWORD privilege
CREATE ROLE otherrole4 NOSETPASSWORD