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) |
General Problems FAQs
- Why Do I Need to Use a Data Warehouse?
- Why Should I Use Public Cloud GaussDB(DWS)?
- Should I Choose Public Cloud GaussDB(DWS) or RDS?
- What Are the Differences Between Users and Roles?
- When Should I Use GaussDB(DWS) and MRS?
- How Do I Check the Creation Time of a Database User?
- Regions and AZs
- Is My Data Secure in GaussDB(DWS)?
- How Is GaussDB(DWS) Secured?
- Can I Modify the Security Group of a GaussDB(DWS) Cluster?
- How Are LibrA, GaussDB A, and GaussDB(DWS) Related?
- What Is a Database/Data Warehouse/Data Lake/Lakehouse?
- How Are Dirty Pages Generated in GaussDB(DWS)?
- How Do I Use VPC Sharing to Process GaussDB(DWS) Resources?
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.
Chatbotmore