How to start a database in ReadOnly mode?

Поиск
Список
Период
Сортировка
Искать

How to start a database in ReadOnly mode?

От:
Paul Schluck <pschluck@gmail.com>
Дата:
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

Re: How to start a database in ReadOnly mode?

От:
Simon Riggs <simon@2ndQuadrant.com>
Дата:
On Thu, Jul 7, 2011 at 8:08 AM, Paul Schluck  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

Re: How to start a database in ReadOnly mode?

От:
Achilleas Mantzios <achill@matrix.gatewaynet.com>
Дата:
Hello
Στις Thursday 07 July 2011 10:26:19 ο/η Simon Riggs έγραψε:
> On Thu, Jul 7, 2011 at 8:08 AM, Paul Schluck  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  FROM readonly;
(for each table) GRANT SELECT ON  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

Re: How to start a database in ReadOnly mode?

От:
"Greg Sabino Mullane" <greg@turnstep.com>
Дата:

-----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-----


Re: How to start a database in ReadOnly mode?

От:
Guillaume Lelarge <guillaume@lelarge.info>
Дата:
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

FAQ