Re: calculating the MD5 hash of role passwords in C

[Adrian Klaver]

On 1/22/20 12:58 PM, Matthias Apitz wrote:

El día miércoles, enero 22, 2020 a las 03:32:17p. m. -0500, Justin escribió:


Not sure what your after  but  here is more information regarding how to
store passwords in Postgresql,  ...


I just want to write a piece of C-code to generate the same string as
stored in rolpassword based on the roles password (and as I learned
added the rolname), i.e. how to generate md52f128a1fbbecc4b16462e8fc8dda5cd5
from sisis123 (password) and sisis (rolename). And this, not to brute
force credentials, but to understand the hash.


The way Postgres does it:

https://doxygen.postgresql.org/src_2common_2md5_8c.html#ad1cda4632643f79bbb60f0466fec0e41



matthias





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: calculating the MD5 hash of role passwords in C

[Justin]

Not sure what your after  but  here is more information regarding how to
store passwords in Postgresql,  not related to database roles but for
storing passwords for things like websites...

https://www.postgresql.org/docs/current/pgcrypto.html
section F.25.2.XXX


On Wed, Jan 22, 2020 at 2:41 PM Matthias Apitz  wrote:

> El día miércoles, enero 22, 2020 a las 07:58:47p. m. +0100, Christoph
> Moench-Tegeder escribió:
>
> > ## Matthias Apitz (g...@unixarea.de):
> >
> > > sisis71=# select rolname, rolpassword from pg_authid where rolname =
> 'sisis';
> > >  rolname | rolpassword
> > > -+-
> > >  sisis   | md52f128a1fbbecc4b16462e8fc8dda5cd5
> > >
> > > I know the clear text password of the role, it is simple 'sisis123',
> how
> > > could I calculate the above MD5 hash from the clear text password, for
> > > example in C? Which salt is used for the crypt(3) function?
> >
> > The documentation on pg_authid has the details:
> > "The MD5 hash will be of the user's password concatenated to their user
> name."
> > https://www.postgresql.org/docs/12/catalog-pg-authid.html
>
> Thanks to all who replied.
>
> This is still not exactly what I was looking for. But has an interesting
> detail (salting the role password by adding the role name to it). An
> implementation with UNIX crypt(3) for MD5 would need an additional salt
> like '$1$salt' to encrypt 'sisis123sisis'. For sure the next place to
> look is the implementation of the PostgreSQL's md5() function.
>
> Thanks again
>
> matthias
>
>
> --
> Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/
> +49-176-38902045
> Public GnuPG key: http://www.unixarea.de/key.pub
>
> Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit
> Russland!
> Germany out of NATO! NATO out of Germany! Peace with Russia!
> ¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia!
>
>
>

Re: calculating the MD5 hash of role passwords in C

[Matthias Apitz]

El día miércoles, enero 22, 2020 a las 07:52:51p. m. +, Igor Neyman 
escribió:

> -Original Message-
> From: Matthias Apitz [mailto:g...@unixarea.de] 
> Sent: Wednesday, January 22, 2020 2:41 PM
> To: pgsql-general@lists.postgresql.org
> Subject: Re: calculating the MD5 hash of role passwords in C
> 
> 
> --
> Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 
> Public GnuPG key: http://www.unixarea.de/key.pub
> 
> Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit 
> Russland!
> Germany out of NATO! NATO out of Germany! Peace with Russia!
> ¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia!
> 
> _
> 
> I don't think that political slogans in your signature are appropriate for 
> this forum.
> 

This is technical just a signature and normally I delete it when posting
to groups, I forgot it in this case.

Said that, in any case, you are free to "think" whatever you
want, as I am free to write whatever I think. And you are free to just ignore 
it.

matthias


-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

RE: calculating the MD5 hash of role passwords in C

[Igor Neyman]

-Original Message-
From: Matthias Apitz [mailto:g...@unixarea.de] 
Sent: Wednesday, January 22, 2020 2:41 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: calculating the MD5 hash of role passwords in C



.


