Support authentication by PostgreSQL login

Hi,

I'd like to see PgAdmin authenticate by asking for a Postgres role and password and authenticating by connecting to a Postgres cluster with the given credentials.

Having to create and maintain a separate authentication database is an unnecessary hassle. Considering that the point of PgAdmin is to directly manipulate all aspects of a Postgres cluster, "hiding" the Postgres login role used for db connection behind a level of indirection provided by a 2nd, non-Postgres, authentication database often has no benefit.

CONFIGURATION SETTINGS:

The configuration settings would be:

AUTHENTICATION_SOURCES would allow a "postgresql" value, turning on the feature.

There would also be a POSTGRESQL_SERVERS parameter. Its value would be a dictionary, keyed by the pg server name to be displayed to the users in the web interface. The value of each key (server name) is another dict, with libpq key words for keys and connection values for values. (See https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS) The accepted keys would be:

• host
• hostaddr
• port
• dbname
• user
• password
• passfile
• require_auth
• channel_binding
• connect_timeout
• client_encoding
• options
• application_name
• keepalives
• keepalives_idle
• keepalives_interval
• keepalives_count
• tcp_user_timeout
• gssencmode
• sslmode
• sslcompression
• sslcert
• sslkey
• sslpassword
• sslcertmode
• sslrootcert
• sslcrl
• sslclrdir
• sslsni
• requirepeer
• ssl_min_protocol_version
• ssl_max_protocol_version
• krbsrvname
• gsslib
• gssdelegation
• service
• target_session_attrs
• load_balance_hosts

Really, I am only interested in "host" and "port", but I see no reason not to include all the libpq parameters that make sense. If any of the supplied values conflict with other pgadmin settings it makes sense to me that the most specific override the most general setting, which as far as I can see means having the POSTGRESQL_SERVER value set for a particular server override the "overall" pgadmin setting. (But see BEHAVIOR below.)

If a key is omitted (or a value is Python None) the pg default would be used, just as when the parameter is not supplied to libpq.

The following parameters cannot be specified, for the reasons given:

• fallback_application_name: No application
• replication: No application (right?)
• requiressl: Depreciated

A typical example configuration snippet to connect to a remote server would look like:

AUTHENTICATION_SOURCES = ['postgresql']
POSTGRESQL_SERVERS = {'Our Server': {'host': 'ourserver.example.com', 'dbname': 'postgres'}}

For connecting to a local server:

AUTHENTICATION_SOURCES = ['postgresql']
POSTGRESQL_SERVERS = {'My Server': {'dbname': 'postgres'}}

An unusual configuration with both a local and remote server:

AUTHENTICATION_SOURCES = ['postgresql']
POSTGRESQL_SERVERS = {
'My Server': {'dbname': 'postgres'}},
'Our Server': {'host': 'ourserver.example.com', 'dbname': 'postgres'}}
}

BEHAVIOR:

Pgadmin would attempt to connect to the specified "dbname" of the POSTGRESQL_SERVERS in order. (Python dicts are now ordered. There may or may not be issues with older pythons.) If a connection succeeds access to pgadmin is granted. If no connection succeeds the next AUTHENTICATION_SOURCES is tried.

If any of the POSTGRESQL_SERVERS connection attempts succeeds, all POSTGRESQL_SERVERS keys are shown to the user in the user interface.
(Alternately, pgadmin could show only those to which connection succeeds. But why not keep it simple for now? The user might be in possession of another set of credentials that reach other servers displayed and it might
be useful to show what servers are configured regardless of access privileges.)

If the "postgresql" authentication source fails, no connection attempts succeed, POSTGRESQL_SERVERS is ignored. It might make sense, instead, to use POSTGRESQL_SERVERS values for defaults or to entirely replace some connection values stored in the CONFIG_DATABASE_URI database. But that is out of scope for this feature request.

If the "postgresql" authentication source succeeds, the user's credentials are cached and used for all subsequent db connections. The "dbname" parameter is ignored; it is used only when granting access to pgadmin itself.
The other parameters are used when making db connections.

On startup connection parameter keys should be validated and (minimally) unknown values logged in order to catch typos. Or do whatever pgadmin does that's smarter when it detects configuration errors.

DISCUSSION:

I imagine that the admin will almost always want to use the "dbname" parameter, and supply a value of "postgres", because the "each user has their own database" default which postgres assumes is probably atypical.

Alternately, to keep it even simpler, the default "dbname" parameter value for pgadmin could be "postgres". (Chosen because the postgres db always exists. An alternative would be 'template1".) But that might necessitate another parameter that would restore the pg default of using the pg login role as the db name, e.g, "pgadmin_use_user" (values of "yes" or "no") . (This would only ever matter where pg_hba.conf is tuned to limit access to the "postgres" db. Somebody might care.)

As PostgreSQL introduces new authentication methods pgamdin will have access to them through this feature, although that's not the point. (As stated, the point is to cut down on the administrative overhead involved in maintaining a set of authentication credentials just for pgadmin.)

In my use-case, having a single authentication database used by both pgadmin and pg means that the user's don't have to keep track of 2 separate passwords, one for getting into pg and another for getting into pgadmin. Pgadmin is not they only way they access the cluster. We do try to keep it simple and have the pg role names match the pgadmin login names. But because the users manage their own passwords we can't enforce identical passwords in the separate authentication databases, which sooner or later leads to confusion.

Setting "user" and "password" and "passfile" open up opportunities for the admin to configure access without authentication, similar (I presume) to what the master password does although providing more control. This also is a side-effect and not the point.

UNANSWERED QUESTIONS:

It may be that the CONFIG_DATABASE_URI database can be optional when using the "postgresql" authentication method. This would ease setup and lower the barrier to entry. (Apologies, I don't really know what goes in the configuration database.) It seems theoretically possible to get by with only login credentials and db connection parameters, which this feature would supply. If the configuration database enables other features these can be disabled if there is no configuration database.

Even having a sqlite configuration database requires some management. It'd be nice if this overhead could also go away.

Likewise, shipping with the authentication method set to "postgresql" as the default, with a

POSTGRESQL_SERVERS = {'My Server': {'dbname': 'postgres'}}

would serve the common case of a local PG server and eliminate the need for initial configuration/user setup.

Start with the existing AUTHENTICATION_SOURCES and CONFIG_DATABASE_URI configuration paths, then trace how PostgreSQL connections authenticate through libpq. Review the proposed POSTGRESQL_SERVERS settings and the fallback behavior described in the issue. Done would require an agreed design for connection attempts, credential reuse, validation, and the user-interface server list.