1

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.

4

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`;

More about the search_path in this related answer on SO:

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. Per documentation:

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).

More about default privileges and the basics:

  • @Ivan: I saw your rejected edit and added the missing key word SCHEMA. Still revoking from SCHEMA this_user, though. – Erwin Brandstetter Aug 15 '14 at 23:25
  • 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 '18 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 '18 at 11:03
  • @raarts: Yes. See: dba.stackexchange.com/a/71568/3684 – Erwin Brandstetter Nov 5 '18 at 12:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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