CVE-2025-1094: PostgreSQL psql SQL injection

CVE-2025-1094 is a critical SQL injection vulnerability discovered in PostgreSQL's interactive terminal, psql. This issue stems from improper handling of quoting syntax in the PostgreSQL libpq functions—namely PQescapeLiteral(), PQescapeIdentifier(), PQescapeString(), and PQescapeStringConn(). When these functions process untrusted input, they may fail to correctly neutralize quoting syntax, allowing attackers to execute arbitrary SQL commands.

What makes this vulnerability especially dangerous is its potential to lead to arbitrary code execution. By exploiting this flaw, an attacker can exploit psql’s ability to execute meta-commands, such as the exclamation mark (!) symbol, which in turn can run operating system shell commands. A successful attack could allow attackers to run arbitrary commands on the host system.

This vulnerability affects PostgreSQL versions prior to 17.3, 16.7, 15.11, 14.16, and 13.19. To mitigate the risk, organizations should promptly upgrade to the latest patched versions of PostgreSQL. The PostgreSQL Global Development Group has released patches to address this security issue.

The emergence of CVE-2025-1094 highlights the need for regular software updates and strong security practices. Organizations are strongly advised to apply the necessary patches without delay and to conduct regular security assessments. Additionally, implementing rigorous input validation can further safeguard systems from similar vulnerabilities.

PostgreSQL work_mem: What is work_mem? When to Consider Increasing work_mem?

PostgreSQL is renowned for its robust query optimization and memory management capabilities. One of the most critical yet often misunderstood parameters is work_mem. This memory setting can dramatically impact query performance, but it's not always clear when or how to tune it effectively.
we'll explore what work_mem is, how PostgreSQL uses it, and when you should consider increasing it to optimize your database performance.

What is work_mem?
work_mem is a PostgreSQL configuration parameter that defines the amount of memory allocated to each database operation that requires temporary storage. Unlike shared_buffers which is shared across all connections, work_mem is allocated per operation, per connection.

Key Characteristics:
- Per-operation allocation: Each sort, hash join, or merge operation gets its own work_mem
- Temporary memory: Used only during query execution
- Automatic cleanup: Memory is released when the operation completes
- Default value: 4MB (PostgreSQL 13+), 1MB in older versions

How PostgreSQL Uses work_mem:
PostgreSQL allocates work_mem for several types of operations:
1. Sort Operations
-- This query will use work_mem for sorting
SELECT * FROM large_table  ORDER BY created_at DESC LIMIT 1000;
2. Hash Joins
-- Hash joins use work_mem for building hash tables
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;
3. Merge Joins
-- Merge joins use work_mem for sorting inputs
SELECT * FROM table1 t1 JOIN table2 t2 ON t1.id = t2.ref_id;
4. Window Functions
-- Window functions may use work_mem for sorting
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM employees;
5. Index Creation
-- Creating indexes uses work_mem for sorting
CREATE INDEX idx_created_at ON large_table(created_at);

Memory Allocation in Practice:
-- This query might use work_mem multiple times:
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2023-01-01'
GROUP BY u.name
ORDER BY order_count DESC;
Memory allocation:
 1. Hash join: 1 × work_mem
 2. GROUP BY sorting: 1 × work_mem  
 3. ORDER BY sorting: 1 × work_mem
    Total: 3 × work_mem per connection

When to Consider Increasing work_mem:
1. Disk Spills (Temporary Files)
The most obvious sign that you need more work_mem is when PostgreSQL writes temporary files to disk:
-- Check for disk spills
SELECT query, temp_files, temp_bytes,  temp_bytes / temp_files as avg_temp_file_size FROM pg_stat_statements 
WHERE temp_files > 0 ORDER BY temp_bytes DESC;
What to look for:
   temp_files > 0: Indicates operations spilled to disk
   High temp_bytes: Shows significant disk usage
   Frequent spills: Operations consistently exceeding memory

2. Slow Query Performance 
Monitor query execution plans for external sorts:
-- Check if sorts are using external files
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM large_table  ORDER BY created_at DESC;
Look for:
- External Sort in the execution plan
- High I/O time in EXPLAIN ANALYZE
- temp_files in the plan output

3. Large Data Processing
Consider increasing work_mem when:
- Processing large datasets (>1GB)
- Running complex analytical queries
- Creating indexes on large tables
- Performing bulk data operations

4. Memory-Intensive Operations
Operations that typically benefit from more work_mem:
- Large sorts: ORDER BY on millions of rows
- Hash joins: Joining large tables
- Window functions: Complex analytical queries
- Aggregations: GROUP BY on large datasets

How to Configure work_mem
1. Check Current Setting
-- View current work_mem setting
SHOW work_mem;
-- View all memory-related settings
SELECT name, setting, unit, context FROM pg_settings WHERE name LIKE '%mem%';

2. Set work_mem for Session
-- Temporary setting for current session
SET work_mem = '256MB';
-- Verify the change
SHOW work_mem;

3. Set work_mem Globally
-- Set globally (requires superuser privileges)
ALTER SYSTEM SET work_mem = '256MB';

-- Reload configuration
SELECT pg_reload_conf();
-- Restart may be required for some changes

4. Set work_mem in postgresql.conf
# In postgresql.conf
work_mem = 256MB
Restart PostgreSQL after changes

Memory Calculation and Planning
Total Memory Usage
Total work_mem = work_mem × max_connections × concurrent_operations
Example calculation:
- work_mem = 256MB
- max_connections = 100
- Average concurrent operations per connection = 2
- Total work_mem = 256MB × 100 × 2 = 51.2GB

Check your memory configuration:
SELECT  name,setting,unit, CASE 
 WHEN name = 'shared_buffers' THEN 'Shared across all connections'
        WHEN name = 'work_mem' THEN 'Per operation, per connection'
        WHEN name = 'maintenance_work_mem' THEN 'Per maintenance operation'
        END as description
FROM pg_settings 
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem');

Common Pitfalls to Avoid:
1. Setting Too High
- Can cause memory pressure
- May lead to OOM (Out of Memory) errors
- Reduces available memory for other operations
2. Ignoring Concurrent Operations
- Multiple operations per connection multiply memory usage
- Consider peak concurrent load, not just max_connections
3. Not Monitoring Long-term
- Performance can change as data grows
- Regular monitoring is essential
4. One-Size-Fits-All Approach
- Different workloads may need different settings
- Consider per-query tuning for critical operations

Monitoring Queries:
-- Monitor work_mem usage
SELECT  pid,usename,application_name,state,query_start,query,temp_files,temp_bytes
FROM pg_stat_activity WHERE temp_files > 0;

-- Check for memory pressure
SELECT name,setting,unit FROM pg_settings WHERE name IN ('work_mem', 'shared_buffers', 'effective_cache_size');

Unable to start postgres db with db=,user=,app=,client=DETAIL: Permissions should be u=rwx (0700) error

Unable start postgres database, when i tried to start db it exit with below error

root@postgresvm01.int data10 #  systemctl start edb-as-10

Job for edb-as-10.service failed because the control process exited with error code. See "systemctl status edb-as-10.service" and "journalctl -xe" for details.

Verified pgstartup.log file

[enterprisedb@postgresvm01 log]$ cat pgstartup.log

WARNING --> PERL_INSTALL_PATH is not set in /u01/edb/as10/etc/sysconfig/plLanguages.config file

WARNING --> PYTHON_INSTALL_PATH is not set in /u01/edb/as10/etc/sysconfig/plLanguages.config file

WARNING --> TCL_INSTALL_PATH is not set in /u01/edb/as10/etc/sysconfig/plLanguages.config file

2022-04-22 08:34:34 PDT [6885]: [1-1] db=,user=,app=,client=FATAL:  data directory "/pg_base/data10" has group or world access

2022-04-22 08:34:34 PDT [6885]: [2-1] db=,user=,app=,client=DETAIL:  Permissions should be u=rwx (0700).

Startup command failing due to permission issues 

Check Permission 

root@postgresvm01.int # ls -ltr

total 8

drwxr-xr-x. 22 enterprisedb enterprisedb 4096 Apr 22 08:31 data10

Change Permission 

root@postgresvm01.int # chmod 700 data10

root@postgresvm01.int # ls -ltr

total 8

drwx------. 22 enterprisedb enterprisedb 4096 Apr 22 08:31 data10

Db came up without any issues 

root@postgresvm01.int #systemctl start edb-as-10

root@postgresvm01.int # systemctl status edb-as-10

● edb-as-10.service - EDB Postgres Advanced Server 10

   Loaded: loaded (/usr/lib/systemd/system/edb-as-10.service; enabled; vendor preset: disabled)

   Active: active (running) since Fri 2022-04-22 08:37:47 PDT; 2h 8min ago

  Process: 6887 ExecStopPost=/bin/bash -c rm -f /var/lock/edb/as10/edb-as-10 (code=exited, status=0/SUCCESS)

  Process: 27092 ExecStop=/u01/edb/as10/bin/pg_ctl stop -m fast -w -D /pg_base/data10 (code=exited, status=0/SUCCESS)

  Process: 7294 ExecStartPost=/bin/bash -c touch /var/lock/edb/as10/edb-as-10 (code=exited, status=0/SUCCESS)

  Process: 7212 ExecStart=/bin/bash -c /usr/lib/systemd/system/edb-as-10.sh (code=exited, status=0/SUCCESS)

 Main PID: 7234 (edb-postgres)

   Memory: 7.4G

   CGroup: /system.slice/edb-as-10.service

           ├─7234 /u01/edb/as10/bin/edb-postgres -D /pg_base/data10

           ├─7235 postgres: logger process

           ├─7236 postgres: startup process   recovering 0000000400001E420000006E

           ├─7257 postgres: checkpointer process

           ├─7258 postgres: writer process

           ├─7292 postgres: stats collector process

           ├─7293 postgres: wal receiver process   streaming 1E42/6E09A000

           └─7392 postgres: enterprisedb edb [local] idle

Terraform script to create a PostgreSQL database on AWS

This script will create a PostgreSQL database instance using the aws_db_instance resource. The aws_security_group resource is used to allow inbound traffic on port 5432, which is the default port used by PostgreSQL. The output block will display the endpoint of the database instance created.

You will need to set the appropriate values for the region, identifier, engine_version, instance_class, allocated_storage, username, and password parameters to match your specific requirements. You can also customize the security group rules and tags as needed.

Once you have saved this script to a .tf file, you can run terraform init, terraform plan, and terraform apply commands to provision the PostgreSQL database instance on AWS.

 

Terraform scripts:

provider "aws" {
  region = "us-west-2"
}

resource "aws_security_group" "postgres" {
  name_prefix = "postgres"
  ingress {
    from_port   = 5432
    to_port     = 5432
    protocol    = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

resource "aws_db_instance" "postgres" {
  identifier            = "my-postgres-db"
  engine                = "postgres"
  engine_version        = "12.4"
  instance_class        = "db.t2.micro"
  allocated_storage     = 10
  storage_type          = "gp2"
  username              = "postgres"
  password              = "mysecretpassword"
  skip_final_snapshot   = true
  vpc_security_group_ids = [aws_security_group.postgres.id]

  tags = {
    Name = "My PostgreSQL DB"
  }
}

output "database_endpoint" {
  value = aws_db_instance.postgres.endpoint
}

Reload Postgres config settings without restarting database

We can load config settings updated on pg_hba.conf to postgres database without restarting database using below commands

From Command Prompt:

/usr/bin/pg_ctl reload

Using sql Command from psql:

SELECT pg_reload_conf();

PostgreSQL psql.bin: could not connect to server: Connection refused

-bash-4.2$ psql

psql.bin: could not connect to server: Connection refused

     Is the server running locally and accepting

     connections on Unix domain socket "/tmp/.s.PGSQL.5444"?

-bash-4.2$

Not able to connect to psql,,pg server is down

Start server using pg_ctl

-bash-4.2$ pg_ctl start -U postgres -D /u01/app/edb/data

waiting for server to start....2018-07-17 14:09:50 IST FATAL:  data directory "/u01/app/edb/data" has group or world access

2018-07-17 14:09:50 IST DETAIL:  Permissions should be u=rwx (0700).

 stopped waiting

pg_ctl: could not start server

Examine the log output.

-bash-4.2$ pg_ctl start -U postgres -D /u01/app/edb/data

waiting for server to start....2018-07-17 14:10:34 IST LOG:  listening on IPv4 address "0.0.0.0", port 5444

2018-07-17 14:10:34 IST LOG:  listening on IPv6 address "::", port 5444

2018-07-17 14:10:34 IST LOG:  listening on Unix socket "/tmp/.s.PGSQL.5444"

2018-07-17 14:10:34 IST LOG:  redirecting log output to logging collector process

2018-07-17 14:10:34 IST HINT:  Future log output will appear in directory "log".

 done

server started

-bash-4.2$ psql

Password:

psql.bin (10.1.5)

Type "help" for help.

enterprisedb=# \l

                                      List of databases

     Name     |    Owner     | Encoding |   Collate   |    Ctype    | ICU |       Access privileges      

--------------+--------------+----------+-------------+-------------+-----+-------------------------------

 edb          | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |

 enterprisedb | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |

 postgres     | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |

 template0    | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/enterprisedb              +

              |              |          |             |             |     | enterprisedb=CTc/enterprisedb

 template1    | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/enterprisedb              +

              |              |          |             |             |     | enterprisedb=CTc/enterprisedb

 testdb       | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |

(6 rows)