How is creating a new user complicated? The normal CREATE USER is all I've ever needed to create a new user in postgres (assuming I don't have set up the pg_hba so that I need to allow every user separately)
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.
"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.
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.
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.
"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.
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.
"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.
Does postgres still require a separate user to access the db.
I remember this being a limitation in 2008.
That forced user creation always pushed me to mysql because I hate having separate users for each service because you still have to manage and account for these extra accounts.
Schemas are similar to databases in mysql. They serve as a namespace. In mysql you can have a database `foo` and a table `foo.bar`. In postgres you can have a schema `foo` and a table `foo.bar`. In postgres you can have multiple databases in a cluster, and multiple schemas within each of those databases.
They are not complex, and they are entirely optional. They are just a namespace. You are free to never, ever use them.
For a company the size of Uber, I don't think spending five minutes reading the documentation for createuser is a significant burden to deployment. PostgreSQL is very easy to deploy.
It's another layer, you don't have to use it. If you pretend schemas don't exist you basically never know they do, unless you go looking for complexity in the postgres bowels.
To be honest, it's better to think of MySQL db = Postgres schema, because I'm MySQL you can do cross db queries and there is no intermediary schema level, and in Postgres you can do cross schema queries, but not cross db queries.
No, what you mistake for "complexity" seems to be your general unfamiliarity of what a schema is. In fact, when you understand what schemas are, then it actually makes a lot more sense.
It would be great if there was some management GUI for these tasks so you don’t have to look up the syntax for these things that in many deployments you only do once.
This actually looks pretty reasonable, I am going to look into it. First I need to figure out how to open up the server for connections but still limit it, though.