How to Create Vertica Schema on the Fly

In this short article i will go over the steps used to create a simple Vertica UDP (user defined procedure) that will help us create a new schema and also create the database roles in a single SQL command. This will help you work faster and spend less time writing simple SQL code. Normally when i create schema i make sure that the default role also come with it. My Default roles:

  • this is optional, i only do this to avoid the headache of not being able to see who has access to what.
  • i never give direct access to a user, always prefer role to propagate.
  • the main reason i use this approach is to avoid clear text password and this is a serious security issue.
  • the file can only be accesed¬†by the dbadmin user.
  • #!/bin/bash
    
    
    username=dbadmin
    password=UltraSecretPassowrd
    export username
    export password
    The add_schema.sh content:
    • the location of the add_schema.sh should be inside the default procedure folder of your catalog.
    #!/bin/bash
    
    . /home/dbadmin/.profile
    
    /opt/vertica/bin/vsql -U $username -w $password -t -c"
    create schema $1;
    --add inheritance
    ALTER SCHEMA $1 DEFAULT INCLUDE PRIVILEGES;
    
    --create roles
    create role $1_datareader;
    create role $1_datawriter;
    create role $1_schemausage;
    
    --grant specific access to role
    grant select on schema $1 to $1_datareader;
    grant usage on schema $1 to  $1_schemausage;
    grant insert,update,delete on schema $1 to $1_datawriter;
    "
    • the script will user the username and password that is passed by the .profile file and will execute the SQL script attaching the parameter value we will pass to the Procedure at execution time.
    Make sure you run the following command when you create the add_schema.sh script:
    chmod 4750 add_schema.sh
    Create the UDP using the admintools
    . /home/dbadmin/.profile
    admintools -t install_procedure -f /vertica_catalog/catalog/dbname/procedures/add_schema.sh -d $username -p $password
    Create the Procedure in your database
    • i normally have specific schema for my own use that i call dba, here i store all my procedures and all other tables & views used to manage the database.
    . /home/dbadmin/.profile
    /opt/vertica/bin/vsql -U $username -w $password -c "CREATE PROCEDURE dba.add_schema(schema_name varchar) AS 'add_schema.sh' LANGUAGE 'external' USER 'dbadmin';"
    • i called my procedure add_schema, i am trying to make it as obvious as possible.
    After the procedure add_schema was created try to see if it works.
    • See if schema was created.
    dbadmin= select schema_name,schema_owner from schemata where schema_name ilike 'test_schema%';
     schema_name | schema_owner
    -------------+--------------
     test_schema | dbadmin
    (1 row)
    • See if the roles have been created.
    dbadmin= select * from roles where name ilike 'test_schema%';
          role_id      |          name           | assigned_roles
    -------------------+-------------------------+----------------
    
     45036003061793588 | test_schema_datareader  |
     45036003061793590 | test_schema_datawriter  |
     45036003061793592 | test_schema_schemausage |
    (3 rows)
    • See if the roles have the right grants on the new schema.
    dbadmin= select grantor,privileges_description,object_name,object_type,grantee
     from grants where grantee in (select name from roles where name ilike 'test_schema%');
     grantor | privileges_description | object_name | object_type |         grantee
    ---------+------------------------+-------------+-------------+-------------------------
    
     dbadmin | SELECT                 | test_schema | SCHEMA      | test_schema_datareader
     dbadmin | USAGE                  | test_schema | SCHEMA      | test_schema_schemausage
     dbadmin | INSERT, UPDATE, DELETE | test_schema | SCHEMA      | test_schema_datawriter
    (3 rows)
    Ok, that looks good, so the procedure work well. Feel free to use it and make it better.