set client_min_messages to warning;
create extension roleman cascade;
-- whitelist errors
-- no tables so pick a random number and cast to oid
select roleman.grant_table('postgres', 1::oid, array['execute', 'drop']);
ERROR:  bad database permissions for postgres,  table 1, perms execute, drop
CONTEXT:  PL/pgSQL function grant_table(text,regclass,text[]) line 4 at RAISE
select roleman.grant_schema('postgres', 'foo', array['execute']);
ERROR:  bad permissions for postgres,  schema foo, perms execute
CONTEXT:  PL/pgSQL function grant_schema(text,text,text[]) line 4 at RAISE
select roleman.grant_schema_all('postgres', 'foo', 'tables', array['execute', 'drop everything']);
ERROR:  bad database permissions for postgres,  schema foo, type tables, perms execute, drop everything
CONTEXT:  PL/pgSQL function grant_schema_all(text,text,text,text[]) line 5 at RAISE
select roleman.grant_database('postgres', 'foo', array['execute']);
ERROR:  bad database permissions for postgres, dbname foo, perms execute
CONTEXT:  PL/pgSQL function grant_database(text,text,text[]) line 4 at RAISE
select roleman.grant_function('postgres', 1::OID, array['wheeee']);
ERROR:  bad database permissions for postgres,  function 1, perms wheeee
CONTEXT:  PL/pgSQL function grant_function(text,regprocedure,text[]) line 4 at RAISE
select roleman.grant_seq('postgres', 1::oid, array['execute']);
ERROR:  bad database permissions for postgres,  sequence 1, perms execute
CONTEXT:  PL/pgSQL function grant_seq(text,regclass,text[]) line 4 at RAISE
create schema testing;
create table testing.foo(id int);
create table testing."foo(id int)"" drop table testing.foo; --"(id int); 
create table testing."foo(id int)' drop table testing.foo; --"(id int); 
insert into testing.foo values (1);
insert into  testing."foo(id int)"" drop table testing.foo; --" values (2); 
insert into testing."foo(id int)' drop table testing.foo; --" values (3); 
create schema "testing; drop table testing.foo; --";
create table "testing; drop table testing.foo; --".foo();
create schema "testing'; drop table testing.foo; --";
create table "testing'; drop table testing.foo; --".foo();
create schema "testing""; drop table testing.foo; --";
create table "testing""; drop table testing.foo; --".foo();
create sequence testing."foo1"" drop table testing.foo; --";
create function testing.foo(testing."foo(id int)"" drop table testing.foo; --", testing."foo(id int)' drop table testing.foo; --")
returns bool language sql as $$ select true; $$;
create schema testing2;
create table testing2.foo(id int);
create schema testing3;
create table testing3.foo(id int);
-- Create roles for Bobby Tables
-- die with error
select roleman.create_role('bobby"; drop table testing.foo; --', array['login', 'password ''foo''']);
ERROR:  Bad option for role bobby"; drop table testing.foo; --, options were login, password 'foo'
CONTEXT:  PL/pgSQL function create_role(text,text[]) line 9 at RAISE
-- succeed
select roleman.create_role('bobby"; drop table testing.foo; --', array['login', 'noinherit']);
 create_role 
-------------
 t
(1 row)

select roleman.create_role('bobby_tables"; foo', array['inherit', 'nologin']);
 create_role 
-------------
 t
(1 row)

select roleman.create_role('bobby''; drop table testing.foo; --');
 create_role 
-------------
 t
(1 row)

-- succeed no perms
select roleman.role_blank_perms('bobby"; drop table testing.foo; --');
 role_blank_perms 
------------------
 t
(1 row)

set role "bobby'; drop table testing.foo; --";
-- permission denied errors
select * from testing.foo;
ERROR:  permission denied for schema testing
LINE 1: select * from testing.foo;
                      ^
select * from testing.foo(null, null);
ERROR:  permission denied for schema testing
LINE 1: select * from testing.foo(null, null);
                      ^
reset role;
select roleman.grant_schema(r, s, array['all'])
  FROM unnest(array['bobby"; drop table testing.foo; --'::text, 'bobby_tables"; foo', 'bobby''; drop table testing.foo; --']) r
  cross join unnest (array['testing', 'testing''; drop table testing.foo; --', 'testing; drop table testing.foo; --', 'testing"; drop table testing.foo; --']) s;
 grant_schema 
--------------
 
 
 
 
 
 
 
 
 
 
 
 
(12 rows)

