Preserving Objects Owned by a Dropped User: Quick Tip

Jim Knicely authored this tip.

When you drop a user with the CASCADE option, all objects owned by that user are 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/latest/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/SecurityParameters.htm

Have fun!