Database authentication methods for PostgreSQL connector datasets

This topic describes database authentication on provisioned PostgreSQL connector virtual databases.

PostgreSQL authentication

PostgreSQL connector offers different client authentication methods. The method used to authenticate a particular application/client connection can be selected based on the (client) environment address, database, and user.

PostgreSQL connector database user names are logically separate from user names of the operating system in which the server runs. If all the users of a particular server also have accounts on the server's machine, it makes sense to assign database user names that match their operating system usernames. However, a server that accepts remote connections might have many database users who have no local operating system account, and in such cases, any connection between database user names and environment user names is not needed.

Delphix Continuous Data Engine PostgreSQL connector authentication

The Delphix Continuous Data Engine PostgreSQL connector requires the staging and target environments to already have the necessary users and authentication systems created/installed on them. Delphix Continuous Data Engine will neither create users nor change database passwords as part of the provisioning process. As a best practice, use a secure PostgreSQL connector authentication mechanism on the source, staging, and target servers.
To connect to the PostgreSQL connector VDB. Add an entry in pg_hba.conf like below.
You can choose the method as per your requirements.

CODE

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             0.0.0.0/0               scram-sha-256

The following section describes some important authentication methods used by PostgreSQL connector architecture.

Trust authentication

When trust authentication is specified, the PostgreSQL connector assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names). Of course, restrictions made in the database and user columns still apply. This method should only be used when there is adequate operating-system-level protection on connections to the server.

Password authentication

The password-based authentication methods are md5 and password. These methods operate similarly except for the way that the password is sent across the connection, namely MD5-hashed and clear-text respectively.

PostgreSQL connector database passwords are separate from operating system user passwords. The password for each database user is stored in the pg_authid system catalog. Passwords can be managed with the SQL commands CREATE USER and ALTER ROLE, e.g., CREATE USER ‘foo’ WITH PASSWORD 'secret'. If no password has been set up, the stored password is null and password authentication will always fail.
Some more authentication mechanisms are less often used. For more information, refer to the following PostgreSQL connector links.