#!/usr/bin/perl -w
 use DBI;
 use strict;

 #Database connection 
 my $dbh=DBI->connect('DBI:Pg:dbname=БД','ЛОГИН','ПАРОЛЬ')
      or
       die "connecting: $DBI::errstr";

 my $sth=$dbh->prepare("select usename, passwd, usecreatedb, usesuper, usesysid from pg_shadow;");
 
 $sth->execute;

 print "\n -- Создание всех пользователей PostgreSql --\n";

 my %users;

 while(my @row=$sth->fetchrow)
  {
   my $nam   = $row[ 0 ];
   my $pas   = $row[ 1 ];
   my $credb = $row[ 2 ];
   my $super = $row[ 3 ];
   $users{$row[ 4 ]} = $row[ 0 ];

   if ($pas) {$pas = qq(WITH PASSWORD '$pas');} 

   if ($credb) {
         $credb = "CREATEDB";}
       else {
         $credb = "NOCREATEDB";}

   if ($super) {
         $super = "CREATEUSER";}
      else {
         $super = "NOCREATEUSER";}
  
   if ($pas) { print qq(CREATE USER $nam $pas $credb $super SYSID $row[ 4 ];\n);}
  }

 $sth=$dbh->prepare("select groname, grolist from pg_group where grolist is not null;");

 $sth->execute;

 print "\n -- Создание всех групп и распределение пользователей --\n";

 while(my @row=$sth->fetchrow)
  {
    my $groname = $row[ 0 ];
    my $grolist = $row[ 1 ];

    $grolist=~s/^\{(.+)\}$/$ 1 /;

    my @grolist=split /,/, $grolist;

    print qq(\nCREATE GROUP $groname;\n);

    foreach (@grolist) {
       my $username = $_;
    if ($username) { print qq( ALTER GROUP $groname ADD USER $users{$username};\n); }
    }
  }
 $dbh->disconnect;