Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

User priviliges do not work as expected

Hi,
I have created a role and assigned prviliges and a user to that role. However, the user does not seem to get the role's priviliges:


CREATE ROLE TestAdmin;
GRANT ALL ON SCHEMA public TO TestAdmin;
GRANT All ON ALL TABLES IN SCHEMA public TO TestAdmin;
CREATE USER TestUser IDENTIFIED BY 'test_pw';
GRANT TestAdmin TO TestUser;


-> SELECT grantor, privileges_description, object_schema, object_name, grantee
FROM grants WHERE grantee='TestAdmin' AND object_schema= 'public':
grantor | privileges_description | object_schema | object_name | grantee
---------+------------------------------------------------------+---------------+------------------+-----------
dbadmin | INSERT, SELECT, UPDATE, DELETE, REFERENCES, TRUNCATE | public | Persons | TestAdmin


-> select user_name, all_roles, is_super_user from v_catalog.users where user_name = 'TestUser';
user_name | all_roles | is_super_user
-----------+-----------+---------------+
TestUser | TestAdmin | f


However, when I log in as user 'TestUser', I get
TestUser=> Select * from public.Persons;
ERROR 4367: Permission denied for relation Persons
Any idea, what the problem is?

Best Answer

  • Jim_KnicelyJim_Knicely Administrator
    edited April 29 Accepted Answer

    If you want the user to always use that role when they log in, you have to set the user's DEFAULT ROLE.

    ALTER USER TestUser DEFAULT ROLE TestAdmin;

    See:
    Enabling Roles Automatically

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.