The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Projection Permission and Ownership
edited July 2019 in General Discussion
How come there is no ALTER PROJECTION... OWNER TO... statement available, but I get a permission denied when trying to read directly from a projection even if I have proper read access to the anchor table?
"Instead, the privileges to create, access, or alter a projection are based on the anchor tables that the projection references, as well as the schemas that contain them."
Sounds like this is a bug.
Make sure the projection you are attempting to query is actually anchored to the table you think it is.
If it is, do you actually see different owners/schemas between in the above query?
yes, I do see different owners.
how do you fix an anchor table with projections from multiple owners?
Can you run the above query and show the results? Replace the table name and schema name of the table in question.
oh you're right. the projection's schema and the anchor table's schema can be different
No. The projection and its anchor table must be in the same schema.
If you are seeing otherwise, then yeah, you are hitting a bug. That's why you need to run the query I posted to check.
Yes, I'm able to create a projection where the projection schema <> anchor table schema. In the query you posted, I didn't see the projection I was looking for
This will actually run:
CREATE PROJECTION my_schema_01.projection_name
What is the exact version of Vertica you are using? That is, what is the x? the requirement for a projection schema to match anchor table schema has been around since 8.1.1.
Vertica Analytic Database v9.0.1-5
This works because Vertica silently assigns "test_b" to schema "test_a". If you run:
you'll find the projection is in the correct schema 'test_a'. I tried to assign a projection to an explicit schema and got:
using Vertica 9.2.1-1 here. For the permission issue, you must also have GRANT USAGE on the schema to access a table and presumably a projection - do you have access to both schema? See GRANT (Table) documentation for schema USAGE requirement: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/GRANT/GRANTTable.htm
I see what happened:
The table was copied using the COPY_TABLE function where table owner ( User A ) and user ( User B ) who performed the copy were different. When this was done, the projections were copied as well but the ownership of the projections did not change.
User B created additional projections for the table resulting to multiple owners of the projections.
As an improvement, maybe the COPY_TABLE process should also update the owners of the projections.