Обсуждение: Disabling START TRANSACTION for a SuperUser

Hi,

I have created a readonly user by executing the following statements,
CREATE USER backupadm SUPERUSER  password 'mypass';
ALTER USER backupadm set default_transaction_read_only = on;

But the backupadm user is able to create/update table when using START
TRANSACTION READ WRITE and then COMMIT;

Is there any way to block/disabling an User from running Transactions?

Thanks in advance.



--
View this message in context: http://postgresql.nabble.com/Disabling-START-TRANSACTION-for-a-SuperUser-tp5871630.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 27 October 2015 at 18:25, rajan <vgmonnet@gmail.com> wrote:
> Hi,
>
> I have created a readonly user by executing the following statements,
> CREATE USER backupadm SUPERUSER  password 'mypass';

A superuser can never be a read only user.

> ALTER USER backupadm set default_transaction_read_only = on;

They can just

SET default_transaction_read_only = off;

to get around that. It has no useful effect for security.

> But the backupadm user is able to create/update table when using START
> TRANSACTION READ WRITE and then COMMIT;
>
> Is there any way to block/disabling an User from running Transactions?

No, it's fundamentally impossible, because the statements you
mentioned - like CREATE USER - also run within transactions.

You could stop them from running an explicit transaction, but that
wouldn't stop them using CREATE TABLE, UPDATE, etc, as stand-alone
statements.

What you appear to want can be achieved, albeit with some difficulty,
using an ExecutorStart_hook and ProcessUtility_hook, implemented with
a C extension. You can find an example of one in pg_stat_statements,
sepgsql, and in the BDR source code. The latter uses it for a similar
purpose to what you describe - to limit what commands can be run.
Doing that securely will be challenging.


-- Craig Ringer                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services

Hey Craig,

Thanks for your response. Seems like the workaround is difficult.

I am trying to understand 
"
ExecutorStart_hook and ProcessUtility_hook, implemented with 
a C extension. You can find an example of one in pg_stat_statements, 
sepgsql, and in the BDR source code. The latter uses it for a similar 
purpose to what you describe - to limit what commands can be run. 
"

Let me see what i can do...

Thanks again...



--
View this message in context:
http://postgresql.nabble.com/Disabling-START-TRANSACTION-for-a-SuperUser-tp5871630p5871645.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On 27 October 2015 at 21:19, rajan <vgmonnet@gmail.com> 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

On Tuesday, October 27, 2015 8:52 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
> On 27 October 2015 at 21:19, rajan <vgmonnet@gmail.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

<p dir="ltr">Thanks Craig,<p dir="ltr">There are a lot of details and its overwhelming.... :-) Let me digest and will
postfor any help....<div class="gmail_quote">On 27-Oct-2015 7:21 PM, "Craig Ringer" <<a
href="mailto:craig@2ndquadrant.com">craig@2ndquadrant.com</a>>wrote:<br type="attribution" /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> On 27 October 2015 at 21:19,
rajan<<a href="http:///user/SendEmail.jtp?type=node&node=5871647&i=0" link="external" rel="nofollow"
target="_blank">[hiddenemail]</a>> wrote: <br />> Hey Craig, <br />> <br />> Thanks for your response.
Seemslike the workaround is difficult. <br />> <br />> I am trying to understand <br />> " <br />>
ExecutorStart_hookand ProcessUtility_hook <br /><br />Doing what you want will require being willing to spend a fair
bitof <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 programming and reading source code. <br
/><br/>Here's some code that filters allowable commands. It doesn't care <br />which user id is used, but it's pretty
simpleto add a check to only <br />run the filter when a particular user ID is the active user. This <br />won't do
whatyou want, but serves as a rough example of how you can <br />filter statements based on the parsed statement data:
<br/><br /><a href="https://github.com/2ndQuadrant/bdr/blob/bdr-plugin/next/bdr_commandfilter.c" link="external"
rel="nofollow"target="_blank">https://github.com/2ndQuadrant/bdr/blob/bdr-plugin/next/bdr_commandfilter.c</a><br /><br
/>andalso: <br /><br /><a href="http://www.postgresql.org/docs/current/static/xfunc-c.html" link="external"
rel="nofollow"target="_blank">http://www.postgresql.org/docs/current/static/xfunc-c.html</a><br /><a
href="http://www.postgresql.org/docs/current/static/extend-extensions.html"link="external" rel="nofollow"
target="_blank">http://www.postgresql.org/docs/current/static/extend-extensions.html</a><br/><a
href="http://www.postgresql.org/docs/current/static/extend-pgxs.html"link="external" rel="nofollow"
target="_blank">http://www.postgresql.org/docs/current/static/extend-pgxs.html</a><br/><br />Note that BDR's command
filterdoesn't do anything to <br />insert/update/delete/select. For that you'd *also* need an <br />ExecutorStart_hook
orsimilar. <br /><br />If this is going way too deep, perhaps you should post to <br />pgsql-general with a description
ofthe underlying problem you are <br />trying to solve, i.e. *why* you want to be able to have a superuser <br />who
canalter users but can't select, etc. What's the problem you're <br />trying to solve with this? <br /><br />-- <br
/> CraigRinger                   <a href="http://www.2ndQuadrant.com/" link="external" rel="nofollow"
target="_blank">http://www.2ndQuadrant.com/</a><br/> PostgreSQL Development, 24x7 Support, Training & Services <br
/><br/><br />-- <br />Sent via pgsql-hackers mailing list (<a
href="http:///user/SendEmail.jtp?type=node&node=5871647&i=1"link="external" rel="nofollow"
target="_blank">[hiddenemail]</a>) <br />To make changes to your subscription: <br /><a
href="http://www.postgresql.org/mailpref/pgsql-hackers"link="external" rel="nofollow"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/><br /><br /><hr color="#cccccc" noshade
size="1"/><div style="color:#444;font:12px tahoma,geneva,helvetica,arial,sans-serif"><div style="font-weight:bold">If
youreply to this email, your message will be added to the discussion below:</div><a
href="http://postgresql.nabble.com/Disabling-START-TRANSACTION-for-a-SuperUser-tp5871630p5871647.html"
target="_blank">http://postgresql.nabble.com/Disabling-START-TRANSACTION-for-a-SuperUser-tp5871630p5871647.html</a></div><div
style="color:#666;font:11pxtahoma,geneva,helvetica,arial,sans-serif;margin-top:.4em;line-height:1.5em"> To unsubscribe
fromDisabling START TRANSACTION for a SuperUser, <a
href="http://postgresql.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5871630&code=dmdtb25uZXRAZ21haWwuY29tfDU4NzE2MzB8MTg2MjE3MzA5Nw=="
target="_blank">clickhere</a>.<br /><a
href="http://postgresql.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml"
rel="nofollow"style="font:9px serif" target="_blank">NAML</a></div></blockquote></div> 

<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> 

On 2015/10/28 12:57, Muthiah Rajan wrote:
> Hello Kevin,
> 
> This may be a trivial thing.... But what do you mean by shops? I actually
> can't get it.... :-)

I think it's casually used to mean a company or a production environment.

Thanks,
Amit

Thanks Amit... :-)



--
View this message in context:
http://postgresql.nabble.com/Disabling-START-TRANSACTION-for-a-SuperUser-tp5871630p5871739.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Tuesday, October 27, 2015 10:57 PM, Muthiah Rajan <vgmonnet@gmail.com> wrote:
> On 27-Oct-2015 7:37 PM, "Kevin Grittner" <kgrittn@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

--On 27. Oktober 2015 14:07:06 +0000 Kevin Grittner <kgrittn@ymail.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