16

Postgres's official docs indicate that functions defined with SECURITY DEFINER run with privileges of the user who created it.

However other sources, such as here and here, claim it is the privileges of the owner of the function.

Which is correct?

(for 9.4+)

Community
  • 1
  • 1
ExactaBox
  • 3,235
  • 16
  • 27
  • 1
    The user creating the function is the owner (unless you change the owner manually) –  Oct 23 '16 at 07:31
  • 1
    @a_horse_with_no_name But that's the whole point... generally, when I create a new function, I'm logged in with very high privileges. I want the users who run those functions to have limited (sometimes, SELECT only) privileges. – ExactaBox Oct 23 '16 at 08:24
  • Then leave out the security definer (that's the whole point of `security definer`: give unprivileged users access to privileged things) –  Oct 23 '16 at 08:37
  • 2
    @a_horse_with_no_name Right, `security definer` lets users "borrow" some elevated privileges only while executing the function. Let's say I have a function that INSERTs data, that I want a restricted user to be able to execute. I would prefer the user to temporarily emulate a function "owner" who can only SELECT and INSERT, rather than a superuser who can create functions and also DELETE, TRUNCATE, UPDATE, etc. – ExactaBox Oct 23 '16 at 09:04

1 Answers1

23

Usually (initially) the creator is the owner. However, if the owner of the function has been changed, security definer applies to the new owner. Per the documentation:

new_owner - The new owner of the function. Note that if the function is marked SECURITY DEFINER, it will subsequently execute as the new owner.

klin
  • 112,967
  • 15
  • 204
  • 232