First we need to create the a readonly role.
- Launch vsql as the database admin user (example: dradmin)
cd /opt/vertica/bin
Log into vsql
./vsql -Udradmin -wdbpass
- Run the following queries to create the role and give it permissions.
Create a Read Only role:
CREATE ROLE RO_role;
Grant usage on a particular schema to the Read only role:
GRANT USAGE ON SCHEMA dauser TO RO_role;
Grant select privileges on some or all tables of a particular schema to the Read only role:
GRANT SELECT ON ALL TABLES IN SCHEMA dauser TO RO_role;
- After the role has been created, use the following queries to create a new readonly user and give it access to RO_role.
CREATE USER <readonly username> IDENTIFIED BY '<password>';
GRANT RO_Role TO <readonly username>;
ALTER USER <readonly username> DEFAULT ROLE RO_role;
ALTER USER <readonly username> SEARCH_PATH <dauser schema name>;