This article describes how to add and delete PostgreSQL databases and users from the command line.
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:
su - postgres
You can now run commands as the PostgreSQL superuser. To create a user, type the following command:
createuser --interactive --pwprompt
To create a PostgreSQL database, follow these steps:
su - postgres
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
To grant an existing user privileges to a database, follow these steps:
GRANT permissions ON DATABASE dbname TO username;
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:
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:
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.
To view the official PostgreSQL documentation, please visit http://www.postgresql.org/docs.
Subscribe to receive weekly cutting edge tips, strategies, and news you need to grow your web business.
No charge. Unsubscribe anytime.