S3 Storage Location for User Access
At the moment, I am utilizing Vertica in the enterprise mode. And I am copying data from S3 source (MinIO) into Vertica columnar tables.
Per instructions available at, https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/BulkLoadCOPY/SpecifyingCOPYFROMOptions.htm under the section 'Loading from an S3 Bucket', we should create a 'User' Storage Location for S3 bucket so that users without superuser privileges are able to copy data off of the bucket objects.
At the moment, I do not have a need for external tables (based on S3) or writing any data to S3 from Vertica. And I am running Vertica strictly in an enterprise mode. I want to be able to use a non superuser to invoke copy commands against any bucket from an S3 source such as MinIO. Is there any way to secure access to all S3 locations for users without superuser privileges globally in Vertica rather than doing this on a bucket by bucket basis?
Sandeep.
Answers
That instruction only applies if reading from the defined Eon mode communal storage bucket. To read from MinIO S3, set the following at the session (per user) or DB level (for all users):
awsauth = PIWHSNDGSHVRPIQ:339068001+e904816E02E5fe9103f8MQOEAEHFFVPKBAAL
awsendpoint = 10.20.30.40:9000
awsenablehttps = 0
Substitute keys with correct access to the bucket(s) (note, currently only one key pair can be set at a time, but can switch keys in session) and also set the MinIO server host and port. More details on exactly how to set these parameters as well as other S3 tuning options are at https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/S3Parameters.htm
(These largely apply to 9.x also but let us know if you run into issues)
I was also thinking that the instructions make sense if Vertica is deployed in EON mode. But in my case, I am deploying Vertica in enterprise mode.
I am already setting up parameters as you described above for copy commands to work. These instructions work well when we use a use with superuser privileges. However, they do not work when we use a user without superuser privileges. And in the later case they work only when we create a USER storage location and grant access to the less privileged user.
In my case, MinIO is tls enabled. So, I set awsenablehttps=1 and I also set the awscapath and awscapathfile parameters.
Without the need to grant user by user, to allow specific users to access data in S3 you can either assign users to a Role using GRANT (Role), OR if that is your intention, grant it to PUBLIC.