DO $$
DECLARE
_tables text := (
SELECT
string_agg(
'GRANT SELECT, INSERT, UPDATE ON '
|| table_schema || '.' || table_name
||' to my_user;',
E'\n')
FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema = 'my_schema'
);
_sequences text := (
SELECT
string_agg(
'GRANT USAGE ON ' || n.nspname || '.' || c.relname ||' to my_user;',
E'\n')
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind='S' AND n.nspname = 'my_schema'
);
BEGIN
RAISE NOTICE E'\n%\n%', _sequences, _tables;
END;$$
Note: I personally (normally) don't like to allow deletes on my tables from apps - feel free to add a DELETE grant if you need it.
No comments:
Post a Comment