How to map object name in storage location grant to actual storage location

Sergey_Cherepan_1 · January 30

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

SruthiA · January 30

please check vs_storage_locations table.

Sergey_Cherepan_1 · January 31

Thanks!

Sergey_Cherepan_1 · February 21

@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.

Sergey_Cherepan_1 · February 21

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;

Chrislord · February 21

Hi

SruthiA · February 21

@Sergey_Cherepan_1 : Sure I will request for it.

We're Moving!

Create My New Community Account Now

How to map object name in storage location grant to actual storage location

Best Answer

Answers