Steps to setup Postgres
- Allow local role to access using password
- Create Role and Grant Permission
1. Allow password authentication to local role
Postgres comes with different role management mechanisms from MySQL's. By default, local users connect with Postgres throught Unix socket without password. Postgres delegates user authentication to Linux, which means that user connect to Postgres locally needs not only a role in Postgres but also a Linux account. For a local development, this setting is quite inconvenient so I switch local user authencation to password also.
Postgres authencation mechanism is set in pg_hba.conf. In Ubuntu 19.04 (Disco) with Postgres installed from apt, the file is stored under /etc/postgresql/11/main/pg_hba.conf
Open a file with edit permission (which means using sudo in Ubuntu), look for the following files, and change METHOD from peer to md5
... # "local" is for Unix domain socket connections only --- local all all peer +++ local all all md5 ...
Explanation is written in detail in file comment. After edit and save file, we restart Postgres.
$ sudo systemctl restart postgresql $ systemctl status postgresql ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Fri 2019-04-19 00:50:53 UTC; 40min ago Process: 5168 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 5168 (code=exited, status=0/SUCCESS) Apr 19 00:50:53 disco systemd: Starting PostgreSQL RDBMS... Apr 19 00:50:53 disco systemd: Started PostgreSQL RDBMS
2. Add Role and Grant Permission to RoleWe need a role (users) and its permissions. Permissions include permission to connect to database, permission to use public schema, permission on all tables, and permission for sequences.
2.1 Create Role and DatabaseI will try to create a role with name: tele
$ sudo su - postgres $ psql postgres=> CREATE ROLE tele WITH LOGIN; postgres=> \password tele Enter new password: ***** Enter it again: ***** postgres=> CREATE DATABASE teledb;
2.2 Grant Connect and Usage
postgres=> GRANT CONNECT ON DATABASE teledb to tele; postgres=> GRANT USAGE ON SCHEMA public to tele;
2.3 Grant permissions on table and sequence
postgres=> GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public to tele; postgres=> GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public to tele;
Verify that user tele can login into database using username and password you have setup.
$ psql -U tele -d teledb -W password: ***** tele=>