---------------------------------------------------------------
-------------------test pg directory-------------------
---------------------------------------------------------------
CREATE SCHEMA test_directory;
CREATE USER JACK with sysadmin IDENTIFIED BY 'Gauss@123';
CREATE USER ROSE with sysadmin IDENTIFIED BY 'Gauss@123';
CREATE USER LEON IDENTIFIED BY 'Gauss@123';
---create directory
CREATE DIRECTORY REGRESSDIR AS '@libdir@';
CREATE DIRECTORY NormalDir AS '/home/test_directory/work/';
CREATE DIRECTORY DummyDir AS '/home/dummydir';
CREATE OR REPLACE DIRECTORY DummyDir AS '/home/dummydirreplace';
CREATE DIRECTORY ErrorQuote AS '/home''DIR';
CREATE DIRECTORY RelativePath AS '../DIR/subdir';
CREATE OR REPLACE DIRECTORY RelativePath AS 'DIR';
CREATE OR REPLACE DIRECTORY DummyDir AS '/home/dummy.dir';
SELECT * FROM PG_DIRECTORY;
SELECT pg_sleep(1);
--grant and revoke
GRANT READ, WRITE ON DIRECTORY DummyDir TO JACK;
GRANT ALL ON DIRECTORY DummyDir TO LEON;
SELECT * FROM PG_DIRECTORY;
REVOKE READ ON DIRECTORY DummyDir FROM LEON;
SELECT * FROM PG_DIRECTORY;
REVOKE ALL ON DIRECTORY DummyDir FROM ROSE;
--alter owner to
ALTER DIRECTORY DummyDir OWNER TO LEON;
ALTER DIRECTORY DummyDir OWNER TO JACK;
--drop directory
DROP DIRECTORY NormalDir;
DROP DIRECTORY IF EXISTS NormalDir;
SELECT * FROM PG_DIRECTORY;
--drop user
DROP USER ROSE;
DROP USER JACK;
DROP USER JACK Cascade;
DROP USER LEON Cascade;
SELECT * FROM PG_DIRECTORY;
--audit
select type, detail_info from pg_query_audit('2019-01-01 00:00:00','9999-12-31') where detail_info like '%DIRECTORY%' or type='ddl_directory';
-- test permission
SHOW enableSeparationOfDuty;
SHOW enable_access_server_directory;
CREATE DIRECTORY data_dir AS '@abs_srcdir@/data';
DROP DIRECTORY data_dir;

CREATE USER CARRIE sysadmin PASSWORD 'Gauss@123';
SET SESSION AUTHORIZATION CARRIE PASSWORD 'Gauss@123';
CREATE DIRECTORY data_dir AS '@abs_srcdir@/data';
DROP DIRECTORY data_dir;
RESET SESSION AUTHORIZATION;
SET enable_access_server_directory = ON;
DROP USER CARRIE;
DROP SCHEMA test_directory;

-- gs_role_directory_create, gs_role_directory_drop
RESET ROLE;
CREATE USER test_user1 PASSWORD '1234567i*';
CREATE USER test_user2 PASSWORD '1234567i*';
CREATE USER test_user3 PASSWORD '1234567i*';
CREATE USER test_sysadmin SYSADMIN PASSWORD '1234567i*';
CREATE USER test_create PASSWORD '1234567i*' IN ROLE gs_role_directory_create;
CREATE USER test_drop PASSWORD '1234567i*' IN ROLE gs_role_directory_drop;

CREATE DIRECTORY test_dir1 AS '@testtablespace@/dir1';
GRANT DROP ON DIRECTORY test_dir1 TO test_user1 WITH GRANT OPTION;

SET ROLE test_user1 PASSWORD '1234567i*';
CREATE DIRECTORY test_dir2 AS '@testtablespace@/dir2';
ALTER DIRECTORY test_dir1 OWNER TO test_user3;
DROP DIRECTORY test_dir1;

SET ROLE test_sysadmin PASSWORD '1234567i*';
CREATE DIRECTORY test_dir2 AS '@testtablespace@/dir2';
ALTER DIRECTORY test_dir1 OWNER TO test_user3;
DROP DIRECTORY test_dir1;

