Often it may be desirable for a standard database user to temporarily modify certain Postgres Grand Unified Configuration (GUC) variables which are normally restricted to superusers. The most common workaround for this limitation is through the utilization of SECURITY DEFINER functions. This article will describe how to implement an example without compromising database security.
First, we need an example configuration parameter that is only settable by a superuser. The log_min_duration_statement parameter is commonly restricted because it is easy to flood the Postgres log by setting this to a very low value. However, some automated jobs may provide more debugging information if they're allowed to set this.
We will begin by defining a function that will set the value for the current session, and return the interpreted result as proof it was properly accepted by Postgres. This function should be created by a Postgres superuser, such as postgres.
CREATE OR REPLACE FUNCTION set_whitelisted_guc(
setting_name VARCHAR,
setting_value VARCHAR
)
RETURNS VARCHAR AS
$$
BEGIN
IF setting_name IN ('log_min_duration_statement', 'whatever_else')
THEN
EXECUTE 'SET ' || quote_ident(setting_name) || ' TO ' ||
quote_literal(setting_value);
END IF;
RETURN (
SELECT setting
FROM pg_settings
WHERE name = setting_name
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;Next, all Postgres functions are normally granted for PUBLIC use; this is clearly undesirable behavior for elevated privilege functions, so that should be revoked. Afterwards, it's good practice to create a role specifically to represent this particular function, so its use can be strictly controlled.
Again, as a Postgres superuser:
REVOKE EXECUTE
ON FUNCTION set_whitelisted_guc(VARCHAR, VARCHAR)
FROM PUBLIC;
CREATE ROLE guc_auth;
GRANT EXECUTE
ON FUNCTION set_whitelisted_guc(VARCHAR, VARCHAR)
TO guc_auth;NOTE: All of the above commands can be run in a transaction to prevent the small window of opportunity to call the function between it being created and securing its access, if this level of security is needed.
Finally, the dedicated role should be granted to any user who needs to be able to manipulate one of these whitelisted configuration settings. This way, direct access to the function is restricted to the role, but the user will have access. If this type of access is no longer necessary in the future, it can easily be revoked.
Again, as a Postgres superuser:
GRANT guc_auth to some_authorized_user;More comprehensive versions of this function can also incorporate rules that only allow certain settings for some variables.