--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 
Public GnuPG key: http://www.unixarea.de/key.pub

Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit Russland!
Germany out of NATO! NATO out of Germany! Peace with Russia!
¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia!

_

I don't think that political slogans in your signature are appropriate for this 
forum.

Re: calculating the MD5 hash of role passwords in C

[Matthias Apitz]

El día miércoles, enero 22, 2020 a las 07:58:47p. m. +0100, Christoph 
Moench-Tegeder escribió:

> ## Matthias Apitz (g...@unixarea.de):
> 
> > sisis71=# select rolname, rolpassword from pg_authid where rolname = 
> > 'sisis';
> >  rolname | rolpassword
> > -+-
> >  sisis   | md52f128a1fbbecc4b16462e8fc8dda5cd5
> > 
> > I know the clear text password of the role, it is simple 'sisis123', how
> > could I calculate the above MD5 hash from the clear text password, for
> > example in C? Which salt is used for the crypt(3) function?
> 
> The documentation on pg_authid has the details:
> "The MD5 hash will be of the user's password concatenated to their user name."
> https://www.postgresql.org/docs/12/catalog-pg-authid.html

Thanks to all who replied.

This is still not exactly what I was looking for. But has an interesting
detail (salting the role password by adding the role name to it). An
implementation with UNIX crypt(3) for MD5 would need an additional salt
like '$1$salt' to encrypt 'sisis123sisis'. For sure the next place to
look is the implementation of the PostgreSQL's md5() function.

Thanks again

matthias


-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit Russland!
Germany out of NATO! NATO out of Germany! Peace with Russia!
¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia!

Re: Memory usage of pg_basebackup...

[Magnus Hagander]

On Wed, Jan 22, 2020 at 11:23 AM Mladen Marinović
 wrote:
>
> Hi,
>
> Does anybody know how much RAM does pg_basebackup use and if it depends on 
> the size of the database that the backup is being created?

pg_basebackup uses very little RAM, as the data received is written to
disk immediately. If you are doing compression it uses a little more,
but still within the "you don't really need to care" range. The RAM
usage is not dependent on the size of the database, it is always that
small.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Memory usage of pg_basebackup...

[Mladen Marinović]

Hi,

Does anybody know how much RAM does pg_basebackup use and if it depends on
the size of the database that the backup is being created?

Regards,
Mladen Marinović

Re: calculating the MD5 hash of role passwords in C

[Christoph Moench-Tegeder]

## Matthias Apitz (g...@unixarea.de):

> sisis71=# select rolname, rolpassword from pg_authid where rolname = 'sisis';
>  rolname | rolpassword
> -+-
>  sisis   | md52f128a1fbbecc4b16462e8fc8dda5cd5
> 
> I know the clear text password of the role, it is simple 'sisis123', how
> could I calculate the above MD5 hash from the clear text password, for
> example in C? Which salt is used for the crypt(3) function?

The documentation on pg_authid has the details:
"The MD5 hash will be of the user's password concatenated to their user name."
https://www.postgresql.org/docs/12/catalog-pg-authid.html

Regards,
Christoph

-- 
Spare Space

Re: calculating the MD5 hash of role passwords in C

[Adrian Klaver]

On 1/22/20 10:20 AM, Matthias Apitz wrote:


Hello,

If I look into the database I see:

sisis71=# select rolname, rolpassword from pg_authid where rolname = 'sisis';
  rolname | rolpassword
-+-
  sisis   | md52f128a1fbbecc4b16462e8fc8dda5cd5

I know the clear text password of the role, it is simple 'sisis123', how
could I calculate the above MD5 hash from the clear text password, for
example in C? Which salt is used for the crypt(3) function?


https://www.postgresql.org/docs/12/runtime-config-connection.html

"Because md5 uses the user name as salt on both the client and server, 
md5 cannot be used with db_user_namespace."


~/src/common/md5.c
/*
 * Place salt at the end because it may be known by users 
trying to crack

 * the MD5 output.
 */


So:
select md5('sisis123sisis');
   md5
--
 2f128a1fbbecc4b16462e8fc8dda5cd5