SET ROLE test_create PASSWORD '1234567i*';
CREATE DIRECTORY test_dir2 AS '@testtablespace@/dir2';
ALTER DIRECTORY test_dir1 OWNER TO test_user3;
DROP DIRECTORY test_dir1;

SET ROLE test_drop PASSWORD '1234567i*';
CREATE DIRECTORY test_dir2 AS '@testtablespace@/dir2';
ALTER DIRECTORY test_dir1 OWNER TO test_user3;
DROP DIRECTORY test_dir1;

\! @abs_bindir@/gs_guc reload -Z datanode -D @abs_srcdir@/tmp_check/datanode1 -c "enable_access_server_directory=on" >/dev/null 2>&1

select pg_sleep(1);
SET ROLE test_user1 PASSWORD '1234567i*';
CREATE DIRECTORY test_dir2 AS '@testtablespace@/dir2';
ALTER DIRECTORY test_dir1 OWNER TO test_user3;
DROP DIRECTORY test_dir1;

RESET ROLE;
CREATE DIRECTORY test_dir1 AS '@testtablespace@/dir1';
GRANT DROP ON DIRECTORY test_dir1 TO test_user1 WITH GRANT OPTION;

SELECT has_directory_privilege('test_user1','test_dir1','DROP');
SELECT has_directory_privilege('test_dir1','DROP');
SELECT has_directory_privilege('test_user1',(select oid from pg_directory where dirname='test_dir1'),'DROP');
SELECT has_directory_privilege((select oid from pg_directory where dirname='test_dir1'),'DROP');
SELECT has_directory_privilege((select oid from pg_authid where rolname='test_user1'),'test_dir1','DROP');
SELECT has_directory_privilege((select oid from pg_authid where rolname='test_user1'),(select oid from pg_directory where dirname='test_dir1'),'DROP');

SET ROLE test_user1 PASSWORD '1234567i*';
GRANT DROP ON DIRECTORY test_dir1 TO test_user2;

SET ROLE test_user2 PASSWORD '1234567i*';
CREATE DIRECTORY test_dir2 AS '@testtablespace@/dir2';
ALTER DIRECTORY test_dir1 OWNER TO test_user3;
DROP DIRECTORY test_dir1;

RESET ROLE;
CREATE DIRECTORY test_dir1 AS '@testtablespace@/dir1';

SET ROLE test_create PASSWORD '1234567i*';
ALTER DIRECTORY test_dir1 OWNER TO test_user3;
DROP DIRECTORY test_dir1;
CREATE DIRECTORY test_dir2 AS '@testtablespace@/dir2';
ALTER DIRECTORY test_dir2 OWNER TO test_user3;
DROP DIRECTORY test_dir2;
CREATE DIRECTORY test_dir2 AS '@testtablespace@/dir2';

SET ROLE test_drop PASSWORD '1234567i*';
CREATE DIRECTORY test_di3 AS '@testtablespace@/dir3';
DROP DIRECTORY test_dir1;
DROP DIRECTORY test_dir2;

\! rm -rf @testtablespace@/dir1
\! rm -rf @testtablespace@/dir2
\! @abs_bindir@/gs_guc reload -Z datanode -D @abs_srcdir@/tmp_check/datanode1 -c "enable_access_server_directory=off" >/dev/null 2>&1

RESET ROLE;

alter system set enable_access_server_directory=on;
\! sleep 3
create user u_reassign_owned_0008_1 sysadmin password 'test@123';
create user u_reassign_owned_0008_2 sysadmin password 'test@123';
SET ROLE u_reassign_owned_0008_1 PASSWORD 'test@123';
show enable_access_server_directory;
create or replace directory dir_object_reassign_owned_0008 as '/tmp';
select t1.dirname, t2.usename, t1.dirpath from pg_directory t1 inner join pg_user t2 on t2.usesysid=t1.owner where t1.dirname='dir_object_reassign_owned_0008';
reassign owned by u_reassign_owned_0008_1 to u_reassign_owned_0008_2;

RESET ROLE;
alter system set enable_access_server_directory=off;

DROP USER test_user1,test_user2,test_user3;
DROP USER test_sysadmin,test_create,test_drop;
DROP USER u_reassign_owned_0008_1, u_reassign_owned_0008_2 cascade;