Preserving Objects Owned by a Dropped User

Jim_Knicely

When you drop a user with the CASCADE option, all objects owned by that user a lost forever! If you’d prefer to save those objects, you first set the GlobalHeirUserName security parameter to a user who will inherit objects after their owners are dropped. This setting ensures preservation of data otherwise lost.

Example:

dbadmin=> CREATE USER previous;
CREATE USER

dbadmin=> CREATE USER current;
CREATE USER

dbadmin=> GRANT CREATE ON SCHEMA public TO previous, current;
GRANT PRIVILEGE

dbadmin=> \c - previous
You are now connected as user "previous".

dbadmin=> CREATE TABLE public.a_table (c INT);
CREATE TABLE

dbadmin=> \c - dbadmin
You are now connected as user "dbadmin".

dbadmin=> SELECT table_schema, owner_name
dbadmin->   FROM tables
dbadmin->  WHERE table_name = 'a_table';
table_schema | owner_name
--------------+------------
public       | previous
(1 row)

dbadmin=> ALTER DATABASE test_db SET GlobalHeirUserName = 'current';
ALTER DATABASE

dbadmin=> DROP USER previous CASCADE;
DROP USER

dbadmin=> SELECT table_schema, owner_name
dbadmin->   FROM tables
dbadmin->  WHERE table_name = 'a_table';
table_schema | owner_name
--------------+------------
public       | current
(1 row)

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/SecurityParameters.htm

Have fun!