Trying to grant account access to insert data on a view

Question

I have been trying to give a specific user account insert access to a view (So that it can only create records). However, the whole process has been very confusing and difficult. I am looking for what a proper set of steps is and how they should be executed.

Postgresql Version: 2.5.2 (Mac)

Data Structure:

• database1
  • schema1

    • table1

      • id (Serial)
      • name (Varchar)
      • location (Varchar)

    • view1

      SELECT * FROM table1 name, location (VALUES);
      
      

    • view2

      SELECT name, location FROM table1 WHERE [Constraints];
      
      

    • view3

      SELECT name, location FROM table1 WHERE [Constraints];
      
      

    • view4

      SELECT asterisk FROM table1 WHERE [Constraints];
      

Users: postgres, insertrecords, selectrecords, updaterecords, deleterecords

I want 'insertrecords' to only be able to insert into name and location, 'selectrecords' to be able to select id, name, location, 'updaterecords' to be able to update name, location, and 'deleterecords' to be able to delte id, name, location.

However I am having trouble with the grant options such as access to schemas, and tables when I only want views. I am very confused in what to do, and the formatting of the syntax, I have tried

GRANT INSERT ON view2 IN SCHEMA schema1 TO insertrecords;

But I keep getting a syntax error at the word IN.

Answer 1

IN SCHEMA can only be specified with ALL TABLES. You have to add the schema to the view name:

GRANT INSERT ON schema1.view2 TO insertrecords;