First, I ran:

sudo su postgres
createuser -U postgres foouser -P

which worked fine, and I ran:

createdb -U foouser -E utf8 -O foouser foodatabase -T template0

and got "permission denied: cannot create database"

Firstly, should I even su as postgres to do operations like the first one (assuming my postgres data dir is owned by postgres), or is -U postgres from any user (assuming trust is used in pg_hba.conf) sufficient?

Secondly, why am I running into this error? Is this because the user foouser is a non-superuser? Should I create foodatabase using the postgres user and simply -O foouser?

  • You seem to be asking a lot of (relatively) basic Postgres questions. While we don't mind answering them you would really be better served by cracking open the Postgres documentation. If this is your first experience with Postgres their tutorial/getting started pages will give you a pretty effective crash course. – voretaq7 Dec 15 '12 at 20:53
  • @voretaq7 - thanks. I'm not really new to Postgres (which you can tell by my suggested answers in my questions, here and on SO), though I'm not a pro. I ask all my questions in this format because it helps me learn things in a non-linear manner. People give out all sorts of great info when I ask questions like this and I extrapolate info from their answers, combined with docs/Google. For example, I just learned (from you) that using the command line tools is a bit frowned upon (nobody from my work uses them, but I've never really asked anyone why). – orokusaki Dec 16 '12 at 0:48

You are receiving this error because (as you correctly surmised) foouser is not a superuser, and does not have the CREATE DATABASE privilege.

You can create the database as your postgres superuser, with foouser as the owner, which is probably the most sane option.
Alternatively you could make foouser a DB superuser (definitely not recommended), or grant that user the CREATE DATABASE privilege (also not really recommended - your individual database users should be plain ordinary users, Database Owner is the highest level of privilege they should have.)

Generally I advise against using the Postgres command-line tools. There is nothing wrong with them, but using the SQL command line to interact with your database is preferable in my opinion.
The documentation on the CREATE DATABASE SQL command is, IMHO, better than the documentation for the command-line equivalent as well.

The SQL equivalent to what you're doing on the command like would be:

CREATE DATABASE foodatabase WITH OWNER foouser ENCODING 'utf8' TEMPLATE template0;
  • Thanks voretaq. Do you prefer to use SQL just because the command lines are a bit of a black box? – orokusaki Dec 16 '12 at 0:53
  • 2
    @orokusaki Creating databases from SQL is the typical way DBAs approach the task. You will likely need to use SQL for many related tasks. The complete creation process can be scripted in SQL much easier than from the command line. – BillThor Dec 16 '12 at 3:55
  • Pretty much what @BillThor said - If you know what SQL is being executed behind the scenes the command line tools make sense (their options map exactly to the SQL options), but I've found the Postgres SQL documentation to be better, and I often need to do "Other Stuff" like creating procedural languages after creating a database, so it's easier/faster if I'm already inside the SQL environment. – voretaq7 Dec 16 '12 at 4:43
  • @BillThor - thanks, and thanks voretaq7. – orokusaki Dec 17 '12 at 15:01

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.