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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
