Installing and upgrading user_profile

Install the user_profile package on each host in your WarehousePG cluster, as it doesn't ship as part of the WarehousePG server package.

Supported platforms

user_profile platform support differs by WarehousePG version:

WarehousePG versionArchitectureOperating systems
7x86_64RHEL 8, 9
7ppc64leRHEL 8, 9
6x86_64RHEL 7, 8, 9

RHEL 7 support for WarehousePG 6 starts with user_profile 1.0.3. See the release notes.

Installing the package

Refer to Downloading and installing an extension for instructions on downloading the package and installing it on every host. The package name is edb-whpg6-user_profile for WarehousePG 6, or edb-whpg7-user_profile for WarehousePG 7.

Loading and registering user_profile

Load the extension as a shared library, then create it in your database.

  1. Check for existing shared libraries:

    gpconfig -s shared_preload_libraries
  2. Use the output of the previous command to enable the new extension, along with any other shared libraries, and restart WarehousePG:

    gpconfig -c shared_preload_libraries -v '<other_libraries>, user_profile'
    gpstop -ar
  3. Create the extension in your database:

    CREATE EXTENSION user_profile;
Important

For user_profile versions earlier than 1.0.2, manually create the extension's SQLite database before you restart the database, to avoid connection failures for non-local, password-authenticated users:

  1. Check for existing shared libraries:

    gpconfig -s shared_preload_libraries
  2. Use the output of the previous command to enable the new extension, along with any other shared libraries. Don't restart WHPG yet:

    gpconfig -c shared_preload_libraries -v '<other_libraries>, user_profile'
  3. Create the extension in your database:

    CREATE EXTENSION user_profile;
  4. Create the SQLite database for the extension:

    SELECT up_init();
  5. Restart WHPG:

    gpstop -ar

Verifying the installation

Confirm that user_profile is registered in the database:

\dx user_profile

Managing the user_profile storage

user_profile stores profiles and per-role state in a SQLite database, not in the WarehousePG catalog. The user_profile.db file is in the coordinator data directory, which is $COORDINATOR_DATA_DIRECTORY on WarehousePG 7 and $MASTER_DATA_DIRECTORY on WarehousePG 6. The coordinator creates the database automatically when it loads the preloaded library, so a restart with gpstop -ar is enough to get a working storage.

If the storage is lost, for example because it was deleted by accident, create a new, empty one. Profiles, role assignments, and password history aren't restored, so set them up again or restore the user_profile.db file from a backup. A restart with gpstop -ar also creates the storage. The up_init() function is safe to run more than once:

SELECT up_init();

Keep the following in mind when you plan your cluster:

  • The SQLite database isn't replicated to the standby coordinator. After a failover, the new coordinator doesn't have your profiles or per-role state, so you must restore the user_profile.db file yourself.
  • In user_profile 1.0.2 and later, if user_profile can't open the storage, WarehousePG still allows password-based logins and writes a warning to the coordinator log. No profile rules are enforced, and setting a password with CREATE ROLE or ALTER ROLE fails until the storage is available again.

Upgrading user_profile

The release notes state whether a release changes the SQL objects. If it doesn't, the extension version stays the same and you don't need to run ALTER EXTENSION.

  1. Upgrade the user_profile package on every host. Refer to Upgrading an extension for downloading and distributing the new package. The package name is edb-whpg6-user_profile for WarehousePG 6, or edb-whpg7-user_profile for WarehousePG 7.

  2. Restart WarehousePG to load the new library:

    gpstop -ar
  3. If the release notes say the release changes the SQL objects, update the extension in each database where you registered it:

    ALTER EXTENSION user_profile UPDATE;

Could this page be better? Report a problem or suggest an addition!