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
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
Explanation is written in detail in file comment. After edit and save file, we restart Postgres.
Verify that user tele can login into database using username and password you have setup.
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
Steps to setup Postgres
- Allow local role to access using password
- 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/4470d377b448e56468baef85af7fd6142. 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