What is the best way to handle automating storage location grants across all available hosts?

richleland

We have a need to automate the grant (storage location) across all nodes in our cluster after installation in some post-install scripts that we run. I know we need to use the ON 'node' clause of the GRANT statement, and I'm thinking we would want to query Vertica to get a list of nodes and then run a GRANT statement for each result. My question is - does this sound like a sane approach and if so, which table should I be querying to get that canonical list of all nodes?

Thanks!
- Rich

Prasanta_Pal

Are you looking for the below to get the Grant 'ON' node(?)

dbadmin=> select * from nodes;
       node_name       |      node_id      | node_state | node_address | export_address |                          catalog_path                          | is_ephemeral
-----------------------+-------------------+------------+--------------+----------------+----------------------------------------------------------------+--------------
 v_vertica613_node0001 | 45035996273704980 | UP         | 10.50.50.151 | 10.50.50.151   | /home/dbadmin/vertica613/v_vertica613_node0001_catalog/Catalog | f
 v_vertica613_node0002 | 45035996273719008 | UP         | 10.50.50.152 | 10.50.50.152   | /home/dbadmin/vertica613/v_vertica613_node0002_catalog/Catalog | f
 v_vertica613_node0003 | 45035996273719012 | UP         | 10.50.50.153 | 10.50.50.153   | /home/dbadmin/vertica613/v_vertica613_node0003_catalog/Catalog | f
 v_vertica613_node0004 | 45035996273719016 | UP         | 10.50.50.154 | 10.50.50.154   | /home/dbadmin/vertica613/v_vertica613_node0004_catalog/Catalog | f
 v_vertica613_node0005 | 45035996273719020 | UP         | 10.50.50.155 | 10.50.50.155   | /home/dbadmin/vertica613/v_vertica613_node0005_catalog/Catalog | f
 v_vertica613_node0006 | 45035996273719024 | UP         | 10.50.50.156 | 10.50.50.156   | /home/dbadmin/vertica613/v_vertica613_node0006_catalog/Catalog | f

richleland

Yeah exactly - I just wanted to make sure querying that table to get a list and then running the grants against that list sounded sane.

richleland

For anyone else that needs to do this, we handled this via a bash script:

NODES='/opt/vertica/bin/vsql -U dbadmin -w $PASSWD -c 'SELECT node_name FROM v_catalog.nodes;' -t'  for NODE in $NODES  do    /opt/vertica/bin/vsql -U dbadmin -w $PASSWD -c "GRANT ALL ON LOCATION '/path/to/log.file' ON $NODE TO user"  done