Re: Disabling START TRANSACTION for a SuperUser

On Tuesday, October 27, 2015 8:52 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> On 27 October 2015 at 21:19, rajan <vgmonnet(at)gmail(dot)com> wrote:

>> Thanks for your response. Seems like the workaround is difficult.
>>
>> I am trying to understand
>> "
>> ExecutorStart_hook and ProcessUtility_hook

> Doing what you want will require being willing to spend a fair bit of
> time becoming familiar with PostgreSQL's innards, writing extensions,
> etc. It's not a simple "download, compile, run" process. You will need
> to be confident with C programming and reading source code.

> If this is going way too deep, perhaps you should post to
> pgsql-general with a description of the underlying problem you are
> trying to solve, i.e. *why* you want to be able to have a superuser
> who can alter users but can't select, etc. What's the problem you're
> trying to solve with this?

This is a question I have seen before, as well as slight variations
on it related to transaction isolation level. Right now you can
implement a read-only user by granting only SELECT rights to tables
and also by setting the default_transaction_read_only = on. The
problem is that the latter is essentially just a suggestion, not an
order. I actually don't think it's as big a problem with read-only
users, since that can still be accomplished (with enough work) by
using the GRANT/REVOKE commands. (Think how much faster and easier
it could be if there is a role that allows the appropriate set of
SELECTs but also allows some DML -- just set a read-only rule for
the user and the existing role could work.)

It is more problematic where a shop wants to use serializable
transactions to ensure data integrity. The only way to prevent
someone from subverting the business rules is to code a lot of
triggers on a lot of objects that throw an error if the isolation
level is wrong. 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.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company