GRANT ALL PRIV TO A ROLE
 
 ###### start of grantall.sh #######
 
 #!/bin/ash
  SQL="SELECT relname FROM pg_class WHERE (relkind = 'r' OR relkind = 'S')
  AND relname !~ '^pg_' ORDER BY relname"
  OBJ=`psql -t -c "${SQL}" $1`
  # OBJ=`echo ${OBJ} | sed 's/EOF//g'`
  OBJ=`echo ${OBJ} | sed 's/ /, /g'`
 # SQL="REVOKE ALL ON ${OBJ} FROM role1"
  SQL="GRANT ALL ON ${OBJ} TO role1"
  echo ${SQL}
  psql -c "${SQL}" $1
 
 
 
 ###### end of grantall.sh #######
 -- test.sql
 --
 -- Outputs connected ip address to a CSV file that
 -- is easily imported into spreadsheet software.
 \o report.csv
 \a
 \f ,
 select connected_ip_address from asset
 where os_type like :ostype;
 \o
 
 -- test.sh
 #!/bin/sh
 # test.sh
 #
 # Wrapper script for test.sql
 echo "Please enter OS Type:"
 read ostype
 psql --set ostype=\'$ostype\' -f test.sql em_db emsowner
 
 -- test.sh
 #!/bin/sh
 # test.sh
 #
 # Wrapper script for test.sql
 echo "Please enter OS Type:"
 read ostype
 psql --set ostype=\'$1\' -f test.sql em_db emsowner
 
 List tables:
 
 SELECT relname FROM pg_class WHERE relname !~ '^(pg_|sql_)' AND relkind = 'r';
 
 -- using INFORMATION_SCHEMA:
 
 SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema');
 
 List Views:
 SELECT viewname FROM pg_views WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND viewname !~ '^pg_';
 
 -- using INFORMATION_SCHEMA:
 
 SELECT table_name FROM information_schema.tables WHERE table_type = 'VIEW' AND table_schema NOT IN ('pg_catalog', 'information_schema') AND table_name !~ '^pg_';
 
 List users:
 SELECT usename FROM pg_user;
 List Indexes:
 SELECT relname FROM pg_class WHERE oid IN ( SELECT indexrelid FROM pg_index, pg_class WHERE pg_class.relname='test2' AND pg_class.oid=pg_index.indrelid AND indisunique != 't' AND indisprimary != 't' );
 
 List CONSTRAINTs:
 SELECT relname FROM pg_class WHERE oid IN ( SELECT indexrelid FROM pg_index, pg_class WHERE pg_class.relname='test' AND pg_class.oid=pg_index.indrelid AND ( indisunique = 't' OR indisprimary = 't' ) );
 
 -- with INFORMATION_SCHEMA:
 
 SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'test';
 List table fields:
 SELECT a.attname FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'test' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
 
 -- with INFORMATION_SCHEMA:
 
 SELECT column_name FROM information_schema.columns WHERE table_name = 'test';
 
 SELECT a.attnum AS ordinal_position, a.attname AS column_name, t.typname AS data_type, a.attlen AS character_maximum_length, a.atttypmod AS modifier, a.attnotnull AS notnull, a.atthasdef AS hasdefault FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'test' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum;
 
 -- with INFORMATION_SCHEMA:
 
 SELECT ordinal_position, column_name, data_type, column_default, is_nullable, character_maximum_length, numeric_precision FROM information_schema.columns WHERE table_name = 'test' ORDER BY ordinal_position;
 
 List sequences:
 SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN ( SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema' );
 
 List TRIGGERs:
 SELECT trg.tgname AS trigger_name FROM pg_trigger trg, pg_class tbl WHERE trg.tgrelid = tbl.oid AND tbl.relname = 'newtable';
 
 -- with INFORMATION_SCHEMA:
 
 SELECT DISTINCT trigger_name FROM information_schema.triggers WHERE event_object_table = 'newtable' AND trigger_schema NOT IN ('pg_catalog', 'information_schema');
 
 SELECT trg.tgname AS trigger_name FROM pg_trigger trg, pg_class tbl WHERE trg.tgrelid = tbl.oid AND tbl.relname = 'newtable';
 
 -- with INFORMATION_SCHEMA:
 
 SELECT DISTINCT trigger_name FROM information_schema.triggers WHERE event_object_table = 'newtable' AND trigger_schema NOT IN ('pg_catalog', 'information_schema');
 
 SELECT trg.tgname AS trigger_name, tbl.relname AS table_name, p.proname AS function_name, CASE trg.tgtype & cast(2 as int2) WHEN 0 THEN 'AFTER' ELSE 'BEFORE' END AS trigger_type, CASE trg.tgtype & cast(28 as int2) WHEN 16 THEN 'UPDATE' WHEN 8 THEN 'DELETE' WHEN 4 THEN 'INSERT' WHEN 20 THEN 'INSERT, UPDATE' WHEN 28 THEN 'INSERT, UPDATE, DELETE' WHEN 24 THEN 'UPDATE, DELETE' WHEN 12 THEN 'INSERT, DELETE' END AS trigger_event FROM pg_trigger trg, pg_class tbl, pg_proc p WHERE trg.tgrelid = tbl.oid AND trg.tgfoid = p.oid AND tbl.relname !~ '^pg_';
 
 -- with INFORMATION_SCHEMA:
 
 SELECT * FROM information_schema.triggers WHERE trigger_schema NOT IN ('pg_catalog', 'information_schema');
 
 List FUNCTIONs:
 SELECT proname FROM pg_proc pr, pg_type tp WHERE tp.oid = pr.prorettype AND pr.proisagg = FALSE AND tp.typname <> 'trigger' AND pr.pronamespace IN ( SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema' );
 
 -- with INFORMATION_SCHEMA:
 
SELECT routine_name FROM information_schema.routines WHERE specific_schema NOT IN ('pg_catalog', 'information_schema') AND type_udt_name != 'trigger';