Security Profiles

Security profiles allow group configuration of DBMS security policies for users.

Catalog Description

The association of a user with a specific security profile is stored in the catalog qhb_auth_profile_authid, where the role_id column points to the DBMS user OID located in the catalog pg_authid, and the profile_id column points to the profile itself located in the catalog qhb_auth_profile.

By default, one initial security profile is available (the only entry in qhb_auth_profile) with OID 9352. Removing this profile may damage the stability of the system, since it is used for all users who have not yet been assigned a dedicated profile by the DBMS administrator.

Information about blocked users is stored in the catalog qhb_user_lockout, and blocking is applied only when the logon_jobs = on parameter is enabled in the DBMS configuration in qhb.conf. By default, this parameter is disabled, i.e. logon_jobs = off.

Before updating the cluster with the qhb_upgrade utility, you should disable the logon_jobs parameter by setting it to off and restart the DBMS. Otherwise, the stability of the cluster update process is not guaranteed.

SQL Procedures for Profile Management

qhb_auth_profile_unlock_by_profile ( id_profile oid )

Unlocks all users for this profile. id_profile is the profile ID.

Example:

SELECT qhb_auth_profile_unlock_by_profile(9352);

qhb_auth_profile_lockout_by_profile ( id_profile oid )

Locks all users for this profile. id_profile is the profile ID.

Example:

SELECT qhb_auth_profile_lockout_by_profile(9352);

qhb_auth_profile_set_password_alphabet_size ( id_profile oid, total_char integer )

Sets the minimum password alphabet strength for this profile. id_profile is the profile ID. total_char is the alphabet strength (total character number) or NULL to disable the check.

Example:

SELECT qhb_auth_profile_set_password_alphabet_size(9352, 60);

qhb_auth_profile_set_password_len ( id_profile oid, pass_length integer )

Sets the minimum password length for this profile. id_profile is the profile ID. pass_length is minimum password length or NULL to disable the check.

Example:

SELECT qhb_auth_profile_set_password_len(9352, 6);

qhb_auth_profile_disable_password_check ( id_profile oid )

Disables password complexity checking in this profile. id_profile is the profile ID.

Example:

SELECT qhb_auth_profile_disable_password_check(9352);

qhb_auth_profile_enable_password_check ( id_profile oid )

Enables password complexity checking in this profile. id_profile is the profile ID.

Example:

SELECT qhb_auth_profile_enable_password_check(9352);

qhb_auth_profile_lockout_user ( id_user oid )

Locks the specified user. id_user is the user ID.

Example:

SELECT qhb_auth_profile_lockout_user(10);

qhb_auth_profile_unlock_user ( id_user oid )

Unlocks the specified user. id_user is the user ID.

Example:

SELECT qhb_auth_profile_unlock_user(10);

qhb_auth_profile_set_auto_unlock ( id_profile oid, status bool )

Enables and disables automatic unlocking of this profile users. id_profile is the profile ID. status is the parameter value.

Example:

SELECT qhb_auth_profile_set_auto_unlock(9352, true);

qhb_auth_profile_set_max_failed_attempts ( id_profile oid, num_errors integer )

Sets the number of errors allowed when entering a password for this profile users. id_profile is the profile ID. num_errors is the number of errors allowed when entering a password.

Example:

SELECT qhb_auth_profile_set_max_failed_attempts(9352, 10);

qhb_auth_profile_set_unlimited_attempts ( id_profile oid )

Removes the check for the maximum number of errors when entering a password for this profile users. id_profile is the profile ID.

Example:

SELECT qhb_auth_profile_set_unlimited_attempts(9352);

qhb_auth_profile_unset ( id_user oid )

Assigns the default profile to the specified user. id_user is the user ID.

Example:

SELECT qhb_auth_profile_unset(10);

qhb_auth_profile_set ( id_user oid, id_profile oid )

Assigns the given security profile to the specified user. id_user is the user ID. id_profile is the profile ID.

Example:

SELECT qhb_auth_profile_set(10, 9352);

qhb_auth_profile_delete ( id_profile oid )

Deletes a security profile. id_profile is the profile ID.

Example:

SELECT qhb_auth_profile_delete(1);

qhb_auth_profile_create ( id_profile oid )

Creates a new security profile based on the specified template. id_profile is the template profile ID.

Example:

SELECT qhb_auth_profile_create(9352);

WARNING!
In case of manual management of catalog tables, when executing SQL queries directly to the catalogs pg_authid, qhb_auth_profile, qhb_user_lockout, qhb_auth_profile_authid, it is possible to disrupt the stability of the security profile subsystem and the DBMS as a whole. These catalogs are located in a shared catalog and therefore do not support triggers to track changes in them. Therefore, the logic was implemented in the SQL procedures above.

View the Current User Security Profile

qhb_auth_profile_current()

The function qhb_auth_profile_current allows you to display the name of the current security profile, for example:

SELECT qhb_auth_profile_current();

qhb_auth_profile_show( profile_name text )

profile_name is the name of an existing security profile.

The function qhb_auth_profile_show allows you to view the attributes of a given security profile, for example:

SELECT * FROM qhb_auth_profile_show(qhb_auth_profile_current());

This query will return the attributes of the user current security profile.

SQL Syntax for Managing Security Profiles

In addition to access to profile management using the specified procedures, SQL syntax is available in the form of commands: CREATE PROFILE, ALTER PROFILE, DROP PROFILE.

Views for Viewing Current Settings

The following SQL views are available for viewing security profiles: role_profiles, role_profiles_list, role_lockouts:

  • the role_profiles_list view allows the DBMS Administrator to view all available security profiles in the system. A regular user will see his own profile;

  • the role_profiles view allows the DBMS Administrator to view the security profile assignments to users of the system. A regular user will see his own assignment;

  • The role_lockouts view allows the DBMS Administrator to view the current lockouts of users of the system.

Example of using views:

SELECT * FROM role_profiles; -- to view appointments
SELECT * FROM role_profiles_list; -- to view security profiles
SELECT * FROM role_lockouts; -- to view lockouts

Checking Passwords for Compliance with Profile Policy

Passwords are checked for complexity required by the profile policy only if a password is set with password_check enabled in the corresponding profile. If the DBMS administrator uses procedures to manage password complexity policies, new passwords should also be set for all users belonging to the profile and the users should be unlocked for successful login. When the password complexity parameter is weakened, the lockout does not occur and the user can login with previous password.

Integration with Information Security Roles

Users designated as DBMS Administrators (qhb_dbms_admin) have the ability to create, modify, delete, and assign security profiles. They also have the ability to lock and unlock other users (except for other superusers and DBMS Administrators).

Users designated as Database Administrators (qhb_db_admin) are not granted additional privileges such as creating, modifying, deleting, and assigning security profiles.

Copyright © KVANTOM LLC, 2019-2026