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
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 220.127.116.11/32 password host myuser myuser 127.0.0.1/32 password local myuser myuser ident # restart PostgreSQL service afterwards.