PostgreSQL User Privilege Requirements

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

MetaDefender Core 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 Core.

MetaDefender Core requires 2 types of PostgreSQL users:

Purposes
Main PostgreSQL userCreate databases, schemas, roles, PostgreSQL extensions, internal PostgreSQL users, etc.
Internal PostgreSQL user
  • Uses to invoke almost query executions during MetaDefender Core runtime. MetaDefender Core uses this internal PostgreSQL users instead of the main PostgreSQL user to narrow down permissions leveraged by MetaDefender Core while operating.
  • The internal PostgreSQL user will be automatically generated by MetaDefender Core (in the format of usr_xxxxxxx) while starting up the product service, and will be also automatically dropped when MetaDefender Core service is stopped.
  • You can also configure MetaDefender Core to designate your own PostgreSQL user Customize Internal PostgreSQL User. If configured then MetaDefender Core 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 Core instances, and one internal PostgreSQL user per each MetaDefender Core instance.

PostgreSQL User Privilege Requirements

Main PostgreSQL User

PrivilegeDescription
SUPERUSER

Create PostgreSQL extensions, create MetaDefender Core databases, create internal PostgreSQL user, terminate connections and rename database while upgrading MetaDefender Core (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 Core could not be upgraded from standalone to share mode.
    • MetaDefender Core could not drop the default generated internal users usr_xxxxxxx
CREATEDB

Create MetaDefender Core databases (fresh install, and upgrade from standalone to share mode).

Notes:

  • If you revoke this privilege and drop MetaDefender Core database, then MetaDefender Core 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, Core could not generate sub PostgreSQL users usr_xxxxxxx.
LOGINAccess the client connection command.

Internal PostgreSQL User

PrivilegeDescription
LOGINAccess the client connection command.

Privileges Setting Up Guidelines

Before MetaDefender Core Installation

Prepare Main PostgreSQL User

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

SQL
Copy

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:
Copy

Restart PostgreSQL server service to take effect.

After MetaDefender Core Installation

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

Just in case you are letting MetaDefender Core 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/

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 Core could not drop the internal user usr_xxxxxxxautomatically. You should use a custom internal PostgreSQL user instead Customize Internal PostgreSQL User. MetaDefender Core also could not create PostgreSQL extensions in case the database needed to be recreated.
  • Without CREATEDB, MetaDefender Core could not create a new one in case the old database was dropped.
  • Without CREATEROLE, MetaDefender Core could not generate a new one in case you let the internal PostgreSQL user usr_xxxxxxx is automatically generated by MetaDefender Core.

Steps to revoke SUPERUSER privilege on the main PostgreSQL user:

1) Install MetaDefender Core and set it up successfully.

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

metadefender_core for the shared deployment model.

metadefender_core_xxxxxx for standalone deployment model.

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

SQL
Copy

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

5) Restart MetaDefender Core service.

SQL
Copy

Steps to revoke other privileges on the main PostgreSQL user:

1) Install MetaDefender Core and set it up successfully.

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

SQL
Copy

3) Restart MetaDefender Core service.

Type to search, ESC to discard
Type to search, ESC to discard
Type to search, ESC to discard
Next to read:
Remote PostgreSQL User Privileges Checking Tool
On This Page
PostgreSQL User Privilege RequirementsPostgreSQL User Privilege RequirementsMain PostgreSQL UserInternal PostgreSQL UserPrivileges Setting Up GuidelinesBefore MetaDefender Core InstallationPrepare Main PostgreSQL UserConfigure PostgreSQL .confAfter MetaDefender Core InstallationConfigure PostgreSQL .conf (for auto generated internal PostgreSQL user)Revoke some privileges from the main PostgreSQL user (If needed)