2

Here I have a view in public schema referring to the table in private schema:

CREATE OR REPLACE VIEW public.products AS 
 SELECT products.id, products.product_id, products.title, products.company_id
   FROM private.products;

ALTER TABLE products
  OWNER TO postgres;
GRANT ALL ON TABLE public.products TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE products TO viewers;


CREATE OR REPLACE RULE "_INSERT" AS
    ON INSERT TO products DO INSTEAD  INSERT INTO private.products (product_id, title, company_id) 
  VALUES (new.product_id, new.title, new.company_id);

the whole thing works fine, but I want the client to only be able to operate in public schema and not in private. Is there a way to setup such permissions?

1 Answer 1

4

Yes you can. Per documentation:

Note that the user performing the insert, update or delete on the view must have the corresponding insert, update or delete privilege on the view. In addition the view's owner must have the relevant privileges on the underlying base relations, but the user performing the update does not need any permissions on the underlying base relations.

2
  • I get a "ERROR: permission denied for sequence <sequance name>" how do I fix that
    – Ben
    Dec 8, 2014 at 8:49
  • 1
    GRANT USAGE ON ALL SEQUENCES IN SCHEMA private TO viewers; fixed it
    – Ben
    Dec 8, 2014 at 8:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.