April 19, 2019

Technical Note: Roles, Grants for newly installed PostgreSQL

I'm familiar with MySQL yet I have always wanted to use PosegreSQL due to its feature richness. Due to differences between the 2 databases, I always find it hard to remember initial setups. Today, It took me a while to re-navigate Postgres official documentation again, before I can create a role and grant permissions to newly created roles. I think it's useful to note those tasks for my future references.

Steps to setup Postgres
  1. Allow local role to access using password
  2. Create Role and Grant Permission 

Manual


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[1]: Starting PostgreSQL RDBMS...
Apr 19 00:50:53 disco systemd[1]: Started PostgreSQL RDBMS

2. Add Role and Grant Permission to Role

We 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 Database

I 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;


3. Confirmation


Verify that user tele can login into database using username and password you have setup.

$ psql -U tele -d teledb -W
password: *****
tele=>

Reference

1. https://gist.github.com/AtulKsol/4470d377b448e56468baef85af7fd614
2. https://www.postgresql.org/docs/11/user-manag.html
3.https://dba.stackexchange.com/questions/117109/how-to-manage-default-privileges-for-users-on-a-database-vs-schema/117661#117661
4. https://stackoverflow.com/questions/22483555/give-all-the-permissions-to-a-user-on-a-db


No comments: