On 27 October 2015 at 18:25, rajan <vgmon...@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
