Postgresql – Permission for sequence in another schema

permissionspostgresqlpostgresql-9.3schemasequence

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.

Best Answer

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.

Related Solutions

Sql-server – Resetting a SQL Server 2012 sequence

Try

ALTER SEQUENCE foo.fee
RESTART

Or:

ALTER SEQUENCE foo.fee
RESTART WITH 1

http://msdn.microsoft.com/en-us/library/ff878572.aspx

Sql-server – Granting permissions only on a set list of objects

I've done this a couple of weeks ago - Created LOGIN, ROLE and USER. Added USER to ROLE and granted explicit permissions to the ROLE itself. The advantage in my opinion is, that you can add further users later on without the struggle to grant the permissions, again.

First I created the login:

IF NOT EXISTS 
( 
  SELECT 
    "name" 
  FROM 
    "master"."dbo"."syslogins" 
  WHERE 
    "name" = 'your_login'
)
BEGIN 
  CREATE LOGIN your_login WITH PASSWORD = 'your_password', CHECK_POLICY = OFF; 
END

I've added the CHECK_POLICY param because things are created on user interaction and I'm not able to see, if policies are activated server-sided.

Followed by the user:

IF NOT EXISTS 
( 
  SELECT 
    1 
  FROM 
    "sys"."database_principals" 
  WHERE 
    "name" = N'your_username'
)
BEGIN 
  CREATE USER your_username FOR LOGIN your_login;
END

And the role third:

IF NOT EXISTS 
( 
  SELECT 
    1 
  FROM 
    "sys"."database_principals" 
  WHERE
    "name" = N'your_role_name'  
)
BEGIN
  CREATE ROLE "your_role_name"; 
END

Finally I added the user to the created role:

EXECUTE sp_addrolemember N'your_role_name', N'your_username';

After that, permissions can be set for each type of object, eg

GRANT EXECUTE ON "dbo"."your_sproc" TO your_role_name;

You don't have to add denydatareader or else, if you explicitly grant permissions to the specific objects. Beware that an for example INSERT permission doesn't include the SELECT permission on the same object. ( at least in my case it didn't ;) )

If you have problems executing or selecting from your UDF, check if you have granted all permissions on the referenced objects in the UDF itself!

Update after Cobus' update in OP:

I've executed the queries you provided based on my initial answer, results following:

select * from dbo.testtable

-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

update dbo.testtable set id = 4 where id = 2

-- The SELECT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'. -- The UPDATE permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

update dbo.testtable set id = 4

-- The INSERT permission was denied on the object 'testtable', database 'xyz', schema 'dbo'.

insert into dbo.testtable (id) values (5)