Error on setting the Storage Policy

Krishna_Murthy_1

Getting this error when tried to set the storage policy on a location [Vertica][VJDBC](5662) ERROR: Storage tier TBSPOOL1 has not been found on all nodes [SQL State=HY000, DB Errorcode=5662] and these are the steps followed as in the Vertica document(s) 1. SELECT ADD_LOCATION('/data10/xtremdb/pool_user_location', '', 'USER','TBSPOOL1'); 2. GRANT ALL ON LOCATION '/data10/xtremdb/pool_user_location' TO tuser 3. select SET_OBJECT_STORAGE_POLICY ( 'tuser', 'TBSPOOL1' ); Am I missing something here?. Thanks Krishna

wwong2

Hi Krishna - 

The set_object_storage_policy only works with usage DATA type only. Anything written to the new object (schema, table, etc) will be written to the new storage location associated with that label.  The usage USER type is used when you need to create a storage location for non-dbadmin users so they can import from or export to the storage location.
 

Please refer to doc link for set_object_storage_policy, https://my.vertica.com/docs/6.1.x/HTML/index.htm#19324.htm 
As mentioned, in link, the object_name which is the first argument for set_object_name, Identifies the database object assigned to a labeled storage location. The object_name can resolve to a database, schema, or table.

Regards
Wayne

Nimmi_gupta

Hi Krisha, You can also follow following examples. The purpose of add_location for user is so the user can get to data files, it is different than 'DATA' or 'TEMP'. here is an example from the doc. In the following series of commands, a superuser creates a new storage location and grants it to user Bob: dbadmin=> SELECT ADD_LOCATION('/home/dbadmin/UserStorage/BobStore', 'v_mcdb_node0007', 'USER'); ADD_LOCATION ------------------------------------------- /home/dbadmin/UserStorage/BobStore' added. (1 row) Now the superuser grants Bob READ/WRITE permissions on the /BobStore location: dbadmin=> GRANT ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' TO Bob; GRANT PRIVILEGE Revoke all storage location privileges from Bob: dbadmin=> REVOKE ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' FROM Bob; REVOKE PRIVILEGE Grant privileges to Bob on the BobStore location again, this time specifying the node: dbadmin=> GRANT ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' ON v_mcdb_node0007 TO Bob; GRANT PRIVILEGE Revoke all storage location privileges from Bob: dbadmin=> REVOKE ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' ON v_mcdb_node0007 FROM Bob; REVOKE PRIVILEGE Best, Nimmi

Krishna_Murthy_1

Hi Wayne, No where in any of the Vertica documents (Admin or SQL guides) mentioned that the set_object_storage_policy only works with usage DATA type only. Anyhow it works with DATA usage but the again grant cannot be executed for DATA usage location to a User. Is this the expected behavior?. Thanks Krishna

Krishna_Murthy_1

Hi Nimmi, Thanks for the example and it works by creating location with usage as USER and granting that location to a user. Thanks Krishna

[Deleted User]

Hello Krishna: I've added several updates to the docs noting that you cannot create a storage policy on a USER type. Should be seen in next doc release in these places: ADD_LOCATION SET_OBJECT_STORAGE_POLICY Administator's Guide: Adding Storage Locations Creating Storage Policies Thanks for helping us clarify the docs. Kanti

Krishna_Murthy_1

Thanks Kanti. That would help others too in the future. Thanks Krishna