How Do I Check the Creation Time of a Database User?
Method 1:
When you create a GaussDB(DWS) database user, if the time when the user takes effect (VALID BEGIN) is the same as the creation time of the user, and the time when the user takes effect has not been changed, you can check the valbegin column in the PG_USER view to check the user creation time.
The following is an example:
Create user jerry and set its validity start time to its current creation time.
1
|
CREATE USER jerry PASSWORD 'password' VALID BEGIN '2022-05-19 10:31:56'; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT * FROM PG_USER; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valbegin | valuntil | respool | parent | spacelimit | useconfig | nodegroup | tempspacelimit | spillspacelimit ---------+----------+-------------+----------+-----------+---------+----------+------------------------+----------+--------------+--------+------------+-----------+-----------+----------------+ ----------------- Ruby | 10 | t | t | t | t | ******** | | | default_pool | 0 | | | | | dbadmin | 16393 | f | f | f | f | ******** | | | default_pool | 0 | | | | | jack | 451897 | f | f | f | f | ******** | | | default_pool | 0 | | | | | emma | 451910 | f | f | f | f | ******** | | | default_pool | 0 | | | | | jerry | 457386 | f | f | f | f | ******** | 2022-05-19 10:31:56+08 | | default_pool | 0 | | | | | (5 rows) |
Method 2:
Check the passwordtime column in the PG_AUTH_HISTORY system catalog. This column indicates the time when the user's initial password was created. Only users with system administrator permissions can access the catalog.
1
|
select roloid, min(passwordtime) as create_time from pg_auth_history group by roloid order by roloid; |
The following is an example:
Query the PG_USER view to obtain the OID of user jerry, which is 457386. Query the passwordtime column to obtain the creation time of user jerry, which is 2022-05-19 10:31:56.
1 2 3 4 5 6 7 8 9 |
select roloid, min(passwordtime) as create_time from pg_auth_history group by roloid order by roloid; roloid | create_time --------+------------------------------- 10 | 2022-02-25 09:53:38.711785+08 16393 | 2022-02-25 09:55:17.992932+08 451897 | 2022-05-18 09:42:26.897855+08 451910 | 2022-05-18 09:46:33.152354+08 457386 | 2022-05-19 10:31:56.037706+08 (5 rows) |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot