Sonia Notes
https://www.postgresql.org/docs/current/monitoring-stats.html
collected statistics view
pg_stat_archiver One row only, showing statistics about the WAL archiver process's activity. See pg_stat_archiver for details.
pg_stat_bgwriter One row only, showing statistics about the background writer process's activity. See pg_stat_bgwriter for details.
pg_stat_checkpointer One row only, showing statistics about the checkpointer process's activity. See pg_stat_checkpointer for details.
pg_stat_database One row per database, showing database-wide statistics. See pg_stat_database for details.
pg_stat_database_conflicts One row per database, showing database-wide statistics about query cancels due to conflict with recovery on standby servers. See pg_stat_database_conflicts for details.
pg_stat_io One row for each combination of backend type, context, and target object containing cluster-wide I/O statistics. See pg_stat_io for details.
pg_stat_replication_slots One row per replication slot, showing statistics about the replication slot's usage. See pg_stat_replication_slots for details.
pg_stat_slru One row per SLRU, showing statistics of operations. See pg_stat_slru for details.
pg_stat_subscription_stats One row per subscription, showing statistics about errors and conflicts. See pg_stat_subscription_stats for details.
pg_stat_wal One row only, showing statistics about WAL activity. See pg_stat_wal for details.
pg_stat_all_tables One row for each table in the current database, showing statistics about accesses to that specific table. See pg_stat_all_tables for details.
pg_stat_sys_tables Same as pg_stat_all_tables, except that only system tables are shown.
pg_stat_user_tables Same as pg_stat_all_tables, except that only user tables are shown.
pg_stat_xact_all_tables Similar to pg_stat_all_tables, but counts actions taken so far within the current transaction (which are not yet included in pg_stat_all_tables and related views). The columns for numbers of live and dead rows and vacuum and analyze actions are not present in this view.
pg_stat_xact_sys_tables Same as pg_stat_xact_all_tables, except that only system tables are shown.
pg_stat_xact_user_tables Same as pg_stat_xact_all_tables, except that only user tables are shown.
pg_stat_all_indexes One row for each index in the current database, showing statistics about accesses to that specific index. See pg_stat_all_indexes for details.
pg_stat_sys_indexes Same as pg_stat_all_indexes, except that only indexes on system tables are shown.
pg_stat_user_indexes Same as pg_stat_all_indexes, except that only indexes on user tables are shown.
pg_stat_user_functions One row for each tracked function, showing statistics about executions of that function. See pg_stat_user_functions for details.
pg_stat_xact_user_functions Similar to pg_stat_user_functions, but counts only calls during the current transaction (which are not yet included in pg_stat_user_functions).
pg_statio_all_tables One row for each table in the current database, showing statistics about I/O on that specific table. See pg_statio_all_tables for details.
pg_statio_sys_tables Same as pg_statio_all_tables, except that only system tables are shown.
pg_statio_user_tables Same as pg_statio_all_tables, except that only user tables are shown.
pg_statio_all_indexes One row for each index in the current database, showing statistics about I/O on that specific index. See pg_statio_all_indexes for details.
pg_statio_sys_indexes Same as pg_statio_all_indexes, except that only indexes on system tables are shown.
pg_statio_user_indexes Same as pg_statio_all_indexes, except that only indexes on user tables are shown.
pg_statio_all_sequences One row for each sequence in the current database, showing statistics about I/O on that specific sequence. See pg_statio_all_sequences for details.
pg_statio_sys_sequences Same as pg_statio_all_sequences, except that only system sequences are shown. (Presently, no system sequences are defined, so this view is always empty.)
pg_statio_user_sequences Same as pg_statio_all_sequences, except that only user sequences are shown.
How is AI used in Databasus development?
There have been questions about AI usage in project development in issues and discussions. As the project focuses on security, reliability and production usage, it's important to explain how AI is used in the development process.
AI is used as a helper for:
Verification of code quality and searching for vulnerabilities
Cleaning up and improving documentation, comments and code
Assistance during development
Double-checking PRs and commits after human review
AI is NOT used for:
Writing entire code
"Vibe code" approach
Code without line-by-line verification by a human
Code without tests
The project has:
Solid test coverage (both unit and integration tests)
CI/CD pipeline automation with tests and linting to ensure code quality
Verification by experienced developers with experience in large and secure projects
So AI is just an assistant and a tool for developers to increase productivity and ensure code quality. The work is done by developers.
The practitioner may report on any of the trust services categories of security, availability, processing integ-
rity, confidentiality, or privacy, either individually or in combination with one or more of the other trust
services categories. For each category addressed by the engagement, all criteria for that category are
usually addressed. However, in limited circumstances, such as when the scope of the engagement is to
report on a system and a particular criterion is not relevant to the services provided by a service organi-
zation, one or more criteria may not be applicable to the engagement. For example, when reporting on privacy for a service organization’s system, criterion P3.1, Personal information is collected consistent
with the entity’s objectives related to privacy, is not applicable for a service organization that does not
directly collect personal information from data subjects.
rity, confidentiality, or privacy, either individually or in combination with one or more of the other trust
services categories. For each category addressed by the engagement, all criteria for that category are
usually addressed. However, in limited circumstances, such as when the scope of the engagement is to
report on a system and a particular criterion is not relevant to the services provided by a service organi-
zation, one or more criteria may not be applicable to the engagement. For example, when reporting on privacy for a service organization’s system, criterion P3.1, Personal information is collected consistent
with the entity’s objectives related to privacy, is not applicable for a service organization that does not
directly collect personal information from data subjects.
👍1
Security. Information and systems are protected against unauthorized access, unauthorized dis-
closure of information, and damage to systems that could compromise the availability, integrity,
confidentiality, and privacy of information or systems and affect the entity’s ability to achieve its
objectives.
Availability. Information and systems are available for operation and use to meet the entity’s ob-
jectives.
Processing integrity (over the provision of services or the production, manufacturing, or distri-
bution of goods). System processing is complete, valid, accurate, timely, and authorized to meet
the entity’s objectives.
Confidentiality. Information designated as confidential is protected to meet the entity’s objec-
e. tives.
Privacy. Personal information is collected, used, retained, disclosed, and disposed of to meet the
entity’s objectives.
closure of information, and damage to systems that could compromise the availability, integrity,
confidentiality, and privacy of information or systems and affect the entity’s ability to achieve its
objectives.
Availability. Information and systems are available for operation and use to meet the entity’s ob-
jectives.
Processing integrity (over the provision of services or the production, manufacturing, or distri-
bution of goods). System processing is complete, valid, accurate, timely, and authorized to meet
the entity’s objectives.
Confidentiality. Information designated as confidential is protected to meet the entity’s objec-
e. tives.
Privacy. Personal information is collected, used, retained, disclosed, and disposed of to meet the
entity’s objectives.
😁1
In an entity-wide SOC for Cybersecurity examination, the entity establishes cybersecurity objec-
tives. Cybersecurity objectives are those that could be affected by cybersecurity risk and, there-
fore, affect the achievement of the entity’s compliance, reporting, and operational objectives.
The nature of an entity’s cybersecurity objectives will vary depending on the environment in
which the entity operates, the entity’s mission and vision, the overall business objectives estab-
lished by management, and other factors. For example, a telecommunication entity may have a
cybersecurity objective related to the reliable functioning of those aspects of its operations that
are deemed to be critical infrastructure, whereas an online dating entity is likely to regard the
privacy of the personal information collected from customers to be a critical factor in achieving
its operating objectives. fn 7
tives. Cybersecurity objectives are those that could be affected by cybersecurity risk and, there-
fore, affect the achievement of the entity’s compliance, reporting, and operational objectives.
The nature of an entity’s cybersecurity objectives will vary depending on the environment in
which the entity operates, the entity’s mission and vision, the overall business objectives estab-
lished by management, and other factors. For example, a telecommunication entity may have a
cybersecurity objective related to the reliable functioning of those aspects of its operations that
are deemed to be critical infrastructure, whereas an online dating entity is likely to regard the
privacy of the personal information collected from customers to be a critical factor in achieving
its operating objectives. fn 7
🥰1
COSO Principle 1: The entity demonstrates a commitment to integrity and ethical values.
COSO Principle 2: The board of directors demonstrates independence from management and
exercises oversight of the development and performance of internal control.
COSO Principle 3: Management establishes, with board oversight, structures, reporting lines,
and appropriate authorities and responsibilities in the pursuit of objectives.
COSO Principle 4: The entity demonstrates a commitment to attract, develop, and retain com-
petent individuals in alignment with objectives.
COSO Principle 5: The entity holds individuals accountable for their internal control responsi-
bilities in the pursuit of objectives.
COSO Principle 6: The entity specifies objectives with sufficient clarity to enable the identifica-
tion and assessment of risks relating to objectives.
COSO Principle 7: The entity identifies risks to the achievement of its objectives across the enti-
ty and analyzes risks as a basis for determining how the risks should be managed.
COSO Principle 8: The entity considers the potential for fraud in assessing risks to the
achievement of objectives.
COSO Principle 9: The entity identifies and assesses changes that could significantly impact the
system of internal control.
COSO Principle 10: The entity selects and develops control activities that contribute to the mit-
igation of risks to the achievement of objectives to acceptable levels.
COSO Principle 11: The entity also selects and develops general control activities over technol-
ogy to support the achievement of objectives.
COSO Principle 12: The entity deploys control activities through policies that establish what is
expected and in procedures that put policies into action.
COSO Principle 13: The entity obtains or generates and uses relevant, quality information to
support the functioning of internal control.
COSO Principle 14: The entity internally communicates information, including objectives and
responsibilities for internal control, necessary to support the functioning of internal control.
COSO Principle 15: The entity communicates with external parties regarding matters affecting
the functioning of internal control.
COSO Principle 16: The entity selects, develops, and performs ongoing and/or separate evalua-
tions to ascertain whether the components of internal control are present and functioning.
COSO Principle 17: The entity evaluates and communicates internal control deficiencies in a
timely manner to those parties responsible for taking corrective action, including senior man-
agement and the board of directors, as appropriate.
COSO Principle 2: The board of directors demonstrates independence from management and
exercises oversight of the development and performance of internal control.
COSO Principle 3: Management establishes, with board oversight, structures, reporting lines,
and appropriate authorities and responsibilities in the pursuit of objectives.
COSO Principle 4: The entity demonstrates a commitment to attract, develop, and retain com-
petent individuals in alignment with objectives.
COSO Principle 5: The entity holds individuals accountable for their internal control responsi-
bilities in the pursuit of objectives.
COSO Principle 6: The entity specifies objectives with sufficient clarity to enable the identifica-
tion and assessment of risks relating to objectives.
COSO Principle 7: The entity identifies risks to the achievement of its objectives across the enti-
ty and analyzes risks as a basis for determining how the risks should be managed.
COSO Principle 8: The entity considers the potential for fraud in assessing risks to the
achievement of objectives.
COSO Principle 9: The entity identifies and assesses changes that could significantly impact the
system of internal control.
COSO Principle 10: The entity selects and develops control activities that contribute to the mit-
igation of risks to the achievement of objectives to acceptable levels.
COSO Principle 11: The entity also selects and develops general control activities over technol-
ogy to support the achievement of objectives.
COSO Principle 12: The entity deploys control activities through policies that establish what is
expected and in procedures that put policies into action.
COSO Principle 13: The entity obtains or generates and uses relevant, quality information to
support the functioning of internal control.
COSO Principle 14: The entity internally communicates information, including objectives and
responsibilities for internal control, necessary to support the functioning of internal control.
COSO Principle 15: The entity communicates with external parties regarding matters affecting
the functioning of internal control.
COSO Principle 16: The entity selects, develops, and performs ongoing and/or separate evalua-
tions to ascertain whether the components of internal control are present and functioning.
COSO Principle 17: The entity evaluates and communicates internal control deficiencies in a
timely manner to those parties responsible for taking corrective action, including senior man-
agement and the board of directors, as appropriate.
👍1
Change Management :
Manages Changes Throughout the System Life Cycle — A process for managing
system changes throughout the life cycle of the system and its components (infra-
structure, data, software, and procedures) is used to support system availability and
processing integrity.
• Authorizes Changes — A process is in place to authorize system changes prior to
development.
• Designs and Develops Changes — A process is in place to design and develop sys-
tem changes.
• Documents Changes — A process is in place to document system changes to sup-
port ongoing maintenance of the system and to support system users in performing
their responsibilities.
• Tracks System Changes — A process is in place to track system changes prior to
implementation.
• Configures Software — A process is in place to select and implement the configura-
tion parameters used to control the functionality of software.
• Tests System Changes — A process is in place to test system changes prior to im-
plementation.
• Approves System Changes — A process is in place to approve system changes prior
to implementation.
• Deploys System Changes — A process is in place to implement system changes.
• Identifies and Evaluates System Changes — Objectives affected by system changes
are identified and the ability of the modified system to meet the objectives is evalu-
ated throughout the system development life cycle.
• Identifies Changes in Infrastructure, Data, Software, and Procedures Required to
Remediate Incidents — Changes in infrastructure, data, software, and procedures
required to remediate incidents to continue to meet objectives are identified and the
change process is initiated upon identification.
Manages Changes Throughout the System Life Cycle — A process for managing
system changes throughout the life cycle of the system and its components (infra-
structure, data, software, and procedures) is used to support system availability and
processing integrity.
• Authorizes Changes — A process is in place to authorize system changes prior to
development.
• Designs and Develops Changes — A process is in place to design and develop sys-
tem changes.
• Documents Changes — A process is in place to document system changes to sup-
port ongoing maintenance of the system and to support system users in performing
their responsibilities.
• Tracks System Changes — A process is in place to track system changes prior to
implementation.
• Configures Software — A process is in place to select and implement the configura-
tion parameters used to control the functionality of software.
• Tests System Changes — A process is in place to test system changes prior to im-
plementation.
• Approves System Changes — A process is in place to approve system changes prior
to implementation.
• Deploys System Changes — A process is in place to implement system changes.
• Identifies and Evaluates System Changes — Objectives affected by system changes
are identified and the ability of the modified system to meet the objectives is evalu-
ated throughout the system development life cycle.
• Identifies Changes in Infrastructure, Data, Software, and Procedures Required to
Remediate Incidents — Changes in infrastructure, data, software, and procedures
required to remediate incidents to continue to meet objectives are identified and the
change process is initiated upon identification.
SOC 2 engagement
An examination engagement to report on the fairness of the presentation of
management’s description of the service organization’s system, the suitability of the design of
the controls included in the description, and, in a type 2 engagement, the operating effectiveness
of those controls. This engagement is performed in accordance with the attestation standards and
AICPA Guide SOC 2® Reporting on an Examination of Controls at a Service Organization: Rel-
evant to Security, Availability, Processing Integrity, Confidentiality, or Privacy.
SOC 3 engagement
An examination engagement to report on the suitability of design and the oper-
ating effectiveness of an entity’s controls over a system relevant to one or more of the trust ser-
vices categories.
SOC for Cybersecurity examination
An examination engagement to report on whether (a) man-
agement’s description of the entity’s cybersecurity risk management program is presented in ac-
cordance with the description criteria and (b) the controls within that program were effective to
achieve the entity’s cybersecurity objectives based on the control criteria. A SOC for Cybersecu-
rity examination is performed in accordance with the attestation standards and AICPA Guide
Reporting on an Entity's Cybersecurity Risk Management Program and Controls.
SOC for Supply Chain examination
An examination engagement to report on whether (a) the de-
scription of the entity’s system is presented in accordance with the description criteria and (b) the
controls stated in the description, which are necessary to provide reasonable assurance that the
entity achieved its principal system objectives, were effective based on the applicable trust ser-
vices criteria. Such an examination is based on guidance contained in AICPA Guide SOC for
Supply Chain: Reporting on an Examination of Controls Relevant to Security, Availability, Pro-
cessing Integrity, Confidentiality, or Privacy in a Production, Manufacturing, or Distribution
System.
An examination engagement to report on the fairness of the presentation of
management’s description of the service organization’s system, the suitability of the design of
the controls included in the description, and, in a type 2 engagement, the operating effectiveness
of those controls. This engagement is performed in accordance with the attestation standards and
AICPA Guide SOC 2® Reporting on an Examination of Controls at a Service Organization: Rel-
evant to Security, Availability, Processing Integrity, Confidentiality, or Privacy.
SOC 3 engagement
An examination engagement to report on the suitability of design and the oper-
ating effectiveness of an entity’s controls over a system relevant to one or more of the trust ser-
vices categories.
SOC for Cybersecurity examination
An examination engagement to report on whether (a) man-
agement’s description of the entity’s cybersecurity risk management program is presented in ac-
cordance with the description criteria and (b) the controls within that program were effective to
achieve the entity’s cybersecurity objectives based on the control criteria. A SOC for Cybersecu-
rity examination is performed in accordance with the attestation standards and AICPA Guide
Reporting on an Entity's Cybersecurity Risk Management Program and Controls.
SOC for Supply Chain examination
An examination engagement to report on whether (a) the de-
scription of the entity’s system is presented in accordance with the description criteria and (b) the
controls stated in the description, which are necessary to provide reasonable assurance that the
entity achieved its principal system objectives, were effective based on the applicable trust ser-
vices criteria. Such an examination is based on guidance contained in AICPA Guide SOC for
Supply Chain: Reporting on an Examination of Controls Relevant to Security, Availability, Pro-
cessing Integrity, Confidentiality, or Privacy in a Production, Manufacturing, or Distribution
System.
The AICPA’s Assurance Services Executive Committee (ASEC), through its Trust Information Integrity
Task Force’s SOC 2 Working Group, has developed a set of benchmarks, known as description criteria,
to be used when preparing and evaluating the description of the service organization’s system (descrip-
tion) in an examination of a service organization’s controls over security, availability, processing integ-
rity, confidentiality, or privacy (SOC 2 examination). This document presents the description criteria for
use in that examination. (This document does not address the AICPA’s trust services criteria, fn 3 which
are used in a SOC 2 examination to evaluate whether controls stated in the description were suitably de-
signed and operated effectively to provide reasonable assurance that the service organization’s service
commitments and system requirements were achieved based on the applicable trust services criteria.)
Task Force’s SOC 2 Working Group, has developed a set of benchmarks, known as description criteria,
to be used when preparing and evaluating the description of the service organization’s system (descrip-
tion) in an examination of a service organization’s controls over security, availability, processing integ-
rity, confidentiality, or privacy (SOC 2 examination). This document presents the description criteria for
use in that examination. (This document does not address the AICPA’s trust services criteria, fn 3 which
are used in a SOC 2 examination to evaluate whether controls stated in the description were suitably de-
signed and operated effectively to provide reasonable assurance that the service organization’s service
commitments and system requirements were achieved based on the applicable trust services criteria.)
AICPA revises guidance on applying
its Trust Services Criteria and SOC 2
Key considerations
Revisions to the Trust Services Criteria guidance
While the AICPA didn’t change the criteria in the TSC, it added new points of focus and clarified
existing points of focus that address the evaluation of whether controls are suitably designed
and operating effectively to achieve the entity’s service commitments and system requirements.
The AICPA said the revised points of focus are intended “to better support application of the
criteria” in an environment of ever-changing threats and vulnerabilities; new technologies;
legal, regulatory and cultural expectations regarding privacy; and changing expectations
regarding data management and confidentiality. The revisions also provide guidance on topics
that entities have struggled with, such as which privacy points of focus apply to data controllers
and which ones apply to data processors.
Service organizations should consider whether and how their controls address the clarifications
and new points of focus. In evaluating the new guidance, organizations should pay particular
attention to the following changes:
• Types of relevant information — New points of focus clarify that the types of information
relevant to systems of internal control include information about data flow, asset
inventory and location, information classification, and the completeness and accuracy of
information used in the system.
• Risk assessment — The revised points of focus on risk assessment provide users with a
more granular approach to evaluating risks by understanding the underlying components
of risk assessment: threat and vulnerability identification and the evaluation of the
likelihood and magnitude of a threat event intersecting with a vulnerability. The revised
points of focus also include the consideration of residual risk after considering internal
controls and management’s decisions to accept, reduce or share risks.
• Monitoring activities — The revised points of focus encourage service organizations to
consider activities performed by the first and second lines of defense (i.e., monitoring
performed by the people who perform a function and monitoring performed by managers
who oversee them, respectively) in addition to internal audit functions and other recurring
information technology (IT) assessments many service organizations have historically
identified in their SOC 2 reports.
• Logical access — Modified points of focus encourage consideration of logical access controls
across the system architecture, including all relevant infrastructure, IT tools, and types of
access, such as employee, contractor, vendor, business partner, system and service accounts.
In addition, recovery of devices, such as laptops, is now considered in the points of focus.
2 | To the Point AICPA revises guidance on applying its Trust Services Criteria and SOC 2 Description Criteria 2 November 2022
• Change management — Two new points of focus have been added to address change
management. The first relates to the identification, testing and implementation of
software patches. The second addresses the consideration of resilience requirements
during the change management process if a SOC 2 report addresses system availability.
• Availability — Given the increase in ransomware attacks, a new point of focus was added on
management’s identification of threats to data recoverability and mitigation procedures.
• Privacy — A number of points of focus were revised to better align with widely used
privacy practices.
The AICPA also emphasized that the applicability of any particular point of focus depends on
the facts and circumstances and that the points of focus provided are unlikely to be exhaustive
for most service organizations. Consequently, use of the TSC does not require that every
point of focus be met. However, a service organization should consider the applicability of the
new points of focus and whether other points of focus also need to be met to achieve their
service commitments and system requirements.
its Trust Services Criteria and SOC 2
Key considerations
Revisions to the Trust Services Criteria guidance
While the AICPA didn’t change the criteria in the TSC, it added new points of focus and clarified
existing points of focus that address the evaluation of whether controls are suitably designed
and operating effectively to achieve the entity’s service commitments and system requirements.
The AICPA said the revised points of focus are intended “to better support application of the
criteria” in an environment of ever-changing threats and vulnerabilities; new technologies;
legal, regulatory and cultural expectations regarding privacy; and changing expectations
regarding data management and confidentiality. The revisions also provide guidance on topics
that entities have struggled with, such as which privacy points of focus apply to data controllers
and which ones apply to data processors.
Service organizations should consider whether and how their controls address the clarifications
and new points of focus. In evaluating the new guidance, organizations should pay particular
attention to the following changes:
• Types of relevant information — New points of focus clarify that the types of information
relevant to systems of internal control include information about data flow, asset
inventory and location, information classification, and the completeness and accuracy of
information used in the system.
• Risk assessment — The revised points of focus on risk assessment provide users with a
more granular approach to evaluating risks by understanding the underlying components
of risk assessment: threat and vulnerability identification and the evaluation of the
likelihood and magnitude of a threat event intersecting with a vulnerability. The revised
points of focus also include the consideration of residual risk after considering internal
controls and management’s decisions to accept, reduce or share risks.
• Monitoring activities — The revised points of focus encourage service organizations to
consider activities performed by the first and second lines of defense (i.e., monitoring
performed by the people who perform a function and monitoring performed by managers
who oversee them, respectively) in addition to internal audit functions and other recurring
information technology (IT) assessments many service organizations have historically
identified in their SOC 2 reports.
• Logical access — Modified points of focus encourage consideration of logical access controls
across the system architecture, including all relevant infrastructure, IT tools, and types of
access, such as employee, contractor, vendor, business partner, system and service accounts.
In addition, recovery of devices, such as laptops, is now considered in the points of focus.
2 | To the Point AICPA revises guidance on applying its Trust Services Criteria and SOC 2 Description Criteria 2 November 2022
• Change management — Two new points of focus have been added to address change
management. The first relates to the identification, testing and implementation of
software patches. The second addresses the consideration of resilience requirements
during the change management process if a SOC 2 report addresses system availability.
• Availability — Given the increase in ransomware attacks, a new point of focus was added on
management’s identification of threats to data recoverability and mitigation procedures.
• Privacy — A number of points of focus were revised to better align with widely used
privacy practices.
The AICPA also emphasized that the applicability of any particular point of focus depends on
the facts and circumstances and that the points of focus provided are unlikely to be exhaustive
for most service organizations. Consequently, use of the TSC does not require that every
point of focus be met. However, a service organization should consider the applicability of the
new points of focus and whether other points of focus also need to be met to achieve their
service commitments and system requirements.