-- set up users
create user acl_test1;
create user "acl test2";
create user """acl=""s,/a//acl_test1";
create user acl_temporary_user;
-- ACL format checks
select ''::ace;
ERROR:  missing ACE type
LINE 1: select ''::ace;
               ^
select 'a'::ace;
ERROR:  missing "/" sign
LINE 1: select 'a'::ace;
               ^
select 'q'::ace;
ERROR:  invalid ACE type: must be one of "ad"
LINE 1: select 'q'::ace;
               ^
select 'a*'::ace;
ERROR:  missing "/" sign
LINE 1: select 'a*'::ace;
               ^
select 'a/'::ace;
ERROR:  missing ACE flags
LINE 1: select 'a/'::ace;
               ^
select 'a/hq'::ace;
ERROR:  invalid ACE flag: must be one of "hpcoi0123456789ABCDEFGHIJKLMNOP"
LINE 1: select 'a/hq'::ace;
               ^
select 'a/h'::ace;
ERROR:  missing "/" sign
LINE 1: select 'a/h'::ace;
               ^
select 'a/h/'::ace;
ERROR:  missing ACE who
LINE 1: select 'a/h/'::ace;
               ^
select 'a/h/='::ace;
ERROR:  missing ACE mask
LINE 1: select 'a/h/='::ace;
               ^
select 'a/h/acl_test1='::ace;
ERROR:  missing ACE mask
LINE 1: select 'a/h/acl_test1='::ace;
               ^
select 'a/h/acl_test1=d,'::ace;
ERROR:  invalid ACE mask: must be one of "scdwr0123456789ABCDEFGHIJKLMNOPQ"
LINE 1: select 'a/h/acl_test1=d,'::ace;
               ^
select 'a/h/acl_test1=dw'::ace;
       ace        
------------------
 a/h/acl_test1=dw
(1 row)

select 'a/ihpc/acl_test1=wdddw'::ace;
         ace         
---------------------
 a/hpci/acl_test1=dw
(1 row)

select 'd/ihpc/acl_test1=wdddw'::ace;
         ace         
---------------------
 d/hpci/acl_test1=dw
(1 row)

select 'a/ihpc/"acl test2"=dw0'::ace;
          ace           
------------------------
 a/hpci/"acl test2"=0dw
(1 row)

-- invalid role
select 'a//blah=d'::ace;
  ace   
--------
 a/x/=d
(1 row)

-- deleted role
create table acl_test (ace ace);
insert into acl_test (ace) values ('a//acl_temporary_user=s');
drop user acl_temporary_user;
select ace::text ~ '#' from acl_test;
 ?column? 
----------
 t
(1 row)

-- invalid acl
select (ace::text::ace::text) ~ '#' from acl_test;
 ?column? 
----------
 t
(1 row)

-- current user
set role 'acl_test1';
select coalesce(acl_check_access('{}'::ace[], 'sd0', false), 'NULL');
 coalesce 
----------
 
(1 row)

select acl_check_access('{}'::ace[], (1 << 0) | (1 << 27) | (1 << 29), false)::bit(32);
         acl_check_access         
----------------------------------
 00000000000000000000000000000000
(1 row)

select coalesce(acl_check_access(null::ace[], 'sd0', false), 'NULL');
 coalesce 
----------
 
(1 row)

select acl_check_access(null::ace[], (1 << 0) | (1 << 27) | (1 << 29), false)::bit(32);
         acl_check_access         
----------------------------------
 00000000000000000000000000000000
(1 row)

select acl_check_access('{d//=s,a//acl_test1=sdw}'::ace[], 'sd0', false);
 acl_check_access 
------------------
 d
(1 row)

select acl_check_access('{d//=s,a//acl_test1=sdw}'::ace[], (1 << 0) | (1 << 27) | (1 << 29), false)::bit(32);
         acl_check_access         
----------------------------------
 00100000000000000000000000000000
(1 row)

select acl_check_access('{a//acl_test1=s0,d//=sdw}'::ace[], 'sd0', false);
 acl_check_access 
------------------
 0s
(1 row)

select acl_check_access('{a//acl_test1=s0,d//=sdw}'::ace[], (1 << 0) | (1 << 27) | (1 << 29), false)::bit(32);
         acl_check_access         
----------------------------------
 00001000000000000000000000000001
(1 row)

reset role;
set role """acl=""s,/a//acl_test1";
select acl_check_access('{d/hpc/acl_test1=dw0,a//\"\"\"acl=\"\"s\,/a//acl_test1\"=c}'::ace[], 'c', false);
 acl_check_access 
