Re: Granting read-only access to an existing database?

Eric Jain wrote:
>
> I came across some PL/pgSQL procedures for doing batch GRANTs, but
> they appear to be outdated (i.e. don't work with 8.3) and are "run at
> your own risk". There was also mention that pgAdmin had a function to
> batch GRANTs, but I couldn't find any such thing in the current
> version...

Must be pretty old if they don't work in 8.3. I've attached code for a
plpgsql exec_all function that matches table-names etc per schema.
Should be obvious enough how to wrap it for a grant_all or revoke_all
function. It assumes you've got a "util" schema to put it in.

--
  Richard Huxton
  Archonet Ltd
--
-- This code is being placed in the public domain - R.Huxton 2008
--

CREATE OR REPLACE FUNCTION util.exec_all(objtype name, schname name, objname name, cmd text) RETURNS text
    AS $$
DECLARE
    obj_qry TEXT := '';
    r   RECORD;
    sql TEXT;
    out TEXT;
BEGIN
    out := cmd || ': ';

    -- Tables includes views
    IF objtype = 'tables' THEN
        obj_qry := $a$SELECT table_name AS nm FROM information_schema.tables WHERE table_schema=$a$ ||
quote_literal(schname)|| $a$ AND table_name LIKE $a$ || quote_literal(objname); 
    ELSIF objtype = 'tables-base' THEN
        obj_qry := $a$SELECT table_name AS nm FROM information_schema.tables WHERE table_type='BASE TABLE' AND
table_schema=$a$|| quote_literal(schname) || $a$ AND table_name LIKE $a$ || quote_literal(objname); 
    ELSIF objtype = 'views' THEN
        obj_qry := $a$SELECT table_name AS nm FROM information_schema.tables WHERE table_type='VIEW' AND
table_schema=$a$|| quote_literal(schname) || $a$ AND table_name LIKE $a$ || quote_literal(objname); 
    ELSIF objtype = 'sequences' THEN
        obj_qry := $a$SELECT sequence_name AS nm FROM information_schema.sequences WHERE sequence_schema=$a$ ||
quote_literal(schname)|| $a$ AND sequence_name LIKE $a$ || quote_literal(objname); 
    END IF;

    FOR r IN EXECUTE obj_qry LOOP
        sql := regexp_replace( cmd, E'\\?\\?', quote_ident(r.nm), 'g' );
        sql := regexp_replace( sql, E'\\?', quote_ident(schname) || '.' || quote_ident(r.nm), 'g' );
        EXECUTE sql;
        out := out || r.nm || ' ';
    END LOOP;

    RETURN out;
END;
$$
LANGUAGE plpgsql;

COMMENT ON FUNCTION util.exec_all(name,name,name,text) IS $$
util.exec_all(OBJECT-TYPE, SCHEMA, OBJECT-NAME, COMMAND-TEXT)

Runs COMMAND-TEXT over the database objects matched by the wildcarded pattern in OBJECT-NAME.

OBJECT-TYPE:  tables | tables-base | views | sequences
SCHEMA:       name of a single schema
OBJECT-NAME:  wildcarded name (e.g. 'sales_%')
COMMAND-TEXT: SQL statement(s) to execute, with object-names replaced by '?' or '??'
              ?? is replaced by the object-name
              ?  is replaced by the full <schema-name>.<object-name>

EXAMPLE
=======
util.exec_all('tables', 'reports', '%', 'GRANT SELECT ON ? TO someuser');
$$;