Got it, I totally get how tedious it is to piece together permission details for schemas, roles, and objects manually—especially when you need to replicate those permissions elsewhere. Below are practical, database-specific solutions to pull full permission info and generate bulk grant statements you can reuse right away.

PostgreSQL: Retrieve Permissions & Generate Grant Statements #

1. Fetch Full Permission Details #

This query combines data from PostgreSQL's system catalogs to show you which roles have access to which schemas, tables, views, and sequences, along with their specific permissions:

SELECT
    r.rolname AS role_name,
    n.nspname AS schema_name,
    c.relname AS object_name,
    CASE c.relkind
        WHEN 'r' THEN 'TABLE'
        WHEN 'v' THEN 'VIEW'
        WHEN 'S' THEN 'SEQUENCE'
        WHEN 'f' THEN 'FUNCTION'
        ELSE c.relkind::TEXT
    END AS object_type,
    array_to_string(ARRAY(
        SELECT privilege_type
        FROM pg_catalog.pg_permissions
        WHERE grantee = r.oid
          AND (pg_catalog.pg_permissions.objoid = c.oid OR pg_catalog.pg_permissions.objoid = n.oid)
    ), ', ') AS permissions
FROM
    pg_catalog.pg_roles r
LEFT JOIN
    pg_catalog.pg_auth_members m ON r.oid = m.member
LEFT JOIN
    pg_catalog.pg_roles g ON m.roleid = g.oid
LEFT JOIN
    pg_catalog.pg_namespace n ON n.nspowner = g.oid OR n.nspowner = r.oid
LEFT JOIN
    pg_catalog.pg_class c ON c.relnamespace = n.oid
WHERE
    r.rolname NOT IN ('pg_signal_backend', 'pg_read_all_data', 'pg_write_all_data') -- Exclude built-in roles if needed
    AND c.relkind IN ('r', 'v', 'S', 'f') -- Filter object types you care about
ORDER BY
    role_name, schema_name, object_name;

2. Generate Bulk Grant Statements #

If you need to replicate these permissions (e.g., for a staging environment), this query will generate ready-to-execute GRANT statements:

SELECT
    'GRANT ' || array_to_string(ARRAY(
        SELECT privilege_type
        FROM pg_catalog.pg_permissions
        WHERE grantee = r.oid
          AND pg_catalog.pg_permissions.objoid = c.oid
    ), ', ') || ' ON ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' TO ' || quote_ident(r.rolname) || ';' AS grant_statement
FROM
    pg_catalog.pg_roles r
LEFT JOIN
    pg_catalog.pg_auth_members m ON r.oid = m.member
LEFT JOIN
    pg_catalog.pg_roles g ON m.roleid = g.oid
LEFT JOIN
    pg_catalog.pg_namespace n ON n.nspowner = g.oid OR n.nspowner = r.oid
LEFT JOIN
    pg_catalog.pg_class c ON c.relnamespace = n.oid
WHERE
    r.rolname NOT IN ('pg_signal_backend', 'pg_read_all_data', 'pg_write_all_data')
    AND c.relkind IN ('r', 'v', 'S')
    AND EXISTS (
        SELECT 1 FROM pg_catalog.pg_permissions
        WHERE grantee = r.oid AND pg_catalog.pg_permissions.objoid = c.oid
    )
ORDER BY
    grant_statement;

MySQL: Retrieve Permissions & Generate Grant Statements #

1. Fetch Full Permission Details #

Use MySQL's information_schema tables to get a holistic view of user permissions across schemas and objects:

SELECT
    u.user AS role_name,
    u.host AS role_host,
    s.table_schema AS schema_name,
    s.table_name AS object_name,
    s.table_type AS object_type,
    GROUP_CONCAT(DISTINCT s.privilege_type SEPARATOR ', ') AS permissions
FROM
    information_schema.USER u
LEFT JOIN
    information_schema.SCHEMA_PRIVILEGES s ON u.user = s.grantee AND u.host = SUBSTRING_INDEX(s.grantee, '@', -1)
LEFT JOIN
    information_schema.TABLE_PRIVILEGES t ON u.user = t.grantee AND u.host = SUBSTRING_INDEX(t.grantee, '@', -1) AND s.table_schema = t.table_schema
GROUP BY
    u.user, u.host, s.table_schema, s.table_name, s.table_type
ORDER BY
    u.user, s.table_schema, s.table_name;

2. Generate Bulk Grant Statements #

This query generates GRANT statements for table-level permissions—adjust it for schema/column-level permissions as needed:

SELECT
    CONCAT(
        'GRANT ', GROUP_CONCAT(DISTINCT privilege_type SEPARATOR ', '),
        ' ON `', table_schema, '`.`', table_name, '` ',
        'TO ''', SUBSTRING_INDEX(grantee, '@', 1), '''@''', SUBSTRING_INDEX(grantee, '@', -1), ''';'
    ) AS grant_statement
FROM
    information_schema.TABLE_PRIVILEGES
GROUP BY
    grantee, table_schema, table_name
ORDER BY
    grant_statement;

Quick Notes: #

• Permissions Required: You'll need SELECT access to the system catalogs (PostgreSQL) or information_schema (MySQL) to run these queries.
• Customization: Filter out built-in roles, adjust object types, or add column-level permissions by extending the queries (e.g., join COLUMN_PRIVILEGES in MySQL).
• Validation: Always review the generated grant statements before executing them—double-check for typos or overly broad permissions.

内容的提问来源于stack exchange，提问作者Rajesh