Re: How to configure a read-only database server and session? (Was: read-only UNLOGGED tables)

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