Managing PostgreSQL databases and users from the command line

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

🚧

Important

If your account includes cPanel, you must use it instead to manage PostgreSQL databases and users; you cannot do this from the command line. For information about how to manage PostgreSQL databases and users in cPanel, please see this article.

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:

   Bash

   su - postgres
   

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

   Bash

   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:

   Bash

   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:

   Bash

   createdb -O user dbname
   

   👍

   Tip

   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:

   Bash

   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:

   Bash

   GRANT permissions ON DATABASE dbname TO username;
   

   📘

   Note

   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

❗️

Warning

The dropdb program does not ask for confirmation before deleting a database. As soon as you press Enter, PostgreSQL deletes the database and all of the data it contains.

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

🚧

Important

If the user owns any databases or other objects, you cannot drop the user. Instead, you receive an error message similar to the following:

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

You should change the database's owner (or drop the database entirely), and then you can drop the user.

More Information

To view the official PostgreSQL documentation, please visit http://www.postgresql.org/docs.

Related Articles

• Connecting to PostgreSQL using psql