Grant usage partially on schema to user on Postgres

Question

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.

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:

• How does the search_path influence identifier resolution and the "current schema"

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:

• Explicitly granting permissions to update the sequence for a serial column necessary?

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:

• Why can a new user select from any table?