postgres table privileges do not allow user to create/select view

Question

I am managing a database that has a number of schemas. I am having some difficulty with setting privileges and would like to request some help.

I have the schema called schemaA and a group_role db_writer

I use the following sql:

GRANT USAGE ON SCHEMA schemaA TO db_writer;
GRANT UPDATE, INSERT, SELECT, DELETE ON ALL TABLES IN SCHEMA schemaA TO db_writer;

However, the db_writer is unable to create views. They are returned with a permission denied error. Also, when I create views, she is then unable to select them...and I have to set the priviliges again for that view.

It was my understanding the views were treated as tables with respect to privileges...and if one is granted certain permissions to all tables in a schema this would apply to views also.

What am I missing? Any advice appreciated.

Answer 1

The problem is USAGE does not allow users to create objects within the database.

Try

GRANT USAGE, CREATE ON SCHEMA schemaA TO db_writer 

Edit:

New objects will get default privileges, for the user to have those privileges for objects created in the future you can do it as:

ALTER DEFAULT PRIVILEGES IN SCHEMA schemaA GRANT UPDATE, INSERT, SELECT, DELETE ON TABLES TO db_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA schemaA GRANT SELECT ON TABLES TO db_reader;

Check this answer for more info