# spool this stuff to a file from your source db, then run the spooled SQL on your destination db

# recreate your groups – the ones you want

select ‘create group ‘||groname||’;’ from pg_group
where groname like ‘sale%’
or groname like ‘mfg%’;

# generate the create user statements

select ‘create user ‘||trim(usename)||’ password ‘||”’Temp123#”;’ from pg_user , pg_group where
pg_user.usesysid = ANY(pg_group.grolist) and
pg_group.groname in (‘sales_group’, ‘mfg_group”);

# now generate the alter group add user commands

select ‘alter group sales_group add user ‘|| trim(usename) || ‘;’ from pg_user , pg_group where
pg_user.usesysid = ANY(pg_group.grolist) and
pg_group.groname = ‘sales_group’;

select ‘alter group mfg_group add user ‘|| trim(usename) || ‘;’ from pg_user , pg_group where
pg_user.usesysid = ANY(pg_group.grolist) and
pg_group.groname = ‘mfg_group’;

# yes there is more to it if you need to generate the grants to the groups…

More from LonzoDB on AWS

Leave a Reply

Your email address will not be published. Required fields are marked *