Remove superuser permission


List all databases

postgresql - How do I list all databases and tables using psql? - Database Administrators Stack Exchange

psql -U username -l

Restore dump

psql -U postgres -d bpc -f bpc-2012-11-14.sql

Change postgres user password

sudo vim /etc/postgresql/9.1/main/pg_hba.conf 

Make auth method trust.

sudo /etc/init.d/postgresql restart
sudo psql -U postgres
postgres=# ALTER USER postgres with password 'new_password';

Replace auth method back.

Option to run commands with TCP method


$ psql -U postgres -h localhost

Change password for postgres user

$ sudo -u postgres psql
Enter password: ...

Configuring to use password not PEER auth

After configuring the password, edit the file /etc/postgresql/8.4/main/pg_hba.conf to use MD5 authentication with the postgres user:

local   all         postgres                          md5
sudo /etc/init.d/postgresql-8.4 restart

Drop (delete) user

dropuser -U postgres username

Allow a user create database

ALTER USER your_username CREATEDB;

Change postgres user password

Assuming you still have TRUST authentication setup on the local server, open a psql session as the postgres user (you won't be asked for the password under TRUST authentication) to the database and execute the command:

sudo -s
sudo -u postgres psql 
ALTER USER Postgres WITH PASSWORD '<newpassword>';

Quit pqsl

Type \q and press Enter.



Dropping a role

What if we want to drop (delete, remove) a role? Easy:

#DROP ROLE demorole1;

For convenience, the programs createuser and dropuser are provided as wrappers around these SQL commands that can be called from the shell command line:

createuser name
dropuser name
sudo -u postgres createuser -P django

Create database

To create the first database, which we will call "mydb", simply type:

sudo -u postgres createdb mydb

Drop a database



sudo apt-get install postgresql

pgAdmin III is a handy GUI for PostgreSQL, it is essential to beginners. To install it, type at the command line:

sudo apt-get install pgadmin3

To start off, we need to change the PostgreSQL postgres user password; we will not be able to access the server otherwise. As the “postgres” Linux user, we will execute the psql command.

sudo -u postgres psql postgres

Set a password for the "postgres" database role using the command:

\password postgres