Output PGSQL commands from inside of an anonymous block to a SQL file

Question

I have tried to output some pgsql commands - specifically grants - to an SQL file.

My code looks like this:

DO
$$

DECLARE
rec record;

BEGIN

FOR rec in
SELECT 
    pu.usename,
    pr.rolname,
    tp.privilege_type,
    tp.table_name
FROM 
    pg_user pu
JOIN 
    pg_auth_members ON pu.usesysid = pg_auth_members.member
JOIN 
    pg_roles pr ON pr.oid = pg_auth_members.roleid
LEFT JOIN 
    information_schema.table_privileges tp ON pr.rolname = tp.grantee
  
  LOOP
    
    CONTINUE WHEN rec.table_name is null;
    execute 'copy (''grant ' ||rec.privilege_type|| ' on ' ||rec.table_name|| ' to ' ||rec.usename|| ';'') to
    /mydir/backup.csv with csv';

  END LOOP;
    
END
$$;

In my code, I am rather trying to output the code into a CSV file, because I don't know how to do it with an SQL file, and anyway if it is not possible, I can reuse the CSV file.

I am getting an error:

ERROR:  syntax error at or near "'grant SELECT on t_table to user2;'"
LINE 1: copy ('grant SELECT on t_table to user2;') to
              ^
QUERY:  copy ('grant SELECT on t_table to user2;') to
    /mydir/backup.csv with csv
CONTEXT:  PL/pgSQL function inline_code_block line 27 at EXECUTE

I unfortunately don't know what syntax error I am making and I don't know if I will achieve the goal to output the grants to the CSV or SQL file after having corrected the syntax error.

Could anyone tell me what is the syntax error? And will this execute command really create a CSV file with my grants? Should I create an empty file before?

Answer 1

COPY does not write a string to a file, it writes the contents of a table (or the result of an SQL statement) to a file.

What you could do is collect your GRANT statements in a temporary table and COPY that to a file in the end:

BEGIN
   ...

   CREATE TEMP TABLE tt (x text);

   LOOP
      ...
      INSERT INTO tt VALUES (
         /* prevent SQL injection */
         format(
            'GRANT %s ON %I to %I;',
            rec.privilege_type, rec.table_name, rec.usename
         )
      );
   END LOOP;

   ...
   COPY tt TO '/mydir/backup.csv';
END;

But in your special case, you don't need a loop at all. You can write a query that returns a table of GRANT statements and use it like

COPY (SELECT ...) TO '/mydir/backup.csv';