Wiki-World

Login: standard access only on local socks connection

 $ sudo -u postgres psql

Login with user

 $ psql <dbname> <user>

Create new user

 =# CREATE ROLE <user>;

Create new user who can login and has a password of 'password'

 =# CREATE ROLE <user> WITH LOGIN PASSWORD 'password';

Enable user to login (if forgotten when creating user)

 =# ALTER ROLE "<user>" WITH LOGIN;

Set password on user

 =# ALTER ROLE "<user>" WITH ENCRYPTED PASSWORD '<password>';

Create a database

 $ createdb <dbname>

or

 =# CREATE DATABASE <dbname>;

Drop database

 =# DROP DATABASE <dbname>;

Add user to database

 =# grant all privileges on database <dbname> to <username>

Create a table

 =# CREATE TABLE <tbname> (
    id SERIAL,
    title TEXT
    );

Other commands

\?	show help
\l	list databases
\q	quit postgres client (psql)
\c <dbname>	change active database
\c <dbname> <username>	change active database and user
\d	describe current database (list tables)
\d <table>	describe table
\d+ <table>	describe table with extra information
\du	list of roles
\du+	list of roles with description
\dt	show tables

Disable local login without password Edit config file /etc/postgresql/9.5/main/pg_hba.conf (location may differ depending on distro)

 (type)  (db)  (user)  (address)    (method)
  local   all    all                  md5
  host    all    all   127.0.0.1/32   md5

Maybe the following would be better though. Here the default user still has peer access but all others must supply a password.

 (type)  (db)  (user)  (address)    (method)
  local   all    postgres             peer
  local   all    all                  md5
  host    all    all   127.0.0.1/32   md5