POSTGRES – This script will include roles, their attributes, and the permissions (grants) they have on various objects.

Here is a comprehensive script to achieve this:

“`sql — Script to extract roles and their permissions in PostgreSQL — Function to get role creation statements CREATE OR REPLACE FUNCTION get_roles() RETURNS TEXT AS $$ DECLARE r RECORD; result TEXT := ”;
BEGIN FOR r IN SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls FROM pg_roles LOOP result := result || ‘CREATE ROLE ‘ || r.rolname; IF r.rolsuper THEN result := result || ‘ SUPERUSER’;
ELSE result := result || ‘ NOSUPERUSER’;
END IF; IF r.rolinherit THEN result := result || ‘ INHERIT’; ELSE result := result || ‘ NOINHERIT’;
END IF; IF r.rolcreaterole THEN result := result || ‘ CREATEROLE’; ELSE result := result || ‘ NOCREATEROLE’;
END IF; IF r.rolcreatedb THEN result := result || ‘ CREATEDB’; ELSE result := result || ‘ NOCREATEDB’;
END IF; IF r.rolcanlogin THEN result := result || ‘ LOGIN’; ELSE result := result || ‘ NOLOGIN’;
END IF; IF r.rolreplication THEN result := result || ‘ REPLICATION’; ELSE result := result || ‘ NOREPLICATION’;
END IF; IF r.rolbypassrls THEN result := result || ‘ BYPASSRLS’; ELSE result := result || ‘ NOBYPASSRLS’;
END IF; result := result || ‘;\n’; END LOOP; RETURN result; END; $$ LANGUAGE plpgsql;

— Function to get role membership CREATE OR REPLACE FUNCTION get_role_memberships() RETURNS TEXT AS $$ DECLARE r RECORD; result TEXT := ”;

BEGIN FOR r IN SELECT roleid::regrole, member::regrole FROM pg_auth_members LOOP result := result || ‘GRANT ‘ || r.roleid || ‘ TO ‘ || r.member || ‘;\n’; END LOOP; RETURN result; END; $$ LANGUAGE plpgsql;

— Function to get privileges on schemas, tables, and other objects CREATE OR REPLACE FUNCTION get_grants() RETURNS TEXT AS $$ DECLARE r RECORD; result TEXT := ”;

BEGIN FOR r IN SELECT nspname, relname, (aclexplode(relacl)).grantor::regrole AS grantor, (aclexplode(relacl)).grantee::regrole AS grantee, (aclexplode(relacl)).privilege_type AS privilege FROM pg_class JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid WHERE relacl IS NOT NULL LOOP result := result || ‘GRANT ‘ || r.privilege || ‘ ON ‘ || r.nspname || ‘.’ || r.relname || ‘ TO ‘ || r.grantee || ‘;\n’;
END LOOP;
RETURN result;
END; $$ LANGUAGE plpgsql;

— Get all roles SELECT get_roles() AS roles; — Get role memberships SELECT get_role_memberships() AS role_memberships; — Get grants SELECT get_grants() AS grants;

“` ### Explanation:

1. get_roles Function: – This function extracts the roles and their attributes, formatting them into CREATE ROLE statements.
   2.get_role_memberships Function: – This function retrieves the role memberships and formats them into GRANT statements.
2. get_grants Function:

• This function extracts privileges on schemas, tables, and other objects and formats them into GRANT statements.

1. Calling the Functions: – The final part of the script calls each function to output the roles, memberships, and grants. You can run this script in your PostgreSQL environment, and it will generate SQL statements that you can use to recreate the roles, their memberships, and their permissions.