Postgresql – Restricting Insert and Update to functions

functionspostgresqlusers

We have a function in postgresql that inserts some rows in a table and updates some other rows based on an input provided by the user. Is it possible to somehow execute the function without giving write access to the user for those tables which the functions inserts and updates?
So i basically want the user to be able to execute the function and for the changes to happen through the function, but the user should not be able to insert or update the table on his own outside of the function.

TIA.

Best Answer

Yes this is possible.

You (obviously) need two database users for this:

one who is allowed to call the function
one who ones the table and is allowed to directly modify it.

Then revoke the update, insert and delete privilege from the restricted user.

Then create the function with the privileged user as the owner and use the attribute SECURITY DEFINER when creating it.

Thus the restricted user cannot change the table, but when he calls the function, the function runs with the privileges of the other user and thus can modify the table.

Something like this:

Logged in as the privileged user:

create user restricted_user password 'hidden';
create table foo (id serial not null primary key, some_info text);
grant select on foo to restricted_user;
create or replace function public.insert_foo(p_info text)
  RETURNS void
  security definer  -- << this is important
  LANGUAGE sql
AS
$body$
   insert into public.foo (some_info) values (p_info);
$body$;

grant execute on function public.insert_foo(text) to restricted_user;

Now you log in as the restricted_user:

insert into foo (some_info) values ('foo');
==> ERROR: permission denied for relation foo

select insert_foo('foo');
==> one row inserted

Related Solutions

Sql-server – Granting Execute Permissions on Stored Procedures, Functions and Views

It is true that you cannot grant EXEC permissions on a function that returns a table. This type of function is effectively more of a view than a function. You need to grant SELECT instead, e.g.:

GRANT SELECT ON dbo.Table_Valued_Function TO [testuser];

So your script would look more like this (sorry, but I absolutely loathe INFORMATION_SCHEMA and much prefer to use the catalog views, which also don't need functions like OBJECTPROPERTY):

DECLARE 
    @sql      NVARCHAR(MAX) = N'',
    @username VARCHAR(255)  = 'testuser';

SELECT @sql += CHAR(13) + CHAR(10) + N'GRANT ' + CASE 
    WHEN type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION'
      OR type_desc = 'VIEW'
    THEN ' SELECT ' ELSE ' EXEC ' END 
    + ' ON ' + QUOTENAME(SCHEMA_NAME([schema_id])) 
    + '.' + QUOTENAME(name) 
    + ' TO ' + @username + ';'
 FROM sys.all_objects
WHERE is_ms_shipped = 0 AND
( 
    type_desc LIKE '%PROCEDURE' 
    OR type_desc LIKE '%FUNCTION'
    OR type_desc = 'VIEW'
);

PRINT @sql;
-- EXEC sp_executesql @sql;

Now you can grant EXEC on a schema, and always create these procedures in that schema (actually one of the purposes of schemas!), which @jgardner04 already suggested, however in order for this solution to apply to table-valued functions as well, you'd also have to grant SELECT. Which is okay if you're not storing any data in tables in that schema (or at least that you want to hide from them), but it will apply to any tables and views as well, which might not be your intention.

Another idea (e.g. if you can't, or don't want to, use schemas) is to write a DDL Trigger that captures the CREATE_PROCEDURE, CREATE_FUNCTION and CREATE_VIEW events, and grants permissions to a user (or a set of users, if you want to store them in a table):

CREATE TRIGGER ApplyPermissionsToAllProceduressAndFunctions -- be more creative!
    ON DATABASE FOR CREATE_PROCEDURE, CREATE_FUNCTION, CREATE_VIEW
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @sql       NVARCHAR(MAX),
        @EventData XML = EVENTDATA();

    ;WITH x ( sch, obj ) 
    AS
    (
        SELECT
          @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'), 
          @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)')
    )
    SELECT @sql = N'GRANT ' + CASE 
      WHEN o.type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION' 
        OR o.type_desc = 'VIEW'
      THEN ' SELECT ' 
      ELSE ' EXEC ' END 
        + ' ON ' + QUOTENAME(x.sch) 
        + '.' + QUOTENAME(x.obj) 
        + ' TO testuser;' -- hard-code this, use a variable, or store in a table
    FROM x
    INNER JOIN sys.objects AS o
    ON o.[object_id] = OBJECT_ID(QUOTENAME(x.sch) + '.' + QUOTENAME(x.obj));

    EXEC sp_executesql @sql;
END
GO

The drawback I find with DDL Triggers is that you can quickly forget that they're there. So a year down the road when you decide to stop granting these permissions to all new objects, it might take a while to troubleshoot why it's still happening. At my last job we logged all actions invoked by DDL triggers to a central "event log" of sorts, and that was our go-to place for tracking down any actions that happened on the server that nobody seems to remember (and it was a DDL trigger about half the time). So you may consider adding some additional logic that will help with that.

EDIT

Adding code for schema-based, and I'll mention again that this will grant permissions on any procedures, functions and tables created in the foo schema.

CREATE SCHEMA foo;
GRANT EXEC, SELECT ON SCHEMA::foo TO testuser;

Now if you create the following procedure, testuser will be able to execute:

CREATE PROCEDURE foo.proc1
AS 
BEGIN
    SET NOCOUNT ON;
    SELECT 1;
END
GO

Sql-server – Running functions in parallel

