PostgreSQL User Privilege Requirements

You are setting up a dedicated remote PostgreSQL database server for MetaDefender ICAP Server to operate on, and your own PostgreSQL server has a strict security policy, then this article is relevant to your deployment.

MetaDefender ICAP Server will need to create and use dedicated PostgreSQL users for database setup, upgrade and its own operations. Those users requires certain privileges configured on PostgreSQL server, otherwise expecting failures to occur while installing and operating MetaDefender ICAP Server.

MetaDefender ICAP Server requires 2 types of PostgreSQL users:

	Purpose
Main PostgreSQL user	Create databases, schemas, roles, PostgreSQL extensions, internal PostgreSQL users, etc.
Internal PostgreSQL user	Uses to invoke almost query executions during MetaDefender ICAP Server runtime. MetaDefender ICAP Server uses this internal PostgreSQL users instead of the main PostgreSQL user to narrow down permissions leveraged by MetaDefender ICAP Server while operating. The internal PostgreSQL user will be automatically generated by MetaDefender ICAP Server (in the format of `usr_xxxxxxx`) while starting up the product service, and will be also automatically dropped when MetaDefender ICAP Server service is stopped. You can also configure MetaDefender ICAP Server to designate your own PostgreSQL user Customize Internal PostgreSQL User. If configured then MetaDefender ICAP Server will no longer drop and create a new internal PostgreSQL user when restarting its service. In the shared database deployment model, it requires at least one main PostgreSQL user for all MetaDefender ICAP Server instances, and one internal PostgreSQL user per each MetaDefender ICAP Server instance.

PostgreSQL User Privilege Requirements

Main PostgreSQL User

Privilege	Description
SUPERUSER	Create PostgreSQL extensions, create MetaDefender ICAP Server databases, create internal PostgreSQL user, terminate connections and rename database while upgrading MetaDefender ICAP Server(in case upgrading standalone mode to shared mode only). Notes: If this privilege is granted, needless to grant the other privileges (see below in the table). If you revoke this privilege: MetaDefender ICAP Server could not be upgraded from standalone to share mode. MetaDefender ICAP Server could not drop the default generated internal users `usr_xxxxxxx`
CREATEDB	Create MetaDefender ICAP Server databases (fresh install, and upgrade from standalone to share mode). Notes: If you revoke this privilege and drop MetaDefender ICAP Server database, then MetaDefender ICAP Server will not be able to create a new one.
CREATEROLE	Create internal PostgreSQL user (in the format of `usr_xxxxxxx`). Notes: If you’re using your own custom PostgreSQL internal user, you will not need this privilege (refer to this guideline Customize Internal PostgreSQL User). If you’re using the default generated user `usr_xxxxxxx` and you revoke this privilege, ICAP Server could not generate sub PostgreSQL users `usr_xxxxxxx`.
LOGIN	Access the client connection command.

Internal PostgreSQL User

Privilege	Description
LOGIN	Access the client connection command.

Privileges Setting Up Guidelines

Before MetaDefender ICAP Server Installation

Prepare Main PostgreSQL User

SQL
    
 
create role mdicapsrv with SUPERUSER CREATEDB CREATEROLE LOGIN password 'password';
Copy

You can create a new main PostgreSQL user, or use your existing one, for example:

Configure PostgreSQL.conf

Configure your pg_hba.conf to allow the access on main PostgreSQL user.

Just in case you use the customized internal PostgreSQL user Customize Internal PostgreSQL User, then you should also configure the access for this user as well.
For example:

Bash
    
​x
 
host    all             mdicapsrv              0.0.0.0/0                       md5host    all             mdicapsrv              ::/0                            md5​host    all             yourinternaluser    0.0.0.0/0                       md5host    all             yourinternaluser    ::/0                            md5
Copy

Restart PostgreSQL server service to take effect.

After MetaDefender ICAP Serve Installation

Configure PostgreSQL .conf (for auto generated internal PostgreSQL user)

Just in case you are letting MetaDefender ICAP Server to auto create and use internal PostgreSQL user (usr_xxxxxxx), then configure your pg_hba.conf to allow the access on this auto generated internal PostgreSQL user.

Note: To find out the exact username of the default generated user usr_xxxxxxx: https://www.postgresqltutorial.com/postgresql-administration/postgresql-list-users/

Bash
    
 
host    all             usr_<>              0.0.0.0/0                       md5host    all             usr_<>              ::/0                            md5
Copy

Restart PostgreSQL server service to take effect.

Revoke some privileges from the main PostgreSQL user (If needed)

If you need to revoke some privileges from the main PostgreSQL user for any security reason, you can do that for these privileges: SUPERUSER, CREATEDB, CREATEROLE

Please read carefully these notes to make sure you can revoke privileges:

Without SUPERUSER privilege, MetaDefender ICAP Server could not drop the internal user usr_xxxxxxxautomatically. You should use a custom internal PostgreSQL user instead Customize Internal PostgreSQL User. MetaDefender ICAP Server also could not create PostgreSQL extensions in case the database needed to be recreated.
Without CREATEDB, MetaDefender ICAP Server could not create a new one in case the old database was dropped.
Without CREATEROLE, MetaDefender ICAP Server could not generate a new one in case you let the internal PostgreSQL user usr_xxxxxxx is automatically generated by MetaDefender ICAP Server .

Steps to revoke SUPERUSER privilege on the main PostgreSQL user:

1) Install MetaDefender ICAP Server and set it up successfully.

2) Use another SUPERUSER and log in to the correct MetaDefender ICAP Server database.

metadefender_icapsrv for the shared deployment model.

metadefender_icapsrv_xxxxxx for standalone deployment model.

3) Then grant access to these 2 schemas pg_catalog and information_schema to the main PostgreSQL user.

Bash
    
 
GRANT USAGE ON SCHEMA pg_catalog TO mdicapsrv;GRANT ALL ON ALL TABLES IN SCHEMA pg_catalog TO mdicapsrv;GRANT USAGE ON SCHEMA information_schema TO mdicapsrv;GRANT ALL ON ALL TABLES IN SCHEMA information_schema TO mdicapsrv;
Copy

4) Revoke the SUPERUSER privilege from the main PostgreSQL user.

5) Restart MetaDefender ICAP Server service.

Bash
    
 
alter user mdicapsrv NOSUPERUSER CREATEDB CREATEROLE LOGIN;
Copy

Steps to revoke other privileges on the main PostgreSQL user:

1) Install MetaDefender ICAP Server and set it up successfully.

2) Revoke the CREATEDB CREATEROLE privilege from the main PostgreSQL user.

Bash
    
 
alter user mdicapsrv NOSUPERUSER CREATEDB CREATEROLE LOGIN;
Copy

3) Restart MetaDefender ICAP Server service.

Last updated on

Was this page helpful?