Postgres User Management Simple Step-by-Step Tutorial

Managing users in PostgreSQL is a crucial aspect of database administration, allowing you to control access, permissions, and privileges effectively. In this guide, we’ll explore various options for creating, modifying, and revoking user permissions in PostgreSQL. From basic user creation to granting and revoking privileges, let’s dive into the essential commands.

Creating Users in PostgreSQL

Syntax: CREATE ROLE username WITH PASSWORD ‘password’;

• Option 1: Create a New PostgreSQL User

CREATE ROLE demo WITH PASSWORD ‘demo@123’;

Note: This creates a new user without login permission, restricting access to the database.

• Option 2: Create a New PostgreSQL User with Login Permission

CREATE ROLE demo1 WITH LOGIN PASSWORD ‘demo1@123’;

Note: This allows the user to log in to the database.

• Option 3: Create a User with Connection Limit and Password Validity

CREATE ROLE demo2 WITH LOGIN PASSWORD ‘demo2@123’ CONNECTION LIMIT 5 VALID UNTIL ‘2024-05-26’;

Note: Sets connection limit to 5 with password validity until a specified date.

• Option 4: Create a User with Database, Role, and Replication Privileges

CREATE ROLE demo3 WITH LOGIN CREATEDB CREATEROLE REPLICATION PASSWORD ‘demo3@123’;

Note: Grants privileges for creating a database, role, and replication.

• Option 5: Create a User with Superuser Privileges

CREATE ROLE demo4 WITH LOGIN SUPERUSER PASSWORD ‘demo4@123’;

Note: Grants the user the highest level of privileges.

• Option 6: Rename User

Syntax:- ALTER ROLE old_username RENAME TO new_username;

ALTER ROLE demo RENAME TO test;

• Option 7: Reset User Password

Syntax: ALTER USER username WITH PASSWORD ‘newpassword’;

ALTER USER demo WITH PASSWORD ‘Demo@123’;

• Option 8: Delete User

Syntax:- DROP USER username;

DROP USER demo1;

• Option 9: Check Available Users and Their Roles

\du

Granting Permissions in PostgreSQL

In postgres grants give’s on database object.

Syntax:- GRANT privilege_name ON object_name TO user_name;

• Option 1: Grant Specific Privileges on a Table

GRANT SELECT, UPDATE, INSERT, DELETE ON customers TO demo2;

• Option 2: Grant Privileges on All Tables in a Schema

Syntax:- GRANT [privilege type] ON ALL TABLES IN SCHEMA [schema name] TO [user]

GRANT SELECT, TRIGGER, UPDATE ON ALL TABLES IN SCHEMA public TO demo3;

• Option 3: Grant All Privileges on a Table

GRANT ALL PRIVILEGES ON employees TO test;

• Option 4: Grant All Privileges on a Schema

GRANT ALL PRIVILEGES ON SCHEMA public TO demo2;

• Option 5: Grant Privileges with Grant Option

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA PUBLIC TO demo4 WITH GRANT OPTION;

• Option 6: Grant Ability to Create Database and Role

ALTER ROLE demo2 CREATEDB CREATEROLE;

• Option 7: Grant Superuser Privileges

ALTER ROLE test SUPERUSER;

Revoking Permissions in PostgreSQL

• Option 1: Revoke Specific Privileges from a Table

REVOKE SELECT, UPDATE, INSERT, DELETE ON customers FROM demo2;

• Option 2: Revoke All Privileges from All Tables in a Schema

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM demo3;

Option 3: Revoke All Privileges from a Schema

REVOKE ALL PRIVILEGES ON SCHEMA public FROM demo4;

• Option 4: Check User Permissions

\dp

• Option 5: Check Specific User Permissions in Information Schema

SELECT * FROM information_schema.role_table_grants WHERE grantee=’test’;

This comprehensive guide will empower you to manage PostgreSQL users efficiently, ensuring secure and controlled access to your database. Whether you are a beginner or an experienced administrator, these commands will help you master user management in PostgreSQL.