Projection Permission and Ownership

edited July 2019 in General Discussion

version: 9.0.x
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?


  • Options
    edited July 2019

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

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited July 2019

    Make sure the projection you are attempting to query is actually anchored to the table you think it is.

    dbadmin=> SELECT DISTINCT table_schema, table_name, t.owner_name table_owner_name, projection_schema, projection_name, p.owner_name projection_owner_name, anchor_table_name
    dbadmin->   FROM tables t
    dbadmin->   JOIN projections p
    dbadmin->     ON anchor_table_id = table_id
    dbadmin->  WHERE table_name = 'test'
    dbadmin->    AND table_schema = 'public';
     table_schema | table_name | table_owner_name | projection_schema | projection_name | projection_owner_name | anchor_table_name
     public       | test       | dbadmin          | public            | test_rep_pr     | dbadmin               | test
     public       | test       | dbadmin          | public            | test_p_b1       | dbadmin               | test
     public       | test       | dbadmin          | public            | test_p_b0       | dbadmin               | test
    (3 rows)

    If it is, do you actually see different owners/schemas between in the above query?

  • Options
    edited July 2019

    yes, I do see different owners.

    how do you fix an anchor table with projections from multiple owners?

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator

    Can you run the above query and show the results? Replace the table name and schema name of the table in question.

  • Options

    oh you're right. the projection's schema and the anchor table's schema can be different

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator

    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.

  • Options
    edited July 2019

    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

  • Options

    This will actually run:

    CREATE PROJECTION my_schema_01.projection_name
    (columnlist) as
    FROM my_schema_02.table_name

    version: 9.0.x

  • Options
    Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited July 2019

    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.


    dbadmin=> create schema a;
    dbadmin=> create schema b;
    dbadmin=> create table a.a (c INT);
    dbadmin=> create projection b.a_pr as select * from a.a;
    ROLLBACK 8139:  Projection must be created in the same schema as its anchor table
  • Options

    Vertica Analytic Database v9.0.1-5

  • Options
    Bryan_HBryan_H Vertica Employee Administrator
    edited July 2019

    This works because Vertica silently assigns "test_b" to schema "test_a". If you run:

    SELECT EXPORT_OBJECTS('','test_a.test_tbl_01');

    you'll find the projection is in the correct schema 'test_a'. I tried to assign a projection to an explicit schema and got:

    dbadmin=> create projection test_b.test_ba (myint) as select myint from test_a.test_a segmented by hash(myint) all nodes;
    ROLLBACK 8139: Projection must be created in the same schema as its anchor table

    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

  • Options
    edited July 2019

    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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file