Re: Disabling START TRANSACTION for a SuperUser

--On 27. Oktober 2015 14:07:06 +0000 Kevin Grittner <kgrittn(at)ymail(dot)com>
wrote:

> It would be a boon to big shops if they could
> declare (preferably with the option to set it at a role level) that
> specific default_transaction_* settings could not be overridden.

A while ago i was faced with exactly the same problem. Thinking about it
again, i think that this also applies to various other parameters a DBA
wants to restrict to its roles. E.g. resource consumption limits (work_mem,
...), session constraints like the discussed transaction modes or even not
allowing to change the application_name.

afaicr, Oracle has a CREATE PROFILE which is primilarily intended to add
resource or password restrictions to users. Maybe this can be seen as a
blueprint to introduce the concept of GUC profiles to postgres, where a set
with maybe restrictions on the allowed values for certain GUCs can be
attached to roles. That for sure is a huge project.

Another idea (and maybe not that invasive like the profile idea) might be
to just introduce a concept of "read only" GUCs. A role would get a list of
GUCs which it is not allowed to change if given with ALTER ROLE...SET
(maybe restricted to PGC_USERSET). That could be stored along the same way
like pg_db_role_settings. However, i haven't checked how complicated this
would be to incorporate into the guc assign hooks, but maybe its doable
somehow.

--
Thanks

Bernd