create or replace function ismd5(pwd text) returns bool as $$ begin return left(pwd, 3) = 'md5'; end; $$ language plpgsql;
--alter with admin
create user md5user_default with password 'Gauss@123';
create role md5role_default with password 'Gauss@123';
alter user md5user_default with pguser; --error
alter role md5role_default with pguser; --error
alter user md5user_default with pguser password 'Gauss@1234';
alter role md5role_default with pguser password 'Gauss@1234';
alter user md5user_default with pguser; --OK
alter role md5role_default with pguser; --OK
select usename, ismd5(passwd) from pg_shadow where usename ilike 'md5%' order by usename; --md5 now
select rolname, ismd5(rolpassword) from pg_authid where rolname ilike 'md5%' order by rolname; --md5 now
alter user md5user_default with password 'Gauss@12345';
alter role md5role_default with password 'Gauss@12345';
select usename, ismd5(passwd) from pg_shadow where usename ilike 'md5%' order by usename; --md5 still
select rolname, ismd5(rolpassword) from pg_authid where rolname ilike 'md5%' order by rolname; --md5 still
drop user md5user_default;
drop role md5role_default;
--alter by userself
create user md5user_default with password 'Gauss@12345';
set role md5user_default password 'Gauss@12345';
alter user md5user_default with pguser; --error
alter user md5user_default with pguser identified by 'Gauss@123456' replace 'Gauss@12344'; --invalid password;
alter user md5user_default with pguser identified by 'Gauss@123456' replace 'Gauss@12345'; --OK
\c
select usename, ismd5(passwd) from pg_shadow where usename ilike 'md5%' order by usename; --md5 now
select rolname, ismd5(rolpassword) from pg_authid where rolname ilike 'md5%' order by rolname; --md5 now
set role md5user_default password 'Gauss@123456';
alter user md5user_default with pguser; --OK
alter user md5user_default identified by 'Gauss@123456' replace 'Gauss@12344'; --invalid password;
alter user md5user_default identified by 'Gauss@1234567' replace 'Gauss@123456'; --OK
\c
select usename, ismd5(passwd) from pg_shadow where usename ilike 'md5%' order by usename; --md5 still
select rolname, ismd5(rolpassword) from pg_authid where rolname ilike 'md5%' order by rolname; --md5 still
drop user md5user_default cascade;
drop function ismd5;
create user test_createrole_lock_cr with createrole password "Gauss_234";
create user test_createrole_lock_sys with sysadmin password "Gauss_234";
create user test_createrole_lock_audit with auditadmin password "Gauss_234";
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U test_createrole_lock_cr -W Gauss_234 -c "Alter user test_createrole_lock_sys account lock;"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U test_createrole_lock_cr -W Gauss_234 -c "Alter user test_createrole_lock_audit account lock;"
drop user test_createrole_lock_cr;
drop user test_createrole_lock_sys;
drop user test_createrole_lock_audit;
\h create user
\h create role
\h alter user
\h alter role
\h create group
-- Test PG system functions by normal user
create user test_normal_user password 'Gauss@123';
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U test_normal_user -W Gauss@123 -c "select * from pg_ls_dir('./');"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U test_normal_user -W Gauss@123 -c "select * from pg_read_file('tmp.txt');"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U test_normal_user -W Gauss@123 -c "select * from pg_read_file('tmp.txt', 2, 9);"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U test_normal_user -W Gauss@123 -c "select * from pg_read_binary_file('tmp.txt');"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U test_normal_user -W Gauss@123 -c "select * from pg_stat_file('tmp.txt');"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U test_normal_user -W Gauss@123 -c "select * from pg_stat_file_recursive('tmp.txt');"
drop user test_normal_user cascade;
-- End Test PG system functions
--Test client ecological compatibility with PG
\! @abs_bindir@/gs_guc reload -D @abs_srcdir@/tmp_check/coordinator1 -Z coordinator -c "password_encryption_type=1" >/dev/null 2>&1
select pg_sleep(1);
create user md5_compatible_user1 with pguser password 'gauss@123';
create user md5_compatible_user2 password 'md529c2b5b4676f802b4e20e393024e280a';
create user normal_compatible_user1 password 'gauss@123';
create user normal_compatible_user2 password 'sha256fd063ca2fa3716f5a0db1b60879cac866284b81c1c9f8258138613991c567c7c1e98455895ed488f4d4a2b8f3f6901fd4bc7f608d5f3145acd4effce514535e42c83975ab970e69699a5448270540c669ad06562360125618d1f249a238882e2md5eec0ac50069b1884cb639071e3dee4ebecdfecefade';
select length(rolpassword) from pg_authid where rolname = 'md5_compatible_user1';
select length(rolpassword) from pg_authid where rolname = 'md5_compatible_user2';
select length(rolpassword) from pg_authid where rolname = 'normal_compatible_user1';
select length(rolpassword) from pg_authid where rolname = 'normal_compatible_user2';
set role md5_compatible_user1 password 'gauss@123';
reset role;
set role md5_compatible_user2 password 'gauss@123';
reset role;
set role md5_compatible_user2 password 'md529c2b5b4676f802b4e20e393024e280a';
alter role md5_compatible_user2 identified by 'md529c2b5b4676f802b4e20e393024e280a' replace 'gauss@123';
alter role md5_compatible_user2 identified by 'Gauss@123' replace 'md529c2b5b4676f802b4e20e393024e280a';
alter role md5_compatible_user2 identified by 'Gauss@123' replace 'gauss@123';
reset role;
set role normal_compatible_user1 password 'gauss@123';
reset role;
set role normal_compatible_user2 password 'gauss@123';
reset role;
set role normal_compatible_user2 password 'sha256fd063ca2fa3716f5a0db1b60879cac866284b81c1c9f8258138613991c567c7c1e98455895ed488f4d4a2b8f3f6901fd4bc7f608d5f3145acd4effce514535e42c83975ab970e69699a5448270540c669ad06562360125618d1f249a238882e2md5eec0ac50069b1884cb639071e3dee4ebecdfecefade';
alter role normal_compatible_user2 identified by 'sha256fd063ca2fa3716f5a0db1b60879cac866284b81c1c9f8258138613991c567c7c1e98455895ed488f4d4a2b8f3f6901fd4bc7f608d5f3145acd4effce514535e42c83975ab970e69699a5448270540c669ad06562360125618d1f249a238882e2md5eec0ac50069b1884cb639071e3dee4ebecdfecefade' replace 'gauss@123';
alter role normal_compatible_user2 identified by 'Gauss@123' replace 'sha256fd063ca2fa3716f5a0db1b60879cac866284b81c1c9f8258138613991c567c7c1e98455895ed488f4d4a2b8f3f6901fd4bc7f608d5f3145acd4effce514535e42c83975ab970e69699a5448270540c669ad06562360125618d1f249a238882e2md5eec0ac50069b1884cb639071e3dee4ebecdfecefade';
alter role normal_compatible_user2 identified by 'Gauss@123' replace 'gauss@123';
reset role;
\! @abs_bindir@/gs_guc reload -D @abs_srcdir@/tmp_check/coordinator1 -Z coordinator -c "password_encryption_type=2" >/dev/null 2>&1
select pg_sleep(1);
set role normal_compatible_user1 password 'gauss@123';
alter role normal_compatible_user1 identified by 'Gauss@123' replace 'gauss@123';
reset role;
select length(rolpassword) from pg_authid where rolname = 'normal_compatible_user1';
drop user md5_compatible_user1;
drop user md5_compatible_user2;
drop user normal_compatible_user1;
drop user normal_compatible_user2;
--The supported special character of password are: ~!@#$%^&*()-_=+\\|[{}];:,<.>/?
create role special_character_password_role password 'gauss""123';
drop role special_character_password_role;
--test create table for non-eixt/invalid/available schema
create user schematest_user1 password 'Gauss_234';
create schema schematest_schema_1;
set current_schema=schematest_schema_1;
create table schematest_tbtest(a int); drop table schematest_schema_1.schematest_tbtest;
create table schematest_schema_1.schematest_tbtest(a int); drop table schematest_schema_1.schematest_tbtest;
set current_schema=schema_no_exit;
create table schematest_tbtest(a int);
create table schema_no_exit.schematest_tbtest(a int);
set current_schema=schema_no_exit,schematest_schema_1;
create table schematest_tbtest(a int); drop table schematest_schema_1.schematest_tbtest;
set role schematest_user1 password "Gauss_234";
set current_schema=schematest_schema_1;
create table schematest_tbtest(a int);
create table schematest_schema_1.schematest_tbtest(a int);
set current_schema=schema_no_exit;
create table schematest_tbtest(a int);
create table schema_no_exit.schematest_tbtest(a int);
set current_schema=schematest_schema_1,schema_no_exit;
create table schematest_tbtest(a int);
set current_schema=schematest_schema_1,schematest_user1;
create table schematest_tbtest(a int);
drop table schematest_tbtest;
reset role;
drop user if exists schematest_user1;
drop schema schematest_schema_1;
--test disable gram for iam authenication
create user iam_auth_user01 password disable;
create user iam_auth_user02 identified by disable;
alter user iam_auth_user01 password 'gauss@123';
alter user iam_auth_user02 identified by 'gauss@123';
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U iam_auth_user01 -W gauss@123 -c "alter user iam_auth_user01 password disable;"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U iam_auth_user02 -W gauss@123 -c "alter user iam_auth_user02 identified by disable;"
alter user iam_auth_user01 password disable;
alter user iam_auth_user02 identified by disable;
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U iam_auth_user01 -W gauss@123 -c "\q"
\! @abs_bindir@/gsql -d postgres -p @portstring@ -U iam_auth_user02 -W gauss@123 -c "\q"
alter user iam_auth_user01 password 'gauss@234';
alter user iam_auth_user02 identified by 'gauss@234' replace 'gauss@34';
drop user iam_auth_user01;
drop user iam_auth_user02;
--test user/role name check
CREATE USER "hacker02-f" password "hack@123";
CREATE ROLE "hacker02-f" password "hack@123";
CREATE USER "hackuser02" password "hack@123";
CREATE ROLE "hackrole02" password "hack@123";
ALTER USER "hackuser02" RENAME TO "hackuser02-f";
ALTER ROLE "hackrole02" RENAME TO "hackrole02-f";
DROP USER "hackuser02";
DROP ROLE "hackrole02";