Session 1 as superuser:
CREATE USER u1;
CREATE SCHEMA u1 AUTHORIZATION u1;
GRANT ALL PRIVILEGES ON SCHEMA public TO u1;
Session 2 as u1:
SET search_path= "$user", public;
CREATE TABLE u1.x(t) AS SELECT 'data in u1.x';
CREATE TABLE public.x(t) AS SELECT 'data in public.x';
SELECT t FROM x; -- uses u1.x
Session 1 as superuser:
ALTER ROLE u1 RENAME TO u2;
Session 2 as u1:
SELECT CURRENT_USER; -- shows u2
SHOW search_path; -- $user, public
SELECT t FROM x; -- UNEXPECTED: uses u1.x still
SET search_path = public;
SET search_path TO default;
SELECT t FROM x; -- uses public.x
The fix is simple, attached.
--
Jeff Davis
PostgreSQL Contributor Team - AWS
