Re: Grant privileges to database

Hi,

Here's a perl script that I use to grant read access to all users and
everything to a few people, it handles tables and associated sequences.

Cheers,
Geoff Russell

P.S. If you use perl/dbi, you don't really have to worry about this stuff
because your programs connect with setuid like privileges. This protects
you against accidents, but not against people who read your perl, extract
the passwords and proceed to wreak havoc!




#!/usr/local/bin/perl -w
use Sys::Hostname;
use DBI;
my $stmt;
my $sth;
#-------------------------------------------------
# Modify the following lines as appropriate
#-------------------------------------------------
my $DBNAME='YOURDATABASE';
my $logname='THE REAL DBA WHO CREATED THE DATA BASE';
my $logpw='A PASSWORD IF YOU HAVE ONE (you could prompt for it if you want)';

my @updaters=('GOD','TOM','BRUCE','OTHER GODLIKE PEOPLE TOO NUMEROUS TO MENTION');
#-------------------------------------------------
# End of configurable stuff
#-------------------------------------------------

$SIG{'INT'}='closem';
$SIG{'QUIT'}='closem';
$SIG{'TERM'}='closem';

&usage unless @ARGV>0;
while (@ARGV) {
    my $a=shift @ARGV;
    if ($a=~/^-l/) {
        $logname=shift @ARGV;
        next;
    }
    if ($a=~/^-u/) {
        @updaters=split(',',shift @ARGV);
        next;
    }
    $DBNAME=$a;
    last;
}
&usage unless $logname && $DBNAME && @updaters>0;

my $url="DBI:Pg:dbname=$DBNAME";
my $dbh = DBI->connect($url,$logname,$logpw,{
        PrintError=>0,
    }) || die($DBI::errstr);
print STDERR "Connected to PostgreSQL $DBNAME database\n" if $dbh;

my $hr;
my @tables=();
my $tstr='';
$sth=$dbh->prepare("select * from pg_tables");
if (!$sth->execute) { die "Can't read pg_tables table: ",$dbh->errstr,"\n"; }
while($hr=$sth->fetchrow_hashref) {
    next if $$hr{tablename}=~/^pg_/;
    next if $$hr{tablename}=~/^pga_/;
    push @tables,$$hr{tablename};
    $tstr.=",$$hr{tablename}";
}
$sth->finish;

my $seqstr='';
$sth=$dbh->prepare("select * from pg_class");
if (!$sth->execute) { die "Can't read pg_class table: ",$dbh->errstr,"\n"; }
while($hr=$sth->fetchrow_hashref) {
    next if $$hr{relkind} ne 'S';
    $seqstr.=",$$hr{relname}";
}
$sth->finish;
$seqstr=~s/,//;
$tstr=~s/,//;
#
# Let anybody read stuff
#
print "TABLES: ($tstr)\n";
print "SEQS: ($seqstr)\n";

$stmt="grant select on $seqstr,$tstr to PUBLIC";
$sth=$dbh->prepare($stmt);
if (!$sth->execute) {
    warn $dbh->errstr;
}
#
# but control other accesses
#
for $p (@updaters) {
    $stmt="grant all on $seqstr,$tstr to $p";
    $sth=$dbh->prepare($stmt);
    if (!$sth->execute) {
        warn $dbh->errstr;
    }
    $sth->finish;

}
&closem;
exit;
#----------------------------------------------------------
# subroutines
#----------------------------------------------------------
sub usage {
    print "Usage: grantall -u u1,u2,u3 data-base\n";
    print "    -u update-user-list   : people in namelist can update\n";
    print " Everyone else can select\n";
    exit 3;
}
sub closem {
    $dbh->disconnect if $dbh;
    exit;
}