I had reason to create a new, read only user on one of my databases this week, which caused me to have to go digging for the function I generally use to do this. In an effort to not lose it again, I’m posting it here for for all to use:
CREATE OR REPLACE FUNCTION pg_grant(usr text, prv text, ptrn text, nsp text)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
obj record;
num integer;
BEGIN
num:=0;
FOR obj IN SELECT relname FROM pg_class c
JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE
relkind in ('r','v','S') AND
nspname = nsp AND
relname LIKE ptrn
LOOP
EXECUTE 'GRANT ' || prv || ' ON ' || obj.relname || ' TO ' || usr;
num := num + 1;
END LOOP;
RETURN num;
END;
$$;
The basic idea is to pass in a database user, a privilege to grant to the objects, a “LIKE” based string matching pattern (% mean all matches), and a desired schema name. This information is the used to generate a series of grant statements which can then be executed by the function to grant the desired changes. For example, if we added a reports user into the pagila sample database, and wanted to give them read-only access to everything, it would look like this:
pagila=# create user reports;
CREATE ROLE
pagila=# select pg_grant('reports','select','%','public');
pg_grant
----------
39
(1 row)
Simple eh? There are a number of things one could do with this function, such as making a 3 argument version that hardcodes the public schema, or changing the function to either raise a notice or return a result set of all of the objects that get updated, or even adding an option to drop privieges rather than grant them, but the simple form worked for me in this case, and should be enough to get most people started. Hope this helps.