In Postgres, is an unprivileged role able to execute a function with elevated privileges?

Question

For context, I am using:

• postgREST: a thin REST client on top of Postgres (which does all the heavy lifting)
• Azure AD: which handles identity and access of the organization

Since users, roles, groups, etc (and even password reset, etc) are handled in Azure AD, all I want to do with my API is allow the user that provided a validated JWT in.

When a user first faces the API, their role doesn't exist. And when they first face the API, they effectively have the most basic permissions (that of anonymous user).

I want an anonymous user to be able to POST to an /rpc/* endpoint with a valid id token from Azure AD. This would do some very privileged stuff like creating and granting roles (but controlled by the function).

i.e. Anonymous user would not be able to create a role, but anonymous user would be able to execute function (with valid security JWT as input), and indirectly create roles through the function.

Answer 1

Yes, it is possible to grant a user the ability to execute a specific function without granting them the ability to create roles directly. To do this, you can define the function with a SECURITY DEFINER attribute. This attribute causes the function to be executed with the privileges of the user who defined the function, rather than the privileges of the user who is calling the function.

Here is an example of how you could define a function with the SECURITY DEFINER attribute:

CREATE OR REPLACE FUNCTION public.create_role(role_name text)
RETURNS void AS $$
BEGIN
    EXECUTE format('CREATE ROLE %I NOLOGIN', role_name);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

This function takes a role_name parameter and uses dynamic SQL with the EXECUTE statement to create a new role with the specified name. The SECURITY DEFINER attribute is specified at the end of the CREATE FUNCTION statement.

Once you have defined the function, you can grant the web_anon user the ability to execute it using the GRANT statement like this:

GRANT EXECUTE ON FUNCTION public.create_role(text) TO web_anon;

This grants the web_anon user the ability to execute the public.create_role(text) function. When this user calls the function, it will be executed with the privileges of the user who defined the function, allowing it to create a new role even if the web_anon user does not have the CREATEROLE privilege.

It is important to note that using a SECURITY DEFINER function can be a security risk if not used carefully. You should make sure that the function only performs actions that are safe for any user to perform and that it properly validates its input to prevent SQL injection attacks.

---

For the postgREST example, something like this would do for onboarding users (create and own function as admin):

CREATE OR REPLACE FUNCTION api.onboard(id_token text)
RETURNS json AS $$
DECLARE
    CLAIMS json := auth.decode_id_token(id_token)::json;
    ROLE_NAME text := CLAIMS->>'preferred_username';
BEGIN
    EXECUTE format('CREATE ROLE %I NOLOGIN', ROLE_NAME);
    EXECUTE format('GRANT %I TO authenticator', ROLE_NAME);
    EXECUTE format('GRANT USAGE ON SCHEMA auth TO %I', ROLE_NAME);
    EXECUTE format('GRANT USAGE ON SCHEMA api TO %I', ROLE_NAME);
    EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE admin IN SCHEMA api GRANT SELECT,USAGE ON SEQUENCES  TO %I', ROLE_NAME);
    EXECUTE format('ALTER DEFAULT PRIVILEGES FOR ROLE admin IN SCHEMA api GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES  TO %I', ROLE_NAME);
    EXECUTE format('GRANT SELECT,INSERT,DELETE,UPDATE ON ALL TABLES IN SCHEMA api TO %I', ROLE_NAME);
    EXECUTE format('GRANT SELECT,USAGE ON ALL SEQUENCES IN SCHEMA api TO %I', ROLE_NAME);
    RETURN format('{
        "status": "success",
        "message": "User successfully onboarded",
        "user": {
            "oid": "%s",
            "name": "%s",
            "preferred_username": "%s"
        }
    }', CLAIMS->>'oid', CLAIMS->>'name', ROLE_NAME)::json;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION api.onboard(id_token text) OWNER TO admin;
GRANT EXECUTE ON FUNCTION api.onboard(id_token text) TO web_anon;

And the auth.decode_id_token function might look like this (there is probably a way to do it without PL/Python3u, which needs the superuser privileges to create the function); note that it is important that the client ID and tenant ID are validated here:

CREATE EXTENSION plpython3u;
CREATE SCHEMA auth;
ALTER SCHEMA auth OWNER TO admin;
CREATE OR REPLACE FUNCTION auth.decode_id_token(id_token text)
  RETURNS json
AS $$
  import asyncio
  import json
  from guardpost.jwts import JWTValidator
  settings = plpy.execute("SELECT current_setting('app.settings.AZURE_TENANT_ID') AS tid, current_setting('app.settings.AZURE_CLIENT_ID') AS cid")
  tenant_id = settings[0]["tid"]
  client_id = settings[0]["cid"]
  async def main():
    validator = JWTValidator(
      authority=f"https://login.microsoftonline.com/{tenant_id}/",
      valid_issuers=[f"https://login.microsoftonline.com/{tenant_id}/v2.0"],
      valid_audiences=[client_id]
    )
    return await validator.validate_jwt(id_token)
  return json.dumps(asyncio.run(main()))
$$ LANGUAGE plpython3u;
ALTER FUNCTION auth.decode_id_token(id_token text) OWNER TO admin;