select roleman.grant_table(r, t.oid::regclass, array['select'])
  FROM unnest(array['bobby"; drop table testing.foo; --'::text, 'bobby_tables"; foo', 'bobby''; drop table testing.foo; --']) r
 CROSS JOIN
       pg_class t
 WHERE t.relname like '%--%' and t.relkind = 'r';
 grant_table 
-------------
 
 
 
 
 
 
(6 rows)

SET ROLE "bobby'; drop table testing.foo; --";
select * from  testing."foo(id int)"" drop table testing.foo; --"; 
 id 
----
  2
(1 row)

select * from testing."foo(id int)' drop table testing.foo; --"; 
 id 
----
  3
(1 row)

SELECT * FROM testing.foo;
ERROR:  permission denied for relation foo
RESET ROLE;
SET ROLE "bobby_tables""; foo";
select * from  testing."foo(id int)"" drop table testing.foo; --"; 
 id 
----
  2
(1 row)

select * from testing."foo(id int)' drop table testing.foo; --"; 
 id 
----
  3
(1 row)

SELECT * FROM testing.foo;
ERROR:  permission denied for relation foo
RESET ROLE;
SET ROLE "bobby""; drop table testing.foo; --";
select * from  testing."foo(id int)"" drop table testing.foo; --"; 
 id 
----
  2
(1 row)

select * from testing."foo(id int)' drop table testing.foo; --"; 
 id 
----
  3
(1 row)

SELECT * FROM testing.foo;
ERROR:  permission denied for relation foo
RESET ROLE;
select roleman.grant_schema_all(r, s, 'table', array['select'])
  FROM unnest(array['bobby"; drop table testing.foo; --'::text, 'bobby_tables"; foo', 'bobby''; drop table testing.foo; --']) r
  cross join unnest (array['testing''; drop table testing.foo; --', 'testing; drop table testing.foo; --', 'testing"; drop table testing.foo; --']) s;
ERROR:  bad tyoe table
CONTEXT:  PL/pgSQL function grant_schema_all(text,text,text,text[]) line 9 at RAISE
SET ROLE "bobby'; drop table testing.foo; --";
select * from  testing."foo(id int)"" drop table testing.foo; --"; 
 id 
----
  2
(1 row)

select * from testing."foo(id int)' drop table testing.foo; --"; 
 id 
----
  3
(1 row)

SELECT * FROM testing.foo;
ERROR:  permission denied for relation foo
RESET ROLE;
SET ROLE "bobby_tables""; foo";
select * from  testing."foo(id int)"" drop table testing.foo; --"; 
 id 
----
  2
(1 row)

select * from testing."foo(id int)' drop table testing.foo; --"; 
 id 
----
  3
(1 row)

SELECT * FROM testing.foo;
ERROR:  permission denied for relation foo
RESET ROLE;
SET ROLE "bobby""; drop table testing.foo; --";
select * from  testing."foo(id int)"" drop table testing.foo; --"; 
 id 
----
  2
(1 row)

select * from testing."foo(id int)' drop table testing.foo; --"; 
 id 
----
  3
(1 row)

SELECT * FROM testing.foo;
ERROR:  permission denied for relation foo
RESET ROLE;
select roleman.grant_function(r, testing.foo::regproc::oid::regprocedure, array['execute']))
  FROM unnest(array['bobby"; drop table testing.foo; --'::text, 'bobby_tables"; foo', 'bobby''; drop table testing.foo; --']) r;
ERROR:  syntax error at or near ")"
LINE 1: ... testing.foo::regproc::oid::regprocedure, array['execute']))
                                                                      ^
SET ROLE "bobby'; drop table testing.foo; --";
select * from  testing."foo(id int)"" drop table testing.foo; --"; 
 id 
----
  2
(1 row)

reset role;
select roleman.role_blank_perms('bobby"; drop table testing.foo; --');
 role_blank_perms 
------------------
 t
(1 row)

select roleman.drop_role('bobby"; drop table testing.foo; --');
 drop_role 
-----------
 
(1 row)

select roleman.role_blank_perms('bobby''; drop table testing.foo; --');
 role_blank_perms 
------------------
 t
(1 row)

select roleman.drop_role('bobby''; drop table testing.foo; --');
 drop_role 
-----------
 
(1 row)

select roleman.role_blank_perms('bobby_tables"; foo');
 role_blank_perms 
------------------
 t
(1 row)

select roleman.drop_role('bobby_tables"; foo');
 drop_role 
-----------
 
(1 row)

select 'testing.foo'::regclass::text;
    text     
-------------
 testing.foo
(1 row)