Using T-SQL scalar functions will frequently lead to performance problems* because SQL Server makes a separate function call (using a whole new T-SQL context) for each row. In addition, parallel execution is disallowed for the whole query.

T-SQL scalar functions can also make it difficult to troubleshoot performance problems (whether those problems are caused by the function or not). The function appears as a 'black box' to the query optimizer: it is assigned a fixed low estimated cost, regardless of the actual content of the function.

See this and this for more on the pitfalls of scalar functions.

You will probably be better off using the new TRY_CONVERT function in SQL Server 2012:

SELECT
    InsertID,
    dt1 = TRY_CONVERT(smalldatetime, MangledDateTime1),
    dt2 = TRY_CONVERT(smalldatetime, MangledDateTime2),
    dt3 = TRY_CONVERT(smalldatetime, MangledDateTime3)
FROM dbo.RawData;

╔══════════╦═════════════════════╦═════════════════════╦═════════════════════╗
║ InsertID ║         dt1         ║         dt2         ║         dt3         ║
╠══════════╬═════════════════════╬═════════════════════╬═════════════════════╣
║        1 ║ 2000-10-10 00:00:00 ║ NULL                ║ NULL                ║
║        1 ║ NULL                ║ 2013-06-30 00:00:00 ║ NULL                ║
║        1 ║ NULL                ║ NULL                ║ 2013-06-30 00:00:00 ║
╚══════════╩═════════════════════╩═════════════════════╩═════════════════════╝

After the edit to the question

I see the function contains some specific logic. You could still look to use TRY_CONVERT as part of that, but you should definitely convert the scalar function to an in-line function. In-line functions (RETURNS TABLE) use a single SELECT statement and are expanded into the calling query and fully optimized in much the same way views are. It can be helpful to think of in-line functions as parameterized views.

For example, an approximate translation of the scalar function to an in-line version is:

CREATE FUNCTION dbo.CleanDate
    (@UnformattedString  varchar(12))
RETURNS TABLE
AS RETURN
SELECT Result =
    -- Successful conversion or NULL after
    -- workarounds applied in CROSS APPLY
    -- clauses below
    TRY_CONVERT(smalldatetime, ca3.string)
FROM
(
    -- Logic starts here
    SELECT        
        CASE
            WHEN @UnformattedString IS NULL
                THEN NULL
            WHEN LEN(@UnformattedString) <= 1
                THEN NULL
            WHEN LEN(@UnformattedString) = 12
                THEN LEFT(@UnformattedString, 8)
            ELSE @UnformattedString
        END
) AS Input (string)
CROSS APPLY
(
    -- Next stage using result so far
    SELECT 
        CASE 
            WHEN @UnformattedString = '20000000' 
            THEN '20790606' 
            ELSE Input.string
        END
) AS ca1 (string)
CROSS APPLY 
(
    -- Next stage using result so far
    SELECT CASE
        WHEN LEFT(ca1.string, 2) = '00' THEN '20' + RIGHT(ca1.string, 6)
        WHEN LEFT(ca1.string, 2) = '18' THEN '19' + RIGHT(ca1.string, 6)
        WHEN LEFT(ca1.string, 2) = '19' THEN ca1.string
        WHEN LEFT(ca1.string, 2) = '20' THEN ca1.string
        WHEN LEN(ca1.string) <> 6 THEN '20' + RIGHT(ca1.string, 6)
        ELSE ca1.string
    END
) AS ca2 (string)
CROSS APPLY
(
    -- Next stage using result so far
    SELECT
        CASE 
            WHEN TRY_CONVERT(integer, LEFT(ca2.string, 4)) > YEAR(GETDATE())
                THEN '20790606'
            WHEN YEAR(GETDATE()) - TRY_CONVERT(integer, LEFT(ca2.string, 4)) >= 100
                THEN '20790606'
            ELSE ca2.string
        END
) AS ca3 (string);

The function used on the sample data:

SELECT
    InsertID,
    Result1 = CD1.Result,
    Result2 = CD2.Result,
    Result3 = CD3.Result
FROM dbo.RawData AS RD
CROSS APPLY dbo.CleanDate(RD.MangledDateTime1) AS CD1
CROSS APPLY dbo.CleanDate(RD.MangledDateTime2) AS CD2
CROSS APPLY dbo.CleanDate(RD.MangledDateTime3) AS CD3;

Output:

╔══════════╦═════════════════════╦═════════════════════╦═════════════════════╗
║ InsertID ║       Result1       ║       Result2       ║       Result3       ║
╠══════════╬═════════════════════╬═════════════════════╬═════════════════════╣
║        1 ║ 2000-10-10 00:00:00 ║ 2079-06-06 00:00:00 ║ NULL                ║
║        1 ║ 2079-06-06 00:00:00 ║ 2013-06-30 00:00:00 ║ 2079-06-06 00:00:00 ║
║        1 ║ 2000-10-10 00:00:00 ║ 2079-06-06 00:00:00 ║ 2013-06-30 00:00:00 ║
╚══════════╩═════════════════════╩═════════════════════╩═════════════════════╝

_{*CLR scalar functions have a much faster invocation path than T-SQL scalar functions and do not prevent parallelism.}

Best Answer

Related Solutions

Sql-server – Granting Execute Permissions on Stored Procedures, Functions and Views

Sql-server – Running functions in parallel

After the edit to the question

Related Question