Thanks

matthias




--
Adrian Klaver
adrian.kla...@aklaver.com

calculating the MD5 hash of role passwords in C

[Matthias Apitz]

Hello,

If I look into the database I see:

sisis71=# select rolname, rolpassword from pg_authid where rolname = 'sisis';
 rolname | rolpassword
-+-
 sisis   | md52f128a1fbbecc4b16462e8fc8dda5cd5

I know the clear text password of the role, it is simple 'sisis123', how
could I calculate the above MD5 hash from the clear text password, for
example in C? Which salt is used for the crypt(3) function?

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub


signature.asc
Description: PGP signature

Re: PostgresSQL 10 on remote server - Socket is not connected (0x00002749/10057)

[Adrian Klaver]

On 1/22/20 2:18 AM, Tanja Savic wrote:

Hi,

I’m running a pgAdmin from Windows 10 :

pgAdmin Version: 4.17

Python Version:**3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 21:26:53) [MSC 
v.1916 32 bit (Intel)]


and I’m able to connect to the remote server. I can see schemas and 
tables, but when I want to run a query I get this error:


could not send data to server: Socket is not connected 
(0x2749/10057) could not send SSL negotiation packet: Socket is not 
connected (0x2749/10057)


What are the connection settings?

What is the query that fails?

Can you connect to the remote using psql?



Running queries on local DB instance is ok, error happens only when I 
connect to the remote server.


DB Version : PostgresSQL 10.10

I do not remember doing some changes that could cause this error. Is 
there any solution for this problem, cause I didn’t googled out any?


Thank you for you time.

Best,

Tanja




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: SQL Query Syntax help

[David G. Johnston]

On Wednesday, January 22, 2020, srikkanth 
wrote:

> Hi Jan,
>
> Thanks for your input, it is working exactly what i want, but still i m
> facing issue because in my table there are different data types they are 10
> to 15 columns are Boolean, one primary key, 140 columns are character.
>
> when i m applying the logic on this scenario i m getting the below error.
>
> ERROR: UNION types boolean and character cannot be matched
> LINE 10: ..._ID","Critical_Decision" as col_Name, "Critical_... ^
> SQL state: 42804
> Character: 585
>
> After applying the logic i need to take this data into the table.
>
> can you please help me how to write the syntax in this case.
>

Cast everything to text?


https://www.postgresql.org/docs/12/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

David J.

Re: @@TRANCOUNT ?

[Justin]

Postgresql does not support Transaction in the same way mssql,   it  does
support nesting transactions in a limited way from version 11+
https://docs.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql?view=sql-server-ver15
https://www.postgresql.org/docs/current/sql-begin.html
https://wiki.postgresql.org/wiki/Autonomous_subtransactions
https://www.postgresql.org/docs/11/sql-createprocedure.html
https://severalnines.com/database-blog/overview-new-stored-procedures-postgresql-11


>From version 11 and up we have Procedures  it supports commit and rollback
transaction inside a PROCEDURE and must be called with CALL,  there are no
savepoints or transaction naming support...

There are work around this problem using PlPython, or FDW to callback to
postgresql creating a new session to create independent Transactions..







On Wed, Jan 22, 2020 at 7:59 AM İlyas Derse  wrote:

> What's Prostgres' equivalent of select @@trancount
> ?  Do you have an
> idea ?
>

Re: @@TRANCOUNT ?

[Rob Sargent]

1 ;)

> On Jan 22, 2020, at 5:59 AM, İlyas Derse  wrote:
> 
> 
> What's Prostgres' equivalent of select @@trancount?  Do you have an idea ?

@@TRANCOUNT ?

[İlyas Derse]

What's Prostgres' equivalent of select @@trancount
?  Do you have an
idea ?

Re: SQL Query Syntax help

[srikkanth]

