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
|
\set MYSQL_HOST `echo \'"$MYSQL_HOST"\'`
\set MYSQL_PORT `echo \'"$MYSQL_PORT"\'`
\set MYSQL_USER_NAME `echo \'"$MYSQL_USER_NAME"\'`
\set MYSQL_PASS `echo \'"$MYSQL_PWD"\'`
-- Before running this file User must create database mysql_fdw_regress on
-- MySQL with all permission for MYSQL_USER_NAME user with MYSQL_PWD password
-- and ran mysql_init.sh file to create tables.
\c contrib_regression
CREATE EXTENSION IF NOT EXISTS mysql_fdw;
CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host :MYSQL_HOST, port :MYSQL_PORT);
CREATE USER MAPPING FOR public SERVER mysql_svr
OPTIONS (username :MYSQL_USER_NAME, password :MYSQL_PASS);
-- Create foreign table and Validate
CREATE FOREIGN TABLE f_mysql_test(a int, b int)
SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'mysql_test');
SELECT * FROM f_mysql_test ORDER BY 1, 2;
a | b
---+---
1 | 1
(1 row)
-- FDW-121: After a change to a pg_foreign_server or pg_user_mapping catalog
-- entry, existing connection should be invalidated and should make new
-- connection using the updated connection details.
-- Alter SERVER option.
-- Set wrong host, subsequent operation on this server should use updated
-- details and fail as the host address is not correct. The error code in error
-- message is different for different server versions and platform, so check
-- that through plpgsql block and give the generic error message.
ALTER SERVER mysql_svr OPTIONS (SET host 'localhos');
DO
$$
BEGIN
SELECT * FROM f_mysql_test ORDER BY 1, 2;
EXCEPTION WHEN others THEN
IF SQLERRM LIKE 'failed to connect to MySQL: Unknown %server host ''localhos'' (%)' THEN
RAISE NOTICE 'failed to connect to MySQL: Unknown MySQL server host ''localhos''';
ELSE
RAISE NOTICE '%', SQLERRM;
END IF;
END;
$$
LANGUAGE plpgsql;
NOTICE: failed to connect to MySQL: Unknown MySQL server host 'localhos'
-- Set the correct host-name, next operation should succeed.
ALTER SERVER mysql_svr OPTIONS (SET host :MYSQL_HOST);
SELECT * FROM f_mysql_test ORDER BY 1, 2;
a | b
---+---
1 | 1
(1 row)
-- Alter USER MAPPING option.
-- Set wrong password, next operation should fail.
ALTER USER MAPPING FOR PUBLIC SERVER mysql_svr
OPTIONS (SET username :MYSQL_USER_NAME, SET password 'bar1');
DO
$$
BEGIN
SELECT * FROM f_mysql_test ORDER BY 1, 2;
EXCEPTION WHEN others THEN
IF SQLERRM LIKE 'failed to connect to MySQL: Access denied for user ''%''@''%'' (using password: YES)' THEN
RAISE NOTICE 'failed to connect to MySQL: Access denied for MYSQL_USER_NAME';
ELSE
RAISE NOTICE '%', SQLERRM;
END IF;
END;
$$
LANGUAGE plpgsql;
NOTICE: failed to connect to MySQL: Access denied for MYSQL_USER_NAME
-- Set correct user-name and password, next operation should succeed.
ALTER USER MAPPING FOR PUBLIC SERVER mysql_svr
OPTIONS (SET username :MYSQL_USER_NAME, SET password :MYSQL_PASS);
SELECT * FROM f_mysql_test ORDER BY 1, 2;
a | b
---+---
1 | 1
(1 row)
-- FDW-654: Connection details should be fetched from mysql default file when
-- mysql_default_file server level option is set.
CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (mysql_default_file '/tmp/my.cnf');
CREATE USER MAPPING FOR public SERVER mysql_server;
CREATE FOREIGN TABLE f_mysql_file_test(a int, b int) SERVER mysql_server
OPTIONS (dbname 'mysql_fdw_regress', table_name 'mysql_test');
-- Negative scenario. Connection should not happen as default file has incorrect
-- details.
\! echo [client] > /tmp/my.cnf
\! echo host=localhos >> /tmp/my.cnf
\! echo user=$MYSQL_USER_NAME >> /tmp/my.cnf
\! echo password=1234 >> /tmp/my.cnf
-- Set wrong host, subsequent operation should use these connection details
-- and fail as the host address is not correct. The error code in error
-- message is different for different server versions and platform, so check
-- that through plpgsql block and give the generic error message.
DO
$$
BEGIN
SELECT * FROM f_mysql_file_test ORDER BY 1, 2;
EXCEPTION WHEN others THEN
IF SQLERRM LIKE 'failed to connect to MySQL: Unknown %server host ''localhos'' (%)' THEN
RAISE NOTICE 'failed to connect to MySQL: Unknown MySQL server host ''localhos''';
ELSE
RAISE NOTICE '%', SQLERRM;
END IF;
END;
$$
LANGUAGE plpgsql;
NOTICE: failed to connect to MySQL: Unknown MySQL server host 'localhos'
-- Prepare the default file with connection details.
\! echo [client] > /tmp/my.cnf
\! echo host=$MYSQL_HOST >> /tmp/my.cnf
\! echo port=$MYSQL_PORT >> /tmp/my.cnf
\! echo user=$MYSQL_USER_NAME >> /tmp/my.cnf
\! echo password=$MYSQL_PWD >> /tmp/my.cnf
\! echo secure_auth=true >> /tmp/my.cnf
-- Connection should happen as default file exists with details.
SELECT * FROM f_mysql_file_test ORDER BY 1, 2;
a | b
---+---
1 | 1
(1 row)
-- Cleanup
DROP FOREIGN TABLE f_mysql_test;
DROP FOREIGN TABLE f_mysql_file_test;
DROP USER MAPPING FOR public SERVER mysql_svr;
DROP USER MAPPING FOR public SERVER mysql_server;
DROP SERVER mysql_svr;
DROP SERVER mysql_server;
\! rm -f /tmp/my.cnf
DROP EXTENSION mysql_fdw;
|
