Re: [HACKERS] Disabling START TRANSACTION for a SuperUser

[Muthiah Rajan]

Thanks for clarifying my doubt...


--
Muthiah Rajan

On Wed, Oct 28, 2015 at 6:19 PM, Kevin Grittner <kgri...@ymail.com> wrote:

> On Tuesday, October 27, 2015 10:57 PM, Muthiah Rajan <vgmon...@gmail.com>
> wrote:
> > On 27-Oct-2015 7:37 PM, "Kevin Grittner" <kgri...@ymail.com> wrote:
>
> >> It is more problematic where a shop wants to use serializable
> >> transactions to ensure data integrity.
>
> > This may be a trivial thing But what do you mean by shops? I
> > actually can't get it :-)
>
> http://www.merriam-webster.com/dictionary/shop
>
> I was using "shop" in the sense of the second of the short noun
> definitions ("the place where a specified kind of worker works : a
> worker's place of business") or number 5a under the long noun
> definitions ("a business establishment: office").  When used in
> that sense the type of business is usually used ("an I.T. shop"),
> but where it is implied or obvious it is often dropped.  The
> dictionary doesn't list it as a colloquialism, but it is rather
> informal -- approaching the colloquial.  As I used it I was
> intending to convey a group of I.T. professionals under the same
> management with a common set of policies, working on the same set
> of hardware and/or software.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [HACKERS] Disabling START TRANSACTION for a SuperUser

[Muthiah Rajan]

Hello Kevin,

This may be a trivial thing But what do you mean by shops? I actually
can't get it :-)
On 27-Oct-2015 7:37 PM, "Kevin Grittner"  wrote:

> On Tuesday, October 27, 2015 8:52 AM, Craig Ringer 
> wrote:
> > On 27 October 2015 at 21:19, rajan  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
>

Re: [HACKERS] Disabling START TRANSACTION for a SuperUser

[Muthiah Rajan]

Thanks Craig,

There are a lot of details and its overwhelming :-) Let me digest and
will post for any help
On 27-Oct-2015 7:21 PM, "Craig Ringer"  wrote:

> On 27 October 2015 at 21:19, rajan <[hidden email]
> > wrote:
> > Hey Craig,
> >
> > 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.
>
> Here's some code that filters allowable commands. It doesn't care
> which user id is used, but it's pretty simple to add a check to only
> run the filter when a particular user ID is the active user. This
> won't do what you want, but serves as a rough example of how you can
> filter statements based on the parsed statement data:
>
> https://github.com/2ndQuadrant/bdr/blob/bdr-plugin/next/bdr_commandfilter.c
>
> and also:
>
> http://www.postgresql.org/docs/current/static/xfunc-c.html
> http://www.postgresql.org/docs/current/static/extend-extensions.html
> http://www.postgresql.org/docs/current/static/extend-pgxs.html
>
> Note that BDR's command filter doesn't do anything to
> insert/update/delete/select. For that you'd *also* need an
> ExecutorStart_hook or similar.
>
> 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?
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list ([hidden email]
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>
> --
> If you reply to this email, your message will be added to the discussion
> below:
>
> http://postgresql.nabble.com/Disabling-START-TRANSACTION-for-a-SuperUser-tp5871630p5871647.html
> To unsubscribe from Disabling START TRANSACTION for a SuperUser, click
> here
> 
> .
> NAML
> 
>