[chousuke]

With MySQL, you'll still have to switch to root to connect by default? I honestly don't remember, since it's been ages since I set up MySQL manually.

If MySQL actually allows administrative access out-of-the-box without any kind of special authorization, then that's a terribly insecure default.

With PostgreSQL, you have to switch to the superuser to configure things further because that's the only sane default you can have on an unconfigured system. If you can run commands as the user PostgreSQL is running as, you are "safe" to trust, and PostgreSQL will let you in.

UNIX ident authentication is also is extremely convenient for local applications, since you don't even have to have a password for the account, or make the PostgreSQL server network-accessible in any way.

Oracle can do the same thing, and so can MySQL, apparently (with IDENTIFIED VIA unix_socket).

MySQL user management has its own complexity in that you have to manage "user@address" identities, and the same user at different addresses or auth methods can have different permissions. How's that "simple"? With PostgreSQL, your users will at least map to the same user regardless of how they authenticate themselves.

[mgkimsal]

"With MySQL, you'll still have to switch to root to connect by default? "

You connect with a root account from any account, and when installed, the root account password is part of the setup process.

"and the same user at different addresses or auth methods can have different permissions"....

It joe@localhost and joe@remotehost don't have to be 'the same user' in that they're not tied to a system account in any way.

Granting different privileges to joe@local and joe@remote based on where they're coming from isn't necessarily "simple", but no one claimed it was. My own response was validating that PostgreSQL user setup was somewhat confusing.

EDIT: Bringing up "mysql sucks" points when I was explaining how PostgreSQL 'create user' stuff can be confusing just reeks of whataboutism.

[chousuke]

I'm just not sure how it's confusing? PostgreSQL users aren't "tied" to system accounts either. You can have any number of PostgreSQL users that have no system equivalent.

In fact, the process seems to be exactly the same as with MySQL: I just tried installing the MariaDB server (dnf install mariadb-server), and it didn't prompt me for an admin user; instead, I can directly connect to the database as root using sudo, so in this case it appears to be doing the exact same thing that PostgreSQL does.

It just happens to be that by default the "postgres" superuser has a corresponding "postgres" system user that can log in via OS authentication, so you need to switch to the postgres user instead of root.

EDIT: Maybe some of the confusion stems from the fact that the documentation you linked seems to assume that the database is created according to convention to run as the "postgres" user (as it usually is). If your user didn't have the required permission to switch to the postgres user, they wouldn't be able to install the database as said user in the first place.

If you install PostgreSQL as your own user (which is not a good idea if you have any other option), you will not need to switch users as you will obviously have access to the database files and can do whatever you want, anyway.

[mgkimsal]

"Maybe some of the confusion stems from the fact that the documentation you linked seems to assume that the database is created according to convention to run as the "postgres" user (as it usually is)".

The entire point was a reply to someone saying "it's confusing". I'm pointing out how it's confusing, and you come back with that either that 1) MySQL is confusing or 2) you don't think it's confusing. Then you point to documentation which you admit might be a point of confusion.

I've had people say "I installed postgres - here's the password". Then... I can't log in. Because I can't switch to the postgres user. Or they created some login that I can't use. Or something else... because it's somewhat confusing, unless you do this (postgres administration) as part of your regular/periodic work.

re: "I just installed Maria"... If someone uses common default package managers to set up mysql/Maria, and also for postgres, you'll be able to connect to mysql/Maria from any account. You'll only be able to connect to postgres if you switch to the postgres user.

Again - point of the comment was agreeing with an earlier comment that "this is confusing". You seem to acknowledge that it can be confusing.

[chousuke]

> I've had people say "I installed postgres - here's the password". Then... I can't log in. Because I can't switch to the postgres user.

You don't need to switch to the postgres user if you have another database user and password.

Are you talking about a situation where someone has installed a PostgreSQL server but hasn't configured PostgreSQL to allow password authentication? The server admin needs to allow that explicitly, because some distributions don't allow password authentication even on localhost by default, but honestly, it's all very well documented.

> If someone uses common default package managers to set up mysql/Maria, and also for postgres, you'll be able to connect to mysql/Maria from any account.

This is not the case on Fedora at least, since fresh out of the package the MySQL root user has no password; the only way to connect is via local system authentication as the root user.