Disabling START TRANSACTION for a SuperUser

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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" <craig@2ndquadrant.com> wrote:

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" <kgrittn@ymail.com> wrote:

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Thanks for clarifying my doubt...

--
Muthiah Rajan

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

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers