2

I want to set up a workflow, where a third party (still within the org) grants accesses to our database by granting to a role that has the select or update access to the tables. I would also want this third party NOT to have these accesses. I.e. is not able to read or update data. In my understanding DB2's SYSCTRL does a similar thing, but i am not sure how to replicate this in Postgres.

Here is what I am thinking on:

  1. [email protected] requests read access
  2. Third party postgres role grants the read role: e.g. GRANT reader_role TO "[email protected]"; all while logged in as a role that has no select access to the db/tables.

What is the usual way to achieve this in PostgreSQL? Currently the third party role has to be granted to reader_role WITH ADMIN OPTION.

1 Answer 1

1

The manual says:

Roles having CREATEROLE privilege can grant or revoke membership in any role that is not a superuser.

However, it is not very useful, since it can grant the reader_role to itself just as easily as it can to any other role.

A SECURITY DEFINER function that wraps the GRANT statement can be an answer though. As a superuser, create a function like the one below and grant privileges to run it to the specific role, while revoking it from everybody else:

CREATE OR REPLACE FUNCTION grant_reader_role (
    to_role TEXT
)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $BODY$
BEGIN
    IF to_role IS NULL OR to_role = '' THEN
        RAISE EXCEPTION 'Invalid role';
        RETURN FALSE;
    ELSIF to_role = session_user THEN
        RAISE EXCEPTION 'Not allowed';
        RETURN FALSE;
    ELSE
        EXECUTE format('GRANT READER_ROLE TO %s', to_role);
        RETURN TRUE;
    END IF;
END;
$BODY$
;
REVOKE EXECUTE ON FUNCTION grant_reader_role FROM public;
GRANT EXECUTE ON FUNCTION grant_reader_role TO sysctl;

Make sure you read the section about Writing SECURITY DEFINER Functions Safely.

The sysctl role, and only that role, will be able to run this function and execute the appropriate GRANT statement, and only that statement, with the superuser privileges.

4
  • Good answer, but I won't upvote unless you set search_path on the function like you should. Jan 31, 2023 at 22:25
  • I included the link to the safety section in the manual for that reason specifically. Not that I care about your upvotes.
    – mustaccio
    Feb 1, 2023 at 0:50
  • Point about the upvotes taken. It's just that a lot of people copy and paste code, and we shouldn't try to disseminate code with security leaks. It's easy to launch a privilege escalation attack against your function as it is. Feb 1, 2023 at 6:52
  • Appreciate your answer! I need to digest it and investigate the security implications mentioned above. Thanks again!
    – sifear
    Feb 1, 2023 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.