Redshift grants - doing this programmatically

Question

I'm new to the realm of Redshift, but not databases themselves. I'm trying to figure out a way to programmatically re-issue grants in my redshift cluster. It was agreed upon early on that users that have been granted access to a schema will have select or dml access based on their job role and region. I'd like to do something similar to this, but I can't seem to get this to compile in my RS cluster. Cluster is v1.0.18228

CREATE OR REPLACE FUNCTION adminscripts.update_groups(p_schema varchar)
        LANGUAGE plpgsql
        VOLATILE
AS $function$    
      DECLARE 
        v_schema_sel_gro varchar(100) := p_schema||'_sel_group';
        v_schema_dml_gro varchar(100) := p_schema||'_dml_group';
        v_schema_adm_gro varchar(100) := p_schema||'_adm_group';
        
        v_gro_exists int; 
        db          VARCHAR(100); 
        endtime     datetime; 
        SQL text; 
        unload_id    INT; 
        unload_time timestamp; 
        
BEGIN 

    select count(*) into v_gro_exists
    from pg_group where groname = v_schema_sel_gro ;
    
    IF v_gro_exists = 0 THEN 
        create group v_schema_sel_gro ;
    ELSE
        RAISE info 'SELECT Group already exits '; 
    END IF;
    
    GRANT USAGE ON SCHEMA p_schema TO GROUP v_schema_sel_gro;
    GRANT SELECT ON ALL TABLES IN SCHEMA p_schema TO GROUP v_schema_sel_gro;
    ALTER DEFAULT PRIVILEGES IN SCHEMA p_schema GRANT SELECT ON TABLES TO GROUP v_schema_sel_gro;
    
END; 
     $function$
;

Any help would be fantastic with this! Thanks in advance.

Jeremy

What do you mean by "can't seem to get this to compile"? Are there any specific error messages, or a particular line that is giving problems? — John Rotenstein
When using SQLDeveloper/J, the procedure will never compile. I always get an error and the procedure is not created. — JeremyS
Here is the error [Amazon](500310) Invalid operation: unterminated dollar-quoted string at or near "$$ DECLARE v_schema_sel_gro varchar(100) := p_schema||'_sel_group'" Position: 86; [SQL State=42601, DB Errorcode=500310] — JeremyS

John Rotenstein John Rotenstein · Accepted Answer · 2020-09-03T21:15:32

The syntax is not compatible with Amazon Redshift. I don't know whether the content of the function will work in Redshift, but the declaration portion is certainly wrong.

Amazon Redshift Scalar User-Defined Functions use CREATE FUNCTION like this:

create function f_sql_greater (float, float)
  returns float
stable
as $$
  select case when $1 > $2 then $1
    else $2
  end
$$ language sql;

However, they cannot impact the contents of the database.

Therefore, you are probably wanting to use Stored Procedures, which uses CREATE PROCEDURE:

CREATE OR REPLACE PROCEDURE test()
AS $$
BEGIN
  SELECT 1 a;
END;
$$
LANGUAGE plpgsql
;

The documentation also specifically references the error message you are receiving:

Some clients might throw the following error when creating an Amazon Redshift stored procedure.

ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

This error occurs due to the inability of the client to correctly parse the CREATE PROCEDURE statement with semicolons delimiting statements and with dollar sign ($) quoting. This results in only a part of the statement sent to the Amazon Redshift server. You can often work around this error by using the Run as batch or Execute selected option of the client.

For more details, see: Overview of stored procedures in Amazon Redshift - Amazon Redshift

Redshift grants - doing this programmatically

1 Answers