#!/bin/bash


# Configuracion

db=$1
esquema=$2
archivo=set_permisos_$db_$esquema.sql
archivo_log=set_permisos_$db_$esquema.log
archivo_err=set_permisos_$db_$esquema.err

# Fin Seccion Configuracion


> $archivo

psql -d $db --no-align --tuples-only -c\
"--Para cambiar el owner de las tablas.
select
    'alter table \"'
    || t.table_schema
    || '\".\"'
    || t.table_name
    || '\" owner to \"'
    || t.table_catalog
    || '_'
    || t.table_schema
    || '_adm\";'
from
    information_schema.tables t
where
    t.table_schema::text = '$esquema'::text
    and t.table_type = 'BASE TABLE'::text
order by
    t.table_name asc
;" >> $archivo

psql -d $db --no-align --tuples-only -c\
"--Para cambiar el owner de las secuencias.
select
	'alter table \"'
	|| s.sequence_schema
	|| '\".\"'
	|| s.sequence_name
	|| '\" owner to \"'
	|| s.sequence_catalog
	|| '_'
	|| s.sequence_schema
	|| '_adm\";'
from
	information_schema.sequences s
where
	s.sequence_schema::text = '$esquema'::text
order by
	s.sequence_name asc
;" >> $archivo

psql -d $db --no-align --tuples-only -c\
"--Para cambiar el owner a las vistas.
select
	'alter table \"'
	|| v.table_schema
	|| '\".\"'
	|| v.table_name
	|| '\" owner to \"'
	|| v.table_catalog
	|| '_'
	|| v.table_schema
	|| '_adm\";'
from
	information_schema.views v
where
	v.table_schema::text = '$esquema'::text
order by
	v.table_name asc
;" >> $archivo

psql -d $db --no-align --tuples-only -c\
"--Para cambiar el owner a los tipos.
select
	'alter type \"'
	|| t.user_defined_type_schema
	|| '\".\"'
	|| t.user_defined_type_name
	|| '\" owner to \"'
	|| t.user_defined_type_catalog
	|| '_'
	|| t.user_defined_type_schema
	|| '_adm\";'
from
	information_schema.user_defined_types t
where
	t.user_defined_type_schema::text = '$esquema'::text
order by
	t.user_defined_type_name asc
;" >> $archivo

psql -d $db --no-align --tuples-only -c\
"--Para cambiar el owner a las funciones.
select
	'alter function \"'
	|| n.nspname
	|| '\".\"'
	|| p.proname
	|| '\"('
	|| pg_get_function_identity_arguments(p.oid) || ')'
	|| ' owner to \"'
	|| current_database()
	|| '_'
	|| n.nspname
	|| '_adm\";'
from
	pg_catalog.pg_namespace n
	join pg_catalog.pg_proc p on (p.pronamespace = n.oid)
where
	n.nspname = '$esquema'
order by
	p.proname
;" >> $archivo

psql -d $db -f $archivo 1>$archivo_log 2>$archivo_err

psql -d $db -c "\
--Permisos sobre las tablas.
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ${esquema} TO GROUP ${db}_${esquema}_app;
GRANT SELECT ON ALL TABLES IN SCHEMA ${esquema} TO GROUP ${db}_${esquema}_select;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ${esquema} TO GROUP ${db}_${esquema}_adm;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA ${esquema} from public;

--Permisos sobre las secuencias.
GRANT SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA ${esquema} TO GROUP ${db}_${esquema}_app;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA ${esquema} TO GROUP ${db}_${esquema}_select;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA ${esquema} TO GROUP ${db}_${esquema}_adm;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA ${esquema} from public;

--Permisos sobre las funciones.
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ${esquema} TO GROUP ${db}_${esquema}_adm;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ${esquema} TO GROUP ${db}_${esquema}_app;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA ${esquema} from public;
"
