------------------------ TESTS FOR PGUSER -------------------------------------------
/* These cases are for pguser option test in CreateUserStmt.
* A user created with pguser option will have a password stored in MD5 format.
*/
create user md5user_default with pguser password 'gauss@123';
create user md5user_superuser with pguser sysadmin password 'gauss@123';
create user md5user_createdb with pguser createdb password 'gauss@123';
create user md5user_login with pguser login password 'gauss@123';
create user md5user_inherit with pguser inherit password 'gauss@123';
create user md5user_replication with pguser replication password 'gauss@123';
create user md5user_auditadmin with pguser auditadmin password 'gauss@123';
create role md5role_default with pguser password 'gauss@123';
create role md5role_superuser with pguser sysadmin password 'gauss@123';
create role md5role_createdb with pguser createdb password 'gauss@123';
create role md5role_login with pguser login password 'gauss@123';
create role md5role_inherit with pguser inherit password 'gauss@123';
create role md5role_replication with pguser replication password 'gauss@123';
create role md5role_auditadmin with pguser auditadmin password 'gauss@123';
-- Incorrect statements.
create user md5error with pguser pguser password 'gauss@123';
ERROR: conflicting or redundant options
create role md5error with pguser pguser password 'gauss@123';
ERROR: conflicting or redundant options
-- some check.
select usename,
usecreatedb,
usesuper,
usecatupd,
userepl,
left(passwd, 3) = 'md5' as ismd5passwd,
valbegin,
valuntil,
respool from pg_user where usename ilike 'md5%' order by usename;
usename | usecreatedb | usesuper | usecatupd | userepl | ismd5passwd | valbegin | valuntil | respool
---------------------+-------------+----------+-----------+---------+-------------+----------+----------+--------------
md5role_login | f | f | f | f | f | | | default_pool
md5user_auditadmin | f | f | f | f | f | | | default_pool
md5user_createdb | t | f | f | f | f | | | default_pool
md5user_default | f | f | f | f | f | | | default_pool
md5user_inherit | f | f | f | f | f | | | default_pool
md5user_login | f | f | f | f | f | | | default_pool
md5user_replication | f | f | f | t | f | | | default_pool
md5user_superuser | f | f | f | f | f | | | default_pool
(8 rows)
select usename,
usecreatedb,
usesuper,
usecatupd,
userepl,
left(passwd, 3) = 'md5' as ismd5passwd,
valbegin,
valuntil,
respool from pg_shadow where usename ilike 'md5%' order by usename;
usename | usecreatedb | usesuper | usecatupd | userepl | ismd5passwd | valbegin | valuntil | respool
---------------------+-------------+----------+-----------+---------+-------------+----------+----------+--------------
md5role_login | f | f | f | f | f | | | default_pool
md5user_auditadmin | f | f | f | f | f | | | default_pool
md5user_createdb | t | f | f | f | f | | | default_pool
md5user_default | f | f | f | f | f | | | default_pool
md5user_inherit | f | f | f | f | f | | | default_pool
md5user_login | f | f | f | f | f | | | default_pool
md5user_replication | f | f | f | t | f | | | default_pool
md5user_superuser | f | f | f | f | f | | | default_pool
(8 rows)
select rolname,
rolsuper,
rolinherit,
rolcreaterole,
rolcreatedb,
rolcatupdate,
rolcanlogin,
rolreplication,
rolauditadmin,
rolsystemadmin,
rolconnlimit,
rolpassword,
rolvalidbegin,
rolvaliduntil,
rolrespool,
rolconfig
from pg_roles where rolname ilike 'md5%' order by rolname;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | rolpassword | rolvalidbegin | rolvaliduntil | rolrespool | rolconfig
---------------------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+-------------+---------------+---------------+--------------+-----------
md5role_auditadmin | f | t | f | f | f | f | f | t | f | -1 | ******** | | | default_pool |
md5role_createdb | f | t | f | t | f | f | f | f | f | -1 | ******** | | | default_pool |
md5role_default | f | t | f | f | f | f | f | f | f | -1 | ******** | | | default_pool |
md5role_inherit | f | t | f | f | f | f | f | f | f | -1 | ******** | | | default_pool |
md5role_login | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool |
md5role_replication | f | t | f | f | f | f | t | f | f | -1 | ******** | | | default_pool |
md5role_superuser | f | t | f | f | f | f | f | f | t | -1 | ******** | | | default_pool |
md5user_auditadmin | f | t | f | f | f | t | f | t | f | -1 | ******** | | | default_pool |
md5user_createdb | f | t | f | t | f | t | f | f | f | -1 | ******** | | | default_pool |
md5user_default | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool |
md5user_inherit | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool |
md5user_login | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool |
md5user_replication | f | t | f | f | f | t | t | f | f | -1 | ******** | | | default_pool |
md5user_superuser | f | t | f | f | f | t | f | f | t | -1 | ******** | | | default_pool |
(14 rows)
select rolname ,
rolsuper,
rolinherit,
rolcreaterole,
rolcreatedb,
rolcatupdate,
rolcanlogin,
rolreplication,
rolauditadmin,
rolsystemadmin,
rolconnlimit,
left(rolpassword, 3) = 'md5' as ismd5passwd,
rolvalidbegin,
rolvaliduntil,
rolrespool
from pg_authid where rolname ilike 'md5%' order by rolname;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | ismd5passwd | rolvalidbegin | rolvaliduntil | rolrespool
---------------------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+-------------+---------------+---------------+--------------
md5role_auditadmin | f | t | f | f | f | f | f | t | f | -1 | f | | | default_pool
md5role_createdb | f | t | f | t | f | f | f | f | f | -1 | f | | | default_pool
md5role_default | f | t | f | f | f | f | f | f | f | -1 | f | | | default_pool
md5role_inherit | f | t | f | f | f | f | f | f | f | -1 | f | | | default_pool
md5role_login | f | t | f | f | f | t | f | f | f | -1 | f | | | default_pool
md5role_replication | f | t | f | f | f | f | t | f | f | -1 | f | | | default_pool
md5role_superuser | f | t | f | f | f | f | f | f | t | -1 | f | | | default_pool
md5user_auditadmin | f | t | f | f | f | t | f | t | f | -1 | f | | | default_pool
md5user_createdb | f | t | f | t | f | t | f | f | f | -1 | f | | | default_pool
md5user_default | f | t | f | f | f | t | f | f | f | -1 | f | | | default_pool
md5user_inherit | f | t | f | f | f | t | f | f | f | -1 | f | | | default_pool
md5user_login | f | t | f | f | f | t | f | f | f | -1 | f | | | default_pool
md5user_replication | f | t | f | f | f | t | t | f | f | -1 | f | | | default_pool
md5user_superuser | f | t | f | f | f | t | f | f | t | -1 | f | | | default_pool
(14 rows)
-- Password can be changed by "PGUSER" itself.
set role md5user_default password 'gauss@123';
alter role md5user_default identified by 'gauss@123456' replace 'gauss@123';
-- Reset the connection to user postgres
\c
-- Invalid password
set role md5user_default password 'gauss@123';
ERROR: Invalid username/password,set role denied.
\c
-- Login normal
set role md5user_default password 'gauss@123456';
\c
-- The password format of md5user_default should be "md5" still.
select usename,
usecreatedb,
usesuper,
usecatupd,
userepl,
left(passwd, 3) = 'md5' as ismd5passwd,
valbegin,
valuntil,
respool from pg_user where usename ilike 'md5%' order by usename;
usename | usecreatedb | usesuper | usecatupd | userepl | ismd5passwd | valbegin | valuntil | respool
---------------------+-------------+----------+-----------+---------+-------------+----------+----------+--------------
md5role_login | f | f | f | f | f | | | default_pool
md5user_auditadmin | f | f | f | f | f | | | default_pool
md5user_createdb | t | f | f | f | f | | | default_pool
md5user_default | f | f | f | f | f | | | default_pool
md5user_inherit | f | f | f | f | f | | | default_pool
md5user_login | f | f | f | f | f | | | default_pool
md5user_replication | f | f | f | t | f | | | default_pool
md5user_superuser | f | f | f | f | f | | | default_pool
(8 rows)
select usename,
usecreatedb,
usesuper,
usecatupd,
userepl,
left(passwd, 3) = 'md5' as ismd5passwd,
valbegin,
valuntil,
respool from pg_shadow where usename ilike 'md5%' order by usename;
usename | usecreatedb | usesuper | usecatupd | userepl | ismd5passwd | valbegin | valuntil | respool
---------------------+-------------+----------+-----------+---------+-------------+----------+----------+--------------
md5role_login | f | f | f | f | f | | | default_pool
md5user_auditadmin | f | f | f | f | f | | | default_pool
md5user_createdb | t | f | f | f | f | | | default_pool
md5user_default | f | f | f | f | f | | | default_pool
md5user_inherit | f | f | f | f | f | | | default_pool
md5user_login | f | f | f | f | f | | | default_pool
md5user_replication | f | f | f | t | f | | | default_pool
md5user_superuser | f | f | f | f | f | | | default_pool
(8 rows)
select rolname,
rolsuper,
rolinherit,
rolcreaterole,
rolcreatedb,
rolcatupdate,
rolcanlogin,
rolreplication,
rolauditadmin,
rolsystemadmin,
rolconnlimit,
rolpassword,
rolvalidbegin,
rolvaliduntil,
rolrespool,
rolconfig
from pg_roles where rolname ilike 'md5%' order by rolname;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | rolpassword | rolvalidbegin | rolvaliduntil | rolrespool | rolconfig
---------------------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+-------------+---------------+---------------+--------------+-----------
md5role_auditadmin | f | t | f | f | f | f | f | t | f | -1 | ******** | | | default_pool |
md5role_createdb | f | t | f | t | f | f | f | f | f | -1 | ******** | | | default_pool |
md5role_default | f | t | f | f | f | f | f | f | f | -1 | ******** | | | default_pool |
md5role_inherit | f | t | f | f | f | f | f | f | f | -1 | ******** | | | default_pool |
md5role_login | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool |
md5role_replication | f | t | f | f | f | f | t | f | f | -1 | ******** | | | default_pool |
md5role_superuser | f | t | f | f | f | f | f | f | t | -1 | ******** | | | default_pool |
md5user_auditadmin | f | t | f | f | f | t | f | t | f | -1 | ******** | | | default_pool |
md5user_createdb | f | t | f | t | f | t | f | f | f | -1 | ******** | | | default_pool |
md5user_default | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool |
md5user_inherit | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool |
md5user_login | f | t | f | f | f | t | f | f | f | -1 | ******** | | | default_pool |
md5user_replication | f | t | f | f | f | t | t | f | f | -1 | ******** | | | default_pool |
md5user_superuser | f | t | f | f | f | t | f | f | t | -1 | ******** | | | default_pool |
(14 rows)
select rolname ,
rolsuper,
rolinherit,
rolcreaterole,
rolcreatedb,
rolcatupdate,
rolcanlogin,
rolreplication,
rolauditadmin,
rolsystemadmin,
rolconnlimit,
left(rolpassword, 3) = 'md5' as ismd5passwd,
rolvalidbegin,
rolvaliduntil,
rolrespool
from pg_authid where rolname ilike 'md5%' order by rolname;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | ismd5passwd | rolvalidbegin | rolvaliduntil | rolrespool
---------------------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+-------------+---------------+---------------+--------------
md5role_auditadmin | f | t | f | f | f | f | f | t | f | -1 | f | | | default_pool
md5role_createdb | f | t | f | t | f | f | f | f | f | -1 | f | | | default_pool
md5role_default | f | t | f | f | f | f | f | f | f | -1 | f | | | default_pool
md5role_inherit | f | t | f | f | f | f | f | f | f | -1 | f | | | default_pool
md5role_login | f | t | f | f | f | t | f | f | f | -1 | f | | | default_pool
md5role_replication | f | t | f | f | f | f | t | f | f | -1 | f | | | default_pool
md5role_superuser | f | t | f | f | f | f | f | f | t | -1 | f | | | default_pool
md5user_auditadmin | f | t | f | f | f | t | f | t | f | -1 | f | | | default_pool
md5user_createdb | f | t | f | t | f | t | f | f | f | -1 | f | | | default_pool
md5user_default | f | t | f | f | f | t | f | f | f | -1 | f | | | default_pool
md5user_inherit | f | t | f | f | f | t | f | f | f | -1 | f | | | default_pool
md5user_login | f | t | f | f | f | t | f | f | f | -1 | f | | | default_pool
md5user_replication | f | t | f | f | f | t | t | f | f | -1 | f | | | default_pool
md5user_superuser | f | t | f | f | f | t | f | f | t | -1 | f | | | default_pool
(14 rows)
-- Clean
drop user md5user_default cascade;
drop user md5user_superuser cascade;
drop user md5user_createdb cascade;
drop user md5user_login cascade;
drop user md5user_inherit cascade;
drop user md5user_replication cascade;
drop user md5user_auditadmin cascade;
drop role md5role_default ;
drop role md5role_superuser ;
drop role md5role_createdb ;
drop role md5role_login ;
drop role md5role_inherit ;
drop role md5role_replication ;
drop role md5role_auditadmin ;
--audit for set parameter operator.
select * from pg_delete_audit('1111-1-1','2222-2-2');
pg_delete_audit
-----------------
(1 row)
create user set_role_user password 'gauss@123';
set role set_role_user password 'gauss@123';
reset role;
drop user set_role_user;
select type,result,object_name,detail_info from pg_query_audit('1111-1-1','2222-2-2') where detail_info like '%set_role_user%';
type | result | object_name | detail_info
---------------+--------+---------------+------------------------------------------------
ddl_user | ok | set_role_user | create user set_role_user password '********';
set_parameter | ok | role | set role set_role_user password '********';
ddl_user | ok | set_role_user | drop user set_role_user;
(3 rows)
select * from pg_delete_audit('1111-1-1','2222-2-2');
pg_delete_audit
-----------------
(1 row)