------------------
 c
(1 row)

select acl_check_access('{d/hpc/acl_test1=dw0,a//\"\"\"acl=\"\"s\,/a//acl_test1\"=c}'::ace[], (1 << 28), false)::bit(32);
         acl_check_access         
----------------------------------
 00010000000000000000000000000000
(1 row)

reset role;
-- name
select coalesce(acl_check_access('{}'::ace[], 'sd0', 'acl_test1', false), 'NULL');
 coalesce 
----------
 
(1 row)

select acl_check_access('{}'::ace[], (1 << 0) | (1 << 27) | (1 << 29), 'acl_test1', false)::bit(32);
         acl_check_access         
----------------------------------
 00000000000000000000000000000000
(1 row)

select coalesce(acl_check_access(null::ace[], 'sd0', 'acl_test1', false), 'NULL');
 coalesce 
----------
 
(1 row)

select acl_check_access(null::ace[], (1 << 0) | (1 << 27) | (1 << 29), 'acl_test1', false)::bit(32);
         acl_check_access         
----------------------------------
 00000000000000000000000000000000
(1 row)

select coalesce(acl_check_access('{}'::ace[], 'sd0', null, false), 'NULL');
 coalesce 
----------
 NULL
(1 row)

select acl_check_access('{}'::ace[], (1 << 0) | (1 << 27) | (1 << 29), null, false)::bit(32);
 acl_check_access 
------------------
 
(1 row)

select acl_check_access('{d//=s,a//acl_test1=sdw}'::ace[], 'sd0', 'acl_test1', false);
 acl_check_access 
------------------
 d
(1 row)

select acl_check_access('{d//=s,a//acl_test1=sdw}'::ace[], (1 << 0) | (1 << 27) | (1 << 29), 'acl_test1', false)::bit(32);
         acl_check_access         
----------------------------------
 00100000000000000000000000000000
(1 row)

select acl_check_access('{a//acl_test1=s0,d//=sdw}'::ace[], 'sd0', 'acl_test1', false);
 acl_check_access 
------------------
 0s
(1 row)

select acl_check_access('{a//acl_test1=s0,d//=sdw}'::ace[], (1 << 0) | (1 << 27) | (1 << 29), 'acl_test1', false)::bit(32);
         acl_check_access         
----------------------------------
 00001000000000000000000000000001
(1 row)

select acl_check_access('{d/hpc/acl_test1=dw0,a//\"\"\"acl=\"\"s\,/a//acl_test1\"=c}'::ace[], 'c', '"acl="s,/a//acl_test1', false);
 acl_check_access 
------------------
 c
(1 row)

select acl_check_access('{d/hpc/acl_test1=dw0,a//\"\"\"acl=\"\"s\,/a//acl_test1\"=c}'::ace[], (1 << 28), '"acl="s,/a//acl_test1', false)::bit(32);
         acl_check_access         
----------------------------------
 00010000000000000000000000000000
(1 row)

-- oid
select coalesce(acl_check_access('{}'::ace[], 'sd0', (select oid from pg_roles where rolname = 'acl_test1'), false), 'NULL');
 coalesce 
----------
 
(1 row)

select acl_check_access('{}'::ace[], (1 << 0) | (1 << 27) | (1 << 29), (select oid from pg_roles where rolname = 'acl_test1'), false)::bit(32);
         acl_check_access         
