Postgresql – why is granting ALL permissions and privileges to a new role/user for a new database difficult with postgresql

permissionspostgresql

With mysql and am sure countless other database softwares, it is pretty easy to create a new database, create a new user and grant that user to own and be able to do everything on that database

this is NOT the case with postgresql
Every time i am about to create a new database and user on postgresql it is a such a pain to think about because i KNOW i will come across head ache again

Why is postgresql such a pain with uses and database and allowing a user to own a database so the user/role has all permissions

It is crazy to always be getting errors like

permission denied for table sites

How can one create a new database, new user with password and grant ownership of the new database to that new user so the user/role can do EVERY and ANYTHING on that new database? what commands to achieve all these? for postgresql?

Thanks!

Best Answer

There are certainly some differences between PostgreSQL and other databases, such as MySQL. When I first started working with PostgreSQL, I found it to have a lot more friction than the servers I was more familiar with. However, a bit of practice — and a lot of snippets in Evernote — reveals the method to the madness.

That said, to answer your question, there are 4.5 steps to create a database, a user account, and grant all permissions.

From the command line:

  1. Create the user:

    $ sudo -u postgres createuser <username>

  2. Create the database:

    $ sudo -u postgres createdb <dbname>

    The next commands are done in PostgreSQL, so step 2.5 is logging in to the server:

    $ sudo -u postgres psql

  3. Assign the user a password:

    ALTER USER <username> WITH ENCRYPTED PASSWORD '<password>';

  4. Grant privileges on database:

    GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <username> ;

Done and done.

You probably do this already, but a note-taking system like Evernote (or just a well-structured directory with text files) is an indispensable tool for processes like this until muscle memory kicks in ?

Related Question