PostgreSQL: Permission to execute function (that inserts into a table) but no permission to insert directly

That comment on MSDN simply means that there are some situations (such as sometimes when using dynamic SQL) where granting a user EXECUTE on the stored procedure will not necessarily be enough to allow them to execute it.

What you want cannot be done - if they are able to run a stored procedure, they will be able to do whatever the stored procedure does - INSERT, DELETE, UPDATE, whatever. Even SELECTs can make changes via SELECT INTO. And there's always temporary tables or table variables to complicate any attempts to identify a list of safe/unsafe sps.

My advice is to narrow down as much as possible what the read-only user should actually need to read, create a db role with EXECUTE permissions to just those objects, and start from there. And I hope you've not been giving EXECUTE permissions to the Public role, because if so you'll have to DENY EXECUTE on all the other stored procedures as well.

Mass GRANT/REVOKE is only available on PostgreSQL 9.0+.

Instead, you can create the functions pg_grant() and pg_revoke() found at http://blog.bhushangahire.net/2009/06/17/grant-privileges-to-all-tables-in-a-database-for-postgresql/

The parameters to both functions are:

1. User Role
2. Permissions
3. Object name, may be a pattern. "%" affects all objects
4. Schema name

You will need to run the command on each database and each schema. The syntax looks like this:

SELECT pg_grant('web_user','SELECT,INSERT,UPDATE,DELETE','%','public');

If you like, you may be able to change this line in the function definition:

nspname = $4 AND

to this:

nspname like $4 AND

in order to be able to specify schema as a wildcard, but I haven't tested it.