----------------------------------
 00000000000000000000000000000000
(1 row)

select coalesce(acl_check_access(null::ace[], 'sd0', (select oid from pg_roles where rolname = 'acl_test1'), false), 'NULL');
 coalesce 
----------
 
(1 row)

select acl_check_access(null::ace[], (1 << 0) | (1 << 27) | (1 << 29), (select oid from pg_roles where rolname = 'acl_test1'), false)::bit(32);
         acl_check_access         
----------------------------------
 00000000000000000000000000000000
(1 row)

select coalesce(acl_check_access('{}'::ace[], 'sd0', null, false), 'NULL');
 coalesce 
----------
 NULL
(1 row)

select acl_check_access('{}'::ace[], (1 << 0) | (1 << 27) | (1 << 29), null, false)::bit(32);
 acl_check_access 
------------------
 
(1 row)

select acl_check_access('{d//=s,a//acl_test1=sdw}'::ace[], 'sd0', (select oid from pg_roles where rolname = 'acl_test1'), false);
 acl_check_access 
------------------
 d
(1 row)

select acl_check_access('{d//=s,a//acl_test1=sdw}'::ace[], (1 << 0) | (1 << 27) | (1 << 29), (select oid from pg_roles where rolname = 'acl_test1'), false)::bit(32);
         acl_check_access         
----------------------------------
 00100000000000000000000000000000
(1 row)

select acl_check_access('{a//acl_test1=s0,d//=sdw}'::ace[], 'sd0', (select oid from pg_roles where rolname = 'acl_test1'), false);
 acl_check_access 
------------------
 0s
(1 row)

select acl_check_access('{a//acl_test1=s0,d//=sdw}'::ace[], (1 << 0) | (1 << 27) | (1 << 29), (select oid from pg_roles where rolname = 'acl_test1'), false)::bit(32);
         acl_check_access         
----------------------------------
 00001000000000000000000000000001
(1 row)

select acl_check_access('{d/hpc/acl_test1=dw0,a//\"\"\"acl=\"\"s\,/a//acl_test1\"=c}'::ace[], 'c', (select oid from pg_roles where rolname = '"acl="s,/a//acl_test1'), false);
 acl_check_access 
------------------
 c
(1 row)

select acl_check_access('{d/hpc/acl_test1=dw0,a//\"\"\"acl=\"\"s\,/a//acl_test1\"=c}'::ace[], (1 << 28), (select oid from pg_roles where rolname = '"acl="s,/a//acl_test1'), false)::bit(32);
         acl_check_access         
----------------------------------
 00010000000000000000000000000000
(1 row)

-- inherit only
select acl_check_access('{d/i/=s,a//acl_test1=sdw}'::ace[], 'sd0', 'acl_test1', false);
 acl_check_access 
------------------
 sd
(1 row)

select acl_check_access('{d/i/=s,a//acl_test1=sdw}'::ace[], (1 << 0) | (1 << 27) | (1 << 29), 'acl_test1', false)::bit(32);
         acl_check_access         
----------------------------------
 00101000000000000000000000000000
(1 row)

-- merge
select acl_merge(null::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], true, false);
         acl_merge          
----------------------------
 {a//=0,d//=1,a//=23,d//=4}
(1 row)

select acl_merge(null::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], true, true);
         acl_merge          
----------------------------
 {d//=1,d//=4,a//=0,a//=23}
(1 row)

select acl_merge(null::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], false, false);
         acl_merge          
----------------------------
 {a//=0,d//=1,a//=23,d//=4}
(1 row)

select acl_merge(null::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], false, true);
         acl_merge          
