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