Found this a few minutes ago and already lost the source URL. With PostgreSQL requiring you to add data manipulation privileges on a table-by-table basis, it’s handy to have a method to spit out a list of GRANT commands to run when you want to give a user a privilege across the entire database.

SELECT 'GRANT SELECT ON '||schemaname||'.'||tablename||' TO fillinusername;' 
FROM pg_tables 
WHERE schemaname IN ('fillindatabasename', 'fillinschema') 
ORDER BY schemaname, tablename; 

In the above, you need to change:

• fillinusername: The username you wish to grant privileges to.
• fillindatabasename: The database within which you will be granting table privileges upon.
• fillinschema: The schema the tables are contained within (usually “public”).

This will spit out a list like:

                                  ?column?
-----------------------------------------------------------------------------
 GRANT SELECT ON fillinschema.table1 TO fillinusername;
 GRANT SELECT ON fillinschema.table2 TO fillinusername;
 GRANT SELECT ON fillinschema.table3 TO fillinusername;
 GRANT SELECT ON fillinschema.table4 TO fillinusername;
 GRANT SELECT ON fillinschema.table5 TO fillinusername;
 ...

Copy and paste that list back into PostgreSQL to execute the GRANT commands to get your user privileges set up. (If it’s a large list, output the command to a file and run them that way.)