Moving a Table to a New Schema and Inherting the New Schema Privileges
You can move a table from one schema to another schema with a simple ALTER TABLE command. But, you might be surprised that the table will not inherit the new schema’s privileges even if the new schema was created using the DEFAULT INCLUDE PRIVILEGES directive. After moving the table you will need to manually alter the table telling Vertica you want it to inherit the new schema’s privileges.
Why? Because enabling inherited privileges with ALTER SCHEMA … DEFAULT INCLUDE PRIVILEGES only affects newly created tables and views. This setting does not affect already-existing tables and views.
The move operation is a meta-data/catalog update only. Vertica does not issue a CREATE TABLE statement in the target schema
dbadmin=> CREATE USER dennis; CREATE USER dbadmin=> CREATE SCHEMA s DEFAULT INCLUDE PRIVILEGES; CREATE SCHEMA dbadmin=> GRANT USAGE, CREATE, SELECT, INSERT ON SCHEMA s TO dennis; GRANT PRIVILEGE dbadmin=> CREATE TABLE s.t1 (c INT); -- This works and user DENNIS can read from it WARNING 6978: Table "t1" will include privileges from schema "s" CREATE TABLE dbadmin=> \c - dennis You are now connected as user "dennis". dbadmin=> SELECT * FROM s.t1; c --- (0 rows) dbadmin=> \c - dbadmin You are now connected as user "dbadmin". dbadmin=> CREATE TABLE public.t2 (c INT); CREATE TABLE dbadmin=> ALTER TABLE public.t2 SET SCHEMA s; -- This works, but user DENNIS cannot read from it ALTER TABLE dbadmin=> \c - dennis You are now connected as user "dennis". dbadmin=> SELECT * FROM s.t2; ERROR 4367: Permission denied for relation t2 dbadmin=> \c - dbadmin You are now connected as user "dbadmin". dbadmin=> ALTER TABLE s.t2 INCLUDE SCHEMA PRIVILEGES; -- Now DENNIS can from read it! ALTER TABLE dbadmin=> \c - dennis You are now connected as user "dennis". dbadmin=> SELECT * FROM s.t2; c --- (0 rows)