-
Notifications
You must be signed in to change notification settings - Fork 448
/
ForeignServerDdlTest.cpp
1242 lines (1091 loc) · 45.7 KB
/
ForeignServerDdlTest.cpp
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
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
/*
* Copyright 2022 HEAVY.AI, Inc.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
/**
* @file ForeignServerDdlTest.cpp
* @brief Test suite for foreign server DDL commands
*/
#include <gtest/gtest.h>
#include "DBHandlerTestHelpers.h"
#include "DataMgr/ForeignStorage/AbstractFileStorageDataWrapper.h"
#include "Shared/SysDefinitions.h"
#include "TestHelpers.h"
#ifndef BASE_PATH
#define BASE_PATH "./tmp"
#endif
extern bool g_enable_fsi;
extern bool g_enable_s3_fsi;
extern bool g_enable_system_tables;
namespace {
std::string get_file_server_options() {
return
// Supported options are returned in a sorted order
"BASE_PATH, "
"STORAGE_TYPE.";
}
std::string get_file_storage_types() {
return "LOCAL_FILE"
".";
}
std::string get_data_wrapper_types() {
return "PARQUET_FILE, DELIMITED_FILE, REGEX_PARSED_FILE"
".";
}
} // namespace
class CreateForeignServerTest : public DBHandlerTestFixture {
protected:
void SetUp() override {
g_enable_fsi = true;
g_enable_s3_fsi = true;
DBHandlerTestFixture::SetUp();
getCatalog().dropForeignServer("test_server");
}
void TearDown() override {
g_enable_s3_fsi = true;
getCatalog().dropForeignServer("test_server");
DBHandlerTestFixture::TearDown();
}
void assertExpectedForeignServer() {
auto& catalog = getCatalog();
auto foreign_server = catalog.getForeignServerFromStorage("test_server");
ASSERT_GT(foreign_server->id, 0);
ASSERT_EQ("test_server", foreign_server->name);
ASSERT_EQ(foreign_storage::DataWrapperType::CSV, foreign_server->data_wrapper_type);
ASSERT_EQ(shared::kRootUserId, foreign_server->user_id);
ASSERT_TRUE(foreign_server->options.find(
foreign_storage::AbstractFileStorageDataWrapper::STORAGE_TYPE_KEY) !=
foreign_server->options.end());
ASSERT_EQ(foreign_storage::AbstractFileStorageDataWrapper::LOCAL_FILE_STORAGE_TYPE,
foreign_server->options
.find(foreign_storage::AbstractFileStorageDataWrapper::STORAGE_TYPE_KEY)
->second);
ASSERT_TRUE(foreign_server->options.find(
foreign_storage::AbstractFileStorageDataWrapper::BASE_PATH_KEY) !=
foreign_server->options.end());
ASSERT_EQ("/test_path/",
foreign_server->options
.find(foreign_storage::AbstractFileStorageDataWrapper::BASE_PATH_KEY)
->second);
}
};
TEST_F(CreateForeignServerTest, AllValidParameters) {
sql("CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '/test_path/');");
assertExpectedForeignServer();
}
TEST_F(CreateForeignServerTest, AllValidParametersAndReadFromCache) {
sql("CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '/test_path/');");
assertExpectedForeignServer();
}
TEST_F(CreateForeignServerTest, ExistingServerWithIfNotExists) {
sql("CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '/test_path/');");
sql("CREATE SERVER IF NOT EXISTS test_server FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '/test_path/');");
assertExpectedForeignServer();
}
TEST_F(CreateForeignServerTest, ExistingServerWithoutIfNotExists) {
std::string query{
"CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file WITH "
"(storage_type = 'LOCAL_FILE', base_path = '/test_path/');"};
sql(query);
queryAndAssertException(query,
"A foreign server with name \"test_server\" already exists.");
}
TEST_F(CreateForeignServerTest, MissingStorageType) {
std::string query{
"CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file WITH "
"(base_path = '/test_path/');"};
queryAndAssertException(query, "Foreign server options must contain \"STORAGE_TYPE\".");
}
TEST_F(CreateForeignServerTest, MissingBasePath) {
std::string query{
"CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file WITH "
"(storage_type = 'LOCAL_FILE');"};
sql(query);
}
TEST_F(CreateForeignServerTest, InvalidOption) {
std::string query{
"CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file WITH "
"(invalid_key = 'value', storage_type = 'LOCAL_FILE', base_path = '/test_path/');"};
std::string error_message{
"Invalid foreign server option \"INVALID_KEY\". "
"Option must be one of the following: " +
get_file_server_options()};
queryAndAssertException(query, error_message);
}
TEST_F(CreateForeignServerTest, InvalidStorageType) {
std::string query{
"CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file WITH "
"(storage_type = 'INVALID_TYPE', base_path = '/test_path/');"};
std::string error_message{
"Invalid \"STORAGE_TYPE\" option value. Value must be one of the "
"following: " +
get_file_storage_types()};
queryAndAssertException(query, error_message);
}
TEST_F(CreateForeignServerTest, FsiDisabled) {
std::string query{
"CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file WITH "
"(storage_type = 'LOCAL_FILE', base_path = '/test_path/');"};
g_enable_fsi = false;
queryAndAssertException(query, "Unsupported command: CREATE FOREIGN SERVER");
}
TEST_F(CreateForeignServerTest, InvalidDataWrapper) {
std::string query{
"CREATE SERVER test_server FOREIGN DATA WRAPPER invalid_wrapper WITH "
"(storage_type = 'LOCAL_FILE', base_path = '/test_path/');"};
std::string error_message{
"Invalid data wrapper type \"INVALID_WRAPPER\". "
"Data wrapper type must be one of the following: " +
get_data_wrapper_types()};
queryAndAssertException(query, error_message);
}
TEST_F(CreateForeignServerTest, MissingWithClause) {
std::string query{"CREATE SERVER test_server FOREIGN DATA WRAPPER parquet_file;"};
queryAndAssertException(query, "Foreign server options must contain \"STORAGE_TYPE\".");
}
TEST_F(CreateForeignServerTest, CreateOrReplaceServer) {
std::string query{
"CREATE OR REPLACE SERVER test_server FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '/test_path/');"};
// using a partial exception for the sake of brevity
queryAndAssertPartialException(query,
R"(SQL Error: Encountered "SERVER" at line 1, column 19.
Was expecting:
"MODEL" ...)");
}
class ReservedServerNamePrefixTest : public DBHandlerTestFixture,
public ::testing::WithParamInterface<std::string> {};
TEST_P(ReservedServerNamePrefixTest, ReservedPrefix) {
auto prefix = GetParam();
std::string query{"CREATE SERVER " + prefix +
"_server FOREIGN DATA WRAPPER delimited_file WITH "
"(storage_type = 'LOCAL_FILE', base_path = '/test_path/');"};
queryAndAssertException(
query,
"Foreign server names cannot start with \"default\", \"system\", or \"internal\".");
}
INSTANTIATE_TEST_SUITE_P(ReservedPrefixes,
ReservedServerNamePrefixTest,
::testing::Values("default", "internal", "system"),
[](const auto& param_info) { return param_info.param; });
class DropForeignServerTest : public DBHandlerTestFixture {
protected:
void SetUp() override {
g_enable_fsi = true;
DBHandlerTestFixture::SetUp();
dropTestTable();
sql("CREATE SERVER IF NOT EXISTS test_server FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '/test_path/');");
}
void TearDown() override {
g_enable_fsi = true;
dropTestTable();
getCatalog().dropForeignServer("test_server");
getCatalog().dropForeignServer("test_server_1");
DBHandlerTestFixture::TearDown();
}
void createTestServer1() {
const auto base_path =
boost::filesystem::canonical("../../Tests/FsiDataFiles/").string();
sql("CREATE SERVER test_server_1 FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '" +
base_path + "');");
}
void assertNullForeignServer(const std::string& server_name) {
auto& catalog = getCatalog();
auto foreign_server = catalog.getForeignServerFromStorage(server_name);
ASSERT_EQ(nullptr, foreign_server);
}
private:
void dropTestTable() { sql("DROP FOREIGN TABLE IF EXISTS test_table;"); }
};
TEST_F(DropForeignServerTest, ExistingServer) {
sql("DROP SERVER test_server;");
assertNullForeignServer("test_server");
}
TEST_F(DropForeignServerTest, NonExistingServerWithIfExists) {
sql("DROP SERVER IF EXISTS test_server_2;");
assertNullForeignServer("test_server_2");
}
TEST_F(DropForeignServerTest, NonExistingServerWithoutIfExists) {
queryAndAssertException("DROP SERVER test_server_2;",
"Foreign server with name \"test_server_2\" can not "
"be dropped. Server does not exist.");
}
TEST_F(DropForeignServerTest, DefaultServers) {
queryAndAssertException("DROP SERVER default_local_delimited;",
"Default servers cannot be dropped.");
queryAndAssertException("DROP SERVER default_local_parquet;",
"Default servers cannot be dropped.");
}
TEST_F(DropForeignServerTest, ForeignTableReferencingServer) {
createTestServer1();
sql("CREATE FOREIGN TABLE test_table (c1 int) SERVER test_server_1 "
"WITH (file_path = 'example_1.csv');");
std::string error_message{
"Foreign server \"test_server_1\" is referenced by existing "
"foreign tables and cannot be dropped."};
queryAndAssertException("DROP SERVER test_server_1;", error_message);
}
TEST_F(DropForeignServerTest, FsiDisabled) {
g_enable_fsi = false;
queryAndAssertException("DROP SERVER test_server;",
"Unsupported command: DROP FOREIGN SERVER");
}
class ForeignServerPrivilegesDdlTest : public DBHandlerTestFixture {
protected:
static void SetUpTestSuite() {
createDBHandler();
switchToAdmin();
dropTestUser();
createTestUser();
}
static void TearDownTestSuite() { dropTestUser(); }
void SetUp() override {
g_enable_fsi = true;
DBHandlerTestFixture::SetUp();
loginAdmin();
dropServer();
}
void TearDown() override {
g_enable_fsi = true;
loginAdmin();
dropServer();
revokeTestUserServerPrivileges();
}
void revokeTestUserServerPrivileges() {
sql("REVOKE ALL ON DATABASE " + shared::kDefaultDbName + " FROM test_user;");
sql("GRANT ACCESS ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
}
static void createTestUser() {
sql("CREATE USER test_user (password = 'test_pass');");
sql("GRANT ACCESS ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
}
static void dropTestUser() { sql("DROP USER IF EXISTS test_user;"); }
void createTestServer() {
sql("CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '/test_path/');");
}
void dropServer() { sql("DROP SERVER IF EXISTS test_server;"); }
void assertExpectedForeignServerCreatedByUser() {
auto& catalog = getCatalog();
auto foreign_server = catalog.getForeignServerFromStorage("test_server");
ASSERT_GT(foreign_server->id, 0);
ASSERT_EQ(foreign_server->user_id, getCurrentUser().userId);
ASSERT_EQ(getCurrentUser().userName, "test_user");
ASSERT_EQ(foreign_server->name, "test_server");
}
void assertNullForeignServer() {
auto& catalog = getCatalog();
auto foreign_server = catalog.getForeignServerFromStorage("test_server");
ASSERT_EQ(nullptr, foreign_server);
}
};
TEST_F(ForeignServerPrivilegesDdlTest, CreateServerWithoutPrivilege) {
login("test_user", "test_pass");
queryAndAssertException(
"CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '/test_path/');",
"Server test_server will not be created. "
"User has no create privileges.");
}
TEST_F(ForeignServerPrivilegesDdlTest, CreateServerWithPrivilege) {
sql("GRANT CREATE SERVER ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
login("test_user", "test_pass");
createTestServer();
assertExpectedForeignServerCreatedByUser();
}
TEST_F(ForeignServerPrivilegesDdlTest,
CreateServerWithPrivilegeThenDropWithoutDropPrivilege) {
sql("GRANT CREATE SERVER ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
login("test_user", "test_pass");
createTestServer();
assertExpectedForeignServerCreatedByUser();
sql("DROP SERVER test_server;");
assertNullForeignServer();
}
TEST_F(ForeignServerPrivilegesDdlTest, DropServerWithoutPrivilege) {
createTestServer();
login("test_user", "test_pass");
queryAndAssertException("DROP SERVER test_server;",
"Server test_server will not be dropped. "
"User has no DROP SERVER privileges.");
}
TEST_F(ForeignServerPrivilegesDdlTest, DropServerWithPrivilege) {
createTestServer();
sql("GRANT DROP SERVER ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
login("test_user", "test_pass");
sql("DROP SERVER test_server;");
assertNullForeignServer();
}
TEST_F(ForeignServerPrivilegesDdlTest, DropServerWithSpecificPrivilege) {
createTestServer();
sql("GRANT DROP ON SERVER test_server TO test_user;");
login("test_user", "test_pass");
sql("DROP SERVER test_server;");
assertNullForeignServer();
}
TEST_F(ForeignServerPrivilegesDdlTest, AlterServerWithoutPrivilege) {
createTestServer();
login("test_user", "test_pass");
queryAndAssertException(
"ALTER SERVER test_server SET FOREIGN DATA WRAPPER PARQUET_FILE;",
"Server test_server can not be altered. User has no ALTER SERVER "
"privileges.");
}
TEST_F(ForeignServerPrivilegesDdlTest, AlterServerWithPrivilege) {
createTestServer();
sql("GRANT ALTER SERVER ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
login("test_user", "test_pass");
sql("ALTER SERVER test_server SET FOREIGN DATA WRAPPER PARQUET_FILE;");
auto foreign_server = getCatalog().getForeignServerFromStorage("test_server");
ASSERT_EQ(foreign_server->data_wrapper_type, "PARQUET_FILE");
}
TEST_F(ForeignServerPrivilegesDdlTest, AlterServerWithSpecificPrivilege) {
createTestServer();
sql("GRANT ALTER ON SERVER test_server TO test_user;");
login("test_user", "test_pass");
sql("ALTER SERVER test_server SET FOREIGN DATA WRAPPER PARQUET_FILE;");
auto foreign_server = getCatalog().getForeignServerFromStorage("test_server");
ASSERT_EQ(foreign_server->data_wrapper_type, "PARQUET_FILE");
}
TEST_F(ForeignServerPrivilegesDdlTest, GrantRevokeAlterServerWithPrivilege) {
createTestServer();
sql("GRANT ALTER SERVER ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
sql("REVOKE ALTER SERVER ON DATABASE " + shared::kDefaultDbName + " FROM test_user;");
login("test_user", "test_pass");
queryAndAssertException(
"ALTER SERVER test_server SET FOREIGN DATA WRAPPER PARQUET_FILE;",
"Server test_server can not be altered. User has no ALTER SERVER "
"privileges.");
}
TEST_F(ForeignServerPrivilegesDdlTest, GrantRevokeAlterServerWithSpecificPrivilege) {
createTestServer();
sql("GRANT ALTER ON SERVER test_server TO test_user;");
sql("REVOKE ALTER ON SERVER test_server FROM test_user;");
login("test_user", "test_pass");
queryAndAssertException(
"ALTER SERVER test_server SET FOREIGN DATA WRAPPER PARQUET_FILE;",
"Server test_server can not be altered. User has no ALTER SERVER "
"privileges.");
}
TEST_F(ForeignServerPrivilegesDdlTest,
DropServerWithSpecificPrivilegeRepeatedWithoutPrivilege) {
createTestServer();
sql("GRANT DROP ON SERVER test_server TO test_user;");
login("test_user", "test_pass");
sql("DROP SERVER test_server;");
assertNullForeignServer();
loginAdmin();
createTestServer();
login("test_user", "test_pass");
queryAndAssertException("DROP SERVER test_server;",
"Server test_server will not be dropped. "
"User has no DROP SERVER privileges.");
}
TEST_F(ForeignServerPrivilegesDdlTest, CreateServerWithGrantThenRevokePrivilege) {
sql("GRANT CREATE SERVER ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
sql("REVOKE CREATE SERVER ON DATABASE " + shared::kDefaultDbName + " FROM test_user;");
login("test_user", "test_pass");
queryAndAssertException(
"CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '/test_path/');",
"Server test_server will not be created. "
"User has no create privileges.");
}
TEST_F(ForeignServerPrivilegesDdlTest, DropServerWithGrantThenRevokePrivilege) {
createTestServer();
sql("GRANT DROP SERVER ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
sql("REVOKE DROP SERVER ON DATABASE " + shared::kDefaultDbName + " FROM test_user;");
login("test_user", "test_pass");
queryAndAssertException("DROP SERVER test_server;",
"Server test_server will not be dropped. "
"User has no DROP SERVER privileges.");
}
TEST_F(ForeignServerPrivilegesDdlTest, DropServerWithGrantThenRevokeSpecificPrivilege) {
createTestServer();
sql("GRANT DROP ON SERVER test_server TO test_user;");
sql("REVOKE DROP ON SERVER test_server FROM test_user;");
login("test_user", "test_pass");
queryAndAssertException("DROP SERVER test_server;",
"Server test_server will not be dropped. "
"User has no DROP SERVER privileges.");
}
TEST_F(ForeignServerPrivilegesDdlTest, RevokeNonExistentPrivilege) {
createTestServer();
queryAndAssertException(
"REVOKE DROP SERVER ON DATABASE " + shared::kDefaultDbName + " FROM test_user;",
"Can not revoke privileges because"
" test_user has no privileges to " +
shared::kDefaultDbName);
}
TEST_F(ForeignServerPrivilegesDdlTest, RevokeNonExistentSpecificPrivilege) {
createTestServer();
queryAndAssertException("REVOKE DROP ON SERVER test_server FROM test_user;",
"Can not revoke privileges because test_user"
" has no privileges to test_server");
}
TEST_F(ForeignServerPrivilegesDdlTest, RevokeSpecificPrivilegeOnNonExistentServer) {
queryAndAssertException("REVOKE DROP ON SERVER test_server FROM test_user;",
"Failure generating DB object key. "
"Server test_server does not exist.");
}
TEST_F(ForeignServerPrivilegesDdlTest, GrantPrivilegeOnNonExistentDatabase) {
queryAndAssertException("GRANT CREATE SERVER ON DATABASE nonexistent_db TO test_user;",
"Failure generating DB object key."
" Database nonexistent_db does not exist.");
}
TEST_F(ForeignServerPrivilegesDdlTest, GrantPrivilegeFsiDisabled) {
g_enable_fsi = false;
queryAndAssertException(
"GRANT CREATE SERVER ON DATABASE " + shared::kDefaultDbName + " TO test_user;",
"GRANT failed. SERVER object unrecognized.");
}
TEST_F(ForeignServerPrivilegesDdlTest, RevokePrivilegeFsiDisabled) {
g_enable_fsi = false;
queryAndAssertException(
"REVOKE CREATE SERVER ON DATABASE " + shared::kDefaultDbName + " FROM test_user;",
"REVOKE failed. SERVER object unrecognized.");
}
TEST_F(ForeignServerPrivilegesDdlTest, GrantSpecificPrivilegeFsiDisabled) {
createTestServer();
g_enable_fsi = false;
queryAndAssertException("GRANT DROP ON SERVER test_server TO test_user;",
"GRANT failed. SERVER object unrecognized.");
}
TEST_F(ForeignServerPrivilegesDdlTest, GrantAllCreateServerDropServer) {
sql("GRANT ALL ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
login("test_user", "test_pass");
createTestServer();
assertExpectedForeignServerCreatedByUser();
sql("DROP SERVER test_server;");
assertNullForeignServer();
}
TEST_F(ForeignServerPrivilegesDdlTest, GrantAllDropServer) {
createTestServer();
sql("GRANT ALL ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
login("test_user", "test_pass");
sql("DROP SERVER test_server;");
assertNullForeignServer();
}
TEST_F(ForeignServerPrivilegesDdlTest, GrantAllRevokeCreateServerCreateServer) {
sql("GRANT ALL ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
sql("REVOKE CREATE SERVER ON DATABASE " + shared::kDefaultDbName + " FROM test_user;");
login("test_user", "test_pass");
queryAndAssertException(
"CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '/test_path/');",
"Server test_server will not be created. "
"User has no create privileges.");
}
TEST_F(ForeignServerPrivilegesDdlTest, GrantCreateServerRevokeAllCreateServer) {
sql("GRANT CREATE SERVER ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
sql("REVOKE ALL ON DATABASE " + shared::kDefaultDbName + " FROM test_user;");
// must still allow access for login:
sql("GRANT ACCESS ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
login("test_user", "test_pass");
queryAndAssertException(
"CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '/test_path/');",
"Server test_server will not be created. "
"User has no create privileges.");
}
TEST_F(ForeignServerPrivilegesDdlTest, GrantDropServerRevokeAllDropServer) {
createTestServer();
sql("GRANT DROP SERVER ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
sql("REVOKE ALL ON DATABASE " + shared::kDefaultDbName + " FROM test_user;");
// must still allow access for login:
sql("GRANT ACCESS ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
login("test_user", "test_pass");
queryAndAssertException("DROP SERVER test_server;",
"Server test_server will not be dropped. "
"User has no DROP SERVER privileges.");
}
class ShowForeignServerTest : public DBHandlerTestFixture {
protected:
void SetUp() override {
g_enable_fsi = true;
DBHandlerTestFixture::SetUp();
// Check defeault server exist
{
auto foreign_server = getCatalog().getForeignServer("default_local_delimited");
ASSERT_NE(foreign_server, nullptr);
ASSERT_GT(foreign_server->id, 0);
}
{
auto foreign_server = getCatalog().getForeignServer("default_local_parquet");
ASSERT_NE(foreign_server, nullptr);
ASSERT_GT(foreign_server->id, 0);
}
}
void TearDown() override {
loginAdmin();
sql("DROP SERVER IF EXISTS test_server;");
DBHandlerTestFixture::TearDown();
}
enum ColumnIndex { SERVER_NAME, DATA_WRAPPER, CREATED_AT, OPTIONS, NUM_FIELDS };
void assertExpectedFormat(const TQueryResult& result) {
ASSERT_EQ(result.row_set.is_columnar, true);
ASSERT_EQ(result.row_set.columns.size(), size_t(NUM_FIELDS));
ASSERT_EQ(result.row_set.row_desc[SERVER_NAME].col_type.type, TDatumType::STR);
ASSERT_EQ(result.row_set.row_desc[SERVER_NAME].col_name, "server_name");
ASSERT_EQ(result.row_set.row_desc[DATA_WRAPPER].col_type.type, TDatumType::STR);
ASSERT_EQ(result.row_set.row_desc[DATA_WRAPPER].col_name, "data_wrapper");
ASSERT_EQ(result.row_set.row_desc[CREATED_AT].col_type.type, TDatumType::TIMESTAMP);
ASSERT_EQ(result.row_set.row_desc[CREATED_AT].col_name, "created_at");
ASSERT_EQ(result.row_set.row_desc[OPTIONS].col_type.type, TDatumType::STR);
ASSERT_EQ(result.row_set.row_desc[OPTIONS].col_name, "options");
}
// Allow times within a one second window for rounding
static const int TIME_EPSILON = 1.0;
bool isDateTimeAfterTimeStamp(std::time_t datetime, std::time_t reference) {
return std::difftime(datetime, reference) >= -TIME_EPSILON;
}
bool isDateTimeInPast(std::time_t datetime) {
std::time_t current_time = std::time(nullptr);
return std::difftime(datetime, current_time) <= TIME_EPSILON;
}
void assertServerResultFound(const TQueryResult& result,
const std::string& name,
const std::string& wrapper,
const std::string& options) {
int num_matches = 0;
for (size_t index = 0;
index < result.row_set.columns[SERVER_NAME].data.str_col.size();
++index) {
if (result.row_set.columns[SERVER_NAME].data.str_col[index] == name &&
result.row_set.columns[DATA_WRAPPER].data.str_col[index] == wrapper &&
result.row_set.columns[OPTIONS].data.str_col[index] == options) {
num_matches++;
ASSERT_GT(result.row_set.columns[CREATED_AT].data.int_col[index], 0);
ASSERT_TRUE(
isDateTimeInPast(result.row_set.columns[CREATED_AT].data.int_col[index]));
}
}
ASSERT_EQ(num_matches, 1);
}
void assertServerLocalCSVFound(const TQueryResult& result) {
assertServerResultFound(result,
"default_local_delimited",
"DELIMITED_FILE",
"{\"STORAGE_TYPE\":\"LOCAL_FILE\"}");
}
void assertServerLocalParquetFound(const TQueryResult& result) {
assertServerResultFound(result,
"default_local_parquet",
"PARQUET_FILE",
"{\"STORAGE_TYPE\":\"LOCAL_FILE\"}");
}
void assertServerLocalRegexParserFound(const TQueryResult& result) {
assertServerResultFound(result,
"default_local_regex_parsed",
"REGEX_PARSED_FILE",
"{\"STORAGE_TYPE\":\"LOCAL_FILE\"}");
}
void assertNumResults(const TQueryResult& result, size_t num_results) {
ASSERT_EQ(result.row_set.columns[SERVER_NAME].data.str_col.size(), num_results);
ASSERT_EQ(result.row_set.columns[DATA_WRAPPER].data.str_col.size(), num_results);
ASSERT_EQ(result.row_set.columns[CREATED_AT].data.int_col.size(), num_results);
ASSERT_EQ(result.row_set.columns[OPTIONS].data.str_col.size(), num_results);
}
void queryAndAssertAllDefaultServers(const std::optional<TSessionId>& session_id = {}) {
TQueryResult result;
if (session_id.has_value()) {
sql(result, "SHOW SERVERS;", session_id.value());
} else {
sql(result, "SHOW SERVERS;");
}
assertExpectedFormat(result);
assertNumResults(result, LOCAL_DATA_WRAPPERS_COUNT);
assertServerLocalCSVFound(result);
assertServerLocalParquetFound(result);
assertServerLocalRegexParserFound(result);
}
static constexpr size_t LOCAL_DATA_WRAPPERS_COUNT{3};
};
TEST_F(ShowForeignServerTest, SHOWALL_DEFAULT) {
queryAndAssertAllDefaultServers();
}
TEST_F(ShowForeignServerTest, ShowAllDefaultInInformationSchemaDb) {
login("admin", "HyperInteractive", "information_schema");
queryAndAssertAllDefaultServers();
}
TEST_F(ShowForeignServerTest, SHOW_WHERE_EQ) {
TQueryResult result;
std::string query{"SHOW SERVERS WHERE server_name = 'default_local_delimited';"};
sql(result, query);
assertExpectedFormat(result);
assertNumResults(result, 1);
assertServerLocalCSVFound(result);
}
TEST_F(ShowForeignServerTest, SHOW_WHERE_LIKE) {
TQueryResult result;
std::string query{"SHOW SERVERS WHERE server_name LIKE '%_parquet';"};
sql(result, query);
assertExpectedFormat(result);
assertNumResults(result, 1);
assertServerLocalParquetFound(result);
}
TEST_F(ShowForeignServerTest, SHOW_WHERE_LIKE_OR_EQ) {
TQueryResult result;
std::string query{
"SHOW SERVERS WHERE server_name LIKE '%_delimited' OR server_name = "
"'default_local_parquet';"};
sql(result, query);
assertExpectedFormat(result);
assertNumResults(result, 2);
assertServerLocalCSVFound(result);
assertServerLocalParquetFound(result);
}
TEST_F(ShowForeignServerTest, SHOW_WHERE_LIKE_AND_EQ) {
TQueryResult result;
std::string query{
"SHOW SERVERS WHERE server_name LIKE '%_delimited' AND data_wrapper = "
"'DELIMITED_FILE';"};
sql(result, query);
assertExpectedFormat(result);
assertNumResults(result, 1);
assertServerLocalCSVFound(result);
}
TEST_F(ShowForeignServerTest, SHOW_WHERE_LIKE_OR_LIKE_AND_LIKE) {
TQueryResult result;
std::string query{
"SHOW SERVERS WHERE server_name LIKE '%_delimited' OR data_wrapper = "
"'PARQUET_FILE' "
"AND options LIKE '%STORAGE_TYPE%';"};
sql(result, query);
assertExpectedFormat(result);
assertNumResults(result, 2);
assertServerLocalCSVFound(result);
assertServerLocalParquetFound(result);
}
TEST_F(ShowForeignServerTest, SHOW_WHERE_LIKE_EMPTY) {
TQueryResult result;
std::string query{"SHOW SERVERS WHERE server_name LIKE 'unknown%';"};
sql(result, query);
assertNumResults(result, 0);
}
TEST_F(ShowForeignServerTest, SHOW_WHERE_EQ_EMPTY) {
TQueryResult result;
std::string query{"SHOW SERVERS WHERE data_wrapper = 'unknown';"};
sql(result, query);
assertNumResults(result, 0);
}
TEST_F(ShowForeignServerTest, SHOW_TIMESTAMP_LIKE) {
// check that like operator with timestamp will error out
queryAndAssertException("SHOW SERVERS WHERE created_at LIKE 'test';",
"LIKE operator is incompatible with TIMESTAMP data");
}
TEST_F(ShowForeignServerTest, SHOW_TIMESTAMP_EQ) {
// check that = operator with timestamp works
// create new server as default ones may have the same date
std::time_t current_time = std::time(nullptr);
{
TQueryResult result;
std::string query{
"CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file WITH "
"(storage_type = 'LOCAL_FILE', base_path = '/test_path/');"};
sql(result, query);
}
std::string options_json =
"{\"BASE_PATH\":\"/test_path/\",\"STORAGE_TYPE\":\"LOCAL_FILE\"}";
time_t created_at;
{
TQueryResult result;
std::string query{"SHOW SERVERS;"};
sql(result, query);
assertNumResults(result, LOCAL_DATA_WRAPPERS_COUNT + 1);
bool test_server_found{false};
const auto& server_name_column = result.row_set.columns[SERVER_NAME].data.str_col;
for (size_t i = 0; i < server_name_column.size(); i++) {
if (server_name_column[i] == "test_server") {
created_at = result.row_set.columns[CREATED_AT].data.int_col[i];
test_server_found = true;
break;
}
}
ASSERT_TRUE(test_server_found);
}
{
TQueryResult result;
std::tm* ptm = std::gmtime(&created_at);
char buffer[256];
std::strftime(
buffer, 256, "SHOW SERVERS WHERE created_at = '%Y-%m-%d %H:%M:%S';", ptm);
sql(result, buffer);
// Result must return the test_server, but may include the default servers, if the
// previous tests run in under 1s
size_t num_results = result.row_set.columns[CREATED_AT].data.int_col.size();
ASSERT_GT(num_results, static_cast<size_t>(0));
ASSERT_LE(num_results, LOCAL_DATA_WRAPPERS_COUNT + 1);
assertServerResultFound(result, "test_server", "DELIMITED_FILE", options_json);
for (const auto& timestamp : result.row_set.columns[CREATED_AT].data.int_col) {
// Check all returned values
ASSERT_TRUE(isDateTimeAfterTimeStamp(timestamp, current_time));
}
}
}
TEST_F(ShowForeignServerTest, SHOW_ADD_DROP) {
// check that server is added to resultset when created and removed when dropped
std::time_t current_time = std::time(nullptr);
{
TQueryResult result;
std::string query{
"CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file WITH "
"(storage_type = 'LOCAL_FILE', base_path = '/test_path/');"};
sql(result, query);
}
std::string options_json =
"{\"BASE_PATH\":\"/test_path/\",\"STORAGE_TYPE\":\"LOCAL_FILE\"}";
{
TQueryResult result;
std::string query{"SHOW SERVERS;"};
sql(result, query);
assertNumResults(result, LOCAL_DATA_WRAPPERS_COUNT + 1);
assertServerLocalCSVFound(result);
assertServerLocalParquetFound(result);
assertServerLocalRegexParserFound(result);
assertServerResultFound(result, "test_server", "DELIMITED_FILE", options_json);
}
{
TQueryResult result;
std::string query{"SHOW SERVERS WHERE server_name = 'test_server';"};
sql(result, query);
assertNumResults(result, 1);
assertServerResultFound(result, "test_server", "DELIMITED_FILE", options_json);
}
{
TQueryResult result;
std::string query{"SHOW SERVERS WHERE options LIKE '%test_path%';"};
sql(result, query);
assertNumResults(result, 1);
assertServerResultFound(result, "test_server", "DELIMITED_FILE", options_json);
ASSERT_TRUE(isDateTimeAfterTimeStamp(
result.row_set.columns[CREATED_AT].data.int_col[0], current_time));
}
{
TQueryResult result;
std::string query{"DROP SERVER test_server;"};
sql(result, query);
}
queryAndAssertAllDefaultServers();
}
TEST_F(ShowForeignServerTest, SHOW_PRIVILEGE) {
// check that servers are only shown to users with correct privilege
sql("CREATE USER test (password = 'HyperInterative', is_super = 'false', "
"default_db='" +
shared::kDefaultDbName + "');");
sql("GRANT ACCESS ON DATABASE " + shared::kDefaultDbName + " TO test;");
TSessionId test_session_id;
login("test", shared::kDefaultRootPasswd, shared::kDefaultDbName, test_session_id);
{
TQueryResult result;
sql(result, "SHOW SERVERS;", test_session_id);
assertNumResults(result, 0);
}
sql("GRANT ALL ON DATABASE " + shared::kDefaultDbName + " TO test;");
queryAndAssertAllDefaultServers(test_session_id);
logout(test_session_id);
sql("DROP USER test;");
}
TEST_F(ShowForeignServerTest, BadTimestamp) {
std::string query{"SHOW SERVERS WHERE created_at = 'test';"};
queryAndAssertException(query, "Invalid TIMESTAMP string (test)");
}
TEST_F(ShowForeignServerTest, BadQuery) {
std::string query{"SHOW SERVERS WHERE server_name 'x' ;"};
queryAndAssertException(query,
"SQL Error: Encountered \"\\'x\\'\" at line 1, column 32.\nWas "
"expecting one of:\n"
" \"LIKE\" ...\n \"=\" ...\n \".\" ...\n ");
}
TEST_F(ShowForeignServerTest, BadAttribute) {
std::string query{"SHOW SERVERS WHERE invalid_param = 'x';"};
queryAndAssertException(query, "Attribute with name \"invalid_param\" does not exist.");
}
TEST_F(ShowForeignServerTest, FsiDisabled) {
g_enable_fsi = false;
std::string query{"SHOW SERVERS;"};
queryAndAssertException(query, "Unsupported command: SHOW FOREIGN SERVERS");
g_enable_fsi = true;
}
class AlterForeignServerTest : public DBHandlerTestFixture {
protected:
static void SetUpTestSuite() {
createDBHandler();
switchToAdmin();
createTestUser();
setTestUserId();
}
static void TearDownTestSuite() { dropTestUser(); }
static int32_t test_user_id;
void SetUp() override {
g_enable_fsi = true;
DBHandlerTestFixture::SetUp();
loginAdmin();
dropServers();
}
void TearDown() override {
g_enable_fsi = true;
loginAdmin();
dropServers();
}
static void createTestUser() {
sql("CREATE USER test_user (password = 'test_pass');");
sql("GRANT ACCESS ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
sql("GRANT CREATE SERVER ON DATABASE " + shared::kDefaultDbName + " TO test_user;");
}
static void dropTestUser() { sql("DROP USER IF EXISTS test_user;"); }
void createTestServer() {
sql("CREATE SERVER test_server FOREIGN DATA WRAPPER delimited_file "
"WITH (storage_type = 'LOCAL_FILE', base_path = '/test_path/');");
}
void dropServers() {
sql("DROP SERVER IF EXISTS test_server;");
sql("DROP SERVER IF EXISTS renamed_server;");
}
const std::map<std::string, std::string, std::less<>> DEFAULT_OPTIONS{
{"base_path", "/test_path/"},
{"storage_type", "LOCAL_FILE"},
};
foreign_storage::ForeignServer createExpectedForeignServer(
const std::string& server_name,
const std::string& data_wrapper,
const std::map<std::string, std::string, std::less<>>& options,
const int owner_user_id = shared::kRootUserId) {
foreign_storage::OptionsContainer options_container(
foreign_storage::OptionsContainer(options).getOptionsAsJsonString());
foreign_storage::ForeignServer foreign_server{
server_name, to_upper(data_wrapper), options_container.options, owner_user_id};
return foreign_server;
}