From c82725edfa1aec1cad940b15b6e22ee3dbd57f20 Mon Sep 17 00:00:00 2001 From: Noah Misch Date: Thu, 19 Jun 2014 21:41:26 -0400 Subject: [PATCH] Let installcheck-world pass against a server requiring a password. Give passwords to each user created in support of an ECPG connection test case. Use SET SESSION AUTHORIZATION, not a fresh connection, to reduce privileges during a dblink test case. To test against such a server, both the "make installcheck-world" environment and the postmaster environment must provide the default user's password; $PGPASSFILE is the principal way to do so. (The postmaster environment needs it for dblink and postgres_fdw tests.) --- contrib/dblink/expected/dblink.out | 9 +- contrib/dblink/sql/dblink.sql | 9 +- src/interfaces/ecpg/test/connect/test5.pgc | 18 ++-- .../ecpg/test/expected/connect-test5.c | 82 ++++++++++--------- .../ecpg/test/expected/connect-test5.stderr | 18 ++-- 5 files changed, 75 insertions(+), 61 deletions(-) diff --git a/contrib/dblink/expected/dblink.out b/contrib/dblink/expected/dblink.out index f237c43d3d..36fdf733fe 100644 --- a/contrib/dblink/expected/dblink.out +++ b/contrib/dblink/expected/dblink.out @@ -782,18 +782,17 @@ SELECT dblink_disconnect('dtest1'); (1 row) -- test foreign data wrapper functionality -CREATE USER dblink_regression_test; +CREATE ROLE dblink_regression_test; CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (dbname 'contrib_regression'); CREATE USER MAPPING FOR public SERVER fdtest OPTIONS (server 'localhost'); -- fail, can't specify server here ERROR: invalid option "server" HINT: Valid options in this context are: user, password -CREATE USER MAPPING FOR public SERVER fdtest; +CREATE USER MAPPING FOR public SERVER fdtest OPTIONS (user :'USER'); GRANT USAGE ON FOREIGN SERVER fdtest TO dblink_regression_test; GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO dblink_regression_test; -\set ORIGINAL_USER :USER -\c - dblink_regression_test +SET SESSION AUTHORIZATION dblink_regression_test; -- should fail SELECT dblink_connect('myconn', 'fdtest'); ERROR: password is required @@ -821,7 +820,7 @@ SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]) 10 | k | {a10,b10,c10} (11 rows) -\c - :ORIGINAL_USER +\c - - REVOKE USAGE ON FOREIGN SERVER fdtest FROM dblink_regression_test; REVOKE EXECUTE ON FUNCTION dblink_connect_u(text, text) FROM dblink_regression_test; DROP USER dblink_regression_test; diff --git a/contrib/dblink/sql/dblink.sql b/contrib/dblink/sql/dblink.sql index 2a107601c5..30396ed984 100644 --- a/contrib/dblink/sql/dblink.sql +++ b/contrib/dblink/sql/dblink.sql @@ -359,25 +359,24 @@ SELECT dblink_error_message('dtest1'); SELECT dblink_disconnect('dtest1'); -- test foreign data wrapper functionality -CREATE USER dblink_regression_test; +CREATE ROLE dblink_regression_test; CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (dbname 'contrib_regression'); CREATE USER MAPPING FOR public SERVER fdtest OPTIONS (server 'localhost'); -- fail, can't specify server here -CREATE USER MAPPING FOR public SERVER fdtest; +CREATE USER MAPPING FOR public SERVER fdtest OPTIONS (user :'USER'); GRANT USAGE ON FOREIGN SERVER fdtest TO dblink_regression_test; GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO dblink_regression_test; -\set ORIGINAL_USER :USER -\c - dblink_regression_test +SET SESSION AUTHORIZATION dblink_regression_test; -- should fail SELECT dblink_connect('myconn', 'fdtest'); -- should succeed SELECT dblink_connect_u('myconn', 'fdtest'); SELECT * FROM dblink('myconn','SELECT * FROM foo') AS t(a int, b text, c text[]); -\c - :ORIGINAL_USER +\c - - REVOKE USAGE ON FOREIGN SERVER fdtest FROM dblink_regression_test; REVOKE EXECUTE ON FUNCTION dblink_connect_u(text, text) FROM dblink_regression_test; DROP USER dblink_regression_test; diff --git a/src/interfaces/ecpg/test/connect/test5.pgc b/src/interfaces/ecpg/test/connect/test5.pgc index d3efecbf62..5ba59eb822 100644 --- a/src/interfaces/ecpg/test/connect/test5.pgc +++ b/src/interfaces/ecpg/test/connect/test5.pgc @@ -21,7 +21,9 @@ exec sql end declare section; ECPGdebug(1, stderr); exec sql connect to connectdb as main; + exec sql alter user connectdb ENCRYPTED PASSWORD 'insecure'; exec sql alter user connectuser ENCRYPTED PASSWORD 'connectpw'; + exec sql commit; exec sql disconnect; /* <-- "main" not specified */ strcpy(db, "connectdb"); @@ -38,28 +40,28 @@ exec sql end declare section; exec sql connect to 'connectdb' as main; exec sql disconnect main; - exec sql connect to as main user connectdb; + exec sql connect to as main user connectdb/insecure; exec sql disconnect main; - exec sql connect to connectdb as main user connectuser/connectdb; + exec sql connect to connectdb as main user connectuser/connectpw; exec sql disconnect main; - exec sql connect to unix:postgresql://localhost/connectdb as main user connectuser; + exec sql connect to unix:postgresql://localhost/connectdb as main user connectuser/connectpw; exec sql disconnect main; - exec sql connect to "unix:postgresql://localhost/connectdb" as main user connectuser; + exec sql connect to "unix:postgresql://localhost/connectdb" as main user connectuser/connectpw; exec sql disconnect main; - exec sql connect to 'unix:postgresql://localhost/connectdb' as main user :user; + exec sql connect to 'unix:postgresql://localhost/connectdb' as main user :user USING "connectpw"; exec sql disconnect main; - exec sql connect to unix:postgresql://localhost/connectdb?connect_timeout=14&client_encoding=latin1 as main user connectuser; + exec sql connect to unix:postgresql://localhost/connectdb?connect_timeout=14&client_encoding=latin1 as main user connectuser/connectpw; exec sql disconnect main; - exec sql connect to "unix:postgresql://200.46.204.71/connectdb" as main user connectuser; + exec sql connect to "unix:postgresql://200.46.204.71/connectdb" as main user connectuser/connectpw; exec sql disconnect main; - exec sql connect to unix:postgresql://localhost/ as main user connectdb; + exec sql connect to unix:postgresql://localhost/ as main user connectdb IDENTIFIED BY insecure; exec sql disconnect main; /* connect twice */ diff --git a/src/interfaces/ecpg/test/expected/connect-test5.c b/src/interfaces/ecpg/test/expected/connect-test5.c index a8f79f9a95..79decd3595 100644 --- a/src/interfaces/ecpg/test/expected/connect-test5.c +++ b/src/interfaces/ecpg/test/expected/connect-test5.c @@ -43,113 +43,119 @@ main(void) { ECPGconnect(__LINE__, 0, "connectdb" , NULL, NULL , "main", 0); } #line 23 "test5.pgc" - { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "alter user connectuser encrypted password 'connectpw'", ECPGt_EOIT, ECPGt_EORT);} + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "alter user connectdb encrypted password 'insecure'", ECPGt_EOIT, ECPGt_EORT);} #line 24 "test5.pgc" - { ECPGdisconnect(__LINE__, "CURRENT");} + { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "alter user connectuser encrypted password 'connectpw'", ECPGt_EOIT, ECPGt_EORT);} #line 25 "test5.pgc" + + { ECPGtrans(__LINE__, NULL, "commit");} +#line 26 "test5.pgc" + + { ECPGdisconnect(__LINE__, "CURRENT");} +#line 27 "test5.pgc" /* <-- "main" not specified */ strcpy(db, "connectdb"); strcpy(id, "main"); { ECPGconnect(__LINE__, 0, db , NULL, NULL , id, 0); } -#line 29 "test5.pgc" +#line 31 "test5.pgc" { ECPGdisconnect(__LINE__, id);} -#line 30 "test5.pgc" +#line 32 "test5.pgc" { ECPGconnect(__LINE__, 0, "connectdb" , NULL, NULL , "main", 0); } -#line 32 "test5.pgc" +#line 34 "test5.pgc" { ECPGdisconnect(__LINE__, "main");} -#line 33 "test5.pgc" +#line 35 "test5.pgc" { ECPGconnect(__LINE__, 0, "connectdb" , NULL, NULL , "main", 0); } -#line 35 "test5.pgc" +#line 37 "test5.pgc" { ECPGdisconnect(__LINE__, "main");} -#line 36 "test5.pgc" +#line 38 "test5.pgc" { ECPGconnect(__LINE__, 0, "connectdb" , NULL, NULL , "main", 0); } -#line 38 "test5.pgc" +#line 40 "test5.pgc" { ECPGdisconnect(__LINE__, "main");} -#line 39 "test5.pgc" +#line 41 "test5.pgc" - { ECPGconnect(__LINE__, 0, "" , "connectdb" , NULL , "main", 0); } -#line 41 "test5.pgc" + { ECPGconnect(__LINE__, 0, "" , "connectdb" , "insecure" , "main", 0); } +#line 43 "test5.pgc" { ECPGdisconnect(__LINE__, "main");} -#line 42 "test5.pgc" +#line 44 "test5.pgc" - { ECPGconnect(__LINE__, 0, "connectdb" , "connectuser" , "connectdb" , "main", 0); } -#line 44 "test5.pgc" + { ECPGconnect(__LINE__, 0, "connectdb" , "connectuser" , "connectpw" , "main", 0); } +#line 46 "test5.pgc" { ECPGdisconnect(__LINE__, "main");} -#line 45 "test5.pgc" +#line 47 "test5.pgc" - { ECPGconnect(__LINE__, 0, "unix:postgresql://localhost/connectdb" , "connectuser" , NULL , "main", 0); } -#line 47 "test5.pgc" + { ECPGconnect(__LINE__, 0, "unix:postgresql://localhost/connectdb" , "connectuser" , "connectpw" , "main", 0); } +#line 49 "test5.pgc" { ECPGdisconnect(__LINE__, "main");} -#line 48 "test5.pgc" +#line 50 "test5.pgc" - { ECPGconnect(__LINE__, 0, "unix:postgresql://localhost/connectdb" , "connectuser" , NULL , "main", 0); } -#line 50 "test5.pgc" + { ECPGconnect(__LINE__, 0, "unix:postgresql://localhost/connectdb" , "connectuser" , "connectpw" , "main", 0); } +#line 52 "test5.pgc" { ECPGdisconnect(__LINE__, "main");} -#line 51 "test5.pgc" +#line 53 "test5.pgc" - { ECPGconnect(__LINE__, 0, "unix:postgresql://localhost/connectdb" , user , NULL , "main", 0); } -#line 53 "test5.pgc" + { ECPGconnect(__LINE__, 0, "unix:postgresql://localhost/connectdb" , user , "connectpw" , "main", 0); } +#line 55 "test5.pgc" { ECPGdisconnect(__LINE__, "main");} -#line 54 "test5.pgc" +#line 56 "test5.pgc" - { ECPGconnect(__LINE__, 0, "unix:postgresql://localhost/connectdb?connect_timeout=14 & client_encoding=latin1" , "connectuser" , NULL , "main", 0); } -#line 56 "test5.pgc" + { ECPGconnect(__LINE__, 0, "unix:postgresql://localhost/connectdb?connect_timeout=14 & client_encoding=latin1" , "connectuser" , "connectpw" , "main", 0); } +#line 58 "test5.pgc" { ECPGdisconnect(__LINE__, "main");} -#line 57 "test5.pgc" +#line 59 "test5.pgc" - { ECPGconnect(__LINE__, 0, "unix:postgresql://200.46.204.71/connectdb" , "connectuser" , NULL , "main", 0); } -#line 59 "test5.pgc" + { ECPGconnect(__LINE__, 0, "unix:postgresql://200.46.204.71/connectdb" , "connectuser" , "connectpw" , "main", 0); } +#line 61 "test5.pgc" { ECPGdisconnect(__LINE__, "main");} -#line 60 "test5.pgc" +#line 62 "test5.pgc" - { ECPGconnect(__LINE__, 0, "unix:postgresql://localhost/" , "connectdb" , NULL , "main", 0); } -#line 62 "test5.pgc" + { ECPGconnect(__LINE__, 0, "unix:postgresql://localhost/" , "connectdb" , "insecure" , "main", 0); } +#line 64 "test5.pgc" { ECPGdisconnect(__LINE__, "main");} -#line 63 "test5.pgc" +#line 65 "test5.pgc" /* connect twice */ { ECPGconnect(__LINE__, 0, "connectdb" , NULL, NULL , "main", 0); } -#line 66 "test5.pgc" +#line 68 "test5.pgc" { ECPGconnect(__LINE__, 0, "connectdb" , NULL, NULL , "main", 0); } -#line 67 "test5.pgc" +#line 69 "test5.pgc" { ECPGdisconnect(__LINE__, "main");} -#line 68 "test5.pgc" +#line 70 "test5.pgc" /* not connected */ { ECPGdisconnect(__LINE__, "nonexistant");} -#line 71 "test5.pgc" +#line 73 "test5.pgc" return (0); diff --git a/src/interfaces/ecpg/test/expected/connect-test5.stderr b/src/interfaces/ecpg/test/expected/connect-test5.stderr index 9c8dbf27b1..c85696061a 100644 --- a/src/interfaces/ecpg/test/expected/connect-test5.stderr +++ b/src/interfaces/ecpg/test/expected/connect-test5.stderr @@ -2,12 +2,20 @@ [NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ECPGconnect: opening database connectdb on port [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: ecpg_execute on line 24: query: alter user connectuser encrypted password 'connectpw'; with 0 parameter(s) on connection main +[NO_PID]: ecpg_execute on line 24: query: alter user connectdb encrypted password 'insecure'; with 0 parameter(s) on connection main [NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ecpg_execute on line 24: using PQexec [NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ecpg_process_output on line 24: OK: ALTER ROLE [NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 25: query: alter user connectuser encrypted password 'connectpw'; with 0 parameter(s) on connection main +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_execute on line 25: using PQexec +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ecpg_process_output on line 25: OK: ALTER ROLE +[NO_PID]: sqlca: code: 0, state: 00000 +[NO_PID]: ECPGtrans on line 26: action "commit"; connection "main" +[NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ecpg_finish: connection main closed [NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ECPGconnect: opening database connectdb on port @@ -50,11 +58,11 @@ [NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ecpg_finish: connection main closed [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: ECPGconnect: non-localhost access via sockets on line 59 +[NO_PID]: ECPGconnect: non-localhost access via sockets on line 61 [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: raising sqlcode -402 on line 59: could not connect to database "connectdb" on line 59 +[NO_PID]: raising sqlcode -402 on line 61: could not connect to database "connectdb" on line 61 [NO_PID]: sqlca: code: -402, state: 08001 -[NO_PID]: raising sqlcode -220 on line 60: connection "main" does not exist on line 60 +[NO_PID]: raising sqlcode -220 on line 62: connection "main" does not exist on line 62 [NO_PID]: sqlca: code: -220, state: 08003 [NO_PID]: ECPGconnect: opening database on port for user connectdb [NO_PID]: sqlca: code: 0, state: 00000 @@ -66,5 +74,5 @@ [NO_PID]: sqlca: code: 0, state: 00000 [NO_PID]: ecpg_finish: connection main closed [NO_PID]: sqlca: code: 0, state: 00000 -[NO_PID]: raising sqlcode -220 on line 71: connection "nonexistant" does not exist on line 71 +[NO_PID]: raising sqlcode -220 on line 73: connection "nonexistant" does not exist on line 73 [NO_PID]: sqlca: code: -220, state: 08003 -- 2.40.0