Menu
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+)
448
A SECURITY DEFINER function will run with the User ID and security context of the function owner. This can be used to allow a low privileged user to execute an operation that requires high privileges in a controlled fashion: you define a SECURITY DEFINER function owned by a privileged user that executes the operation.
SECURITY DEFINER specifies that the function is to be executed with the privileges of the user that owns it. The key word EXTERNAL is allowed for SQL conformance, but it is optional since, unlike in SQL, this feature applies to all functions not only external ones.
The SQL SECURITY clause (by default DEFINER ) specifies what privileges are used when a routine is called. If SQL SECURITY is INVOKER , the function body will be evaluated using the privileges of the user calling the function.
In PostgreSQL, the dollar-quoted string constants ($$) is used in user-defined functions and stored procedures. In PostgreSQL, you use single quotes for a string constant like this: select 'String constant'; When a string constant contains a single quote ('), you need to escape it by doubling up the single quote.
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.
104
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
