[GENERAL] Reset privileges to builtin defaults

Hi, I've been charged with the administration of a couple of big
databases in an old 8.1 cluster. The databases have many
administrative issues, privileges being the most prominent for my job.
All objects have privileges for specific personal end-user roles
(dozens and dozens of privileges each object) instead of using profile
roles with the correct privileges and membership granted to specific
personal roles. I have identified the profiles and granted them to the
personal roles. Now I need to reset all privileges on all objects to
the builtin defaults and grant the correct privileges to the profile
roles.

Part of the job is also migrating to version 9.6 (current stable as of
today), but as far as I can see in the documentation there's no clean
way of achieving my task not even in that version, so postponing it
will not help. So I decided to take a risk and modify system catalogs
in a test 8.1 environment with success up to what I can see. What I
did is to create three functions that take an OID: one for databases,
one for functions and the last for relations (ordinary table,
sequence, view with extended capability for materialized view,
composite type or foreign table only for the case I'd use these
functions in a more recent cluster some time in the future). They all
perform three basic things:
1) Set the ACL field of the object to NULL in the correct catalog.
2) Delete the ACL dependencies of the object on any role by deleting
from "pg_shdepend".
3) Returning a useful thing: TRUE when privileges are set to the
builtin defaults (wether or not the function actually had to do
something) and FALSE when something goes wrong. They're declared
STRICT, so NULL is returned for NULL input.

