How to map object name in storage location grant to actual storage location
Hi,
If you will do grant on storage location, in grants table you will see in column object_name values like __location_3_shared:
select distinct object_name from grants where object_type='STORAGELOCATION';
__location_3_shared
__location_4_shared
System view storage_locations do not have any references to this object_name.
I have a hard time figuring out map between object_name in grants and actual storage location.
Can you give a hint, how to find what __location_3_shared is referring to?
Vertica v 24.3.
Thank you
Sergey
0
Best Answer
This discussion has been closed.
Answers
Thanks!
@SruthiA
It appears, Vertica is not assigning unique names to storage locations.
Here is you can see, location4 and location 5 are assigned to two different path each, with each path being separate S3 bucket.
oid name path
270 215 985 762 630 176 __location_0_shared s3://p111/
288 230 385 184 731 988 __location_2_shared s3://v222/
153 122 396 738 049 032 __location_4_shared s3://b333/
162 129 600 262 660 574 __location_4_shared s3://v444/
270 215 991 765 160 354 __location_5_shared s3://u555/
301 741 189 150 349 042 __location_5_shared s3://v666/
You have to join grants by oid in vs_storage_location or location_id in storage_locations.
It is kinda messy.
In case development will decide to fix it, can you also ask to add ability to create storage locations on two identically named buckets, each bucket in different S3 instance. It is not possible now.
This is query that show grants on s3 storage locations:
select
g.grantor,
g.privileges_description,
l.location_path,
g.object_type,
g.grantee,
g.object_schema,
g.object_namespace
from
grants g
join storage_locations l on g.object_id = l.location_id
where
1 = 1
and g.object_type = 'STORAGELOCATION'
and l.sharing_type = 'SHARED'
AND grantee != 'dbadmin'
order by
location_path,
grantee;
@Sergey_Cherepan_1 : Sure I will request for it.