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