Skip to main content
Question

alter schema "public" default include privileges

  • August 6, 2021
  • 2 replies
  • 8 views

Pieter_Sheth-Vo
Forum|alt.badge.img+2

I'd like all existing users within schema public to access recently created tables.
Normally I just run "ALTER SCHEMA myschea DEFAULT INCLUDE PRIVILEGES;"
But this fails for schema public . Adding quotes doesn't seem to help.

https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/DBUsersAndPrivileges/Privileges/TableViewInheritance.htm

db=> alter schema "public" default include privileges;
ROLLBACK 4953:  The name "public" is a reserved name

2 replies

Jim_Knicely
Forum|alt.badge.img+2
  • Participating Frequently
  • August 6, 2021

Include the DB name... Like this:

dbadmin=> SELECT database_name FROM databases;
 database_name
---------------
 verticademos
(1 row)

dbadmin=> ALTER SCHEMA "verticademos.public" DEFAULT INCLUDE PRIVILEGES;
ALTER SCHEMA

dbadmin=>  SELECT name, defaultinheritprivileges FROM vs_schemata WHERE name = 'public';
  name  | defaultinheritprivileges
--------+--------------------------
 public | t
(1 row)

Pieter_Sheth-Vo
Forum|alt.badge.img+2
  • Author
  • Participating Frequently
  • August 6, 2021

perfect, thanks for the fast reply!