Skip to main content
Version: Axidian Privilege 3.3

Usage of PostgreSQL and MSSQL Proxy

The MSSQL Proxy and PostgreSQL Proxy components allow opening SQL sessions through console and graphical clients. Text logging of SQL sessions is supported, which simplifies incident investigation.

info

A special license is required to connect to MSSQL and PostgreSQL resources.

DBMS Client Configuration​

When connecting to a server and working with it through an SQL client, multiple sessions may be created. In this case, multiple sessions are also created in PAM, which causes inconvenience when viewing logs. To have one session within a single connection to the server, you need to configure the SQL client.

Configuration using the DBeaver client as an example:

  1. Install the DBeaver client.
  2. In the left part of the screen in the Database Navigator window, find the required server in the list of available connections.
    Right-click on it and select Edit Connection from the context menu.
  3. In the window that opens, go to the Metadata tab and select the Datasource <servername> settings check box.
  4. For the Open separate connection for metadata read parameter, select Never from the drop-down list.
  5. Go to the SQL Editor tab and select the Datasource <servername> settings check box.
  6. For the Open separate connection for each editor parameter, select the Never check box from the drop-down list.
  7. Click OK.
  8. Repeat the listed actions for all database servers.

Configure SSL encryption​

caution

For correct operation, it is necessary to configure SSL both for the proxy and on the server.

To configure PostgreSQL Proxy operation via SSL, enable SSL usage in PostgreSQL Proxy and on the PostgreSQL Server. Follow these steps:

  1. Open the PostgreSQL Proxy configuration file at /etc/axidian-privilege/sql-proxy/appsettings.json
  2. Set the SslIsRequired parameter value to true and save the changes.
  3. Open the PostgreSQL Server configuration file postgresql.conf.
  4. Set the ssl parameter value to on.
  5. For the ssl_cert_file = parameter, specify the path to the SSL certificate.
  6. Save the changes.
Is interaction without SSL possible?

Yes, to do this, disable SSL usage in the proxy and on the server.

Specify MSSQL and PostgreSQL Proxy addresses​

  1. Go to Configuration → System settings.
  2. Specify the proxy address in the PostgreSQL Proxy Address or MSSQL Proxy Address.

Open SQL session​

To open an SQL session, go to the user console and connect to the resource via MSSQL Proxy or PostgreSQL Proxy.

Viewing Text Logs of SQL Sessions​

info

SQL clients may save SQL query text differently. For example, psql cuts out comments from SQL queries, while pgAdmin keeps them.

Only outgoing SQL queries (client → server) are captured in the text log, and their results are not saved.

To view text logs of a session opened via MSSQL Proxy or PostgreSQL Proxy:

  1. Open the administrator console and go to the Active sessions section.
  2. Select the required session.
  3. Click Text Log.

To get the current text log, click Refresh.

If problems or errors occur during operation, collect PostgreSQL Proxy or MSSQL Proxy logs and contact technical support.

Limitations​

  • A user can open sessions via MSSQL Proxy and PostgreSQL Proxy only on behalf of a service account added to PAM with a password. The connection will not be established if the permission has selected:
    • a service account added to PAM without a password;
    • a user service account for which credentials are requested when opening a session.
  • Two-factor authentication is supported only for installations with authentication through RADIUS, where the second factor is request confirmation in the application.
  • For installations with authentication through PAM, the Use two-factor authentication parameter is ignored, meaning the second factor is not requested during connection.
  • The user does not need confirmation from the administrator to open a session. Disable the Start of the sessions must be confirmed by PAM administrator parameter in the session policy, otherwise it is impossible to open an SQL session.
  • When opening a session, users are required to enter the reason for connection if the User must specify the connection reason check box is selected in the session policy.