github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/user (about)

     1  # LogicTest: local
     2  
     3  query TTT colnames
     4  SHOW USERS
     5  ----
     6  username  options     member_of
     7  admin     CREATEROLE  {}
     8  root      CREATEROLE  {admin}
     9  testuser  ·           {}
    10  
    11  statement ok
    12  CREATE USER user1
    13  
    14  query TTT colnames
    15  SHOW USERS
    16  ----
    17  username  options     member_of
    18  admin     CREATEROLE  {}
    19  root      CREATEROLE  {admin}
    20  testuser  ·           {}
    21  user1     ·           {}
    22  
    23  statement error pgcode 42710 a role/user named admin already exists
    24  CREATE USER admin
    25  
    26  statement ok
    27  CREATE USER IF NOT EXISTS admin
    28  
    29  statement error pgcode 42710 a role/user named user1 already exists
    30  CREATE USER user1
    31  
    32  statement ok
    33  CREATE USER IF NOT EXISTS user1
    34  
    35  statement error pgcode 42710 a role/user named user1 already exists
    36  CREATE USER UsEr1
    37  
    38  statement ok
    39  CREATE USER Ομηρος
    40  
    41  statement error username "node" reserved
    42  CREATE USER node
    43  
    44  statement error empty passwords are not permitted
    45  CREATE USER test WITH PASSWORD ''
    46  
    47  statement ok
    48  CREATE USER uSEr2 WITH PASSWORD 'cockroach'
    49  
    50  statement ok
    51  CREATE USER user3 WITH PASSWORD '蟑螂'
    52  
    53  statement error pq: username "foo☂" invalid
    54  CREATE USER foo☂
    55  
    56  statement error pq: username "-foo" invalid
    57  CREATE USER "-foo"
    58  
    59  statement error at or near "-": syntax error
    60  CREATE USER foo-bar
    61  
    62  statement ok
    63  CREATE USER "foo-bar"
    64  
    65  statement ok
    66  PREPARE pcu AS CREATE USER $1 WITH PASSWORD $2;
    67    EXECUTE pcu('foo','bar')
    68  
    69  statement ok
    70  ALTER USER foo WITH PASSWORD somepass
    71  
    72  statement ok
    73  PREPARE chpw AS ALTER USER $1 WITH PASSWORD $2;
    74    EXECUTE chpw('foo', 'bar')
    75  
    76  statement error user blix does not exist
    77  EXECUTE chpw('blix', 'blah')
    78  
    79  query TTT colnames
    80  SHOW USERS
    81  ----
    82  username  options     member_of
    83  admin     CREATEROLE  {}
    84  foo       ·           {}
    85  foo-bar   ·           {}
    86  root      CREATEROLE  {admin}
    87  testuser  ·           {}
    88  user1     ·           {}
    89  user2     ·           {}
    90  user3     ·           {}
    91  ομηρος    ·           {}
    92  
    93  statement error no username specified
    94  CREATE USER ""
    95  
    96  query TTT
    97  SELECT current_user, session_user, user
    98  ----
    99  root  root  root
   100  
   101  user testuser
   102  
   103  statement error pq: user testuser does not have CREATEROLE privilege
   104  CREATE USER user4
   105  
   106  statement error pq: user testuser does not have INSERT privilege on relation users
   107  UPSERT INTO system.users VALUES (user1, 'newpassword', false)
   108  
   109  statement error pq: user testuser does not have SELECT privilege on relation user
   110  SHOW USERS
   111  
   112  query TTT
   113  SELECT current_user, session_user, user
   114  ----
   115  testuser  testuser  testuser
   116  
   117  statement ok
   118  SET SESSION AUTHORIZATION DEFAULT
   119  
   120  query T
   121  SHOW session_user
   122  ----
   123  testuser
   124  
   125  user root
   126  
   127  statement ok
   128  SET SESSION AUTHORIZATION DEFAULT
   129  
   130  query T
   131  SHOW session_user
   132  ----
   133  root
   134  
   135  # Test CREATEROLE privilege.
   136  
   137  statement ok
   138  ALTER USER testuser CREATEROLE
   139  
   140  statement ok
   141  GRANT SELECT ON system.role_options to testuser
   142  
   143  user testuser
   144  
   145  statement ok
   146  CREATE USER user4 CREATEROLE
   147  
   148  statement ok
   149  ALTER USER user4 NOLOGIN
   150  
   151  query TTT
   152  SELECT * FROM system.role_options
   153  ----
   154  admin     CREATEROLE  NULL
   155  root      CREATEROLE  NULL
   156  testuser  CREATEROLE  NULL
   157  user4     CREATEROLE  NULL
   158  user4     NOLOGIN     NULL
   159  
   160  statement ok
   161  DROP USER user4