CREATE ROLE dfm PASSWORD 'Gauss@123';
SELECT a.rolname, n.nspname FROM pg_authid a JOIN pg_namespace n ON (a.oid = n.nspowner AND a.rolname='dfm');
 rolname | nspname 
---------+---------
(0 rows)

DROP ROLE dfm;
CREATE USER dfm PASSWORD 'Gauss@123';
SELECT a.rolname, n.nspname FROM pg_authid a JOIN pg_namespace n ON (a.oid = n.nspowner AND a.rolname='dfm');
 rolname | nspname 
---------+---------
 dfm     | dfm
(1 row)

CREATE TABLESPACE dfm LOCATION '@testtablespace@';
SHOW search_path;
  search_path   
----------------
 "$user",public
(1 row)

SHOW default_tablespace;
 default_tablespace 
--------------------
 
(1 row)

SET behavior_compat_options = 'bind_schema_tablespace';
SHOW default_tablespace;
 default_tablespace 
--------------------
 
(1 row)

SET search_path='dfm';
SHOW search_path;
 search_path 
-------------
 dfm
(1 row)

SHOW default_tablespace;
 default_tablespace 
--------------------
 dfm
(1 row)

RESET behavior_compat_options;
RESET search_path;
RESET default_tablespace;
SHOW search_path;
  search_path   
----------------
 "$user",public
(1 row)

SHOW default_tablespace;
 default_tablespace 
--------------------
 
(1 row)

SET search_path='dfm';
SHOW search_path;
 search_path 
-------------
 dfm
(1 row)

SHOW default_tablespace;
 default_tablespace 
--------------------
 
(1 row)

DROP USER dfm;
SHOW search_path;
 search_path 
-------------
 dfm
(1 row)

SHOW default_tablespace;
 default_tablespace 
--------------------
 
(1 row)

SELECT nspname from pg_namespace where nspname='dfm';
 nspname 
---------
(0 rows)

RESET search_path;
RESET default_tablespace;
CREATE USER dfm PASSWORD 'Gauss@123';
SET SESSION AUTHORIZATION dfm PASSWORD 'Gauss@123';
SHOW search_path;
  search_path   
----------------
 "$user",public
(1 row)

SHOW default_tablespace;
 default_tablespace 
--------------------
 
(1 row)

DROP USER dfm;
ERROR:  Permission denied to drop role.
RESET SESSION AUTHORIZATION;
SET search_path='dfm';
--can not drop current schema
DROP USER dfm CASCADE;
ERROR:  fail to drop the current schema
SELECT nspname from pg_namespace where nspname='dfm';
 nspname 
---------
 dfm
(1 row)

DROP TABLESPACE dfm;