Postgres - Permissions within the function

Asked

Viewed 461 times

0

I am setting up a system in which the user will have a very limited access, just view and perform certain specific functions.

Scenario (with root user)

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 - Consult (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 run the function without releasing direct access to required resources? Ex.: INSERT
postgresql

1 answer

0


This can be done by adding the parameter SECURITY DEFINER in function, 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 also warns to be careful when performing this operation, because a malicious user can take advantage of some present privilege of this function.

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.