Re: Disabling START TRANSACTION for a SuperUser

On 27 October 2015 at 18:25, rajan <vgmonnet(at)gmail(dot)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