The simplest and most common way of deploying Slony-I has been to create a Slony-I database user account on all nodes in the system and give that account database superuser privileges. This allows Slony-I to do "anything it needs."
In some environments, this is too much privilege to give out to an automated system, so this section describes how to minimize the privileges given out.
The minimum privileges for running each component of Slony-I may be more specifically described.
The slonik admin connections need to connect to the database as a database superuser. As part of the installation of Slony-I, the slonik program will create C language functions in the database. This requires superuser access. Some slonik commands will enable and disable indicies which by manipulating pg_class. This also requires superuser access.
- slon Local Connection
Each slon instance has a "local" database connection. This is the database connection that is specified on the either the slon command line or the slon configuration file.
Slon needs to connect to this database with considerable "write" privileges, and requires superuser access in a couple of places.
It must be able to
Alter pg_class to deactivate indices when preparing to COPY a table
Make alterations to any of the Slony-I created tables
Make modifications (INSERT/UPDATE/DELETE/ALTER) to all replicated tables.
set the session_replication_role to replica
- slon Remote Connections
SELECT from sl_event
SELECT any replicated tables that originate on the remote node. This is done as part of the initial COPY during the subscription process
Note that this role does not have any need to modify data; it purely involves SELECT access.
Traditionally, it has been stated that "Slony-I needs to use superuser connections." It turns out that this is not actually true, and and if there are particular concerns about excessive use of superuser roles, it is possible to reduce the "security demands" of Slony-I considerably.
It is simplest to have the replication management user be a superuser, as, in that case, one need not think about what permissions to configure, but this is excessive.
There is only actually one place where Slony-I truly requires superuser access, and that is for installation (slonik) and on the local connetion slon uses.
The slon and slonik programs connect to PostgreSQL as a normal PostgreSQL client connection. How PostgreSQL authenticates the database connection is controlled through the normal libpq authentication options via the pg_hba.conf file. See the PostgreSQL manual for full details. If you choose to require password authentication for Slony-I connections then you have two options on where slon can obtain the passwords from.
You can store the passwords as part of the conninfo string passed to the SLONIK STORE PATH statement. This means that database passwords are stored inside of the database in cleartext.
You can setup a .pgpass file on each node you are running slon on. slon will then retreive the passwords from the .pgpass file. You must make sure that each node running slon have passwords for all paths.
In order to be able to clearly identify which logical roles are being used, it seems advisable to set up users specifically for use by replication, one or more slony users.
As already discussed, these users may have specific permissions attached to indicate what capabilities they are intended to have.
It is also useful for these users to be present so that system monitoring and log monitoring processes are apprised of "who" is doing things in the environment.