3 ms·

Just creating a new user is annoying enough. Permissions are also much more complex. What the hell are schemas?

• oftenwrong 6y ago
  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.
  • eznzt 6y ago
    What you are basically saying is that yes, they are much more complex.
    • desas 6y ago
      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.
      • Tostino 6y ago
        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.
    • rleigh 6y ago
      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.
    • chris_wot 6y ago
      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.
• looperhacks 6y ago
  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)
  • vinger 6y ago
    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.
  • mgkimsal 6y ago
    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 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.
    • chousuke 6y ago
      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.
• tinus_hn 6y ago
  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.
  • mvanbaak 6y ago
    pgadmin ;-P
    • tinus_hn 6y ago
      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.
      • jhauris 6y ago
        Look at the pg_hba.conf file (probably something like /etc/postgresql/<version>/main/pg_hba.conf). https://www.postgresql.org/docs/current/auth-pg-hba-conf.html https://www.postgresql.org/docs/current/auth-pg-hba-conf.htm...
        • tinus_hn 6y ago
          Perhaps it’s better to only listen to localhost and connect through a SSH proxy connection
    • rleigh 6y ago
      Or DataGrip if you already have a JetBrains licence.
  • rektide 6y ago
    both kubernetes operators have some ok user management built in
• paulryanrogers 6y ago
  Schemas are SQL standard namespaces within a DB. You can join between them. Mysql allows joining across databases, so it doesn't implement schemas.
• lucian1900 6y ago
  What MySQL calls databases are actually schemas. They’re even aliases as such. MySQL doesn’t have multiple SQL databases, you’ve been using multiple schemas.
• isoprophlex 6y ago
  "What the hell is scoping? Why not put every variable in global scope? Much less complex"