Re: restrict global access to be readonly

On Feb15, 2015, at 10:13 , David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> happy times wrote
>> Sure, we can utilize the runtime parameter
>> default_transaction_read_only, however, it does not restrict user from
>> changing transaction attribute to non-readonly mode, so is not safe.
>
> ISTM that implementing a means to make this setting only super-user
> changeable would be a quick(er) solution to the problem - the decision as to
> what to disallow is already decided.
>
> It seems like it would have to be a separate GUC but it would require any
> new SQL but would simply leverage the existing settings system to setup
> database-user values that only a super-user can change.

I've wished for a way prevent regular users for changing specific settings
in the past. Maybe we could have

ALTER DATABASE <database> FORCE <parameter> TO <value>
ALTER ROLE <role> [ IN DATABASE <db> ] FORCE <parameter> TO <value>

In postgresql.conf, we could use a syntax like

parameter =!= value

to indicate that the parameter value can only be changed by super-users.

We' have to figure out what happens if a database- or role-specific
FORCEd setting attempts to override a value already FORCEd in postgresql.conf.

Ideally, we'd allow database- or role-specific settings created by super-users
to override previously FORCEd values, but that would require us to store the
role that creates such settings in pg_db_role_setting. For SET clauses attached
to functions, we'd complain if they attempt to change a FORCEd value, unless
they are called by a super-user, or are marked SECURITY DEFINER and owned by
a super-user.

best regards,
Florian Pflug