PostgreSQL : create a user, a database and grant accesses

June 6th, 2007 by Stephane Kattoor

The following step-by-step will let you create a user, a database (DB) and grant full access to the user to this DB.

All the commands are executed as the postgres privileged user.

Create the user

For this, you use the command createuser which is provides with the postgreSQL package. Then answer the questions as you see fit :

postgres@hostname:~$ createuser
Enter name of role to add: username
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE
postgres@hostname:~$

Create the DB

Use the createdb command to create the database :

postgres@hostname:~$ createdb databasename
CREATE DATABASE
postgres@hostname:~$

Grand access for the user to the DB

And last, using the psql command, set a password for the user and grant accesses :

postgres@hostname:~$ psql
postgres=# alter user username with encrypted password 'password';
ALTER ROLE
postgres=# grant all privileges on database databasename to username;
GRANT
postgres@hostname:~$


Most Commented Posts

Posted in Dev | 3 Comments »

3 Responses to “PostgreSQL : create a user, a database and grant accesses”

  1. Peyton Says:
    August 19th, 2007 at 4:57

    hi nice post, i enjoyed it

  2. Stéphane Kattoor Says:
    August 19th, 2007 at 12:02

    hey Peyton ! Thanks for your visit, glad you liked the post !

  3. Rico Says:
    February 14th, 2009 at 16:45

    Great!, but explain more for noobs, for example its esensial use the postgres user

Leave a Reply