User priviliges do not work as expected

joergschaber

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?

Jim_Knicely

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