----------------------------
 {d//=1,d//=4,a//=0,a//=23}
(1 row)

-- inheritance
-- container
-- no flags -> not inherited
select acl_merge('{a//acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], true, false);
         acl_merge          
----------------------------
 {a//=0,d//=1,a//=23,d//=4}
(1 row)

-- inherit only -> not inherited
select acl_merge('{a/i/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], true, false);
         acl_merge          
----------------------------
 {a//=0,d//=1,a//=23,d//=4}
(1 row)

-- object inherit -> inherit only + object inherit
select acl_merge('{a/o/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], true, false);
                  acl_merge                   
----------------------------------------------
 {a//=0,d//=1,a//=23,d//=4,a/hoi/acl_test1=d}
(1 row)

-- object inherit + no propagate inherit -> no inheritance
select acl_merge('{a/op/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], true, false);
         acl_merge          
----------------------------
 {a//=0,d//=1,a//=23,d//=4}
(1 row)

--container inherit -> container inherit
select acl_merge('{a/c/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], true, false);
                  acl_merge                  
---------------------------------------------
 {a//=0,d//=1,a//=23,d//=4,a/hc/acl_test1=d}
(1 row)

-- container inherit + no propagate inherit -> no flags
select acl_merge('{a/cp/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], true, false);
                 acl_merge                  
--------------------------------------------
 {a//=0,d//=1,a//=23,d//=4,a/h/acl_test1=d}
(1 row)

-- container inherit + object inherit -> container inherit + object inherit + inherit only
select acl_merge('{a/co/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], true, false);
                   acl_merge                   
-----------------------------------------------
 {a//=0,d//=1,a//=23,d//=4,a/hcoi/acl_test1=d}
(1 row)

-- container inherit + object inherit + no propagate inherit -> no flags
select acl_merge('{a/cop/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], true, false);
                 acl_merge                  
--------------------------------------------
 {a//=0,d//=1,a//=23,d//=4,a/h/acl_test1=d}
(1 row)

-- object
-- no flags -> not inherited
select acl_merge('{a//acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], false, false);
         acl_merge          
----------------------------
 {a//=0,d//=1,a//=23,d//=4}
(1 row)

-- inherit only -> not inherited
select acl_merge('{a/i/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], false, false);
         acl_merge          
----------------------------
 {a//=0,d//=1,a//=23,d//=4}
(1 row)

-- object inherit -> no flags
select acl_merge('{a/o/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], false, false);
                 acl_merge                  
--------------------------------------------
 {a//=0,d//=1,a//=23,d//=4,a/h/acl_test1=d}
(1 row)

-- object inherit + no propagate inherit -> no flags
select acl_merge('{a/op/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], false, false);
                 acl_merge                  
--------------------------------------------
 {a//=0,d//=1,a//=23,d//=4,a/h/acl_test1=d}
(1 row)

--container inherit -> not inherited
select acl_merge('{a/c/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], false, false);
         acl_merge          
----------------------------
 {a//=0,d//=1,a//=23,d//=4}
(1 row)

-- container inherit + no propagate inherit -> not inherited
select acl_merge('{a/cp/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], false, false);
         acl_merge          
----------------------------
 {a//=0,d//=1,a//=23,d//=4}
(1 row)

-- container inherit + object inherit -> no flags
select acl_merge('{a/co/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], false, false);
                 acl_merge                  
--------------------------------------------
 {a//=0,d//=1,a//=23,d//=4,a/h/acl_test1=d}
(1 row)

-- container inherit + object inherit + no propagate inherit -> no flags
select acl_merge('{a/cop/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], false, false);
                 acl_merge                  
--------------------------------------------
 {a//=0,d//=1,a//=23,d//=4,a/h/acl_test1=d}
(1 row)

-- skip inherited
select acl_merge('{a/h/acl_test1=d}'::ace[], '{a//=0,d//=1,a//=23,d//=4}'::ace[], false, false);
         acl_merge          
----------------------------
 {a//=0,d//=1,a//=23,d//=4}
(1 row)

-- clean up
drop user acl_test1;
drop user "acl test2";
drop user """acl=""s,/a//acl_test1";