Basic PostgreSQL usage notes

Just keeping few useful PostgreSQL commands for myself. 🙂

Login to PostgreSQL admin intereface

# switch to linux user postgres. 
su postgres
# enter admin interface
psql
# or use sudo just one command
sudo -u postgres psql

List users / roles

\du

List databases

\l

Create new user role

# create user role
create role myuser;
# allow user role to login
alter role myuser with LOGIN;

Set user / role password

alter user myuser with password 'my-super-password';

Create database and set user as its owner.

If user uses its linux username as authenticator – then best is to call database the same as user’s role.

create database myuser owner myuser;

Allow newly create user to actually connect

# edit file /var/lib/pgsql/data/pg_hba.conf
# add methods, that user / role is allowed to use.
# host database user host method
host myuser myuser 193.193.93.93/32 password
host myuser myuser 127.0.0.1/32 password
local myuser myuser ident
# restart PostgreSQL service afterwards.

…