Hi Jan,Thanks for your input, it is working exactly what i want, but still i m 
facing issue because in my table there are different data types they are 10 to 
15 columns are Boolean, one primary key, 140 columns are character.when i m 
applying the logic on this scenario i m getting the below error.ERROR: UNION 
types boolean and character cannot be matchedLINE 10: 
..._ID,Critical_Decision as col_Name, Critical_... ^SQL 
state: 42804Character: 585After applying the logic i need to take this data 
into the table.can you please help me how to write the syntax in this 
case.Thanks,Srikanth BFrom: Jan Kohnert 
nospam001-li...@jan-kohnert.deSent: Wed, 22 Jan 2020 17:11:56To: 
pgsql-general@lists.postgresql.orgSubject: Re: SQL Query Syntax helpHi,Am 
Mittwoch, 22. Januar 2020, 12:00:45 CET schrieb srikkanth:[sniped 
HTML]something likeselect id, Col A as Col 
Name, Col A as Col Valuefrom inputunion 
allselect id, Col B as Col Name, Col 
B as Col Valuefrom input...order by 1, 2;should 
work.--Kind regards Jan

Re: SQL Query Syntax help

[Geoff Winkless]

On Wed, 22 Jan 2020 at 11:00, srikkanth 
wrote:

> Can you please help me in writing the syntax for the below mentioned table.
>

Suggest looking at the crosstab function.

https://www.postgresql.org/docs/current/tablefunc.html

crosstab(text source_sql, text category_sql)
Produces a "pivot table" with the value columns specified by a second
query

Geoff

Re: SQL Query Syntax help

[Daniel Verite]

srikkanth wrote:

> Can you please help me in writing the syntax for the below mentioned

This looks like an UNPIVOT operation.

Here's a generic method that does this without having to specify the
columns individually, with the help of json functions:

SELECT ID, key, value FROM
  (SELECT ID, row_to_json(t.*) AS line FROM PivotTableName t) AS r
JOIN LATERAL json_each_text(r.line) on (key <> 'ID');


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

PgQ and NOTIFY/LISTEN

[Олег Самойлов]

Hi all.

I looked for a forum or a mailing list for PgQ, but didn't find. May be someone 
know here?

Does PgQ use NOTIFY/LISTEN for PgQ internal daemon or consumers. And if not, 
why? May be it has internal own simulation of NOTIFY/LISTEN functionality?

Re: SQL Query Syntax help

[Jan Kohnert]

Hi,

Am Mittwoch, 22. Januar 2020, 12:00:45 CET schrieb srikkanth:
[sniped HTML]

something like

select 
id, 'Col A' as "Col Name", "Col A" as "Col Value"
from input
union all
select 
id, 'Col B' as "Col Name", "Col B" as "Col Value"
from input
...
order by
1, 2;

should work.

-- 
Kind regards Jan

SQL Query Syntax help

[srikkanth]

Hi Team,Can you please help me in writing the syntax for the below mentioned 
table.Input :ID   
   Col A   Col B   Col C
   Col D   Col E   Col F
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
    
   Required Output :  
   ID  Col NameCol 
Value   
Col A   
Col B   
Col C   

Col D   
Col E   
Col F   
Col A   

Col B   
Col C   
Col D   

Col E   
Col F   
Col A   

Col B   
Col C   
Col D   
Col E   

Col F   And so on for 
the , , , , , Thanks,Srikanth B

PostgresSQL 10 on remote server - Socket is not connected (0x00002749/10057)

[Tanja Savic]

Hi,

I’m running a pgAdmin from Windows 10 :
pgAdmin  Version: 4.17
Python Version: 3.7.3 (v3.7.3:ef4ec6ed12, Mar 25 2019, 21:26:53) [MSC v.1916 32 
bit (Intel)]

and I’m able to connect to the remote server. I can see schemas and tables, but 
when I want to run a query I get this error:

could not send data to server: Socket is not connected (0x2749/10057) could 
not send SSL negotiation packet: Socket is not connected (0x2749/10057)

Running queries on local DB instance is ok, error happens only when I connect 
to the remote server.
DB Version : PostgresSQL 10.10

I do not remember doing some changes that could cause this error. Is there any 
solution for this problem, cause I didn’t googled out any?

Thank you for you time.
Best,
Tanja