I granted INSERT in a specific table for one user. The problem is that I need to also grant USAGE in the schema for this same user, but granting USAGE in schema also turn all relations in that schema visible for that user.

I need that this specific user can only INSERT in this specific table and is unable of viewing other relations (tables, sequences, etc) in the same schema. By 'unable of viewing' I mean not being able of seeing that these relations exists.

Being even more clear, in the vision of this user, the schema has only that table inside and nothing more.

1 Answer 1


I suggest you create a dedicated schema for this user (if it does not exist already), typically with the same name as the name of the role.

CREATE SCHEMA this_user;
REVOKE ALL ON SCHEMA this_user FROM public;
GRANT USAGE ON SCHEMA this_user TO this_user;

The default search_path in typical Postgres installations is "$user",public. So the new schema is automatically the "current" schema for this_user. If your installation is different, consider setting it explicitly:

ALTER ROLE this_user SET search_path = "$user",public;

About the search_path:

Create a VIEW in this schema:

CREATE VIEW this_user.the_table AS
TABLE other_schema.the_table;  -- shorthand for "SELECT * FROM"

GRANT SELECT, INSERT ON this_user.the_table TO this_user;

You may need privileges on the SEQUENCE additionally, if a serial column is involved. See:

The same is not necessary for IDENTITY columns in Postgres 10 or later.

Since Postgres 9.3 simple views like the above are automatically updatable. The manual:

Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements to be used on the view in the same way as on a regular table. A view is automatically updatable if it satisfies all of the following conditions:

Then REVOKE all privileges from public, that should stay hidden from the public (including this_user).

About default privileges and basics:

  • I did this exact thing (pg10) and it mostly works but when I insert into the view I get (insufficient_privilege): permission denied for sequence users_id_seq
    – raarts
    Nov 4, 2018 at 10:51
  • I fixed it using GRANT USAGE ON public.users_id_seq TO this_user;, but is that the right way to do it?
    – raarts
    Nov 4, 2018 at 11:03
  • @raarts: Yes. See: dba.stackexchange.com/a/71568/3684 Nov 5, 2018 at 12:27

Your Answer

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

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