Adjust test case added by commit 6136e94dc.

Per project policy, transient roles created by regression test cases
should be named "regress_something", to reduce the risks of running
such cases against installed servers.  And no such role should ever
be left behind after running a test.

Discussion: https://postgr.es/m/11297.1576868677@sss.pgh.pa.us

2 files changed, 19 insertions, 10 deletions

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index ea67ae31d08..c915885504d 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8813,9 +8813,9 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700
 -- access rights and superuser
 -- ===================================================================
 -- Non-superuser cannot create a FDW without a password in the connstr
-CREATE ROLE nosuper NOSUPERUSER;
-GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO nosuper;
-SET ROLE nosuper;
+CREATE ROLE regress_nosuper NOSUPERUSER;
+GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO regress_nosuper;
+SET ROLE regress_nosuper;
 SHOW is_superuser;
  is_superuser 
 --------------
@@ -8877,8 +8877,8 @@ DETAIL:  Non-superuser cannot connect if the server does not request a password.
 HINT:  Target server's authentication method must be changed or password_required=false set in the user mapping attributes.
 RESET ROLE;
 -- But the superuser can
-ALTER USER MAPPING FOR nosuper SERVER loopback_nopw OPTIONS (ADD password_required 'false');
-SET ROLE nosuper;
+ALTER USER MAPPING FOR regress_nosuper SERVER loopback_nopw OPTIONS (ADD password_required 'false');
+SET ROLE regress_nosuper;
 -- Should finally work now
 SELECT * FROM ft1_nopw LIMIT 1;
   c1  | c2 | c3 | c4 | c5 | c6 |     c7     | c8 
@@ -8903,6 +8903,10 @@ SELECT * FROM ft1_nopw LIMIT 1;
  1111 |  2 |    |    |    |    | ft1        | 
 (1 row)
 
+-- cleanup
+DROP USER MAPPING FOR public SERVER loopback_nopw;
+DROP OWNED BY regress_nosuper;
+DROP ROLE regress_nosuper;
 -- Clean-up
 RESET enable_partitionwise_aggregate;
 -- Two-phase transactions are not supported.
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index c525897a78a..4f29e7cb44f 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2498,11 +2498,11 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700
 -- ===================================================================
 
 -- Non-superuser cannot create a FDW without a password in the connstr
-CREATE ROLE nosuper NOSUPERUSER;
+CREATE ROLE regress_nosuper NOSUPERUSER;
 
-GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO nosuper;
+GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO regress_nosuper;
 
-SET ROLE nosuper;
+SET ROLE regress_nosuper;
 
 SHOW is_superuser;
 
@@ -2559,9 +2559,9 @@ SELECT * FROM ft1_nopw LIMIT 1;
 RESET ROLE;
 
 -- But the superuser can
-ALTER USER MAPPING FOR nosuper SERVER loopback_nopw OPTIONS (ADD password_required 'false');
+ALTER USER MAPPING FOR regress_nosuper SERVER loopback_nopw OPTIONS (ADD password_required 'false');
 
-SET ROLE nosuper;
+SET ROLE regress_nosuper;
 
 -- Should finally work now
 SELECT * FROM ft1_nopw LIMIT 1;
@@ -2580,6 +2580,11 @@ RESET ROLE;
 -- mapping option, but will work because the current user is a superuser.
 SELECT * FROM ft1_nopw LIMIT 1;
 
+-- cleanup
+DROP USER MAPPING FOR public SERVER loopback_nopw;
+DROP OWNED BY regress_nosuper;
+DROP ROLE regress_nosuper;
+
 -- Clean-up
 RESET enable_partitionwise_aggregate;