Database permissions for provisioned PostgreSQL VDBs
This topic describes the database permissions on provisioned POSTGRES virtual instances
PostgreSQL authentication
Authentication is the process by which the database server establishes the identity of the client, and by extension determines whether the client application (or the user who runs the client application) is permitted to connect with the database username that was requested.
PostgreSQL offers a number of different client authentication methods. The method used to authenticate a particular client connection can be selected on the basis of (client) host address, database, and user.
PostgreSQL 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, there need be no connection between database user names and OS user names.
Delphix postgreSQL authentication
Delphix for PostgreSQL requires that the staging and target hosts must already have the necessary users and authentication systems created/installed on them. Delphix will neither create users nor change database passwords as part of the provisioning process. It is required to have ‘trust’ as a PostgreSQL authentication mechanism on the source with a target server.
The following section describes some important authentication methods used by PostgreSQL architecture.
Trust authentication
When trust authentication is specified, PostgreSQL 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 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 for a user, the stored password is null and password authentication will always fail for that user.
There are some more authentication mechanism which is less often used, refer to the Postgresql link embedded for more insights on them.