On Tuesday 18 March 2008 03:32:56 Glyn Astill wrote:
> Hi Chaps,
>
> We're setting up 3 servers replicating using slony. I was wondering if it'd
> be possible for me to create a set of triggers that fire whenever a user is
> created/dropped/modified on one of the servers that goes and performs the
> same action the other two servers.
>
> Does that sound doable?
>
> I'm not sure what the implications of doing this would be, and I'mm not
> sure the best way to do it.... But this is what I was thinking of doing:
>
> Have a user table containing all the usernames, and every time a user is
> inserted/deleted from the table create/drop the user using a trigger. Then
> put the table in replication using slony.
>
> Obviously I'd have to drop and then recreate the users when I wanted to
> alter them, and I'd have to meke sure permissions were set properly to the
> table.


While I have not tested what I'm about to perpose, it should work fine.

CREATE TABLE replicated_users(
  usename TEXT PRIMARY KEY,
  password TEXT,
  options TEXT);

CREATE OR REPLACE FUNCTION create_user_func() RETURNS TRIGGER AS $$
DECLARE
  v_query TEXT;
BEGIN

if TG_OP = 'INSERT' THEN
  v_query := 'CREATE USER ' || NEW.usename || ' PASSWORD  ''' || 
NEW.password || ''' ' || NEW.options;
ELSIF TG_OP = 'UPDATE' THEN
  v_query := 'ALTER USER ' || NEW.usename || ' PASSWORD ' || 
NEW.password || ' ' || NEW.options;
ELSE 
  v_query := 'DROP USER ' || NEW.usename;
END IF;

PERFORM v_query;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER do_user_stuff AFTER INSERT OR UPDATE OR DELETE ON 
replicated_users FOR EACH ROW EXECUTE PROCEDURE create_user_func() ;

make sure you sue slonik's "STORE TRIGGER" command to enable this trigger to 
fire on the replicas as well. so in theory you will now have it all working.

Note I have not tested the above, and it does not contain any error handleing 
etc, but it should be enough to get you going down the right path.


>
> Are there any better alternatives?
>
> Thanks
>
>
>
>
>       ___________________________________________________________
> Rise to the challenge for Sport Relief with Yahoo! For Good
>
> http://uk.promotions.yahoo.com/forgood/
>
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general



-- 
Darcy Buskermolen
Command Prompt, Inc.
+1.503.667.4564 X 102
http://www.commandprompt.com/
PostgreSQL solutions since 1997