"Mikko Partio" <mpartio at gmail.com> writes:
> I have to set up a replica of our production database to a possibly insecure location (DMZ). I have been using slony in other projects and the log shipping mode would
> seem to fit this purpose perfectly, but, alas, it requires that another slave is configured besides the origin node. This is not very practical for us since the
> replicated database is large and we don't have the hardware to support yet another slave. So, my question is that is there any way to configure log shipping with just
> the origin and the log shipping slave?
> On a side note, I saw recently some discussion about the privileges of the replication user, namely that the superuser mode is only required at the initial
> subscription. Can I just ALTER USER x NOSUPERUSER when in "normal" replication mode?

Actually, there's a bit better result that falls out of some further
analysis we have done on this...

You can set up two "slony users" for the two different roles that they
have:

- The DB user that slon uses to connect to the node that it manages
  needs to be a superuser.  (The configuration for this is in the slon
  command line, in the form of the DSN that you pass in.)

- The DB user that slon uses to connect to *other nodes* does NOT need
  to be so exalted in its powers.

  The DSNs for these connections are set up in the table "sl_path",
  and are stored in the DBMS via the slonik "STORE PATH" command.

You can set up two users at each node:

 - "slonysuper" which is a superuser.

 - "slonyweak" which has pretty minimal permissions.

   Most of the time, it needs little more than read permissions on
   tables in the schema that has Slony-I config and data.  It needs
   write access to sequences sl_nodelock and
   sl_nodelock_nl_conncnt_seq on remote nodes.

See recent versions of the "best practices" page; look for "Lowering
Authority."

-- 
select 'cbbrowne' || '@' || 'ca.afilias.info';
<http://linuxdatabases.info/info/bestpractices.html>
Christopher Browne
(416) 673-4124 (land)