Skip to content

Instantly share code, notes, and snippets.

@justinlewis
Last active January 10, 2018 22:22
Show Gist options
  • Save justinlewis/5247914 to your computer and use it in GitHub Desktop.
Save justinlewis/5247914 to your computer and use it in GitHub Desktop.
A simple function and some commands to grant privileges to every table in a PostgreSQL database. * each file is a different method. You don't need need to use them all in conjunction.
Use the commands below to set privileges using the PSQL terminal commands.
TABLES:
for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done
SEQUENCES:
for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done
VIEWS:
for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do psql -c "alter table $tbl owner to NEW_OWNER" YOUR_DB ; done
-- Function: user_management(text)
-- DROP FUNCTION user_management(text);
CREATE OR REPLACE FUNCTION user_management(text)
RETURNS void AS
'BEGIN execute $1; END;'
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION user_management(text)
OWNER TO postgres;
COMMENT ON FUNCTION user_management(text) IS 'run this command to grant SELECT on all tables in the db.
select user_management('grant select on ' || tablename || ' to reader;') from pg_tables where schemaname = 'public';';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment