What ispg_read_all_data & pg_write_all_data role in PostgreSQL

                    pg_read_all_data & pg_write_all_data

pg_read_all_data is a built-in PostgreSQL role that allows a user to:

- Read all tables (SELECT)

- Read all schemas

- Read all sequences

Across all databases in the cluster

It is read-only (no INSERT/UPDATE/DELETE).

Step 1: Check if pg_read_all_data role exists

Expected result:

Role exists (PostgreSQL 14+)

If it does not exist → then PostgreSQL version is very old

Step 2: Create a read-only user

CREATE ROLE readonly_user LOGIN PASSWORD 'postgres@123';

Step 3: Grant pg_read_all_data to the user

    This instantly gives access to all existing & future tables.

Step 4: Grant CONNECT privilege on database

Step 5: Verify select(read-only) access to user readonly_user.

Select should work.

Step 6: Verify insert into a table from readonly_user.

It will not work.

Step 7: Verify pg_write_all_data role.

Step 8: create a user and grant pg_write_all_data role. Also grant connect to the database.

    

Step 9: Run insert command -

        It will work.

Step 10: Now run the select from pg_write_all_data on departments table -

    It will not work.

Step 11: If any user want select, insert, update and delete permission then we have to grant                             pg_read_all_data and pg_write_all_data both.

Authentication Methods - MD5, SCRAM-SHA-256, password, trust, peer, ident, cert

 In PostgreSQL, an authentication method defines how a client proves its identity to the database server during connection.

We have multiple authentication methods available in Postgres -

1. MD5 Authentication

md5 is the older PostgreSQL password authentication method.

When we set:

local   all   all   md5

PostgreSQL stores the user password like this:

md5<md5(username + password)>

Pros

• Supported by all old PostgreSQL clients.

• Easy to set up.

Cons (VERY IMPORTANT)

• Old and weak hashing.

• MD5 is vulnerable to:

○ hash collisions

○ brute force attacks

○ replay attacks

MD5 is deprecated, but still available for compatibility.

2. SCRAM-SHA-256 Authentication (Recommended)

scram-sha-256 is the modern, secure password authentication introduced in PostgreSQL 10.

When we set:

local   all   all   scram-sha-256

PostgreSQL stores passwords using:

Salted, hashed, iterative SHA-256 (RFC 5802 & RFC 7677)

Example stored format:

SCRAM-SHA-256$iterations:salt$data

Pros

• MUCH stronger security

• Uses salted, iterated hashing

• Resistant to:

○ brute force attacks

○ rainbow table attacks

○ replay attacks

• Password never sent in clear or reusable form

• Industry standard authentication

Cons

• Very old clients (PostgreSQL <10) do not support SCRAM.

Important Difference

MD5

• Server stores MD5-hashed password.

• Client sends MD5-hash of hash (double hash).

• Vulnerable to interception and cracking.

SCRAM-SHA-256

• Server stores a salted hash.

• Client and server perform a challenge–response exchange.

• Password is NEVER sent or derivable.

Much safer authentication handshake.

3. Password

local / host   all   all   password

What it does - 

Client sends the raw, plain-text password to PostgreSQL.

Bad because:

• Password goes over the network unencrypted (unless using SSL).

• Very insecure.

• Rarely recommended.

Use only for testing.

4. peer (local connections only)

local   all   all   peer

What it does

Checks Linux OS username == PostgreSQL database username.

Example:

• OS user: postgres

• DB user: postgres → login works

• OS user: ubuntu

• DB user: user1 → FAILS

Problem:

Users must exist in Linux system.

You cannot \c db user1 from psql unless you are OS user user1.

Note - Use only on local servers where OS users map to DB users (rare in production).

5. ident (remote equivalent of peer)

host   all   all   0.0.0.0/0   ident

What it does -

Checks username using an external ident server.

Note -  Not commonly used today. Most systems don’t run ident servers. Complicated and outdated.

        Avoid unless we have a special ident-based setup.

6. trust

local / host   all   all   trust

What it does

Allows anyone to connect without a password.

Extremely insecure

Whoever can reach the server can log in as any DB user.

Use ONLY for:

• Dev VM

• Inside Docker container

• Local development

Never use in production.

7. cert (SSL certificate authentication)

hostssl   all   all   0.0.0.0/0   cert

What it does

User authenticates using an SSL client certificate, not a password.

Requirements:

• SSL enabled

• Client presents valid certificate

• Common Name (CN) must match DB username

Most secure option for enterprises

Used in:

• Banking

• Secure corporate networks

• Zero-trust environments

Real Time Scenario - Step By Step Slow Running Query Tuning(performance Tuning)

                        Real Time Scenario  - Step By Step Slow Running Query Tuning(performance Tuning)

1) We got a requirement that below query is running slow.

