• Ashiwini
• 01 Jul, 2024
• 0 Comments
• 8 Mins Read

How to Create a Postgres User (Step-by-Step Process)

PostgreSQL 15 – Roles & Privileges Guide

This guide explains how to manage roles and privileges in PostgreSQL 15, with grant examples and verification commands.

1. Role-Level Privileges (Role Attributes)

Role attributes are global capabilities assigned to a role.

Granting Role Privileges

— Allow role u1 to create/alter/drop roles (non-superuser)
ALTER ROLE u1 CREATEROLE;

— Inherit privileges from group roles automatically
ALTER ROLE u1 INHERIT;

— Full power (use carefully)
ALTER ROLE u1 SUPERUSER;

Checking Role Privileges

✅ Example Output:

2. Database-Level Privileges

Privileges applied to an entire database.

Granting Database Privileges

Checking Database Privileges

✅ Example Output:

• C = CREATE

• T = TEMPORARY

• c = CONNECT

3. Schema-Level Privileges

Privileges applied to a schema (namespace) inside a database.

Granting Schema Privileges

Checking Schema Privileges

✅ Example Output:

• U = USAGE

• C = CREATE

4. Object-Level Privileges

a) Tables & Views

Granting

Checking

✅ Example Output:

• a = INSERT

• r = SELECT

• w = UPDATE

• d = DELETE

• D = TRUNCATE

• x = REFERENCES

• t = TRIGGER

b) Sequences

Granting

Checking

✅ Example Output:

• r = SELECT (currval)

• w = UPDATE (nextval, setval)

• U = USAGE

c) Functions & Procedures

Granting

Checking

(Access privileges appear in output)

d) Types

Granting

Checking

e) Foreign Data Wrappers / Servers

Granting

Checking

f) Large Objects

Granting

Checking

Quick Summary Table

Creating a PostgreSQL user involves a few steps. Below is a step-by-step tutorial to guide you through the process.

Prerequisite

Before we start, make sure you have the following:
1 .PostgreSQL Installed: Ensure that PostgreSQL is installed on your system. You can download it from the official PostgreSQL website.
2 .Access to PostgreSQL Server: You need access to the PostgreSQL server, either locally or remotely, with the necessary permissions to create users.

Step 1: Access the PostgreSQL Command Line

Open a terminal or command prompt and access the PostgreSQL server using the following command:

psql -U your_admin_username -d your_database_name -h your_host

Replace your_admin_username, your_database_name, and your_host with your actual PostgreSQL admin username, database name, and host, respectively.

Step 2: Create a New User

Once you are in the PostgreSQL command line, you can create a new user. Replace username with the name you want for your new user.

CREATE USER username;

Set a Password for the New User

Next, you need to set a password for the new user. Replace password with a strong password of your choice.

ALTER USER username WITH PASSWORD 'password';

OR

Another way to create user

CREATE USER your_new_username WITH PASSWORD 'your_password';

CREATE USER your_new_username WITH PASSWORD 'your_password' SUPERUSER;

Step 3:  Grant Privileges to the User

Depending on what you want the new user to do, you might need to grant specific privileges. For example, to grant all privileges on a database to the user, use the following command. Replace database_name with the name of your database.

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

If you want to grant more specific privileges (e.g., only SELECT, INSERT, etc.), you can do so with the GRANT command:

GRANT SELECT, INSERT ON TABLE table_name TO username;

Step 4: Create a Database Owned by the User (Optional)

If you want the new user to own a database, you can create a new database and set the user as the owner. Replace database_name with the desired database name.

CREATE DATABASE database_name OWNER username;

Step 5: Verifying User Creation

To verify that the user has been created successfully, you can use the following SQL command:

SELECT * FROM pg_user WHERE usename = 'your_new_username';

Step 6: Exit the PostgreSQL Command Line

\q

Understanding Postgres Users and Their Significance

