Postgres - Permissions within the function

0

I'm setting up a system where the user will have very limited access, just view and perform certain specific functions.

Scenario (with root user)

It has three tables:

  • Person
  • Device
  • Access

And three 4 functions:

  • sync_person ()
  • sync_device ()
  • sync_access ()
  • sync_access_user ()

Desired (with normal user)

The user should only be able to:

  • table person - Query ( SELECT )
  • sync_person () - Run
  • sync_device () - Run
  • sync_access_user () - Run

Problem

Functions are generating permission error for internal executions.

Hypothetical scenario (Created by root)

CREATE TABLE test(
    name    VARCHAR
);

CREATE OR REPLACE FUNCTION test(
    name    VARCHAR
) RETURNS BOOLEAN AS $func$
    BEGIN
        INSERT INTO test VALUES (name);
        RETURN TRUE;
    END;
$func$ LANGUAGE plpgsql;

GRANT EXECUTE ON FUNCTION test(VARCHAR) TO normal_user;

Execution (Normal user)

SELECT test('Temwifi');

ERROR: permission denied for relation test
SQL state: 42501
Context: SQL statement "INSERT INTO test VALUES (name)"
PL/pgSQL function test(character varying) line 3 at SQL statement

Question

  • How to allow the normal user to perform the function, without releasing direct access to the required resources? Ex: INSERT
asked by anonymous 05.12.2017 / 20:24

1 answer

0

This can be done by adding the parameter SECURITY DEFINER to the function, such as explains in the documentation .

  

The function is executed with the privileges of the user who created it.

So you can grant the privilege to a user to perform this function, without necessarily having privileges to the necessary resources.

Addendum

The documentation still warns to be careful when performing this operation, as a malicious user can take advantage of some present privilege of this function.

    
05.12.2017 / 20:40