Ability to use SQL in "DB Restriction" field (like pgAdmin 3 did) (RM #4479)

[dpage]

Issue migrated from Redmine: https://redmine.postgresql.org/issues/4479
Originally created by John S at 2019-07-19 14:30:18 UTC.

Currently, if you wish to filter the databases shown when you connect to a server in pgAdmin 4, you must add every single database name to the "DB Restriction" field.

myprefix_database1 myprefix_database2 myprefix_database3 myprefix_database4 myprefix_database5 ...

This can be tedious especially if there are many databases, and if you frequently add and delete them for testing different things.

In pgAdmin 3, it was possible to use SQL in the "DB Restriction" field. This allowed you to apply filtering based on regexes or any kind of WHERE clause. For instance, you could enter this to show all databases whose names contained "myprefix_"

SELECT datname FROM pg_database
WHERE datistemplate = false AND datname ILIKE '%myprefix_%'
ORDER BY datname

This was extremely convenient. Not only was it shorter, but was dynamic and would automatically update your list if there were changes. It's a shame this feature was lost in the transition to pgAdmin 4.

Can it please be re-added?

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/4479#note-1
Originally created by Akshay Joshi at 2019-08-09 10:06:50 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Sprint changed		Ready

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/4479#note-2
Originally created by Andreas Klein at 2020-01-17 01:14:04 UTC.

Couldn't agree more. With the current implementation users have to add any newly created database to the DB Restrictions whitelist by hand.
After renaming or creating a new database it will be visible for the duration of the already active connection, but unless the user then adds the new database name to the DB Restrictions filter, which to make matters worse is only possible while the connection is closed, the next time a connection is opened to the server, the database will no longer appear.

We run several Postgres servers in-house, that are shared by all developers and we use naming prefixes to clearly indicate who a database belongs to, so people can stay off each others' turf. It's just annoying if you need to drop a database but can't do so because someone else has (accidentally) opened a connection to it in pgAdmin.

In pgAdmin III we used the same approach the OC mentioned and each developer would restrict access to databases with their personal prefix plus a number of truly shared databases, e.g.:

SELECT datname FROM pg_database WHERE datname ILIKE 'ak_%' OR datname IN ('postgres','dev_i18n')

Losing the ability to filter databases based on plain old SQL in my view pretty much renders the DB Restriction field unusable.

[dpage]

Comment migrated from Redmine: https://redmine.postgresql.org/issues/4479#note-3
Originally created by Nikhil Mohite at 2021-07-02 11:41:57 UTC.

Redmine ticket header update:

Name	Old Value	New Value
Difficulty changed		Medium

[Toff0693]

This feature is much-awaited.
A explained, when we manage a lot of databases with a suffix to differentiate the customers, it will be very helpful to avoid mistake during operation.