An account that connects users or applications to the PostgreSQL database is known as a Postgres user. Each user is a distinct entity with a unique username and password, forming the foundation for access control and database security.
Distinguishing Between Regular Users and Superusers
It’s essential to differentiate between regular users and superusers. Superusers have unrestricted access to the database and can perform any   action. Regular users, on the other hand, have specific roles and permissions, which contribute to a secure and controlled database        environment.
Managing Roles and Permissions
PostgreSQL user management involves roles and permissions. Permissions define what actions a user can perform, such as selecting, inserting, updating, and deleting data. Roles are used to group users, allowing those with similar responsibilities to share common access levels, thus simplifying user management.

The Importance of Postgres Users

1 .Implementing a strong password policy

To strengthen the database’s first line of protection, implement a strict password policy and generate strong passwords.

This policy can be applied when adding or changing users. For example:

CREATE USER secure_user WITH PASSWORD 'StrongP@ssw0rd';

2 .Monitoring user activity and auditing

Keep an eye on user activity and use tools such as pg_stat_statements and pgAudit to spot odd behavior.

These procedures provide a proactive and dependable method for managing PostgreSQL users in addition to user creation and role understanding.

Advanced User Management Features in PostgreSQL

PostgreSQL offers several advanced features for user management, enhancing security, flexibility, and control over database access. Here’s an overview of these advanced features:

1. Role Inheritance

Hierarchical Role Management:
PostgreSQL supports role inheritance, allowing roles to inherit permissions from other roles. This simplifies permission management by grouping related roles together.

CREATE ROLE parent_role;
CREATE ROLE child_role INHERIT;
GRANT parent_role TO child_role;

2. User-Defined Roles

Customizable Access Control:
Administrators can create user-defined roles tailored to specific projects or business units. This provides a modular
approach to managing user permissions.

CREATE ROLE project_team; 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO project_team;

3. Row-Level Security (RLS)

Granular Access Control:
RLS allows for fine-grained control over data access based on user attributes or roles, restricting access to specific rows in a table.

For example, administrators can limit access to particular rows according to user attributes using Row-Level Security:

CREATE POLICY restrict_confidential_data 
ON sensitive_table 
TO your_role 
USING (manager = current_user);

This policy limits access to rows in the “sensitive_table” only to users possessing the specified role, and the condition in the USING clause ensures that only rows where the “manager” attribute matches the current user’s identifier are accessible.

CREATE TABLE employees ( id SERIAL PRIMARY KEY, name TEXT, department TEXT ); 

CREATE POLICY department_policy ON employees USING (department = current_user);

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

Importance of Proper User Management for Database Security

To make PostgreSQL security stronger, user management must be done correctly. This includes having a strict password policy, keeping an eye on user activity, and regularly reviewing and updating user permissions to keep out unwanted access.

Introducing StrongDM for Enhanced Postgres User Management

StrongDM is a feature-rich database access management system that was created in response to the increasing complexity of database management. Its goal is to make user creation, management, and auditing across a range of databases—including PostgreSQL—more straightforward.

Benefits of Using StrongDM

• Centralized Access Control
  

  Unified Access Management:
  StrongDM centralizes access control for all your databases, including PostgreSQL, enabling administrators to manage permissions and monitor activity from a single interface.
  Simplified User Provisioning:
  Easily onboard and offboard users, assign roles, and manage permissions across multiple databases.

• Granular Permission Management

  Role-Based Access Control (RBAC):
  Define roles with specific permissions and assign users to these roles, ensuring that users have only the necessary access to perform their jobs.
  Dynamic Access Policies:
  Create and enforce policies that govern access based on user attributes, roles, or other criteria.

• Enhanced Security Feature
  Multi-Factor Authentication (MFA):
  Integrate MFA to add an extra layer of security, ensuring that only authenticated users can access the PostgreSQL database.
  Secure Access Logging:
  All access attempts and activities are logged, providing a comprehensive audit trail for security and compliance purposes.