How To Use Roles and Manage Grant Permissions in PostgreSQL on a VPS

PostgreSQL, or Postgres, is an open-source relational database management system. As with other relational databases, PostgreSQL stores data in tables made up of rows and columns. Users can define, manipulate, control, and query data using Structured Query Language, more commonly known as SQL. PostgreSQL is a powerful tool that can be used to manage application and web data on a Virtual Private Server.

This guide will demonstrate how to properly manage privileges and grant user permissions. This will allow you to provide your applications the privileges necessary without affecting separate databases.

Useful links
VPS/VDS – https://www.mivocloud.com/

Commands Used

Viewing Roles and Permissions in PostgreSQL
sudo systemctl start postgresql.service
sudo -i -u postgres
psql
du

Creating Roles in PostgreSQL
CREATE ROLE new_role_name;
du
q
sudo -i -u postgres
createuser –interactive
psql
du

Deleting Roles In PostgreSQL
DROP ROLE role_name;
DROP ROLE IF EXISTS role_name;

Defining Privileges Upon Role Creation
CREATE ROLE role_name WITH assigned_permissions;
h CREATE ROLE
CREATE USER role_name;

Changing Privileges of Roles in PostgreSQL
ALTER ROLE role_name WITH attribute_options;
ALTER ROLE demo_role WITH NOLOGIN;
du
ALTER ROLE demo_role WITH LOGIN;

Logging In as a Different User in PostgreSQL
password test_user
q
psql -U user_name -d database_name -h 127.0.0.1 -W
q
sudo u – postgres psql

Granting Permissions in PostgreSQL
GRANT permission_type ON table_name TO role_name;
CREATE TABLE demo (
name varchar(25),
id serial,
start_date date);
d
GRANT UPDATE ON demo TO demo_role;
GRANT INSERT ON demo TO PUBLIC;
z

Removing Permissions in PostgreSQL
REVOKE permission_type ON table_name FROM user_name;

Using Group Roles in PostgreSQL
CREATE ROLE temporary_users;
GRANT temporary_users TO demo_role;
du
SET ROLE temporary_users;
CREATE TABLE hello (
name varchar(25),
id serial,
start_date date);
d
RESET ROLE;
ALTER ROLE test_user INHERIT;
DROP ROLE temporary_users;
ALTER TABLE hello OWNER TO demo_role;
d
DROP ROLE temporary_users;