SELECT creationtime, serviceId FROM (

SELECT DISTINCT ON (serviceuuid, n.siteId) e.id AS serviceId, creationtime, status 

FROM NotificationMessages n

JOIN EquinetServices e ON n.serviceuuid = e.uuid AND n.siteid = e.siteid

JOIN sites_view sv ON sv.id = e.siteid AND NOT sv.decommissioned

WHERE e.id IN (SELECT instanceId FROM Alarms WHERE ruleName=$1 AND outAlarmTime IS NULL)

AND notificationTypeId=$2

ORDER BY serviceuuid, n.siteId, creationtime DESC, n.id DESC

) notalarmed

2) Checked the tables and indexes used in this query -

3)  Got the $1 and $2 values from tables -

4) Generated the explain plan for the above query -

here we can see the total execution time is 1178 milliseconds.

5) Fix - Index creation - upon investigation, we found that below indexes are needed to improve the performance -

6) Explain plan after index creation - 

7) Summary of the issue - 

The query was taking approximately 1180 ms to execute. The slowness was caused by sequential scans and high sort operations on large tables such as NotificationMessages, Alarms, and Sites. This occurred because required composite indexes were missing on key filtering and join columns.

8) Root Cause of the issue -

• No supporting indexes for serviceuuid, siteid, and creationtime ordering
• Missing indexes on notificationtypeid filter and alarm rule filtering
• Inefficient site-state filtering due to lack of index support
• As a result,  query performed full-table scans and expensive sort operation.

9) Fix Applied - 

To improve the query performance, the following indexes were created:

1. NotificationMessages Table –

                 CREATE INDEX idx_notificationmessages_type_uuid_site_ctime

                ON NotificationMessages (notificationtypeid, serviceuuid, siteid, creationtime DESC);

 

2. Alarms Table –

                 CREATE INDEX idx_alarms_rule_out_instance

                ON Alarms (ruleName, outAlarmTime, instanceId);

 

3. Sites Table –

                 CREATE INDEX idx_sites_notdeleted_notdecomm

                ON sites (deleted, decommissioned, id);

 

4. EquinetServices

                 CREATE INDEX idx_equinetservices_uuid_siteid

                    ON EquinetServices(uuid, siteid);

10. Final Result - 

    

    

Metric	Before	After
Execution Time	1180 ms	5.862 ms

        

Also, After applying the new indexes, please run the following commands:

VACUUM ANALYZE NotificationMessages;

VACUUM ANALYZE Alarms;

VACUUM ANALYZE EquinetServices;

VACUUM ANALYZE sites;

 

These commands will refresh the table statistics so query planner can fully utilize the newly created indexes. If you don’t have permissions to run them, please let me know and can run them right after the index creation.

============================The End================================

Streaming Replication Useful Queries

How to Check Replication Status Properly

On Primary -

SELECT pid, usename, application_name, state

 --, client_addr

 , date_trunc('minute', backend_start) as backend_start

 , sent_lsn, round(((pg_current_wal_lsn()-sent_lsn)/(1024*1024.0)), 2) as mb_diff

from pg_stat_replication;

SELECT pid, state, sync_state, write_lag, flush_lag, replay_lag

FROM pg_stat_replication;

1) SELECT pg_current_wal_lsn();

2) SELECT pg_current_wal_insert_lsn();

3) WAL Switch Functions -

#SELECT pg_switch_wal();

Run this on STANDBY

SELECT 

    pg_last_wal_receive_lsn()  AS receive_lsn,

    pg_last_wal_replay_lsn()   AS replay_lsn,

    pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS byte_lag;

1) Last received LSN

SELECT pg_last_wal_receive_lsn();

        

2) Last replayed LSN

        SELECT pg_last_wal_replay_lsn();

    

3) WAL difference

    SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn());

    SELECT pg_wal_lsn_diff(pg_stat_replication.sent_lsn, pg_stat_replication.replay_lsn) as byte_lag        FROM pg_stat_replication

Missing WAL File live scenario in PostgreSQL and how to recover from it

 

            Missing WAL File live scenario in PostgreSQL and how to recover from it

If WAL files are missing on primary side then replication will break and this error message will be displayed in the log file.

Answer - 

When a standby requests a WAL segment that the primary has already recycled or which is missing from archive storage, the standby cannot continue recovery. PostgreSQL physical replication does not support skipping missing WAL files. The only recovery is to re-initialize the standby with a fresh base backup.

I simulate this scenario by stopping the standby, generating excessive WAL on the primary, manually removing old WAL files, and restarting the standby.”

On PROD-1

On Replica -

Took the fresh pg_basebackup and built the replica again.

How to control the streaming replication

 

1. Check if WAL replay (recovery) is paused

SELECT pg_is_wal_replay_paused();

Output:

• true → WAL replay is paused

• false → WAL replay is running

---

2. Pause WAL replay (recovery)

SELECT pg_wal_replay_pause();

    - Pauses WAL replay immediately
    - Works only on a standby server (read replica)

---

3. Resume WAL replay (recovery)

SELECT pg_wal_replay_resume();

    - Resumes WAL replay if it was paused

How to change hostname

 

        

    

                                                

                                               3. It will show like below -

                                                

                                            

    

    

SSH to EC2-Servers from GitBash client