I'm not quite sure who first observed this; props to them whomever it was...
It is possible to significantly diminish the set of privileges required by some of the Slony-I connections. We have long been repeating (almost mantra-like) that Slony-I connections "must run as a PostgreSQL superuser." This is no longer entirely true, and I have documented the precise extent to which it is not true :-). First observation: The connection that a slon makes to the database whose replication it manages must indeed be as a PostgreSQL superuser. The slon must, when managing its node, be able to do such things as: - Altering replicated tables - Fiddling with some stuff in pg_catalog As such, that "role" still remains pretty "super." Some of the things Jan has been proposing for PG 8.3 might change that, but we're certainly not there yet. But now comes the "weak" slony1 user... The connections made to remote nodes, that is, all of the connections whose conninfo is stored via the command "STORE PATH" into the table sl_path, may be rather weaker in their system access. When I started thinking about it, I thought: "Hey! They don't really need more than read access to the Slony-I schema. That's what they consult for information, and they don't need more than read access." That's *close* to true; there are two exceptions: 1. The slon needs to be able to write to a remote node's sl_nodelock table (and an associated sequence); that's what keeps us from having too many slons managing a node. 2. At subscription time, the slon needs to be able to read data from the tables it is pulling data from. There's this COPY statement, you see... I modified the "testbed" to have the STORE PATH statements use a 'weak' user defined with exactly the above set of limited permissions. And all has been well thus far... It warrants doing some more testing, but for those that have been worried about Slony-I involving way too much superuser access, I can report that we can cut that down substantially. Future developments may improve things further... Apologies that I haven't gotten "new site" stuff done today; this is what I was fighting with... _______________________________________________ Slony1-general mailing list [email protected] http://gborg.postgresql.org/mailman/listinfo/slony1-general
