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.

    Copyright 2020. All Rights Reserved