Home / server / Questions / 458401
Accepted
orokusaki
Asked: 2012-12-16 11:19:54 +0800 CST

Why doens't my Postgres user have permissions to add a Postgres database?

  • 772

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?

linux

1 Answers

  1. Best Answer

    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;
    • 4