PostgreSQL: Granting permission on any schema and its tables

$ more grant_schema_table.sh

#Name grant_schmea_table.sh

#Author Deepak Murthy

#Created 20100113

#Version 1.0

#*************************************************************************

#Description

#This script is for granting permission on any schema and its tables.

#####

#example – ./grant_schmea_table.sh sandbox_dba role_dba_ro fimdwh select 5432

#example – ./grant_schmea_table.sh sandbox_dba role_dba_rw fimdwh select,insert,delete,update 5432

#####

echo “grant usage on schema $1 to $2; set search_path=$1; \d” |psql -U gpadmin -p $5 -t -A -d $3| grep $1|cut -d”|” -f2 | nawk -v S=$1 ‘BEGIN{RS=”"} { for(i=1;i<=NF;i++) printf S “.” $(i) “,”;print

“” }’|sed -e ‘s/.$//’|nawk -v U=$2 -v P=$4 ‘{print ”grant “P” on “$1″ to “U”;”}’|psql -U gpadmin -p $5 -t -Aq -d $3

$ more grant_schema_table.sh#Name grant_schmea_table.sh#Author Deepak Murthy#Created 20100113#Version 1.0#*************************************************************************#Description#This script is for granting permission on any schema and its tables.######example – ./grant_schmea_table.sh sandbox_dba role_dba_ro fimdwh select 5432#example – ./grant_schmea_table.sh sandbox_dba role_dba_rw fimdwh select,insert,delete,update 5432#####echo “grant usage on schema $1 to $2; set search_path=$1; \d” |psql -U gpadmin -p $5 -t -A -d $3| grep $1|cut -d”|” -f2 | nawk -v S=$1 ‘BEGIN{RS=”"} { for(i=1;i<=NF;i++) printf S “.” $(i) “,”;print”" }’|sed -e ‘s/.$//’|nawk -v U=$2 -v P=$4 ‘{print ”grant “P” on “$1″ to “U”;”}’|psql -U gpadmin -p $5 -t -Aq -d $3