Permission for sequence in another schema

Question

Postgres 9.3 Debian 7.0

I created a specific schema for a specific user and created a view in this schema for this user, so it's the only table he knows that exists. The problem is that this same user needs usage on the sequence of the primary key of this table, but it says "ERROR: permission denied for sequence"

The original table and its sequence belongs to schema A. This users's schema B has an insert-able view of this table T. I cannot grant usage on schema A for this user, otherwise he will be able to see the names and definition of all my tables.

The question is: Is there some way to create some kind of view for this sequence so he can call nextval() and currval()? The goal is making this sequence usable for this restricted user without giving him access to the main schema where the sequence actually belongs.

Answer 1

This can be done.
The column default for your serial primary key is typically defined as:

ALTER TABLE schema_a.tbl ALTER COLUMN tbl_id
SET DEFAULT nextval('schema_a.tbl_tbl_id_seq'::regclass);

Two options:

1. Move sequence (my preference)

to the public schema - or any schema with sufficient privileges:

GRANT USAGE ON SCHEMA public TO public; -- or: my_group

Moving the sequence to another schema is easy:

ALTER SEQUENCE schema_a.tbl_tbl_id_seq SET SCHEMA public;

Now you can grant USAGE:

GRANT USAGE ON SEQUENCE public.tbl_tbl_id_seq TO public; -- or: my_group

That preserves all references (incl. column defaults).

2. Function wrapper with SECURITY DEFINER

If you cannot move the sequence for some reason (can't think of one), you can alternatively wrap access to it in functions of your own with SECURITY DEFINER. Again, create those functions in the public schema (or any schema with sufficient privileges for your users):

CREATE OR REPLACE FUNCTION public.next_tbl_tbl_id_seq()
  RETURNS bigint AS
$func$
SELECT nextval('schema_a.tbl_tbl_id_seq'::regclass)
$func$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = schema_a, pg_temp;
ALTER FUNCTION shop.f_deswap_name(text) OWNER TO owning_role;

Where owning_role is the owner of the sequence or any role with sufficient privileges. Similar function for currval() ...

Be sure to read the chapter "Writing SECURITY DEFINER Functions Safely" in the manual.