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


Comments

Post a Comment

Popular posts from this blog

Technical Memo: Disk Encryption using Cryptsetup with Vault as Key Management Service

In Linux Operating system, full disk encryption could be achieved by various solutions: encryptfs, dm-crypt, cryptsetup… While a step-by-step setup of disk/partition encryption can be referred from official documentation, integration disk encryption with Key Management solution like Hashicorp Vault is unobvious. Fortunately, there are a package named vaultlocker in Ubuntu Universe repository that ease this integration. I decided to spend my free time to make cryptsetup work with Vault. Notes: A similar request has been made to Cryptsetup but it goes outside of cryptsetup so it was closed. 1. Install Vault As JuJu charm store has working Vault/MySQL charms, Vault deployment is as simple and easy as following commands $ juju deploy percona-cluster $ juju deploy vault $ juju relate vault:shared-db percona-cluster Vault needs to be initialized and unsealed before it can provide its secure services. I followed below steps to initialize Vault $ juju ssh vault/0 $ export VAU
Read more

Use MaaS to manage local computing resource

Image
Besides managing bare metal machines, MaaS (Metal as a Service) can also manage KVM guests as long as those machines are boot from network PXE. This feature allow us to efficiently manage local computing resources, while at the same time, leverage JuJu for automatically middle ware deployment. In this blog post, I will detail steps to manage KVM guest machines using MaaS. System diagram MaaS Managed Machine : MaaS Virtual Machine creates other Virtual machines through KVM Pod. All VMs access the internet through physical machine's NIC using NAT. The MaaS server will be deployed to a virtual machine running inside the physical machine. The reason for installing MaaS into a virtual machine instead of directly install to the physical machine is that we want to have the flexibility of VMs as we want to update MaaS version, or backup... The MaaS server will manage its "parent" physical machine resources through KVM pod. All machines will access to the Internet thr
Read more

Technical Memo: Convert an ova image to qcow2 format

From Wikipedia of Open Virtualization Format , I learnt that an the ova file is just a tar file of ovf directory. So I went open a Windows 10 Developer Evaluation ova file to see what exist inside it. $ tar -tf WinDev2001Eval.ova WinDev2001Eval.ovf WinDev2001Eval-disk001.vmdk WinDev2001Eval.mf So inside the ova file includes A virtual machine specification definition .ovf file A hash checksum .mf file A disk image in .vmdk image As qemu-img supports vmdk as disk image file, we actually could convert vmdk file to any other format that we like. 1. Convert .vmdk to .img $ qemu-img convert -O raw WinDev2001Eval-disk001.vmdk WinDev2001Eval.img 2. Convert .vmdk to .qcow2    $ qemu-img convert -O qcow2 WinDev2001Eval-disk001.vmdk WinDev2001Eval.qcow2 All supported formats can be confirmed by qemu-img -h The converted qcow2 image could be used to create a new virtual machine using virt-manager. I used to think that .ova file is the file format of Virtual Box (There
Read more