Wiki-World

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
Login: standard access only on local socks connection
<code>
 $ sudo -u postgres psql
</code>

Login with user
<code>
 $ psql <dbname> <user>
</code>

Create new user
<code>
 =# CREATE ROLE <user>;
</code>

Create new user who can login and has a password of 'password'
<code>
 =# CREATE ROLE <user> WITH LOGIN PASSWORD 'password';
</code>

Enable user to login (if forgotten when creating user)
<code>
 =# ALTER ROLE "<user>" WITH LOGIN;
</code>

Set password on user
<code>
 =# ALTER ROLE "<user>" WITH ENCRYPTED PASSWORD '<password>';
</code>

Create a database
<code>
 $ createdb <dbname>
</code>
or
<code>
 =# CREATE DATABASE <dbname>;
</code>

Drop database
<code>
 =# DROP DATABASE <dbname>;
</code>

Add user to database
<code>
 =# grant all privileges on database <dbname> to <username>
</code>

Create a table
<code>
 =# CREATE TABLE <tbname> (
    id SERIAL,
    title TEXT
    );
</code>

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)
<code>
 (type)  (db)  (user)  (address)    (method)
  local   all    all                  md5
  host    all    all   127.0.0.1/32   md5
</code>

Maybe the following would be better though.
Here the default user still has peer access but all others must supply a password.
<code>
 (type)  (db)  (user)  (address)    (method)
  local   all    postgres             peer
  local   all    all                  md5
  host    all    all   127.0.0.1/32   md5
</code>