How to manage PostgreSQL databases and users from the command line

This article describes how to add and delete PostgreSQL databases and users from the command line.

Creating PostgreSQL users

A default PostgresSQL installation always includes the postgres superuser. Initially, you must connect to PostgreSQL as the postgres user until you create other users (which are also referred to as roles).

To create a PostgreSQL user, follow these steps:

1. At the command line, type the following command as the server's root user:

   su - postgres

2. You can now run commands as the PostgreSQL superuser. To create a user, type the following command:
   

   createuser --interactive --pwprompt

3. At the Enter name of role to add: prompt, type the user's name.
4. At the Enter password for new role: prompt, type a password for the user.
5. At the Enter it again: prompt, retype the password.
6. At the Shall the new role be a superuser? prompt, type y if you want to grant superuser access. Otherwise, type n.
7. At the Shall the new role be allowed to create databases? prompt, type y if you want to allow the user to create new databases. Otherwise, type n.
8. At the Shall the new role be allowed to create more new roles? prompt, type y if you want to allow the user to create new users. Otherwise, type n.
9. PostgreSQL creates the user with the settings you specified.

Creating PostgreSQL databases

To create a PostgreSQL database, follow these steps:

1. At the command line, type the following command as the server's root user:

   su - postgres

2. You can now run commands as the PostgreSQL superuser. To create a database, type the following command. Replace user with the name of the user that you want to own the database, and replace dbname with the name of the database that you want to create:
   

   createdb -O user dbname

   • PostgreSQL users that have permission to create databases can do so from their own accounts by typing the following command, where dbname is the name of the database to create:

     createdb dbname

Adding an existing user to a database

To grant an existing user privileges to a database, follow these steps:

1. Run the psql program as the database's owner, or as the postgres superuser.
2. Type the following command. Replace permissions with the permissions you want to grant, dbname with the name of the database, and username with the user:

   GRANT permissions ON DATABASE dbname TO username;

   For detailed information about the access privileges that are available in PostgreSQL, please visit http://www.postgresql.org/docs/9.1/static/sql-grant.html.
3. The user can now access the database with the specified permissions.

Deleting PostgreSQL databases

Similar to the createdb command for creating databases, there is the dropdb command for deleting databases. To delete a database, you must be the owner or have superuser privileges.

Type the following command, replacing dbname with the name of the database that you want to delete:

dropdb dbname

Deleting PostgreSQL users

Similar to the createuser command for creating users, there is the dropuser command for deleting users.

To delete a specific user, type the following command. Replace username with the name of the user that you want to delete:

dropuser username

dropuser: removal of role "username" failed: ERROR:  role "username" cannot be dropped because some objects depend on it
DETAIL:  owner of database dbname

More Information

• To view the official PostgreSQL documentation, please visit http://www.postgresql.org/docs.
• To learn more about hosting your PostgreSQL database with A2 Hosting, click here.