Re: BEGIN WORK READ ONLY;

On Sat, Oct 14, 2006 at 11:35:12AM -0700, Joshua D. Drake wrote:
> What is the use case for a READ ONLY transaction?

I use read-only transactions as a safety net for interactive sessions
when I want to avoid modifying anything accidentally. Here's an
example:

CREATE ROLE foo LOGIN PASSWORD 'password';
CREATE ROLE foo_ro LOGIN PASSWORD 'password';
ALTER ROLE foo_ro SET default_transaction_read_only TO on;
GRANT foo TO foo_ro;

The foo_ro role now has the same privileges as foo but it can't
modify anything because its transactions are read-only by default.
Using GRANT/REVOKE would be more secure (foo_ro could set
default_transaction_read_only to off and then do anything that foo
could do) but you'd have to remember to set the correct privileges
on every object the read-only role might need to examine; this would
be easy to automate but you'd still have to remember to do it. When
the intent is to prevent "oops" mistakes rather than to provide
real security, using read-only transactions can be convenient.

--
Michael Fuhr