Support passwordless Postgres database connections

[zackkrida]

Problem

Perhaps bafflingly, there are valid use cases for allowing users to connect to a database without a password set for the user / login role. In terms of Mathesar's UI, this means allowing the following password field to be blank and the form to be submitted:

Proposed solution

Update the frontend form to make password an "optional" field:

mathesar/mathesar_ui/src/systems/databases/create-database/ConnectExistingDatabase.svelte

Line 32 in f8647ae

const password = requiredField('');

Then, ensure that the resulting API calls to connect the database and save the connection also support a "blank" password, represented as an empty string "" or whatever makes the most sense for the code.

Additional context

I think this is important for a few reasons:

• In a localhost context, folks may not have a password set for convenience or any number of reasons
• Interesting and rising-in-popularity tools using embedded PGlite, like https://database.build, provide a connection string without a password (for example postgres://postgres@{unique-hash-subdomain}.browser.db.build/postgres?sslmode=require) and Mathesar is unable to connect. This tool alone is a quite nice one to show off Mathesar's capabilities to folks without database experience, and it's a shame it doesn't work.
• Broadly speaking, since our permissions system is so tightly modeled to postgres we should continue that feature parity in this aspect, too.

[seancolsen]

@mathemancer you might like to weigh in on this

[mathemancer]

I see no problem with supporting folks logging into users which already exist, but don't have a password, in Mathesar. It's not hard to make the changes in the back end.

However: I don't want to have to support folks creating login roles without passwords in the Mathesar UI. You (typically) have to alter a config file for the PostgreSQL server to actually allow logging into such roles, and that config file is not necessarily in a consistent place on all systems. For some situations (e.g., managed PostgreSQL instances), it may not even be possible to alter that file. So, you'd have a whole lot of explaining to do to help a user navigate the situation if we allow actually creating login roles without passwords in the UI.

[zackkrida]

I don't want to have to support folks creating login roles without passwords in the Mathesar UI.

Agreed. I think the passwordless use case is really only relevant to folks with existing databases configured that way. Basically: it's a pattern we should support; not endorse.