0

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!

1
  • i think issue i finally came to realize is that with postgresql, you have to explicitly grant ownership, permissions etc always no matter what...for example just because a user owns a database doesn't mean it can have full permissions to everything on it especially when data is copied over owned by another user...in short my advice...always remember to grant permissions always
    – uberrebu
    Aug 27 at 9:11
0

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 😬

2
  • step 4A alternatively alter database <dbname> owner to <username>;
    – Jasen
    May 24 at 5:18
  • issue was even though a role owns a database...restoring data from another database owned by another role caused issues..i had to make sure i pass ownership in command to restore...postgresql is just very strict
    – uberrebu
    Aug 27 at 9:13
0

Seems simple to me. Where does the problem crop up for you?

create user newuser with password 'newuser';
create database newuser owner newuser;
\c newuser newuser
create table sites (id int);
select * from sites ;
1
  • issue was even though a role owns a database...restoring data from another database owned by another role caused issues..i had to make sure i pass ownership in command to restore...postgresql is just very strict
    – uberrebu
    Aug 27 at 9:13
0

You can set the owner when you create the database

shell: createdb --owner <username> <dbname>

or SQL: create database <dbname> with owner <username>;

if you forgot to set the owner when creating the database run this query (as any superuser eg: postgres):

alter database <dbname> owner to <username>;

As owner they get all permissions on that database (with grant option also).

1
  • issue was even though a role owns a database...restoring data from another database owned by another role caused issues..i had to make sure i pass ownership in command to restore...postgresql is just very strict
    – uberrebu
    Aug 27 at 9:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.