github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/privilege_builtins (about) 1 statement ok 2 CREATE USER bar 3 4 statement ok 5 GRANT CREATE ON DATABASE test TO bar 6 7 statement ok 8 CREATE TABLE t (a INT, b INT); 9 10 statement ok 11 GRANT GRANT ON t TO bar; 12 13 statement ok 14 GRANT DELETE ON t TO bar; 15 16 statement ok 17 CREATE SEQUENCE seq; 18 19 statement ok 20 GRANT SELECT ON seq TO bar; 21 22 statement ok 23 GRANT SELECT ON seq TO testuser; 24 25 statement ok 26 GRANT GRANT ON seq TO testuser; 27 28 29 ## has_any_column_privilege 30 31 query BBBB 32 SELECT has_any_column_privilege(12345, 'SELECT'), 33 has_any_column_privilege(12345, 'INSERT'), 34 has_any_column_privilege(12345, 'UPDATE'), 35 has_any_column_privilege(12345, 'REFERENCES') 36 ---- 37 NULL NULL NULL NULL 38 39 query BBBB 40 SELECT has_any_column_privilege(12345::OID::REGCLASS, 'SELECT'), 41 has_any_column_privilege(12345::OID::REGCLASS, 'INSERT'), 42 has_any_column_privilege(12345::OID::REGCLASS, 'UPDATE'), 43 has_any_column_privilege(12345::OID::REGCLASS, 'REFERENCES') 44 ---- 45 NULL NULL NULL NULL 46 47 query BBBB 48 SELECT has_any_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'SELECT'), 49 has_any_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'INSERT'), 50 has_any_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'UPDATE'), 51 has_any_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'REFERENCES') 52 ---- 53 true false false true 54 55 query BBBB 56 SELECT has_any_column_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'SELECT'), 57 has_any_column_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'INSERT'), 58 has_any_column_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'UPDATE'), 59 has_any_column_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'REFERENCES') 60 ---- 61 true true true true 62 63 query error pgcode 42P01 relation "does_not_exist" does not exist 64 SELECT has_any_column_privilege('does_not_exist', 'SELECT') 65 66 query BBBBB 67 SELECT has_any_column_privilege('pg_type', 'SELECT'), 68 has_any_column_privilege('pg_type', 'INSERT'), 69 has_any_column_privilege('pg_type', 'UPDATE'), 70 has_any_column_privilege('pg_type', 'REFERENCES'), 71 has_any_column_privilege('pg_type', 'SELECT, INSERT, UPDATE') 72 ---- 73 true false false true false 74 75 query BBBBB 76 SELECT has_any_column_privilege('t', 'SELECT'), 77 has_any_column_privilege('t', 'INSERT'), 78 has_any_column_privilege('t', 'UPDATE'), 79 has_any_column_privilege('t', 'REFERENCES'), 80 has_any_column_privilege('t', 'SELECT, INSERT, UPDATE') 81 ---- 82 true true true true true 83 84 query BBBBB 85 SELECT has_any_column_privilege('t', 'SELECT WITH GRANT OPTION'), 86 has_any_column_privilege('t', 'INSERT WITH GRANT OPTION'), 87 has_any_column_privilege('t', 'UPDATE WITH GRANT OPTION'), 88 has_any_column_privilege('t', 'REFERENCES WITH GRANT OPTION'), 89 has_any_column_privilege('t', 'SELECT WITH GRANT OPTION, INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION') 90 ---- 91 true true true true true 92 93 query BBBBB 94 SELECT has_any_column_privilege('t'::Name, 'SELECT'), 95 has_any_column_privilege('t'::Name, 'INSERT'), 96 has_any_column_privilege('t'::Name, 'UPDATE'), 97 has_any_column_privilege('t'::Name, 'REFERENCES'), 98 has_any_column_privilege('t'::Name, 'SELECT, INSERT, UPDATE') 99 ---- 100 true true true true true 101 102 query error pgcode 22023 unrecognized privilege type: "" 103 SELECT has_any_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), '') 104 105 query error pgcode 22023 unrecognized privilege type: "USAGE" 106 SELECT has_any_column_privilege('t', 'USAGE') 107 108 query error pgcode 22023 unrecognized privilege type: "USAGE" 109 SELECT has_any_column_privilege('t', 'SELECT, USAGE') 110 111 query error pgcode 42704 role 'no_user' does not exist 112 SELECT has_any_column_privilege('no_user', 't', 'SELECT') 113 114 query BBBBB 115 SELECT has_any_column_privilege('bar', 't', 'SELECT'), 116 has_any_column_privilege('bar', 't', 'INSERT'), 117 has_any_column_privilege('bar', 't', 'UPDATE'), 118 has_any_column_privilege('bar', 't', 'REFERENCES'), 119 has_any_column_privilege('bar', 't', 'SELECT, INSERT, UPDATE') 120 ---- 121 false false false false false 122 123 query BBBBB 124 SELECT has_any_column_privilege('bar', 't', 'SELECT WITH GRANT OPTION'), 125 has_any_column_privilege('bar', 't', 'INSERT WITH GRANT OPTION'), 126 has_any_column_privilege('bar', 't', 'UPDATE WITH GRANT OPTION'), 127 has_any_column_privilege('bar', 't', 'REFERENCES WITH GRANT OPTION'), 128 has_any_column_privilege('bar', 't', 'SELECT WITH GRANT OPTION, INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION') 129 ---- 130 false false false false false 131 132 133 ## has_column_privilege 134 135 query BBBB 136 SELECT has_column_privilege(12345, 1, 'SELECT'), 137 has_column_privilege(12345, 1, 'INSERT'), 138 has_column_privilege(12345, 1, 'UPDATE'), 139 has_column_privilege(12345, 1, 'REFERENCES') 140 ---- 141 NULL NULL NULL NULL 142 143 query BBBB 144 SELECT has_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 1, 'SELECT'), 145 has_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 1, 'INSERT'), 146 has_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 1, 'UPDATE'), 147 has_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 1, 'REFERENCES') 148 ---- 149 true false false true 150 151 query BBBB 152 SELECT has_column_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 1, 'SELECT'), 153 has_column_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 2, 'INSERT'), 154 has_column_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 1, 'UPDATE'), 155 has_column_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 2, 'REFERENCES') 156 ---- 157 true true true true 158 159 query error pgcode 42P01 relation "does_not_exist" does not exist 160 SELECT has_column_privilege('does_not_exist', 1, 'SELECT') 161 162 query error pgcode 42703 column 100 of relation 'pg_type' does not exist 163 SELECT has_column_privilege('pg_type', 100, 'SELECT') 164 165 query BBBBB 166 SELECT has_column_privilege('pg_type', 1, 'SELECT'), 167 has_column_privilege('pg_type', 1, 'INSERT'), 168 has_column_privilege('pg_type', 1, 'UPDATE'), 169 has_column_privilege('pg_type', 1, 'REFERENCES'), 170 has_column_privilege('pg_type', 1, 'SELECT, INSERT, UPDATE') 171 ---- 172 true false false true false 173 174 query BBBBB 175 SELECT has_column_privilege('t', 1, 'SELECT'), 176 has_column_privilege('t', 1, 'INSERT'), 177 has_column_privilege('t', 1, 'UPDATE'), 178 has_column_privilege('t', 1, 'REFERENCES'), 179 has_column_privilege('t', 1, 'SELECT, INSERT, UPDATE') 180 ---- 181 true true true true true 182 183 query BBBBB 184 SELECT has_column_privilege('t', 1, 'SELECT WITH GRANT OPTION'), 185 has_column_privilege('t', 1, 'INSERT WITH GRANT OPTION'), 186 has_column_privilege('t', 1, 'UPDATE WITH GRANT OPTION'), 187 has_column_privilege('t', 1, 'REFERENCES WITH GRANT OPTION'), 188 has_column_privilege('t', 1, 'SELECT WITH GRANT OPTION, INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION') 189 ---- 190 true true true true true 191 192 query error pgcode 22023 unrecognized privilege type: "USAGE" 193 SELECT has_column_privilege('t', 1, 'USAGE') 194 195 query error pgcode 42704 role 'no_user' does not exist 196 SELECT has_column_privilege('no_user', 't', 1, 'SELECT') 197 198 query BBBBB 199 SELECT has_column_privilege('bar', 't', 1, 'SELECT'), 200 has_column_privilege('bar', 't', 1, 'INSERT'), 201 has_column_privilege('bar', 't', 1, 'UPDATE'), 202 has_column_privilege('bar', 't', 1, 'REFERENCES'), 203 has_column_privilege('bar', 't', 1, 'SELECT, INSERT, UPDATE') 204 ---- 205 false false false false false 206 207 query BBBBB 208 SELECT has_column_privilege('bar', 't', 1, 'SELECT WITH GRANT OPTION'), 209 has_column_privilege('bar', 't', 1, 'INSERT WITH GRANT OPTION'), 210 has_column_privilege('bar', 't', 1, 'UPDATE WITH GRANT OPTION'), 211 has_column_privilege('bar', 't', 1, 'REFERENCES WITH GRANT OPTION'), 212 has_column_privilege('bar', 't', 1, 'SELECT WITH GRANT OPTION, INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION') 213 ---- 214 false false false false false 215 216 query BBBB 217 SELECT has_column_privilege(12345, 'col', 'SELECT'), 218 has_column_privilege(12345, 'col', 'INSERT'), 219 has_column_privilege(12345, 'col', 'UPDATE'), 220 has_column_privilege(12345, 'col', 'REFERENCES') 221 ---- 222 NULL NULL NULL NULL 223 224 query BBBB 225 SELECT has_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'typname', 'SELECT'), 226 has_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'typname', 'INSERT'), 227 has_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'typname', 'UPDATE'), 228 has_column_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'typname', 'REFERENCES') 229 ---- 230 true false false true 231 232 query BBBB 233 SELECT has_column_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'a', 'SELECT'), 234 has_column_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'b', 'INSERT'), 235 has_column_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'a', 'UPDATE'), 236 has_column_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'b', 'REFERENCES') 237 ---- 238 true true true true 239 240 query error pgcode 42P01 relation "does_not_exist" does not exist 241 SELECT has_column_privilege('does_not_exist', 'col', 'SELECT') 242 243 query error pgcode 42703 column 'does not exist' of relation 'pg_type' does not exist 244 SELECT has_column_privilege('pg_type', 'does not exist', 'SELECT') 245 246 query BBBBB 247 SELECT has_column_privilege('pg_type', 'typname', 'SELECT'), 248 has_column_privilege('pg_type', 'typname', 'INSERT'), 249 has_column_privilege('pg_type', 'typname', 'UPDATE'), 250 has_column_privilege('pg_type', 'typname', 'REFERENCES'), 251 has_column_privilege('pg_type', 'typname', 'SELECT, INSERT, UPDATE') 252 ---- 253 true false false true false 254 255 query BBBBB 256 SELECT has_column_privilege('t', 'a', 'SELECT'), 257 has_column_privilege('t', 'a', 'INSERT'), 258 has_column_privilege('t', 'a', 'UPDATE'), 259 has_column_privilege('t', 'a', 'REFERENCES'), 260 has_column_privilege('t', 'a', 'SELECT, INSERT, UPDATE') 261 ---- 262 true true true true true 263 264 query BBBBB 265 SELECT has_column_privilege('t', 'a', 'SELECT WITH GRANT OPTION'), 266 has_column_privilege('t', 'a', 'INSERT WITH GRANT OPTION'), 267 has_column_privilege('t', 'a', 'UPDATE WITH GRANT OPTION'), 268 has_column_privilege('t', 'a', 'REFERENCES WITH GRANT OPTION'), 269 has_column_privilege('t', 'a', 'SELECT WITH GRANT OPTION, INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION') 270 ---- 271 true true true true true 272 273 query BBBBB 274 SELECT has_column_privilege('t'::Name, 'a'::Name, 'SELECT WITH GRANT OPTION'), 275 has_column_privilege('t'::Name, 'a'::Name, 'INSERT WITH GRANT OPTION'), 276 has_column_privilege('t'::Name, 'a'::Name, 'UPDATE WITH GRANT OPTION'), 277 has_column_privilege('t'::Name, 'a'::Name, 'REFERENCES WITH GRANT OPTION'), 278 has_column_privilege('t'::Name, 'a'::Name, 'SELECT WITH GRANT OPTION, INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION') 279 ---- 280 true true true true true 281 282 query error pgcode 22023 unrecognized privilege type: "USAGE" 283 SELECT has_column_privilege('t', 'a', 'USAGE') 284 285 query error pgcode 42704 role 'no_user' does not exist 286 SELECT has_column_privilege('no_user', 't', 'a', 'SELECT') 287 288 query BBBBB 289 SELECT has_column_privilege('bar', 't', 'a', 'SELECT'), 290 has_column_privilege('bar', 't', 'a', 'INSERT'), 291 has_column_privilege('bar', 't', 'a', 'UPDATE'), 292 has_column_privilege('bar', 't', 'a', 'REFERENCES'), 293 has_column_privilege('bar', 't', 'a', 'SELECT, INSERT, UPDATE') 294 ---- 295 false false false false false 296 297 query BBBBB 298 SELECT has_column_privilege('bar', 't', 'a', 'SELECT WITH GRANT OPTION'), 299 has_column_privilege('bar', 't', 'a', 'INSERT WITH GRANT OPTION'), 300 has_column_privilege('bar', 't', 'a', 'UPDATE WITH GRANT OPTION'), 301 has_column_privilege('bar', 't', 'a', 'REFERENCES WITH GRANT OPTION'), 302 has_column_privilege('bar', 't', 'a', 'SELECT WITH GRANT OPTION, INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION') 303 ---- 304 false false false false false 305 306 307 ## has_database_privilege 308 309 query BBBB 310 SELECT has_database_privilege(12345, 'CREATE'), 311 has_database_privilege(12345, 'CONNECT'), 312 has_database_privilege(12345, 'TEMPORARY'), 313 has_database_privilege(12345, 'TEMP') 314 ---- 315 NULL NULL NULL NULL 316 317 query BBBB 318 SELECT has_database_privilege((SELECT oid FROM pg_database WHERE datname = 'system'), 'CREATE'), 319 has_database_privilege((SELECT oid FROM pg_database WHERE datname = 'system'), 'CONNECT'), 320 has_database_privilege((SELECT oid FROM pg_database WHERE datname = 'system'), 'TEMPORARY'), 321 has_database_privilege((SELECT oid FROM pg_database WHERE datname = 'system'), 'TEMP') 322 ---- 323 false true false false 324 325 query BBBB 326 SELECT has_database_privilege((SELECT oid FROM pg_database WHERE datname = 'test'), 'CREATE'), 327 has_database_privilege((SELECT oid FROM pg_database WHERE datname = 'test'), 'CONNECT'), 328 has_database_privilege((SELECT oid FROM pg_database WHERE datname = 'test'), 'TEMPORARY'), 329 has_database_privilege((SELECT oid FROM pg_database WHERE datname = 'test'), 'TEMP') 330 ---- 331 true true true true 332 333 query error pgcode 3D000 database 'does_not_exist' does not exist 334 SELECT has_database_privilege('does_not_exist', 'CREATE') 335 336 query BBBBB 337 SELECT has_database_privilege('system', ' CrEaTe '), 338 has_database_privilege('system', ' CONNECT'), 339 has_database_privilege('system', 'TEMPORARY'), 340 has_database_privilege('system', 'TEMP'), 341 has_database_privilege('system', ' CrEaTe ,CONNECT') 342 ---- 343 false true false false false 344 345 query BBBBB 346 SELECT has_database_privilege('test', ' CrEaTe '), 347 has_database_privilege('test', ' CONNECT'), 348 has_database_privilege('test', 'TEMPORARY'), 349 has_database_privilege('test', 'TEMP'), 350 has_database_privilege('test', ' CrEaTe ,CONNECT') 351 ---- 352 true true true true true 353 354 query BBBBB 355 SELECT has_database_privilege('test', 'CREATE WITH GRANT OPTION'), 356 has_database_privilege('test', 'CONNECT WITH GRANT OPTION'), 357 has_database_privilege('test', 'TEMPORARY WITH GRANT OPTION'), 358 has_database_privilege('test', 'TEMP WITH GRANT OPTION'), 359 has_database_privilege('test', 'CREATE WITH GRANT OPTION, CONNECT WITH GRANT OPTION') 360 ---- 361 true true true true true 362 363 query BBBBB 364 SELECT has_database_privilege('test'::Name, 'CREATE'), 365 has_database_privilege('test'::Name, 'CONNECT'), 366 has_database_privilege('test'::Name, 'TEMPORARY'), 367 has_database_privilege('test'::Name, 'TEMP'), 368 has_database_privilege('test'::Name, 'CREATE, CONNECT') 369 ---- 370 true true true true true 371 372 query error pgcode 22023 unrecognized privilege type: "UPDATE" 373 SELECT has_database_privilege('test', 'UPDATE') 374 375 query error pgcode 22023 unrecognized privilege type: "UPDATE" 376 SELECT has_database_privilege('test', 'CREATE, UPDATE') 377 378 query error pgcode 42704 role 'no_user' does not exist 379 SELECT has_database_privilege('no_user', 'test', 'CREATE') 380 381 query BBBBBB 382 SELECT has_database_privilege('bar', 'test', 'CREATE'), 383 has_database_privilege('bar', 'test', 'CONNECT'), 384 has_database_privilege('bar', 'test', 'TEMPORARY'), 385 has_database_privilege('bar', 'test', 'TEMP'), 386 has_database_privilege('bar', 'test', 'CREATE, CONNECT'), 387 has_database_privilege('bar', 'test', 'CREATE, TEMP') 388 ---- 389 true true true true true true 390 391 query BBBBBB 392 SELECT has_database_privilege('bar', 'test', 'CREATE WITH GRANT OPTION'), 393 has_database_privilege('bar', 'test', 'CONNECT WITH GRANT OPTION'), 394 has_database_privilege('bar', 'test', 'TEMPORARY WITH GRANT OPTION'), 395 has_database_privilege('bar', 'test', 'TEMP WITH GRANT OPTION'), 396 has_database_privilege('bar', 'test', 'CREATE WITH GRANT OPTION, CONNECT WITH GRANT OPTION'), 397 has_database_privilege('bar', 'test', 'CREATE WITH GRANT OPTION, TEMP WITH GRANT OPTION') 398 ---- 399 false true false false false false 400 401 402 ## has_foreign_data_wrapper_privilege 403 404 query B 405 SELECT has_foreign_data_wrapper_privilege(12345, 'USAGE') 406 ---- 407 true 408 409 query error pgcode 42704 foreign-data wrapper 'does_not_exist' does not exist 410 SELECT has_foreign_data_wrapper_privilege('does_not_exist', 'USAGE') 411 412 query error pgcode 42704 foreign-data wrapper 'does_not_exist' does not exist 413 SELECT has_foreign_data_wrapper_privilege('does_not_exist'::Name, 'USAGE') 414 415 query B 416 SELECT has_foreign_data_wrapper_privilege(12345, 'USAGE WITH GRANT OPTION') 417 ---- 418 true 419 420 query error pgcode 22023 unrecognized privilege type: "UPDATE" 421 SELECT has_foreign_data_wrapper_privilege(12345, 'UPDATE') 422 423 query error pgcode 22023 unrecognized privilege type: "UPDATE" 424 SELECT has_foreign_data_wrapper_privilege(12345, 'USAGE, UPDATE') 425 426 query error pgcode 42704 role 'no_user' does not exist 427 SELECT has_foreign_data_wrapper_privilege('no_user', 12345, 'USAGE') 428 429 query B 430 SELECT has_foreign_data_wrapper_privilege('bar', 12345, 'USAGE') 431 ---- 432 true 433 434 435 ## has_function_privilege 436 437 query B 438 SELECT has_function_privilege(12345, 'EXECUTE') 439 ---- 440 NULL 441 442 query B 443 SELECT has_function_privilege((SELECT oid FROM pg_proc LIMIT 1), 'EXECUTE') 444 ---- 445 true 446 447 query error pgcode 42883 unknown function: does_not_exist() 448 SELECT has_function_privilege('does_not_exist', 'EXECUTE') 449 450 query error pgcode 42883 unknown function: does_not_exist() 451 SELECT has_function_privilege('does_not_exist()', 'EXECUTE') 452 453 query B 454 SELECT has_function_privilege('version', ' EXECUTE ') 455 ---- 456 true 457 458 query B 459 SELECT has_function_privilege('version()', 'EXECUTE') 460 ---- 461 true 462 463 query B 464 SELECT has_function_privilege('cos(float)', 'EXECUTE WITH GRANT OPTION') 465 ---- 466 true 467 468 query B 469 SELECT has_function_privilege('version'::Name, 'EXECUTE') 470 ---- 471 true 472 473 query error pgcode 22023 unrecognized privilege type: "UPDATE" 474 SELECT has_function_privilege('acos(float)', 'UPDATE') 475 476 query error pgcode 22023 unrecognized privilege type: "UPDATE" 477 SELECT has_function_privilege('acos(float)', 'EXECUTE, UPDATE') 478 479 query error pgcode 42704 role 'no_user' does not exist 480 SELECT has_function_privilege('no_user', 'acos(float)', 'EXECUTE') 481 482 query B 483 SELECT has_function_privilege('bar', 'current_date'::REGPROC, 'EXECUTE') 484 ---- 485 true 486 487 query B 488 SELECT has_function_privilege('bar', 'current_date'::REGPROC::OID, 'EXECUTE') 489 ---- 490 true 491 492 493 ## has_language_privilege 494 495 query B 496 SELECT has_language_privilege(12345, 'USAGE') 497 ---- 498 NULL 499 500 query error pgcode 42704 language 'does_not_exist' does not exist 501 SELECT has_language_privilege('does_not_exist', 'USAGE') 502 503 query error pgcode 42704 language 'does_not_exist' does not exist 504 SELECT has_language_privilege('does_not_exist'::Name, 'USAGE') 505 506 query B 507 SELECT has_language_privilege(12345, 'USAGE WITH GRANT OPTION') 508 ---- 509 NULL 510 511 query error pgcode 22023 unrecognized privilege type: "UPDATE" 512 SELECT has_language_privilege(12345, 'UPDATE') 513 514 query error pgcode 22023 unrecognized privilege type: "UPDATE" 515 SELECT has_language_privilege(12345, 'USAGE, UPDATE') 516 517 query error pgcode 42704 role 'no_user' does not exist 518 SELECT has_language_privilege('no_user', 12345, 'USAGE') 519 520 query B 521 SELECT has_language_privilege('bar', 12345, 'USAGE') 522 ---- 523 NULL 524 525 526 ## has_schema_privilege 527 528 query BB 529 SELECT has_schema_privilege(12345, 'CREATE'), 530 has_schema_privilege(12345, 'USAGE') 531 ---- 532 NULL NULL 533 534 query BB 535 SELECT has_schema_privilege((SELECT oid FROM pg_namespace WHERE nspname = 'crdb_internal'), 'CREATE'), 536 has_schema_privilege((SELECT oid FROM pg_namespace WHERE nspname = 'crdb_internal'), 'USAGE') 537 ---- 538 true true 539 540 query BB 541 SELECT has_schema_privilege((SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), 'CREATE'), 542 has_schema_privilege((SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), 'USAGE') 543 ---- 544 true true 545 546 query BB 547 SELECT has_schema_privilege((SELECT oid FROM pg_namespace WHERE nspname = 'public'), 'CREATE'), 548 has_schema_privilege((SELECT oid FROM pg_namespace WHERE nspname = 'public'), 'USAGE') 549 ---- 550 true true 551 552 query error pgcode 3F000 schema 'does_not_exist' does not exist 553 SELECT has_schema_privilege('does_not_exist', 'CREATE') 554 555 query BBB 556 SELECT has_schema_privilege('public', 'CREATE'), 557 has_schema_privilege('public', 'USAGE'), 558 has_schema_privilege('public', 'CREATE, USAGE') 559 ---- 560 true true true 561 562 query BBB 563 SELECT has_schema_privilege('public', 'CREATE WITH GRANT OPTION'), 564 has_schema_privilege('public', 'USAGE WITH GRANT OPTION'), 565 has_schema_privilege('public', 'CREATE WITH GRANT OPTION, USAGE WITH GRANT OPTION') 566 ---- 567 true true true 568 569 query BBB 570 SELECT has_schema_privilege('public'::Name, 'CREATE'), 571 has_schema_privilege('public'::Name, 'USAGE'), 572 has_schema_privilege('public'::Name, 'CREATE, USAGE') 573 ---- 574 true true true 575 576 query error pgcode 22023 unrecognized privilege type: "UPDATE" 577 SELECT has_schema_privilege('public', 'UPDATE') 578 579 query error pgcode 22023 unrecognized privilege type: "UPDATE" 580 SELECT has_schema_privilege('public', 'CREATE, UPDATE') 581 582 query error pgcode 42704 role 'no_user' does not exist 583 SELECT has_schema_privilege('no_user', 'public', 'CREATE') 584 585 query BBB 586 SELECT has_schema_privilege('bar', 'public', 'CREATE'), 587 has_schema_privilege('bar', 'public', 'USAGE'), 588 has_schema_privilege('bar', 'public', 'CREATE, USAGE') 589 ---- 590 true false false 591 592 query BBB 593 SELECT has_schema_privilege('bar', 'public', 'CREATE WITH GRANT OPTION'), 594 has_schema_privilege('bar', 'public', 'USAGE WITH GRANT OPTION'), 595 has_schema_privilege('bar', 'public', 'CREATE WITH GRANT OPTION, USAGE WITH GRANT OPTION') 596 ---- 597 false false false 598 599 600 ## has_sequence_privilege 601 602 query BBB 603 SELECT has_sequence_privilege(12345, 'USAGE'), 604 has_sequence_privilege(12345, 'SELECT'), 605 has_sequence_privilege(12345, 'UPDATE') 606 ---- 607 NULL NULL NULL 608 609 query BBB 610 SELECT has_sequence_privilege((SELECT oid FROM pg_class WHERE relname = 'seq'), 'USAGE'), 611 has_sequence_privilege((SELECT oid FROM pg_class WHERE relname = 'seq'), 'SELECT'), 612 has_sequence_privilege((SELECT oid FROM pg_class WHERE relname = 'seq'), 'UPDATE') 613 ---- 614 true true true 615 616 query error pgcode 42P01 relation "does_not_exist" does not exist 617 SELECT has_sequence_privilege('does_not_exist', 'SELECT') 618 619 query error pgcode 42809 't' is not a sequence 620 SELECT has_sequence_privilege('t', 'SELECT') 621 622 query BBB 623 SELECT has_sequence_privilege('seq', 'USAGE'), 624 has_sequence_privilege('seq', 'SELECT'), 625 has_sequence_privilege('seq', 'UPDATE') 626 ---- 627 true true true 628 629 query BBB 630 SELECT has_sequence_privilege('seq', 'USAGE WITH GRANT OPTION'), 631 has_sequence_privilege('seq', 'SELECT WITH GRANT OPTION'), 632 has_sequence_privilege('seq', 'UPDATE WITH GRANT OPTION') 633 ---- 634 true true true 635 636 query BBB 637 SELECT has_sequence_privilege('seq'::Name, 'USAGE'), 638 has_sequence_privilege('seq'::Name, 'SELECT'), 639 has_sequence_privilege('seq'::Name, 'UPDATE') 640 ---- 641 true true true 642 643 query error pgcode 22023 unrecognized privilege type: "DELETE" 644 SELECT has_sequence_privilege('seq', 'DELETE') 645 646 query error pgcode 22023 unrecognized privilege type: "DELETE" 647 SELECT has_sequence_privilege('seq', 'SELECT, DELETE') 648 649 user testuser 650 651 query BBB 652 SELECT has_sequence_privilege('seq', 'USAGE'), 653 has_sequence_privilege('seq', 'SELECT'), 654 has_sequence_privilege('seq', 'UPDATE') 655 ---- 656 true true false 657 658 query BBB 659 SELECT has_sequence_privilege('seq', 'USAGE WITH GRANT OPTION'), 660 has_sequence_privilege('seq', 'SELECT WITH GRANT OPTION'), 661 has_sequence_privilege('seq', 'UPDATE WITH GRANT OPTION') 662 ---- 663 true true false 664 665 user root 666 667 query error pgcode 42704 role 'no_user' does not exist 668 SELECT has_sequence_privilege('no_user', 'seq', 'SELECT') 669 670 query BBB 671 SELECT has_sequence_privilege('bar', 'seq', 'USAGE'), 672 has_sequence_privilege('bar', 'seq', 'SELECT'), 673 has_sequence_privilege('bar', 'seq', 'UPDATE') 674 ---- 675 true true false 676 677 query BBB 678 SELECT has_sequence_privilege('bar', 'seq', 'USAGE WITH GRANT OPTION'), 679 has_sequence_privilege('bar', 'seq', 'SELECT WITH GRANT OPTION'), 680 has_sequence_privilege('bar', 'seq', 'UPDATE WITH GRANT OPTION') 681 ---- 682 false false false 683 684 685 ## has_server_privilege 686 687 query B 688 SELECT has_server_privilege(12345, 'USAGE') 689 ---- 690 true 691 692 query error pgcode 42704 server 'does_not_exist' does not exist 693 SELECT has_server_privilege('does_not_exist', 'USAGE') 694 695 query error pgcode 42704 server 'does_not_exist' does not exist 696 SELECT has_server_privilege('does_not_exist'::Name, 'USAGE') 697 698 query B 699 SELECT has_server_privilege(12345, 'USAGE WITH GRANT OPTION') 700 ---- 701 true 702 703 query error pgcode 22023 unrecognized privilege type: "UPDATE" 704 SELECT has_server_privilege(12345, 'UPDATE') 705 706 query error pgcode 22023 unrecognized privilege type: "UPDATE" 707 SELECT has_server_privilege(12345, 'USAGE, UPDATE') 708 709 query error pgcode 42704 role 'no_user' does not exist 710 SELECT has_server_privilege('no_user', 12345, 'USAGE') 711 712 query B 713 SELECT has_server_privilege('bar', 12345, 'USAGE') 714 ---- 715 true 716 717 718 ## has_table_privilege 719 720 query BBBBBBB 721 SELECT has_table_privilege(12345, 'SELECT'), 722 has_table_privilege(12345, 'INSERT'), 723 has_table_privilege(12345, 'UPDATE'), 724 has_table_privilege(12345, 'DELETE'), 725 has_table_privilege(12345, 'TRUNCATE'), 726 has_table_privilege(12345, 'REFERENCES'), 727 has_table_privilege(12345, 'TRIGGER') 728 ---- 729 NULL NULL NULL NULL NULL NULL NULL 730 731 query BBBBBBB 732 SELECT has_table_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'SELECT'), 733 has_table_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'INSERT'), 734 has_table_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'UPDATE'), 735 has_table_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'DELETE'), 736 has_table_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'TRUNCATE'), 737 has_table_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'REFERENCES'), 738 has_table_privilege((SELECT oid FROM pg_class WHERE relname = 'pg_type'), 'TRIGGER') 739 ---- 740 true false false false false true false 741 742 query BBBBBBB 743 SELECT has_table_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'SELECT'), 744 has_table_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'INSERT'), 745 has_table_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'UPDATE'), 746 has_table_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'DELETE'), 747 has_table_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'TRUNCATE'), 748 has_table_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'REFERENCES'), 749 has_table_privilege((SELECT oid FROM pg_class WHERE relname = 't'), 'TRIGGER') 750 ---- 751 true true true true true true true 752 753 query error pgcode 42P01 relation "does_not_exist" does not exist 754 SELECT has_table_privilege('does_not_exist', 'SELECT') 755 756 query BBBBBBBBB 757 SELECT has_table_privilege('pg_type', 'SELECT'), 758 has_table_privilege('pg_type', 'INSERT'), 759 has_table_privilege('pg_type', 'UPDATE'), 760 has_table_privilege('pg_type', 'DELETE'), 761 has_table_privilege('pg_type', 'TRUNCATE'), 762 has_table_privilege('pg_type', 'REFERENCES'), 763 has_table_privilege('pg_type', 'TRIGGER'), 764 has_table_privilege('pg_type', 'SELECT, INSERT, UPDATE'), 765 has_table_privilege('pg_type', 'SELECT, TRUNCATE') 766 ---- 767 true false false false false true false false false 768 769 query BBBBBBBBB 770 SELECT has_table_privilege('t', 'SELECT'), 771 has_table_privilege('t', 'INSERT'), 772 has_table_privilege('t', 'UPDATE'), 773 has_table_privilege('t', 'DELETE'), 774 has_table_privilege('t', 'TRUNCATE'), 775 has_table_privilege('t', 'REFERENCES'), 776 has_table_privilege('t', 'TRIGGER'), 777 has_table_privilege('t', 'SELECT, INSERT, UPDATE'), 778 has_table_privilege('t', 'SELECT, TRUNCATE') 779 ---- 780 true true true true true true true true true 781 782 query BBBBBBBBB 783 SELECT has_table_privilege('t', 'SELECT WITH GRANT OPTION'), 784 has_table_privilege('t', 'INSERT WITH GRANT OPTION'), 785 has_table_privilege('t', 'UPDATE WITH GRANT OPTION'), 786 has_table_privilege('t', 'DELETE WITH GRANT OPTION'), 787 has_table_privilege('t', 'TRUNCATE WITH GRANT OPTION'), 788 has_table_privilege('t', 'REFERENCES WITH GRANT OPTION'), 789 has_table_privilege('t', 'TRIGGER WITH GRANT OPTION'), 790 has_table_privilege('t', 'SELECT WITH GRANT OPTION, INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION'), 791 has_table_privilege('t', 'SELECT WITH GRANT OPTION, TRUNCATE WITH GRANT OPTION') 792 ---- 793 true true true true true true true true true 794 795 query BBBBBBBBB 796 SELECT has_table_privilege('t'::Name, 'SELECT'), 797 has_table_privilege('t'::Name, 'INSERT'), 798 has_table_privilege('t'::Name, 'UPDATE'), 799 has_table_privilege('t'::Name, 'DELETE'), 800 has_table_privilege('t'::Name, 'TRUNCATE'), 801 has_table_privilege('t'::Name, 'REFERENCES'), 802 has_table_privilege('t'::Name, 'TRIGGER'), 803 has_table_privilege('t'::Name, 'SELECT, INSERT, UPDATE'), 804 has_table_privilege('t'::Name, 'SELECT, TRUNCATE') 805 ---- 806 true true true true true true true true true 807 808 # has_table_privilege works with sequences as well. 809 query BBBBBBBBB 810 SELECT has_table_privilege('seq', 'SELECT'), 811 has_table_privilege('seq', 'INSERT'), 812 has_table_privilege('seq', 'UPDATE'), 813 has_table_privilege('seq', 'DELETE'), 814 has_table_privilege('seq', 'TRUNCATE'), 815 has_table_privilege('seq', 'REFERENCES'), 816 has_table_privilege('seq', 'TRIGGER'), 817 has_table_privilege('seq', 'SELECT, INSERT, UPDATE'), 818 has_table_privilege('seq', 'SELECT, TRUNCATE') 819 ---- 820 true true true true true true true true true 821 822 query error pgcode 22023 unrecognized privilege type: "USAGE" 823 SELECT has_table_privilege('t', 'USAGE') 824 825 query error pgcode 22023 unrecognized privilege type: "USAGE" 826 SELECT has_table_privilege('t', 'SELECT, USAGE') 827 828 query error pgcode 42704 role 'no_user' does not exist 829 SELECT has_table_privilege('no_user', 't', 'SELECT') 830 831 query BBBBBBBBB 832 SELECT has_table_privilege('bar', 't', 'SELECT'), 833 has_table_privilege('bar', 't', 'INSERT'), 834 has_table_privilege('bar', 't', 'UPDATE'), 835 has_table_privilege('bar', 't', 'DELETE'), 836 has_table_privilege('bar', 't', 'TRUNCATE'), 837 has_table_privilege('bar', 't', 'REFERENCES'), 838 has_table_privilege('bar', 't', 'TRIGGER'), 839 has_table_privilege('bar', 't', 'SELECT, INSERT, UPDATE'), 840 has_table_privilege('bar', 't', 'SELECT, TRUNCATE') 841 ---- 842 false false false true true false true false false 843 844 query BBBBBBBBB 845 SELECT has_table_privilege('bar', 't', 'SELECT WITH GRANT OPTION'), 846 has_table_privilege('bar', 't', 'INSERT WITH GRANT OPTION'), 847 has_table_privilege('bar', 't', 'UPDATE WITH GRANT OPTION'), 848 has_table_privilege('bar', 't', 'DELETE WITH GRANT OPTION'), 849 has_table_privilege('bar', 't', 'TRUNCATE WITH GRANT OPTION'), 850 has_table_privilege('bar', 't', 'REFERENCES WITH GRANT OPTION'), 851 has_table_privilege('bar', 't', 'TRIGGER WITH GRANT OPTION'), 852 has_table_privilege('bar', 't', 'SELECT WITH GRANT OPTION, INSERT WITH GRANT OPTION, UPDATE WITH GRANT OPTION'), 853 has_table_privilege('bar', 't', 'SELECT WITH GRANT OPTION, TRUNCATE WITH GRANT OPTION') 854 ---- 855 false false false true true false true false false 856 857 858 ## has_tablespace_privilege 859 860 query B 861 SELECT has_tablespace_privilege(12345, 'CREATE') 862 ---- 863 true 864 865 query B 866 SELECT has_tablespace_privilege((SELECT oid FROM pg_tablespace LIMIT 1), 'CREATE') 867 ---- 868 true 869 870 query error pgcode 42704 tablespace 'does_not_exist' does not exist 871 SELECT has_tablespace_privilege('does_not_exist', 'CREATE') 872 873 query B 874 SELECT has_tablespace_privilege('pg_default', ' CrEaTe ') 875 ---- 876 true 877 878 query B 879 SELECT has_tablespace_privilege('pg_default', 'CREATE WITH GRANT OPTION') 880 ---- 881 true 882 883 query B 884 SELECT has_tablespace_privilege('pg_default'::Name, 'CREATE') 885 ---- 886 true 887 888 query error pgcode 22023 unrecognized privilege type: "CREATE WITH GRANT OPTION" 889 SELECT has_tablespace_privilege('pg_default', 'CREATE WITH GRANT OPTION') 890 891 query error pgcode 22023 unrecognized privilege type: "UPDATE" 892 SELECT has_tablespace_privilege('pg_default', 'UPDATE') 893 894 query error pgcode 22023 unrecognized privilege type: "UPDATE" 895 SELECT has_tablespace_privilege('pg_default', 'CREATE, UPDATE') 896 897 query error pgcode 42704 role 'no_user' does not exist 898 SELECT has_tablespace_privilege('no_user', 'pg_default', 'CREATE') 899 900 query B 901 SELECT has_tablespace_privilege('bar', (SELECT oid FROM pg_tablespace LIMIT 1), 'CREATE') 902 ---- 903 true 904 905 906 ## has_type_privilege 907 908 query B 909 SELECT has_type_privilege(12345, 'USAGE') 910 ---- 911 NULL 912 913 query B 914 SELECT has_type_privilege((SELECT oid FROM pg_type LIMIT 1), 'USAGE') 915 ---- 916 true 917 918 query error pgcode 42704 type 'does_not_exist' does not exist 919 SELECT has_type_privilege('does_not_exist', 'USAGE') 920 921 query B 922 SELECT has_type_privilege('int', ' USAGE ') 923 ---- 924 true 925 926 query B 927 SELECT has_type_privilege('decimal(18,2)', 'USAGE WITH GRANT OPTION') 928 ---- 929 true 930 931 query B 932 SELECT has_type_privilege('int'::Name, 'USAGE') 933 ---- 934 true 935 936 query error pgcode 22023 unrecognized privilege type: "UPDATE" 937 SELECT has_type_privilege('int4', 'UPDATE') 938 939 query error pgcode 22023 unrecognized privilege type: "UPDATE" 940 SELECT has_type_privilege('int4', 'USAGE, UPDATE') 941 942 query error pgcode 42704 role 'no_user' does not exist 943 SELECT has_type_privilege('no_user', 'int4', 'USAGE') 944 945 query B 946 SELECT has_type_privilege('bar', 'text'::REGTYPE, 'USAGE') 947 ---- 948 true 949 950 query B 951 SELECT has_type_privilege('bar', 'text'::REGTYPE::OID, 'USAGE') 952 ---- 953 true 954 955 # Regression test for #39703. 956 957 statement ok 958 DROP TABLE IF EXISTS hcp_test; CREATE TABLE hcp_test (a INT8, b INT8, c INT8) 959 960 statement ok 961 ALTER TABLE hcp_test DROP COLUMN b 962 963 query TI 964 SELECT attname, attnum FROM pg_attribute WHERE attrelid = 'hcp_test'::REGCLASS 965 ---- 966 a 1 967 c 3 968 rowid 4 969 970 query B 971 SELECT has_column_privilege('hcp_test'::REGCLASS, 1, 'SELECT') 972 ---- 973 true 974 975 statement error column 2 of relation hcp_test does not exist 976 SELECT has_column_privilege('hcp_test'::REGCLASS, 2, 'SELECT') 977 978 query B 979 SELECT has_column_privilege('hcp_test'::REGCLASS, 3, 'SELECT') 980 ---- 981 true 982 983 query B 984 SELECT has_column_privilege('hcp_test'::REGCLASS, 4, 'SELECT') 985 ---- 986 true