Smarts NCM or Voyence Control: How do I add a read-only user to the PostgreSQL (Postgres) Control Database?

Products

• VMware Smart Assurance

Issue/Introduction

Symptoms:


To create a user account in the Smarts NCM PostgreSQL Control Database with read-only privileges.


Some organizations that use Smarts NCM may require data contained in the Smarts NCM PostgreSQL Control Database that is not exposed through the Smarts NCM console or Report Advisor. The default accounts created in the database for the Smarts NCM application all have full administrative privileges. Using these accounts exposes the database to additional risk.

Environment

VMware Smart Assurance - NCM

Resolution

If a similar information set needs to be regularly obtained from the Smarts NCM PostgreSQL Control Database, a user account with read-only privileges on the tables containing the target data should be created for that purpose. The following sections provide instructions to:

• Create a read-only user account
• Adjust the account privileges to provide read-only access to database objects
• Remove the account after it is no longer needed

Creating a read-only user account 
To create a read-only user account with appropriate privileges, the following tasks must be completed: 

1. The user account must be created.
2. The  default_transaction_read_only  flag on the new user account must be set.
3. The account must be granted connect privileges to the database.
4. The account must be granted usage privileges on the schema containing the tables to which the user will be given read-only access.
5. The account must be granted specific  select  privileges on each table the user will be allowed read-only access to.
6. All queries run by the read-only access user must refer to the table and its schema in <schema name>.<table name> format.

 

Example: 
If there were a need to list all virtual device names along with which workspace they belong to, you would run the following query (note that the table names are expressed in <schema name>.<table name> format as would be required for the query to run successfully if logged in as a read-only user):

     SELECT
             device_name,
             workspace_name
     FROM
             voyence.cm_device
     JOIN
             voyence.cm_design_workspace
     ON
             cm_device.design_workspace_id = cm_design_workspace.design_workspace_id;
 
This query references both the  cm_device  and  cm_design_workspace  tables in the  voyence  schema inside the  voyencdedb  database.

Adjusting the account privileges to read the database
To create a read-only user account with sufficient privileges to run the above query, the following commands would need to be run while logged into the database as the  voyence  user.
 
     CREATE ROLE read_only_user WITH LOGIN PASSWORD 'changeme';
     ALTER USER read_only_user SET default_transaction_read_only = on;
     GRANT CONNECT ON DATABASE voyencedb TO read_only_user;
     GRANT USAGE ON SCHEMA voyence TO read_only_user;
     GRANT SELECT ON cm_device TO read_only_user;
     GRANT SELECT ON cm_design_workspace TO read_only_user;
 
Removing the read-only user account
Once a user account is no longer needed, it should be removed from the database. To accomplish this, the account permissions must first be removed from the account in order of dependency (typically table, then schema, then database). For example, to remove the user account added in the previous example, the following commands would be run while logged into the 'voyencedb' database as user 'voyence':
 
     REVOKE SELECT ON cm_device FROM read_only_user;
     REVOKE USAGE ON SCHEMA voyence FROM read_only_user;
     REVOKE CONNECT ON DATABASE voyencedb FROM read_only_user;
     DROP USER read_only_user;

Additional Information

For information on logging into the NCM PostgreSQL Control Database, see EMC knowledgebase article 17344" target="_blank"> Link Error: .