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

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?

- Rich


  • Options
    Prasanta_PalPrasanta_Pal - Select Field - Employee
    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         | |   | /home/dbadmin/vertica613/v_vertica613_node0001_catalog/Catalog | f
     v_vertica613_node0002 | 45035996273719008 | UP         | |   | /home/dbadmin/vertica613/v_vertica613_node0002_catalog/Catalog | f
     v_vertica613_node0003 | 45035996273719012 | UP         | |   | /home/dbadmin/vertica613/v_vertica613_node0003_catalog/Catalog | f
     v_vertica613_node0004 | 45035996273719016 | UP         | |   | /home/dbadmin/vertica613/v_vertica613_node0004_catalog/Catalog | f
     v_vertica613_node0005 | 45035996273719020 | UP         | |   | /home/dbadmin/vertica613/v_vertica613_node0005_catalog/Catalog | f
     v_vertica613_node0006 | 45035996273719024 | UP         | |   | /home/dbadmin/vertica613/v_vertica613_node0006_catalog/Catalog | f

  • Options
    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.
  • Options
    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file