These functions could be improved by marking them SECURITY DEFINER and
letting a role reset the privileges of it's owned objects. Also, other
functions should be added for other kind of objects like foreign
servers and such. Certainly, many things might also be useful for
general use but for my current purposes they suffice (although I
wouldn't bother to make these changes as an exercice if someone is
interested and, of course, if I'm on the riht way). I added two kind
of "properties" to the functions defined by "-- @PROTECT: OWNERSHIP
PRIVILEGES" in the code. That makes these functions immune to the
privilege reset function for functions (funny English). You might also
guess I have another function to change the ownership, but that's
another story. Also, superuser owned SECURITY DEFINER functions are
untouched and managed manually for now and only because of my
particular needs.

I tried these functions in the test environment and they seem to work
properly in every use case I could think of, although I would be more
comfortable if an experienced someone gives me it's impressions on
these before I go into production. Please, do!

Here goes the code:

/* BEGIN CODE */
CREATE OR REPLACE FUNCTION reset_privileges_on_database(oid) RETURNS
BOOLEAN AS $BODY$
-- @PROTECT: OWNERSHIP PRIVILEGES

    -- Reset privileges on the database
  UPDATE pg_database
    SET datacl = NULL
    WHERE
      datacl IS NOT NULL AND  -- Only change when necessary
      oid = $1;               -- Change the requested database

    -- Delete all ACL dependencies that might have been introduced
  DELETE
    FROM pg_shdepend AS s
    USING pg_class AS c -- USING list item #1
    WHERE
      ( -- Join conditions for the USING list items
          s.classid = c.oid -- When introduced USING list item #1
        ) AND
      ( -- Specific conditions that normally make up the whole WHERE
clause in a SELECT statement
          s.dbid = 0                AND -- We're deleting a shared
object's (databases) dependencies
          s.deptype = 'a'           AND -- The dependencies we're
deleting are ACLs
          c.relname = 'pg_database' AND -- The shared object we're
deleting dependencies of is a database
          s.objid = $1                  -- Only delete dependencies of
the requested database
        );

    -- Return value
  SELECT EXISTS (
      SELECT 1
        FROM pg_database
        WHERE oid = $1 -- Only the requested database
    );
$BODY$ LANGUAGE sql VOLATILE STRICT;
ALTER FUNCTION reset_privileges_on_database(oid) OWNER TO postgres;
REVOKE ALL ON FUNCTION reset_privileges_on_database(oid) FROM public;
COMMENT ON FUNCTION reset_privileges_on_database(oid) IS
'Diego Molina (20170427): Reset all privileges on a database given by
its OID to the builtin defaults.
Return values:
  * NULL: if NULL argument.
  * TRUE: Privileges have been reset.
  * FALSE: Privileges have not been reset. Reason: the given database
does not exist.';

CREATE OR REPLACE FUNCTION reset_privileges_on_function(oid) RETURNS
BOOLEAN AS $BODY$
-- @PROTECT: OWNERSHIP PRIVILEGES

    -- Reset privileges on the function
  UPDATE pg_proc AS p
    SET proacl = NULL
    FROM pg_authid AS o -- FROM list item #1 (function's owner)
    WHERE
      ( -- Join conditions
          p.proowner = o.oid -- When introduced FROM list item #1
        ) AND
      ( -- Specific conditions that normally make up the whole WHERE clause
          proacl IS NOT NULL                AND -- Only change when necessary
          NOT (o.rolsuper AND p.prosecdef)  AND -- Avoid changing
privileges on superuser-owned security definer functions
          NOT p.proisagg                    AND -- Changing privileges
on aggregates is pointless
          p.oid = $1                        AND -- Change the requested function
            -- Functions may explicitly be excluded from privilege reset
          regexp_replace(p.prosrc,
'@PROTECT:[[:space:]]*(.*[[:space:]])?PRIVILEGES([[:space:]].*)?$',
'', 'i') = p.prosrc
        );

    -- Delete all ACL dependencies that might have been introduced
  DELETE
    FROM pg_shdepend AS s
    USING
      pg_class    AS c, -- USING list item #1
      pg_database AS d, -- USING list item #2
      pg_proc     AS p, -- USING list item #3
      pg_authid   AS o  -- USING list item #4 (function's owner)
    WHERE
      ( -- Join conditions
          s.classid = c.oid   AND -- When introduced USING list item #1
          s.dbid = d.oid      AND -- When introduced USING list item #2
          s.objid = p.oid     AND -- When introduced USING list item #3
          p.proowner = o.oid      -- When introduced USING list item #4
        ) AND
      ( -- Specific conditions that normally make up the whole WHERE
clause in a SELECT statement
          d.datname = current_database()    AND -- We're deleting a
current database object's (function) dependencies
          s.deptype = 'a'                   AND -- The dependencies
we're deleting are ACLs
          c.relname = 'pg_proc'             AND -- The database object
we're deleting dependencies of is a function
          NOT (o.rolsuper AND p.prosecdef)  AND -- Avoid changing
privileges on superuser-owned security definer functions
          NOT p.proisagg                    AND -- Changing privileges
on aggregates is pointless
          s.objid = $1                      AND -- Only delete
dependencies of the requested function
            -- Functions may explicitly be excluded from privilege reset
          regexp_replace(p.prosrc,
'@PROTECT:[[:space:]]*(.*[[:space:]])?PRIVILEGES([[:space:]].*)?$',
'', 'i') = p.prosrc
        );

    -- Return value
  SELECT EXISTS (
      SELECT 1
        FROM
          pg_proc   AS p INNER JOIN
          pg_authid AS o ON p.proowner = o.oid
        WHERE
          NOT (o.rolsuper AND p.prosecdef)  AND -- Avoid
superuser-owned security definer functions
          NOT p.proisagg                    AND -- Avoid aggregates
          p.oid = $1                        AND -- Only the requested function
            -- Functions may explicitly be excluded from privilege reset
          regexp_replace(p.prosrc,
'@PROTECT:[[:space:]]*(.*[[:space:]])?PRIVILEGES([[:space:]].*)?$',
'', 'i') = p.prosrc
    );
$BODY$ LANGUAGE sql VOLATILE STRICT;
ALTER FUNCTION reset_privileges_on_function(oid) OWNER TO postgres;
REVOKE ALL ON FUNCTION reset_privileges_on_function(oid) FROM public;
COMMENT ON FUNCTION reset_privileges_on_function(oid) IS
'Diego Molina (20170427): Reset all privileges on a function given by
its OID to the builtin defaults.
Return values:
  * NULL: if NULL argument.
  * TRUE: Privileges have been reset.
  * FALSE: Privileges have not been reset. Reason: the given function
does not exist, it is an aggregate function, it is a
    superuser owned SECURITY DEFINER function or the function is
explicitly excluded from privilege reset.';

CREATE OR REPLACE FUNCTION reset_privileges_on_relation(oid) RETURNS
BOOLEAN AS $BODY$
-- @PROTECT: OWNERSHIP PRIVILEGES

    -- Reset privileges on the relation
  UPDATE pg_class
    SET relacl = NULL
    WHERE
      relkind IN ('r', 'S', 'v', 'm', 'c', 'f') AND -- Only operate
on: ordinary table, sequence, view, materialized view, composite type
or foreign table
      relacl IS NOT NULL                        AND -- Only change
when necessary
      oid = $1;                                     -- Change the
requested database

    -- Delete all ACL dependencies that might have been introduced
  DELETE
    FROM pg_shdepend AS s
    USING
      pg_class    AS c, -- USING list item #1
      pg_database AS d, -- USING list item #2
      pg_class    AS o  -- USING list item #3 (the object per se)
    WHERE
      ( -- Join conditions
          s.classid = c.oid AND -- When introduced USING list item #1
          s.dbid = d.oid    AND -- When introduced USING list item #2
          s.objid = o.oid       -- When introduced USING list item #3
        ) AND
      ( -- Specific conditions that normally make up the whole WHERE
clause in a SELECT statement
          d.datname = current_database()              AND -- We're
deleting a current database object's (function) dependencies
          s.deptype = 'a'                             AND -- The
dependencies we're deleting are ACLs
          c.relname = 'pg_class'                      AND -- The
database object we're deleting dependencies of is a function
          o.relkind IN ('r', 'S', 'v', 'm', 'c', 'f') AND -- Only
operate on: ordinary table, sequence, view, materialized view,
composite type or foreign table
          s.objid = $1                                    -- Only
delete dependencies of the requested function
        );

    -- Return value
  SELECT EXISTS (
      SELECT 1
        FROM pg_class
        WHERE
          relkind IN ('r', 'S', 'v', 'm', 'c', 'f') AND -- Only
operate on: ordinary table, sequence, view, materialized view,
composite type or foreign table
          oid = $1
    );
$BODY$ LANGUAGE sql VOLATILE STRICT;
ALTER FUNCTION reset_privileges_on_relation(oid) OWNER TO postgres;
REVOKE ALL ON FUNCTION reset_privileges_on_relation(oid) FROM public;
COMMENT ON FUNCTION reset_privileges_on_relation(oid) IS
'Diego Molina (20170427): Reset all privileges on a relation (ordinary
table, sequence, view, materialized view, composite type or foreign
table only) given by its OID to the builtin defaults.
Return values:
  * NULL: if NULL argument.
  * TRUE: Privileges have been reset.
  * FALSE: Privileges have not been reset. Reason: the given relation
does not exist or it is not an ordinary table,
    sequence, view, materialized view, composite type or foreign table.';
/* END CODE */