🤖 Users and Roles

PostgreSQL ma users ane roles essentially same che. Role ek logical entity che je database ma permissions manage kare che.

Roles samajvo

PostgreSQL ma "user" ane "role" almost same che. Difference ek j che:

• Role with LOGIN = User (login kari shake che)
• Role without LOGIN = Group (permissions group mate)

Creating Roles

Basic User Role

-- Login privilege sathe role create karo
CREATE ROLE myuser WITH LOGIN PASSWORD 'secure_password';

-- Aa pan same che (shorthand)
CREATE USER myuser WITH PASSWORD 'secure_password';

Group Roles banavo

Group roles thi permissions manage karvama easy pade che:

-- Different access levels mate roles
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin_group;

Role Attributes

Roles ne different capabilities aapi shakay:

-- Superuser role (badhi j permissions)
CREATE ROLE superadmin WITH SUPERUSER LOGIN PASSWORD 'password';

-- Database creation permission
CREATE ROLE dbcreator WITH CREATEDB LOGIN PASSWORD 'password';

-- Role creation permission (other users manage kari shake)
CREATE ROLE roleadmin WITH CREATEROLE LOGIN PASSWORD 'password';

-- Replication mate special role
CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'password';

-- Connection limit set karo
CREATE ROLE limiteduser WITH LOGIN PASSWORD 'password' CONNECTION LIMIT 5;

-- Expiry date set karo (temporary users mate)
CREATE ROLE tempuser WITH LOGIN PASSWORD 'password' VALID UNTIL '2024-12-31';

Permissions Aapvo

Database Level Permissions

-- Database ma connect karvani permission
GRANT CONNECT ON DATABASE mydb TO myuser;

-- Badhi permissions ek saathe
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

-- Database create/drop permission
ALTER USER myuser WITH CREATEDB;

Table Level Permissions

-- Specific table pe SELECT permission
GRANT SELECT ON table_name TO myuser;

-- Multiple permissions ek saathe
GRANT SELECT, INSERT, UPDATE ON table_name TO myuser;

-- Schema ni badhi tables pe permission
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Future ma create thashe ae tables pe pan permission
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO readonly;

Schema Permissions

-- Schema use karva ni permission (tables access karva mate jaruri)
GRANT USAGE ON SCHEMA public TO myuser;

-- Schema ma badhu j kari shake
GRANT ALL ON SCHEMA public TO myuser;

Role Membership (Groups)

-- User ne group role ma add karo
GRANT readonly TO myuser;

-- Admin option sathe (aa user bija ne pan grant kari shake)
GRANT admin_group TO myuser WITH ADMIN OPTION;

-- Group mathi remove karo
REVOKE readonly FROM myuser;

-- Current user ni roles joyo
\du
-- Ke
SELECT * FROM pg_roles WHERE rolname = 'myuser';

Common Role Patterns

Read-Only User Setup

Production database mate read-only users bahuj useful che:

-- Read-only role create karo
CREATE ROLE readonly;
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Future tables mate pan
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO readonly;

-- User create karo ane role assign karo
CREATE USER analyst WITH PASSWORD 'password';
GRANT readonly TO analyst;

Read-Write User Setup

Application users mate read-write access:

-- Read-write role
CREATE ROLE readwrite;
GRANT CONNECT ON DATABASE mydb TO readwrite;
GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;

-- Future mate
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;

-- User create ane assign
CREATE USER app_user WITH PASSWORD 'password';
GRANT readwrite TO app_user;

Application User (Limited)

Application mate specific database na permissions:

-- Application user
CREATE USER myapp WITH PASSWORD 'secure_app_password' CONNECTION LIMIT 20;

-- Specific database pe j access
GRANT CONNECT ON DATABASE app_db TO myapp;
GRANT USAGE ON SCHEMA public TO myapp;

-- Specific tables pe permissions
GRANT SELECT, INSERT, UPDATE ON users, orders, products TO myapp;
GRANT USAGE ON SEQUENCE users_id_seq, orders_id_seq TO myapp;

Existing Roles Manage Karvo

-- Role ni details joyo
\du rolename
-- Ke
SELECT * FROM pg_roles WHERE rolname = 'myuser';

-- Password change karo
ALTER USER myuser WITH PASSWORD 'new_password';

-- Attributes modify karo
ALTER USER myuser WITH CREATEDB;
ALTER USER myuser WITH NOCREATEDB;

-- Expiry date extend karo
ALTER USER tempuser VALID UNTIL '2025-12-31';

-- Connection limit change karo
ALTER USER myuser CONNECTION LIMIT 10;

-- Role delete karo (objects owned na hoi to j)
DROP ROLE myuser;

-- Owned objects sathe delete karvanu hoi to
DROP OWNED BY myuser;
DROP ROLE myuser;

Best Practices

1. Principle of Least Privilege - Jyare jeti permission joiye teti j apo
2. Group Roles Use Karo - Direct permissions na apo, group roles through manage karo
3. Strong Passwords - Complex passwords use karo, password policies set karo
4. Regular Audits - Permissions regularly review karo
5. Superuser Sparingly - Superuser daily work mate use na karo
6. Document Everything - Kon role shu kare che ae document karo
7. Naming Convention - Consistent naming follow karo (app_readonly, api_user, etc.)

Useful Queries

-- All roles ane permissions joyo
SELECT 
  r.rolname,
  r.rolsuper,
  r.rolcreatedb,
  r.rolcreaterole,
  r.rolcanlogin,
  r.rolconnlimit,
  r.rolvaliduntil
FROM pg_roles r
ORDER BY r.rolname;

-- Specific table ni permissions joyo
SELECT 
  grantee,
  privilege_type 
FROM information_schema.table_privileges 
WHERE table_name = 'users';

-- Role membership joyo
SELECT 
  r.rolname as role,
  m.rolname as member
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member;

---

Next Steps: Authentication configuration (pg_hba.conf) ane SSL setup (coming soon)