[mgkimsal]

Most tutorials/instructions I read have you use "createuser" command from the system shell. But... you have to be able to switch to a system 'postgres' user first, which ... perhaps you don't have privileges to do, or need sudo access or whatnot.

If you can install postgres, connect to it directly with some sort of root identity, then immediately create users and databases (as is the case with pretty much every mysql walk-through I've ever seen), it's not a default.

https://wiki.postgresql.org/wiki/First_steps

"The default authentication mode is set to 'ident' which means a given Linux user xxx can only connect as the postgres user xxx."

This alone is a complicated/confusing thing, because it's mixing system accounts with the db server accounts/access - and none of that is obvious, and doesn't quite map to how other databases handle things. I've never had to have matching system account names for user access in MSSQL, for example.

[rleigh]

This depends entirely on how you want to set up and run the system. For packaged versions running as a system service with a dedicated service user, this is absolutely correct. And I would argue, it's a pretty sensible default arrangement.

But... there's absolutely nothing prohibiting you from running initdb as a regular user and then running the main daemon with your credentials. You are then the database owner and superuser. This type of thing is really useful for integration testing. But it's potentially useful when you don't care about the multiuser aspect and just want to have it run.

[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.