PostgreSQL

📅 Created on: October 6, 2022

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>

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

command	description
`\?`	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

Database