pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to start a database in ReadOnly mode?
5+ messages / 5 participants
[nested] [flat]
* How to start a database in ReadOnly mode?
@ 2011-07-07 07:08 Paul Schluck <pschluck@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Paul Schluck @ 2011-07-07 07:08 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to start a database in ReadOnly mode?
@ 2011-07-07 07:26 Simon Riggs <simon@2ndQuadrant.com>
parent: Paul Schluck <pschluck@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Simon Riggs @ 2011-07-07 07:26 UTC (permalink / raw)
To: Paul Schluck <pschluck@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to start a database in ReadOnly mode?
@ 2011-07-07 07:49 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Simon Riggs <simon@2ndQuadrant.com>
0 siblings, 0 replies; 5+ messages in thread
From: Achilleas Mantzios @ 2011-07-07 07:49 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to start a database in ReadOnly mode?
@ 2011-07-09 11:58 Greg Sabino Mullane <greg@turnstep.com>
parent: Paul Schluck <pschluck@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Greg Sabino Mullane @ 2011-07-09 11:58 UTC (permalink / raw)
To: pgsql-admin; +Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=001485e76f4600a3c704a7756ca4 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Mailing-List: pgsql-admin List-Archive: <http://archives.postgresql.org/pgsql-admin>; List-Help: <mailto:majordomo@postgresql.org?body=help>; List-ID: <pgsql-admin.postgresql.org>; List-Owner: <mailto:pgsql-admin-owner@postgresql.org>; List-Post: <mailto:pgsql-admin@postgresql.org>; List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>; List-Unsubscribe: Paul Schluck <pschluck@gmail.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-----
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to start a database in ReadOnly mode?
@ 2011-07-09 12:26 Guillaume Lelarge <guillaume@lelarge.info>
parent: Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 0 replies; 5+ messages in thread
From: Guillaume Lelarge @ 2011-07-09 12:26 UTC (permalink / raw)
To: Greg Sabino Mullane <greg@turnstep.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-07-09 12:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-07 07:08 How to start a database in ReadOnly mode? Paul Schluck <pschluck@gmail.com>
2011-07-07 07:26 ` Simon Riggs <simon@2ndQuadrant.com>
2011-07-07 07:49 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2011-07-09 11:58 ` Greg Sabino Mullane <greg@turnstep.com>
2011-07-09 12:26 ` Guillaume Lelarge <guillaume@lelarge.info>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox