On 04/18/2011 04:04 AM, Stefan Keller wrote:
> 5. Optimize and secure session by following parameters:
>
> SET transaction_read_only TO FALSE;
> SET TRANSACTION READ ONLY;
AFAIK, neither of those have any effect on security. They're purely
advisory hints to Pg.
Personally I think it'd be cool if read-only transactions were denied
the use of INSERT/UPDATE/DELETE, any "untrusted" PLs, and any
INSERT/UPDATE/DELETE via SPI from PLs. But "would be cool" isn't "want
to try to implement it" and I'm sure if it were easy, it'd have already
been done.
> All user tables reside in schema PUBLIC, Ok?
Yep. Make sure you don't grant CREATE on public to the target user, only
grant USAGE, and revoke all from public.
> => Any comments on making this PostgreSQL instance 'robust'?
> E.g. which situations (except for harddisk crashes) can leave a
> read-only dataset in an inconsistent state where PostgreSQL server
> can't restart? An immediate shutdown?
AFAIK:
- Kill -9 of a backend
- segfault / sigabrt / sigbus / etc of a backend
(though backends may try to handle some of these it'd normally be
unsafe and I doubt it, but I haven't checked)
- Kill -9 of the postmaster
- OS crash or unexpected reset
- .... probably other things
> => Any comments on speeding up/optimizing such a read-only dataset?
Depending on dataset size and access patterns, it could be worth
pinning a few indexes in a tablespace that lives on a ramdisk. Usually
Pg's and the OS's cache management will do the job well, but if you know
more than them - say, that this index will always be really hot, or that
certain queries are more important than others and must be more
responsive - you can play with that sort of thing.
--
Craig Ringer
