Thread: How to start a database in ReadOnly mode?
Hi,
Is it possible to start a postgres cluster or 1 postgres database in readonly mode? And - if yes - how can this be done?
Regards,
Paul Schluck
On Thu, Jul 7, 2011 at 8:08 AM, Paul Schluck <pschluck@gmail.com> wrote: > Is it possible to start a postgres cluster or 1 postgres database in > readonly mode? And - if yes - how can this be done? There is no formal, explicit command to do that. Just avoid doing writes, either in the application or by removing privileges. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Hello Στις Thursday 07 July 2011 10:26:19 ο/η Simon Riggs έγραψε: > On Thu, Jul 7, 2011 at 8:08 AM, Paul Schluck <pschluck@gmail.com> wrote: > > > Is it possible to start a postgres cluster or 1 postgres database in > > readonly mode? And - if yes - how can this be done? > > There is no formal, explicit command to do that. Just avoid doing > writes, either in the application or by removing privileges. > This is how we did it, when there was a special requirement to do so for a special occasion, make a new user (lets call it "readonly") and have the app authenticating with this user. Manage his privileges with smth like: (for every table) CREATE user readonly; REVOKE ALL ON DATABASE <your database> FROM readonly; (for each table) GRANT SELECT ON <your table> TO readonly; For this last one you might need to write a script, producing the sql commands. > -- > �Simon Riggs������������������ http://www.2ndQuadrant.com/ > �PostgreSQL Development, 24x7 Support, Training & Services > -- Achilleas Mantzios
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > Is it possible to start a postgres cluster or 1 postgres database in > readonly mode? And - if yes - how can this be done? Clusterwide: edit postgresql.conf and set default_transaction_read_only = on; No restart needed, just a reload (HUP) Database-wide: ALTER DATABASE foobar SET default_transaction_read_only = true; - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201107090757 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAk4YQkIACgkQvJuQZxSWSsidzgCg1dpGvT6IY35NmMl1psiBlKuj B2sAoJhdD3vlg0F/GwjhO1SCMfK/hhWS =dRd9 -----END PGP SIGNATURE-----
On Sat, 2011-07-09 at 11:58 +0000, Greg Sabino Mullane wrote: > > Is it possible to start a postgres cluster or 1 postgres database in > > readonly mode? And - if yes - how can this be done? > > Clusterwide: edit postgresql.conf and set > default_transaction_read_only = on; > > No restart needed, just a reload (HUP) > > Database-wide: > > ALTER DATABASE foobar SET default_transaction_read_only = true; > The only issue is that any user can unset this parameter with a SET query on his session. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com