-
Notifications
You must be signed in to change notification settings - Fork 3.8k
/
schema
902 lines (655 loc) · 20.5 KB
/
schema
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
statement ok
SET experimental_enable_temp_tables = true;
statement ok
CREATE SCHEMA IF NOT EXISTS public
statement ok
CREATE SCHEMA IF NOT EXISTS crdb_internal
statement ok
CREATE SCHEMA IF NOT EXISTS pg_catalog
statement ok
CREATE SCHEMA IF NOT EXISTS information_schema
statement ok
CREATE SCHEMA derp
statement ok
CREATE SCHEMA IF NOT EXISTS derp
statement error schema \"derp\" already exists
CREATE SCHEMA derp
statement error schema .* already exists
CREATE SCHEMA public
statement error schema .* already exists
CREATE SCHEMA crdb_internal
statement error schema .* already exists
CREATE SCHEMA pg_catalog
statement error schema .* already exists
CREATE SCHEMA information_schema
statement error pq: unacceptable schema name \"pg_temp\"
CREATE SCHEMA pg_temp
# Create some tables and types in a user defined schema, and resolve them.
statement ok
CREATE SCHEMA myschema;
CREATE TABLE myschema.tb (x INT);
CREATE TYPE myschema.typ AS ENUM ('user', 'defined', 'schema');
CREATE VIEW myschema.v AS SELECT x FROM myschema.tb;
CREATE SEQUENCE myschema.s
query TITITI rowsort
SELECT
database_name, parent_id, schema_name, parent_schema_id, name, table_id
FROM crdb_internal.tables
WHERE database_name = 'test'
----
test 52 myschema 54 tb 55
test 52 myschema 54 v 58
test 52 myschema 54 s 59
query I
SELECT * FROM myschema.tb
----
query I
SELECT * FROM myschema.v
----
query I
SELECT last_value FROM myschema.s
----
0
query TT
SELECT 'user'::myschema.typ, ARRAY['defined']::myschema._typ
----
user {defined}
# Set the search path to have myschema at the front.
statement ok
SET search_path TO myschema,public
# Now we should be able to resolve tb and typ without qualification.
query I
SELECT * FROM tb
----
query TT
SELECT 'user'::typ, ARRAY['defined']::_typ
----
user {defined}
# New objects should be created into tb2 by default.
statement ok
CREATE TABLE tb2 (x typ)
query T
SELECT * FROM tb2
----
query T
SELECT * FROM myschema.tb2
----
# Reset the search path.
statement ok
SET search_path TO public
# Try to create a temp table in a user defined schema.
statement error pq: cannot create temporary relation in non-temporary schema
CREATE TEMP TABLE myschema.tmp (x int)
# We should error out trying to modify any virtual schemas.
statement error pq: schema cannot be modified: "pg_catalog"
CREATE TABLE pg_catalog.bad (x int)
# We shouldn't be able to alter virtual or public schemas.
statement error pq: cannot modify schema "public"
ALTER SCHEMA public RENAME TO private
statement error pq: cannot modify schema "pg_catalog"
ALTER SCHEMA pg_catalog RENAME TO mysql_catalog
# We can't rename a schema to a pg_temp prefixed name.
statement error pq: unacceptable schema name "pg_temp_not_temp"
ALTER SCHEMA myschema RENAME TO pg_temp_not_temp
# We can't rename to schemas that already exist.
statement error pq: schema "public" already exists
ALTER SCHEMA myschema RENAME TO public
statement ok
CREATE SCHEMA yourschema
statement error pq: schema "yourschema" already exists
ALTER SCHEMA myschema RENAME TO yourschema
statement ok
ALTER SCHEMA myschema RENAME TO myschema2
# We should be able to resolve objects under the new schema name.
query T
SELECT * FROM myschema2.tb2
----
# The names should be drained after executing, so we should be able
# to make another schema with the old name.
statement ok
CREATE SCHEMA myschema
statement ok
BEGIN
statement ok
ALTER SCHEMA myschema RENAME TO another_schema
statement ok
ALTER SCHEMA another_schema RENAME TO another_one
# The names should be draining, so we can't use the old ones
# anymore.
statement error pq: schema "myschema" already exists
ALTER SCHEMA another_one RENAME TO myschema
statement ok
ROLLBACK
# We should be able to drop an empty schema without CASCADE.
statement ok
CREATE SCHEMA empty;
DROP SCHEMA empty
let $schema_id
SELECT id FROM system.namespace WHERE name = 'myschema'
# Create some objects under myschema, and have them reference some objects
# in other schemas.
statement ok
CREATE TABLE myschema.myschema_t1 (x INT);
CREATE TABLE myschema.myschema_t2 (x INT);
CREATE SEQUENCE myschema.myschema_seq1;
CREATE TABLE myschema.myschema_t3 (x INT DEFAULT nextval('myschema.myschema_seq1'));
CREATE TYPE myschema.myschema_ty1 AS ENUM ('schema');
CREATE SCHEMA otherschema;
CREATE VIEW otherschema.otherschema_v1 AS SELECT x FROM myschema.myschema_t1;
CREATE TABLE otherschema.otherschema_t1 (x INT);
CREATE SEQUENCE otherschema.otherschema_seq1 OWNED BY myschema.myschema_t1.x;
statement error pq: schema "myschema" is not empty and CASCADE was not specified
DROP SCHEMA myschema
# Now drop with cascade.
statement ok
DROP SCHEMA myschema CASCADE
query T
SELECT table_name FROM [SHOW TABLES] WHERE table_name LIKE 'myschema%' OR table_name LIKE 'otherschema%'
----
otherschema_t1
query T
SELECT name FROM [SHOW ENUMS] WHERE name LIKE 'myschema%'
# The schema should be gone.
query I
SELECT id FROM system.namespace WHERE name = 'myschema'
query IT
SELECT * FROM system.descriptor WHERE id = $schema_id
# We can't resolve a schema dropped in the same transaction.
statement ok
CREATE SCHEMA dropped;
BEGIN
statement ok
DROP SCHEMA dropped
statement error pq: cannot create "dropped.t" because the target database or schema does not exist
CREATE TABLE dropped.t (x INT)
statement ok
ROLLBACK
# We shouldn't be able to create a conflicting schema name in the same transaction
# that it is dropped in.
statement ok
BEGIN
statement ok
DROP SCHEMA dropped
statement error pq: schema "dropped" already exists
CREATE SCHEMA dropped
statement ok
ROLLBACK
# Test that we can drop multiple schemas as part of a single DROP statement.
statement ok
CREATE SCHEMA scdrop1;
CREATE SCHEMA scdrop2;
CREATE SCHEMA scdrop3;
CREATE TABLE scdrop1.scdrop1_t1 (x INT);
CREATE TABLE scdrop1.scdrop1_t2 (x INT);
CREATE TABLE scdrop2.scdrop2_t1 (x INT);
CREATE VIEW scdrop2.scdrop2_v1 AS SELECT x FROM scdrop1.scdrop1_t1;
CREATE VIEW scdrop3.scdrop3_v1 AS SELECT x FROM scdrop2.scdrop2_v1;
statement ok
DROP SCHEMA scdrop1, scdrop2, scdrop3 CASCADE
query T
SELECT table_name FROM [SHOW TABLES] WHERE table_name LIKE 'scdrop%'
subtest create_schemas_with_database_prefixes
# Ensure that schemas can be created using with database prefixes
statement ok
CREATE DATABASE create_schemas;
statement ok
CREATE SCHEMA create_schemas.schema1;
statement ok
CREATE SCHEMA create_schemas.schema2 AUTHORIZATION root;
query T
SELECT catalog_name FROM create_schemas.information_schema.schemata WHERE schema_name = 'schema1';
----
create_schemas
query T
SELECT catalog_name FROM create_schemas.information_schema.schemata WHERE schema_name = 'schema2';
----
create_schemas
statement error pq: schema "schema1" already exists
CREATE SCHEMA create_schemas.schema1;
statement error pq: schema "schema2" already exists
CREATE SCHEMA create_schemas.schema2 AUTHORIZATION root;
statement ok
CREATE SCHEMA IF NOT EXISTS create_schemas.schema1;
statement ok
CREATE SCHEMA IF NOT EXISTS create_schemas.schema2 AUTHORIZATION root;
statement error pq: cannot create schemas in the system database
CREATE SCHEMA system.schema3;
subtest drop_schemas_with_database_prefixes
# Test that empty schemas from different databases can be dropped
statement ok
CREATE DATABASE scdrop4_db;
CREATE DATABASE scdrop6_db;
CREATE SCHEMA scdrop4_db.scdrop4;
CREATE SCHEMA scdrop5;
CREATE SCHEMA scdrop6_db.scdrop6;
statement ok
DROP SCHEMA scdrop4_db.scdrop4, scdrop5, scdrop6_db.scdrop6;
# Test that non-empty schemas from different databases can be dropped with cascade
statement ok
CREATE SCHEMA scdrop4_db.scdrop4;
CREATE SCHEMA scdrop5;
CREATE SCHEMA scdrop6_db.scdrop6;
CREATE TABLE scdrop4_db.scdrop4.scdrop4_t1 (x INT);
CREATE TABLE scdrop5.scdrop5_t1 (x INT);
CREATE TABLE scdrop6_db.scdrop6.scdrop6_t1 (x INT);
CREATE VIEW scdrop4_db.scdrop4.scdrop4_v1 AS SELECT x FROM scdrop4_db.scdrop4.scdrop4_t1;
CREATE VIEW scdrop5.scdrop5_v1 AS SELECT x FROM scdrop5.scdrop5_t1;
CREATE VIEW scdrop6_db.scdrop6.scdrop6_v1 AS SELECT x FROM scdrop6_db.scdrop6.scdrop6_t1;
statement error pq: schema "scdrop4" is not empty and CASCADE was not specified
DROP SCHEMA scdrop4_db.scdrop4, scdrop5, scdrop6_db.scdrop6 RESTRICT;
statement ok
DROP SCHEMA IF EXISTS scdrop4_db.scdrop4, scdrop5, scdrop6_db.scdrop6 CASCADE;
statement ok
DROP SCHEMA IF EXISTS scdrop4_db.scdrop4, scdrop5, scdrop6_db.scdrop6 CASCADE;
statement error pq: unknown schema "scdrop4"
DROP SCHEMA scdrop4_db.scdrop4, scdrop5, scdrop6_db.scdrop6 CASCADE;
query T
SELECT schema_name FROM scdrop4_db.information_schema.schemata WHERE schema_name = 'scdrop4_db';
query T
SELECT table_name FROM [SHOW TABLES] WHERE table_name LIKE 'scdrop%'
subtest alter_schema_with_database_prefix
# We should be able to alter schemas in different databases
statement ok
CREATE DATABASE with_alter_schema;
CREATE ROLE jay;
CREATE SCHEMA with_alter_schema.schema_to_alter AUTHORIZATION jay;
statement ok
ALTER SCHEMA with_alter_schema.schema_to_alter RENAME TO altered_schema;
statement ok
ALTER SCHEMA with_alter_schema.altered_schema OWNER TO root;
statement ok
USE with_alter_schema
query T
SELECT owner from [SHOW SCHEMAS] WHERE schema_name = 'altered_schema';
----
root
statement error pq: unknown schema "schema_to_alter"
ALTER SCHEMA with_alter_schema.schema_to_alter RENAME TO altered_schema;
subtest drop_database
# Ensure that user defined schemas are dropped when dropping the parent database.
statement ok
CREATE DATABASE with_schemas;
USE with_schemas;
CREATE SCHEMA dropschema1;
CREATE SCHEMA dropschema2;
CREATE TABLE dropschema1.dropschema1_tb (x INT);
CREATE TYPE dropschema1.dropschema1_typ AS ENUM ('schema');
CREATE TABLE dropschema2.dropschema2_tb (y INT);
USE test
statement ok
DROP DATABASE with_schemas CASCADE
# There shouldn't be any left over namespace entries from the schemas
# or elements within the schemas.
query I
SELECT id FROM system.namespace WHERE name LIKE 'dropschema%'
# Test privilege interactions with schemas.
subtest privileges
# Have root create a schema.
statement ok
CREATE SCHEMA privs
statement ok
GRANT CREATE ON DATABASE test TO testuser
# Test user shouldn't be able to create in privs yet.
user testuser
statement error pq: user testuser does not have CREATE privilege on schema privs
CREATE TABLE privs.denied (x INT)
statement error pq: user testuser does not have CREATE privilege on schema privs
CREATE TYPE privs.denied AS ENUM ('denied')
user root
statement ok
GRANT CREATE ON SCHEMA privs TO testuser
statement ok
CREATE DATABASE db2; USE db2; CREATE SCHEMA privs; USE test
statement error target database or schema does not exist
SHOW GRANTS ON SCHEMA non_existent
query TTTT
SELECT database_name, schema_name, grantee, privilege_type FROM
[SHOW GRANTS ON SCHEMA privs]
ORDER BY database_name, schema_name, grantee
----
test privs admin ALL
test privs root ALL
test privs testuser CREATE
user testuser
# Now the testuser can create objects.
statement ok
CREATE TABLE privs.tbl (x INT)
statement ok
CREATE TYPE privs.typ AS ENUM ('allowed')
# Now revoke the permissions.
user root
statement ok
REVOKE CREATE ON SCHEMA privs FROM testuser
user testuser
statement error pq: user testuser does not have CREATE privilege on schema privs
CREATE TABLE privs.denied (x INT)
statement error pq: user testuser does not have CREATE privilege on schema privs
CREATE TYPE privs.denied AS ENUM ('denied')
# The testuser shouldn't be able to alter or drop the schema.
statement error pq: must be owner of schema "privs"
ALTER SCHEMA privs RENAME TO denied
statement error pq: permission denied to drop schema "privs"
DROP SCHEMA privs
# Test the usage privilege.
user root
# Create some objects in privs (testuser doesn't have USAGE yet).
statement ok
CREATE TABLE privs.usage_tbl (x INT);
CREATE TYPE privs.usage_typ AS ENUM ('usage');
user testuser
# Both mutable and immutable access should fail with this error.
statement error pq: user testuser does not have USAGE privilege on schema privs
SELECT * FROM privs.usage_tbl
statement error pq: user testuser does not have USAGE privilege on schema privs
SELECT 'usage'::privs.usage_typ
statement error pq: user testuser does not have USAGE privilege on schema privs
ALTER TABLE privs.usage_tbl ADD COLUMN y INT DEFAULT NULL
statement error pq: user testuser does not have USAGE privilege on schema privs
CREATE INDEX ON privs.usage_tbl (x)
statement error pq: user testuser does not have USAGE privilege on schema privs
COMMENT ON TABLE privs.usage_tbl IS 'foo'
statement error pq: user testuser does not have USAGE privilege on schema privs
COMMENT ON COLUMN privs.usage_tbl.x IS 'foo'
statement error pq: user testuser does not have USAGE privilege on schema privs
ALTER TYPE privs.usage_typ ADD VALUE 'denied'
# Test privileges for schemas qualified with database names
user root
# Create some other databases with schemas
statement ok
CREATE DATABASE otherdb;
CREATE SCHEMA otherdb.privs;
CREATE DATABASE otherdb2;
CREATE SCHEMA otherdb2.privs;
# testuser should have create privilege on test.priv, otherdb.priv, and otherdb2.priv after grant
statement ok
GRANT CREATE ON SCHEMA privs, otherdb.privs, otherdb2.privs TO testuser;
user testuser
statement ok
CREATE TABLE test.privs.fail_tbl();
statement ok
CREATE TABLE otherdb.privs.fail_tbl();
statement ok
CREATE TABLE otherdb2.privs.fail_tbl();
# Show should support database qualified schema names
user root
statement ok
SET SESSION sql_safe_updates=false;
statement ok
USE ""
query TTTT
SELECT database_name, schema_name, grantee, privilege_type FROM
[SHOW GRANTS ON SCHEMA test.privs, otherdb.privs, otherdb2.privs]
WHERE grantee = 'testuser'
ORDER BY database_name, schema_name, grantee
----
otherdb privs testuser CREATE
otherdb2 privs testuser CREATE
test privs testuser CREATE
statement ok
use test
query TTTT
SELECT database_name, schema_name, grantee, privilege_type FROM
[SHOW GRANTS ON SCHEMA privs, otherdb.privs, otherdb2.privs]
WHERE grantee = 'testuser'
ORDER BY database_name, schema_name, grantee
----
otherdb privs testuser CREATE
otherdb2 privs testuser CREATE
test privs testuser CREATE
# testuser should not have create privilege on test.priv, otherdb.priv, nor otherdb2.priv after revoke
statement ok
REVOKE CREATE ON SCHEMA privs, otherdb.privs, otherdb2.privs FROM testuser;
user testuser
statement error pq: user testuser does not have CREATE privilege on schema privs
CREATE TABLE test.privs.fail_tbl();
statement error pq: user testuser does not have CREATE privilege on schema privs
CREATE TABLE otherdb.privs.fail_tbl();
statement error pq: user testuser does not have CREATE privilege on schema privs
CREATE TABLE otherdb2.privs.fail_tbl();
subtest authorization
user root
# Test the AUTHORIZATION argument to CREATE SCHEMA.
# Create a user to create a schema for.
statement ok
CREATE USER user1;
# Creates a schema for named with user1 as the owner.
statement ok
CREATE SCHEMA AUTHORIZATION user1
statement error pq: schema "user1" already exists
CREATE SCHEMA AUTHORIZATION user1
statement ok
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user1
statement ok
CREATE SCHEMA user1_schema AUTHORIZATION user1
# The created schemas should both be owned by user1.
query TT
SELECT
nspname, usename
FROM
pg_catalog.pg_namespace
LEFT JOIN pg_catalog.pg_user ON pg_namespace.nspowner = pg_user.usesysid
WHERE
nspname LIKE 'user1%';
----
user1 user1
user1_schema user1
# Ensure that we need CREATE on a database to create a schema.
statement ok
CREATE DATABASE perms
user testuser
statement ok
USE perms
statement error pq: user testuser does not have CREATE privilege on database perms
CREATE SCHEMA test
user root
statement ok
GRANT CREATE ON DATABASE perms TO testuser
user testuser
statement ok
USE perms
statement ok
CREATE SCHEMA test
user root
statement ok
USE defaultdb
# Ensure that when we create a schema, we do not modify the database privileges.
subtest create_schema_does_not_modify_db_privileges
user root
statement ok
CREATE DATABASE new_db
statement ok
USE new_db
user testuser
statement ok
USE new_db
statement error user testuser does not have CREATE privilege on database new_db
CREATE TABLE new_db.public.bar()
user root
statement ok
CREATE SCHEMA AUTHORIZATION testuser
user testuser
statement error user testuser does not have CREATE privilege on database new_db
CREATE TABLE new_db.public.bar()
statement ok
CREATE TABLE new_db.testuser.bar()
# cleanup the testuser schema created as part of the CREATE SCHEMA AUTHORIZATION
# command above
statement ok
DROP SCHEMA testuser CASCADE
# If a schema with a username exists, then that should be the first entry in
# the search path.
subtest user_schema_search_path
# Test setup
user root
statement ok
CREATE SCHEMA testuser
statement ok
GRANT ALL ON SCHEMA testuser TO testuser
statement ok
CREATE TABLE public.public_table(a INT)
statement ok
GRANT SELECT ON public.public_table TO testuser
user testuser
statement ok
CREATE TABLE test_table(a INT);
statement error pq: relation "public.test_table" does not exist
SELECT * FROM public.test_table
statement ok
SELECT * FROM testuser.test_table
# Only root has privs to create inside public
user root
statement ok
CREATE TABLE public.test_table(a INT, b INT)
statement ok
GRANT SELECT ON public.test_table TO testuser
user testuser
query I colnames
SELECT * FROM test_table
----
a
query II colnames
SELECT * FROM public.test_table
----
a b
query I colnames
SELECT * FROM public_table
----
a
# The search path is configured to be user specific.
user root
query II colnames
SELECT * FROM test_table
----
a b
query I colnames
SELECT * FROM testuser.test_table
----
a
# Ensure that when we create a schema, it inherits privileges from its parent
# database, but only those which are valid for schemas.
subtest create_schema_inherits_db_privileges
user root
statement ok
CREATE DATABASE d54662;
GRANT CREATE, SELECT ON DATABASE d54662 TO testuser;
USE d54662;
CREATE SCHEMA s
query T
SELECT privilege_type FROM [SHOW GRANTS ON schema s FOR testuser]
----
CREATE
statement ok
GRANT USAGE ON SCHEMA s TO testuser
query T rowsort
SELECT privilege_type FROM [SHOW GRANTS ON schema s FOR testuser]
----
CREATE
USAGE
# Verify that a table can be renamed with a schema prefixes
subtest alter_table_rename
user root
statement ok
CREATE SCHEMA sch;
CREATE TABLE sch.table_to_rename();
CREATE TABLE sch.table_exists();
CREATE TABLE public_table_to_rename();
CREATE TABLE public_table_exists();
statement ok
ALTER TABLE sch.table_to_rename RENAME TO renamed_table;
statement ok
ALTER TABLE sch.renamed_table RENAME TO sch.renamed_table_2;
statement error pq: relation "d54662.sch.table_exists" already exists
ALTER TABLE sch.renamed_table_2 RENAME TO sch.table_exists;
statement ok
ALTER TABLE public_table_to_rename RENAME TO public.renamed_public_table;
statement error pq: relation "d54662.public.public_table_exists" already exists
ALTER TABLE renamed_public_table RENAME TO public_table_exists;
subtest show_tables
statement ok
CREATE DATABASE for_show;
statement ok;
USE for_show;
statement ok;
CREATE TABLE t1 (i INT PRIMARY KEY);
statement ok;
CREATE SCHEMA sc1;
statement ok;
CREATE TABLE sc1.t1 (i INT PRIMARY KEY);
query TT rowsort
SELECT schema_name, table_name FROM [SHOW TABLES]
----
public t1
sc1 t1
query TT
SELECT schema_name, table_name FROM [SHOW TABLES FROM sc1]
----
sc1 t1
statement ok
USE test
query TT rowsort
SELECT schema_name, table_name FROM [SHOW TABLES FROM for_show]
----
public t1
sc1 t1
query TT
SELECT schema_name, table_name FROM [SHOW TABLES FROM for_show.sc1]
----
sc1 t1
# Unit test for #61149
statement ok
CREATE SCHEMA sc2
statement ok
CREATE TYPE sc3 as enum('foo')
statement ok
BEGIN
statement ok
DROP SCHEMA sc2
statement error pgcode 55000 schema "sc2" is being dropped, try again later
CREATE SCHEMA IF NOT EXISTS sc2
statement ok
END
# Regression test for #62920. The bug that motivated this test would populate
# the schema entry in the database with the database's name rather than the
# schemas.
subtest schema_and_database_with_same_name
statement ok
CREATE DATABASE samename
statement ok
USE samename
statement ok
CREATE SCHEMA foo;
CREATE SCHEMA bar
statement ok
DROP SCHEMA foo
statement ok
CREATE SCHEMA samename
statement ok
DROP SCHEMA bar
statement ok
CREATE TABLE samename.samename.t (i INT PRIMARY KEY)
statement ok
SHOW TABLES
statement ok
DROP DATABASE samename CASCADE;
# Verify schema comments
subtest schema_comments
user root
statement ok
CREATE DATABASE comment_db
statement ok;
USE comment_db;
statement ok
CREATE SCHEMA foo
statement ok
COMMENT ON SCHEMA foo IS 'bar'
query T
SELECT comment FROM system.comments LIMIT 1
----
bar
statement ok
DROP SCHEMA foo
query T
SELECT comment FROM system.comments LIMIT 1
----
statement ok
DROP DATABASE comment_db