<commit_before>-- this file creates all of the roles and inherited permissions
-- for socorro users on the PostgreSQL database.
-- it does NOT set passwords for them, which you need to do
-- separately.  Since it does set dummy passwords for a few
-- roles, if you are setting up Socorro on a non-test machine,
-- you will need to immediately reset those

-- create roles idempotently to avoid errors
-- also set dummy passwords for the core login roles
-- if we are creating them for the first time
DO $d$
DECLARE someroles TEXT[];
	rolepass TEXT[];
	iter INT := 1;
BEGIN

someroles := ARRAY['analyst','breakpad','breakpad_ro','breakpad_rw',
	'breakpad_metrics','processor','monitor','monitoring',
	'nagiosdaemon','ganglia','replicator'];
	
rolepass := ARRAY['<PASSWORD>','<PASSWORD>','processor','monitor'];

WHILE iter < array_upper(someroles, 1) LOOP
	PERFORM 1 FROM information_schema.enabled_roles
	WHERE role_name = someroles[iter];
	
	IF NOT FOUND THEN
		EXECUTE 'CREATE ROLE ' || someroles[iter] ||
			' WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN;';
		IF someroles[iter] = ANY ( rolepass ) THEN
			EXECUTE 'ALTER ROLE ' || someroles[iter] ||
				' WITH PASSWORD ''<PASSWORD>''';
		END IF;
	END IF;
	iter := iter + 1;
	
END LOOP;

END;$d$;

-- analyst role, for read-only connections by analytics users
ALTER ROLE analyst CONNECTION LIMIT 10;
ALTER ROLE analyst SET statement_timeout TO '15min';
ALTER ROLE analyst SET work_mem TO '128MB';<commit_msg>Fixed off-by-one bug in roles.sql<commit_after>-- this file creates all of the roles and inherited permissions
-- for socorro users on the PostgreSQL database.
-- it does NOT set passwords for them, which you need to do
-- separately.  Since it does set dummy passwords for a few
-- roles, if you are setting up Socorro on a non-test machine,
-- you will need to immediately reset those

-- create roles idempotently to avoid errors
-- also set dummy passwords for the core login roles
-- if we are creating them for the first time
DO $d$
DECLARE someroles TEXT[];
	rolepass TEXT[];
	iter INT := 1;
BEGIN

someroles := ARRAY['analyst','breakpad','breakpad_ro','breakpad_rw',
	'breakpad_metrics','processor','monitor','monitoring',
	'nagiosdaemon','ganglia','replicator'];
	
rolepass := ARRAY['breakpad_ro','breakpad_rw','processor','monitor'];

WHILE iter <= array_upper(someroles, 1) LOOP
	PERFORM 1 FROM information_schema.enabled_roles
	WHERE role_name = someroles[iter];
	
	IF NOT FOUND THEN
		EXECUTE 'CREATE ROLE ' || someroles[iter] ||
			' WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN;';
		IF someroles[iter] = ANY ( rolepass ) THEN
			EXECUTE 'ALTER ROLE ' || someroles[iter] ||
				' WITH PASSWORD ''<PASSWORD>''';
		END IF;
	END IF;
	iter := iter + 1;
	
END LOOP;

END;$d$;

-- analyst role, for read-only connections by analytics users
ALTER ROLE analyst CONNECTION LIMIT 10;
ALTER ROLE analyst SET statement_timeout TO '15min';
ALTER ROLE analyst SET work_mem TO '128MB';
{'fraction_non_alphanumeric': 0.10792580101180438, 'fraction_numerical': 0.007419898819561552, 'mean_word_length': 4.5543071161048685, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 7, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '292710', 'n_tokens_mistral': 989, 'n_tokens_neox': 924, 'n_words': 353}