Re: Disabling START TRANSACTION for a SuperUser - Mailing list pgsql-hackers

<p dir="ltr">Hello Kevin,<p dir="ltr">This may be a trivial thing.... But what do you mean by shops? I actually can't
getit.... :-)<div class="gmail_quote">On 27-Oct-2015 7:37 PM, "Kevin Grittner" <<a
href="mailto:kgrittn@ymail.com">kgrittn@ymail.com</a>>wrote:<br type="attribution" /><blockquote class="gmail_quote"
style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On Tuesday, October 27, 2015 8:52 AM, Craig Ringer
<<ahref="mailto:craig@2ndquadrant.com">craig@2ndquadrant.com</a>> wrote:<br /> > On 27 October 2015 at 21:19,
rajan<<a href="mailto:vgmonnet@gmail.com">vgmonnet@gmail.com</a>> wrote:<br /><br /> >> Thanks for your
response.Seems like the workaround is difficult.<br /> >><br /> >> I am trying to understand<br /> >>
"<br/> >> ExecutorStart_hook and ProcessUtility_hook<br /><br /> > Doing what you want will require being
willingto spend a fair bit of<br /> > time becoming familiar with PostgreSQL's innards, writing extensions,<br />
>etc. It's not a simple "download, compile, run" process. You will need<br /> > to be confident with C
programmingand reading source code.<br /><br /> > If this is going way too deep, perhaps you should post to<br />
>pgsql-general with a description of the underlying problem you are<br /> > trying to solve, i.e. *why* you want
tobe able to have a superuser<br /> > who can alter users but can't select, etc. What's the problem you're<br />
>trying to solve with this?<br /><br /> This is a question I have seen before, as well as slight variations<br /> on
itrelated to transaction isolation level.  Right now you can<br /> implement a read-only user by granting only SELECT
rightsto tables<br /> and also by setting the default_transaction_read_only = on.  The<br /> problem is that the latter
isessentially just a suggestion, not an<br /> order.  I actually don't think it's as big a problem with read-only<br />
users,since that can still be accomplished (with enough work) by<br /> using the GRANT/REVOKE commands.  (Think how
muchfaster and easier<br /> it could be if there is a role that allows the appropriate set of<br /> SELECTs but also
allowssome DML -- just set a read-only rule for<br /> the user and the existing role could work.)<br /><br /> It is
moreproblematic where a shop wants to use serializable<br /> transactions to ensure data integrity.  The only way to
prevent<br/> someone from subverting the business rules is to code a lot of<br /> triggers on a lot of objects that
throwan error if the isolation<br /> level is wrong.  It would be a boon to big shops if they could<br /> declare
(preferablywith the option to set it at a role level) that<br /> specific default_transaction_* settings could not be
overridden.<br/><br /> --<br /> Kevin Grittner<br /> EDB: <a href="http://www.enterprisedb.com" rel="noreferrer"
target="_blank">http://www.enterprisedb.com</a><br/> The Enterprise PostgreSQL Company<br /></blockquote></div>