CODEWITHSUNDEEP
linuxdatabasepostgresqlubuntusyntax
generic_user
generic_user

Reputation: 3562

Can't create empty database with postgres -- permission denied when changing directory

I am learning SQL, via postgres. I understand the basic concepts of databases, but I'm having some IT trouble. I am following this tutorial.

Go to my working directory:

myname@myname:~$ cd Dropbox/working_directory

make a new user:

myname@myname:~/Dropbox/working_directory$ sudo -u postgres createuser --interactive
[sudo] password for myname: 
could not change directory to "/home/myname/Dropbox/working_directory": Permission denied
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

OK, it couldn't change the working directory, but it let me add "sammy", and set "sammy" to be a superuser. Is this what happened?

According to the tutorial, one should make a database with the same name as the user. This seems arbitrary. Is that so? Anyway, when I go and try to create a database named "sammy", I get an error that I can't change the working directory:

myname@myname:~/Dropbox/working_directory$ sudo -u  postgres createdb sammy
could not change directory to "/home/myname/Dropbox/working_directory": Permission denied

Looking in the file browser, I don't see any sign of "sammy"

1. Why can't I change the working directory? How do I make an empty database? What is the logic behind this behavior?

2. Is naming it "sammy" as ridiculous as it seems? Do users need to be named the same as the databases? Why or why not?

It doesn't seem to help to log into the postgres identity that installs with postgres:

myname@myname:~/Dropbox/working_directory$ sudo -i -u postgres
[sudo] password for myname: 
postgres@myname:~$ pwd
/var/lib/postgresql
postgres@myname:~$ cd /home
postgres@myname:/home$ cd /home/myname/
-bash: cd: /home/myname/: Permission denied

OK, that didn't work. Now, go back to myname@myname:

postgres@myname:/home$ sudo -i -u myname
[sudo] password for postgres: 
Sorry, try again.
[sudo] password for postgres: 
Sorry, try again.
[sudo] password for postgres: 
sudo: 3 incorrect password attempts
postgres@myname:/home$

This is where I start swearing.

3. How does one get out of the postgres identity? Besides closing the terminal and opening another one?

Upvotes: 0

Views: 1790

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246308

You are confused on several levels.

  1. The message

    could not change directory to "...": Permission denied

    is not from PostgreSQL or the createuser and createdb commands, but from sudo.

    It tells you that after becoming operating system user postgres, it cannot run in your current directory, because user postgres doesn't have the required file system permissions.

    Processing continues, however (in a different working directory), and the createuser and createdb commands succeed.

  2. createuser and createdb do not create something in your current working directory, but in the database directory.

    This directory belongs to postgres, and you probably can find it with

    echo $PGDATA

    when you are user postgres.

You exit an interactive sudo session by typing exit, and the name you give to a database is your choice. You would probably not call your production database “sammy”, but then, why not? Just make sure that you use only letters, numbers and underscores and no upper case characters.

Upvotes: 1

Related Questions