linux/pg-show-db-access

   1 #!/store/bin/perl -w
   2 #
   3 # Author: Petter Reinholdtsen <pere@td.org.uit.no>
   4 # Date:   1998-09-28
   5 #
   6 # Make list of 'grant' commands based on current db permissions.
   7 #
   8 # Use './pg-show-db-access.pl |sort +5 +3' to get a nice list
   9
  10 sub usage {
  11     print "$0 [dbname [dbhost]]\n";
  12 }
  13
  14 use vars qw($dbhost $db $cmd);
  15
  16 ($db, $dbhost) = ("", "");
  17 $db = $ARGV[0] if ( defined $ARGV[0] );
  18 $dbhost = "-h $ARGV[1]" if ( defined $ARGV[1] );
  19
  20 $cmd = "psql -t -c \\\\z $dbhost $db";
  21
  22 open(CMD, "$cmd |") || die "Unable to execute \"$cmd\"";
  23
  24 while (<CMD>) {
  25     chomp;
  26     my ($table, $perm) = m/\s+\|\s+(\w+)\s+\|\s+\{(.+)\}\s+\|/;
  27     if ( defined $table ) {
  28         my @perms = split(/,/, $perm);
  29         for $perm (@perms) {
  30             my ($username, $access) = $perm =~ m/(\w*)=(\w+)/;
  31             $username = "public" unless ($username);
  32             if (defined $access) {
  33                 my %ACL;
  34                 $ACL{SELECT} = 1 if ($access =~ m/r/);
  35                 $ACL{INSERT} = 1 if ($access =~ m/a/);
  36                 if ($access =~ m/w/) {
  37                     $ACL{DELETE} = 1;
  38                     $ACL{UPDATE} = 1;
  39                     $ACL{INSERT} = 1;
  40                 }
  41                 # XXX Not sure what 'R' means, skipping it
  42                 # $ACL{PLACE_RULES} = 1 if ($access =~ m/R/);
  43
  44                 for $c (sort keys %ACL) {
  45                     print "GRANT $c ON $table TO $username;\n";
  46
  47                 }
  48             }
  49         }
  50     }
  51 }
  52
  53 close(CMD);