Re: [GENERAL] password in recovery.conf [SOLVED]

[Nelson Green]

On Fri, Sep 26, 2014 at 6:40 PM, John R Pierce  wrote:

> On 9/26/2014 4:32 PM, Nelson Green wrote:
>
>>
>> Thanks Bosco, DrakoRod, and Adrian. Between the three of you it became
>> obvious
>> that I was doing something wrong. And yes, in the end you were right.
>> Doubling
>> the quote does indeed work.
>>
>> It turns out it this particular password also had a \ in it, and my
>> console
>> width wrapped right before it, putting it as the first character on the
>> next
>> line, where I just didn't notice it until a few minutes ago. I changed
>> that to
>> a ^ for the time being, and then doubled the quote whereupon it all
>> worked. I
>> will certainly look into how to escape the backslash too, but that's for
>> next
>> week at this point.
>>
>
> I'd consider using `mkpasswd -l 15 -s 0`  just to avoid any such
> problems.   15 random alphanumerics is already plenty complex, 62^15th
> possible combinations, without needing to mix in special characters.
>
> $ mkpasswd -l 15 -s 0
> eec1kj7ZsthlYmh
>

Thanks John. We use apg which has similar options. But alas, I must comply
with
organizational password policies.

Regards,
Nelson

Re: [GENERAL] password in recovery.conf [SOLVED]

[Nelson Green]

On Fri, Sep 26, 2014 at 6:46 PM, Adrian Klaver 
wrote:

> On 09/26/2014 04:32 PM, Nelson Green wrote:
>
>> On Fri, Sep 26, 2014 at 5:51 PM, Adrian Klaver
>>
>
>  Doubling the quote seems to work here.
>>
>>
>> Thanks Bosco, DrakoRod, and Adrian. Between the three of you it became
>> obvious
>> that I was doing something wrong. And yes, in the end you were right.
>> Doubling
>> the quote does indeed work.
>>
>> It turns out it this particular password also had a \ in it, and my
>> console
>> width wrapped right before it, putting it as the first character on the
>> next
>> line, where I just didn't notice it until a few minutes ago. I changed
>> that to
>> a ^ for the time being, and then doubled the quote whereupon it all
>> worked. I
>> will certainly look into how to escape the backslash too, but that's for
>> next
>> week at this point.
>>
>
> aklaver@panda:~> psql 'dbname=test user=test_user password=test\\pwd'
> psql (9.0.17)
> Type "help" for help.
>
> test=>


Thanks again Adrian! Figures it's that easy.

Confession time. When I'm trying to work through something like this where
different iterations are going to be tried, I sit down and spell them out
first.
But since I was remoted in and things were going so slow (and I was pretty
tired), I just tried different combinations on the single quote. When I
noticed
the backslash I tried to double it, but with no luck. However, in all
honesty I
don't know what I was doing with the single quote at that particular moment.
Bottom line is I probably shot myself in the foot in several ways with this
one.

I appreciate the patience with me.
Nelson

Re: [GENERAL] password in recovery.conf [SOLVED]

[Gavin Flower]

On 27/09/14 11:56, John R Pierce wrote:

On 9/26/2014 4:40 PM, John R Pierce wrote:
I'd consider using `mkpasswd -l 15 -s 0` just to avoid any such 
problems.   15 random alphanumerics is already plenty complex, 
62^15th possible combinations, without needing to mix in special 
characters.


$ mkpasswd -l 15 -s 0
eec1kj7ZsthlYmh


btw, thats 768,909,700,000,000,000,000,000,000 possible passwords. 768 
septillion, using the aamerican 'short scale' naming convention.  if 
you could brute force try 1/second, it would merely take 
24,365,800,000,000 centuries (24 trillion).



So do you think a password like *Nxw7TnC2^}%(}tEz* is strong enough?  :-)

I developed a Java program that generates 20 passwords (each of 16 
characters) at a time, I've attached it for anyone who might be 
interested.  I have put it under the GPL version 3, but I might consider 
releasing under other licences.



Cheers,
Gavin
package gcf.misc;

/**
 * Copyright © 2012 Gavin C. Flower
 * 
 * author: gavin.flo...@archidevsys.co.nz
 * 
 * This program is free software: you can redistribute it and/or modify it under
 * the terms of the GNU General Public License as published by the Free Software
 * Foundation, either version 3 of the License, or (at your option) any later
 * version.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
 * details.
 * 
 * For full details of the license see .
 */

import java.security.SecureRandom;

public class AppPasswordGenerator
{
private final static int PASSWORD_LENGTH = 16;

private final static int MAX_INDEX = PASSWORD_LENGTH - 1;

/*
 * We avoid ambiguous characters, so you won't get 'I1|l', 'B8', 'S5', or
 * 'O0' being produced
 */

private static String DIGITS = "23456789";

private static String SPECIAL = "!@#$%^&*()_+{}[]<>.:";

private static String UPPER = "ACDEFGHJKLMNPQRTVWXY";

private static String LOWER = "abcdefghijklmnopqrstuvwxyz";

private static String FULL = DIGITS + SPECIAL + UPPER + LOWER;

private final StringBuilder SB = new StringBuilder(PASSWORD_LENGTH);

SecureRandom secureRandom = new SecureRandom();

AppPasswordGenerator()
{
/*
 * This is way more complicated than it needs to be for the current
 * application, but it was fun coding it!
 * 
 * The use of sin() & exp() introduce a semirandom delay in obtaining
 * the current time in nano seconds as well as returning values to act
 * as additional randomising factors.
 */
long nanoA = System.nanoTime();
double sinVal = Math.sin(nanoA);
long nanoB = System.nanoTime();
double expVal = Math.exp(sinVal);
long nanoC = System.nanoTime();
int shift = (int) nanoB & 0x3F;
long rotation = Long.rotateRight(nanoC, shift);
long rawBits = Double.doubleToRawLongBits(expVal);
long seed = rotation ^ rawBits;
secureRandom.setSeed(seed);

// System.out.printf("nanoA: %016X\n", nanoA);
// System.out.printf("   sinVal: %16.13f\n", sinVal);
// System.out.printf("nanoB: %016X\n", nanoB);
// System.out.printf("   expVal: %16.13f\n", expVal);
// System.out.printf("nanoC: %016X\n", nanoC);
// System.out.printf("shift: %16d\n", shift);
// System.out.printf("  rawBits: %016X\n", rawBits);
// System.out.printf(" rotation: %016X\n", rotation);
// System.out.printf(" seed: %016X\n", seed);
// System.out.printf("FULL.length(): %16d\n", FULL.length());
}

public static void main(String[] args)
{
AppPasswordGenerator appPasswordGenerator = new AppPasswordGenerator();
appPasswordGenerator.go();
}

private void go()
{
assert PASSWORD_LENGTH > 5; // Actually, later code assume 16...

for (int i = 0; i < 20; i++)
{
printAPassword();
}
}

private void printAPassword()
{
addChar(DIGITS);
addChar(DIGITS);
addChar(SPECIAL);
addChar(UPPER);
addChar(LOWER);

for (int ii = SB.length(); ii < PASSWORD_LENGTH; ii++)
{
addChar(FULL);
}

// Randomise password characters
for (int index_a = 0; index_a < PASSWORD_LENGTH; index_a++)
{
char ca = SB.charAt(index_a);
int index_b = secureRandom.nextInt(PASSWORD_LENGTH);
char cb = SB.charAt(index_b);
SB.setCharAt(index_b, ca);
SB.setCharAt(index_a, cb);
}

// Ensure the last character is not a digit
while (Character.isDigit(SB.charAt(MAX_INDEX)))
{
int index = secureRandom.nextInt(MAX_INDEX);
char ca = SB.charAt(MAX_INDEX);
char cb = SB.charAt(

Re: [GENERAL] password in recovery.conf [SOLVED]

[John R Pierce]

On 9/26/2014 4:40 PM, John R Pierce wrote:
I'd consider using `mkpasswd -l 15 -s 0`  just to avoid any such 
problems.   15 random alphanumerics is already plenty complex, 62^15th 
possible combinations, without needing to mix in special characters.


$ mkpasswd -l 15 -s 0
eec1kj7ZsthlYmh


btw, thats 768,909,700,000,000,000,000,000,000 possible passwords. 768 
septillion, using the aamerican 'short scale' naming convention.  if you 
could brute force try 1/second, it would merely take 
24,365,800,000,000 centuries (24 trillion).


--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password in recovery.conf [SOLVED]

[Adrian Klaver]

On 09/26/2014 04:32 PM, Nelson Green wrote:

On Fri, Sep 26, 2014 at 5:51 PM, Adrian Klaver



Doubling the quote seems to work here.


Thanks Bosco, DrakoRod, and Adrian. Between the three of you it became
obvious
that I was doing something wrong. And yes, in the end you were right.
Doubling
the quote does indeed work.

It turns out it this particular password also had a \ in it, and my console
width wrapped right before it, putting it as the first character on the next
line, where I just didn't notice it until a few minutes ago. I changed
that to
a ^ for the time being, and then doubled the quote whereupon it all
worked. I
will certainly look into how to escape the backslash too, but that's for
next
week at this point.


aklaver@panda:~> psql 'dbname=test user=test_user password=test\\pwd'
psql (9.0.17)
Type "help" for help.

test=>




Apologies for the noise. Just been one of those days.

Thanks,
Nelson



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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password in recovery.conf [SOLVED]

[John R Pierce]

On 9/26/2014 4:32 PM, Nelson Green wrote:


Thanks Bosco, DrakoRod, and Adrian. Between the three of you it became 
obvious
that I was doing something wrong. And yes, in the end you were right. 
Doubling

the quote does indeed work.

It turns out it this particular password also had a \ in it, and my 
console
width wrapped right before it, putting it as the first character on 
the next
line, where I just didn't notice it until a few minutes ago. I changed 
that to
a ^ for the time being, and then doubled the quote whereupon it all 
worked. I
will certainly look into how to escape the backslash too, but that's 
for next

week at this point.


I'd consider using `mkpasswd -l 15 -s 0`  just to avoid any such 
problems.   15 random alphanumerics is already plenty complex, 62^15th 
possible combinations, without needing to mix in special characters.


$ mkpasswd -l 15 -s 0
eec1kj7ZsthlYmh


--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password in recovery.conf [SOLVED]

[Nelson Green]

On Fri, Sep 26, 2014 at 5:51 PM, Adrian Klaver 
wrote:

> On 09/26/2014 12:58 PM, Nelson Green wrote:
>
>> Hello all,
>>
>> I am setting up a streaming replication stand-by, and the replication
>> role password has a single quote in it. I am unable to properly
>> reference the password in the conninfo setting of recovery.conf so it
>> will authenticate to the master. Doubling the quote gives me a syntax
>> error, and escaping it or quoting it with double-quotes gives me an
>> authentication error. The password is correct because I can copy it from
>> the recovery.conf and supply it when prompted by pg_basebackup, so if I
>> may, what is the proper way to handle single quotes within the conninfo
>> string?
>>
>
>
> Doubling the quote seems to work here.
>

Thanks Bosco, DrakoRod, and Adrian. Between the three of you it became
obvious
that I was doing something wrong. And yes, in the end you were right.
Doubling
the quote does indeed work.

It turns out it this particular password also had a \ in it, and my console
width wrapped right before it, putting it as the first character on the next
line, where I just didn't notice it until a few minutes ago. I changed that
to
a ^ for the time being, and then doubled the quote whereupon it all worked.
I
will certainly look into how to escape the backslash too, but that's for
next
week at this point.

Apologies for the noise. Just been one of those days.

Thanks,
Nelson

Re: [GENERAL] password in recovery.conf

[Adrian Klaver]

On 09/26/2014 12:58 PM, Nelson Green wrote:

Hello all,

I am setting up a streaming replication stand-by, and the replication
role password has a single quote in it. I am unable to properly
reference the password in the conninfo setting of recovery.conf so it
will authenticate to the master. Doubling the quote gives me a syntax
error, and escaping it or quoting it with double-quotes gives me an
authentication error. The password is correct because I can copy it from
the recovery.conf and supply it when prompted by pg_basebackup, so if I
may, what is the proper way to handle single quotes within the conninfo
string?



Doubling the quote seems to work here.

aklaver@panda:~> psql 'dbname=test user=test_user password=test''pwd'
psql (9.0.17)
Type "help" for help.

test=>

What is the syntax error you get?

Another option:

http://www.postgresql.org/docs/9.3/static/standby-settings.html

 A password needs to be provided too, if the primary demands password 
authentication. It can be provided in the primary_conninfo string, or in 
a separate ~/.pgpass file on the standby server (use replication as the 
database name)


So you might look at setting up a .pgpass 
file(http://www.postgresql.org/docs/9.3/static/libpq-pgpass.html)




Obviously I can change the password, but we use an automated password
generator so I'd like to not have to keep generating passwords, and
checking them, until I get one that will work, unless that my only option.

Thanks,
Nelson



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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password in recovery.conf

[DrakoRod]

Hi! 

Have you tried escape the Single or Double quote? Maybe this information can
help you:

http://stackoverflow.com/questions/12316953/insert-varchar-with-single-quotes-in-postgresql
http://www.postgresql.org/docs/9.1/static/sql-syntax-lexical.html

Best Regards! 





-
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions 
--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/password-in-recovery-conf-tp5820725p5820737.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password in recovery.conf

[Bosco Rama]

On 09/26/14 12:58, Nelson Green wrote:
>
> I am setting up a streaming replication stand-by, and the replication
> role password has a single quote in it. I am unable to properly
> reference the password in the conninfo setting of recovery.conf so it
> will authenticate to the master. Doubling the quote gives me a syntax
> error, and escaping it or quoting it with double-quotes gives me an
> authentication error.

You may have to double it twice -- once for recovery.conf and once for
the actual usage in the connection.

Thus for password abc'123 you would want to use:
   'user=user_name password=abc123 host=primary_host'

Or possibly even a combination of doubling and escaping:
   'user=user_name password=abc\''123 host=primary_host'
or:
   'user=user_name password=abc\\''123 host=primary_host'

This is just conjecture.  I don't use this method of recovery myself.

HTH.

Bosco.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] password in recovery.conf

[Nelson Green]

Hello all,

I am setting up a streaming replication stand-by, and the replication role
password has a single quote in it. I am unable to properly reference the
password in the conninfo setting of recovery.conf so it will authenticate
to the master. Doubling the quote gives me a syntax error, and escaping it
or quoting it with double-quotes gives me an authentication error. The
password is correct because I can copy it from the recovery.conf and supply
it when prompted by pg_basebackup, so if I may, what is the proper way to
handle single quotes within the conninfo string?

Obviously I can change the password, but we use an automated password
generator so I'd like to not have to keep generating passwords, and
checking them, until I get one that will work, unless that my only option.

Thanks,
Nelson

Re: [GENERAL] password-less access, without using pg_hba

[Reece Hart]

On Fri, Feb 7, 2014 at 8:27 AM, Steve Crawford <
scrawf...@pinpointresearch.com> wrote:

> Ignoring the scary security issues
>

One of the niceties of an RDS deployment is that I don't care much about
the security issues: The machine is not in our VPC, there's only public
data on it, and I presume that AWS has isolated the instance to their
satisfaction. From my point of view, it's an ideal way to make data public
and way better than running it ourselves.

If you can't access pg_hba.conf how about just sticking pgbouncer or
> similar in the middle and have your users connect through that?
>

I like the pgbouncer idea in principle, but it means more work for me that
I'm not willing to take on for this use.

Thanks everyone for the input. I'll stick with an advertised password.

-Reece

Re: [GENERAL] password-less access, without using pg_hba

[Steve Crawford]

On 02/06/2014 06:07 PM, Reece Hart wrote:
I'd like to provide public access, without a password, to a database 
hosted on Amazon RDS.


I'm familiar with using pg_hba.conf to enable trust (no) 
authentication for a user. pg_hba.conf is not available to DBAs on RDS.


Is there any other way to achieve password-less login in postgresql? I 
tried alter user password NULL.



Ignoring the scary security issues

If you can't access pg_hba.conf how about just sticking pgbouncer or 
similar in the middle and have your users connect through that?


Cheers,
Steve


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password-less access, without using pg_hba

[Tatsuo Ishii]

> On Thu, Feb 6, 2014 at 6:37 PM, David Johnston  wrote:
> 
>> Doubtful.
>>
> 
> Yeah, that's what I had assumed too.
> 
> The question is motivated entirely by what I think would make it easier for
> users. In principle it's not difficult to give people a password (as I do
> now), but in practice it's a barrier that I'd like to eliminate.

+1. I told Amazon's RDS guy in Japan that it is a major pain for
PostgreSQL users to not be able to touch pg_hba.conf.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password-less access, without using pg_hba

[David Johnston]

Reece Hart wrote
> On Thu, Feb 6, 2014 at 6:37 PM, David Johnston <

> polobo@

> > wrote:
> 
>> Doubtful.
>>
> 
> Yeah, that's what I had assumed too.
> 
> The question is motivated entirely by what I think would make it easier
> for
> users. In principle it's not difficult to give people a password (as I do
> now), but in practice it's a barrier that I'd like to eliminate.
> 
> -Reece

If your users are connecting directly to a PostgreSQL database then the
presence or absence of a password has no significant impact on usability. 
They have learned SQL and can interact with databases and likely expect to
need a password anyway.  Usually developers make things easier by writing
software that the users interact with instead of the database...

David J.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/password-less-access-without-using-pg-hba-tp5790947p5790966.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password-less access, without using pg_hba

[Reece Hart]

On Thu, Feb 6, 2014 at 6:37 PM, David Johnston  wrote:

> Doubtful.
>

Yeah, that's what I had assumed too.

The question is motivated entirely by what I think would make it easier for
users. In principle it's not difficult to give people a password (as I do
now), but in practice it's a barrier that I'd like to eliminate.

-Reece

[GENERAL] password-less access, without using pg_hba

[Reece Hart]

I'd like to provide public access, without a password, to a database hosted
on Amazon RDS.

I'm familiar with using pg_hba.conf to enable trust (no) authentication for
a user. pg_hba.conf is not available to DBAs on RDS.

Is there any other way to achieve password-less login in postgresql? I
tried alter user password NULL.

Thanks,
Reece

Re: [GENERAL] password-less access, without using pg_hba

[David Johnston]

Reece Hart wrote
> I'd like to provide public access, without a password, to a database
> hosted
> on Amazon RDS.
> 
> I'm familiar with using pg_hba.conf to enable trust (no) authentication
> for
> a user. pg_hba.conf is not available to DBAs on RDS.
> 
> Is there any other way to achieve password-less login in postgresql? I
> tried alter user password NULL.
> 
> Thanks,
> Reece

Doubtful.

You need to give people the correct server ip and user anyway so why not
just give them a password at the same time?

If you are trying to do some automated scripting there are other, better,
solutions than disabling the password requirement. Especially on a
public-visible server.

David J.






--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/password-less-access-without-using-pg-hba-tp5790947p5790948.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password-less access, without using pg_hba

[John R Pierce]

On 2/6/2014 6:07 PM, Reece Hart wrote:
I'd like to provide public access, without a password, to a database 
hosted on Amazon RDS.


I'm familiar with using pg_hba.conf to enable trust (no) 
authentication for a user. pg_hba.conf is not available to DBAs on RDS.


Is there any other way to achieve password-less login in postgresql? I 
tried alter user password NULL.


.pgpass  is supported by any libpq based client.



--
john r pierce  37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password Security Standarts on PostgreSQL

[Chris Travers]

On Fri, Mar 8, 2013 at 4:07 AM, Albe Laurenz wrote:

> Victor Yegorov wrote:
> > 2013/3/8 Albe Laurenz 
> >> This way you can also force a certain password expiry date
> >> (PostgreSQL does not have a password life time).
> >
> > What bout ALTER ROLE ... VALID UNTIL 'timestamp' ?
>
> That's the password expiry date.
>
> Oracle's concept is different: it sets a limit on the time
> between password changes.
> There is no such thing in PostgreSQL.


BTW, your suggestion to use a function here is exactly what we do in
LedgerSMB.  Password expiration is forced to be now() + an interval
specified in a configuration table.

It would be nice to be able to do handling of failed login attempts but
currently I don;t think that's possible from within PostgreSQL (i.e.
without external auth).

Re: [GENERAL] Password Security Standarts on PostgreSQL

[Albe Laurenz]

Victor Yegorov wrote:
> 2013/3/8 Albe Laurenz 
>> This way you can also force a certain password expiry date
>> (PostgreSQL does not have a password life time).
> 
> What bout ALTER ROLE ... VALID UNTIL 'timestamp' ?

That's the password expiry date.

Oracle's concept is different: it sets a limit on the time
between password changes.
There is no such thing in PostgreSQL.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password Security Standarts on PostgreSQL

[Victor Yegorov]

2013/3/8 Albe Laurenz 

> This way you can also force a certain password expiry date
> (PostgreSQL does not have a password life time).
>

What bout ALTER ROLE ... VALID UNTIL 'timestamp' ?


-- 
Victor Y. Yegorov

Re: [GENERAL] Password Security Standarts on PostgreSQL

[Albe Laurenz]

MURAT KOÇ wrote:
> In Oracle, it could be created a user profile called "PROFILE" and this 
> profile could have below
> specifications:
> 
> PASSWORD_LIFE_TIME (that describes when password will expire)
> FAILED_LOGIN_ATTEMPTS (specifies number of failed login attempts before 
> locking user account)
> PASSWORD_LOCK_TIME   (specified time after user account is locked because of 
> failed login attempts
> exceeded)
> PASSWORD_VERIFY_FUNCTION  (this allows setting a strong password verify 
> function - min characters,
> password complexity)
> 
> Has PostgreSQL got any capability like this except LDAP, kerberos or PAM 
> authentication ?

There's the "passwordcheck" contrib:
http://www.postgresql.org/docs/current/static/passwordcheck.html
It does the same thing as Oracle's PASSWORD_VERIFY_FUNCTION.
You can write your own password checking function.
This way you can also force a certain password expiry date
(PostgreSQL does not have a password life time).

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password Security Standarts on PostgreSQL

[Adrian Klaver]

On 03/07/2013 03:10 AM, MURAT KOÇ wrote:

Hi list,
In Oracle, it could be created a user profile called "PROFILE" and this
profile could have below specifications:
PASSWORD_LIFE_TIME (that describes when password will expire)
FAILED_LOGIN_ATTEMPTS (specifies number of failed login attempts before
locking user account)
PASSWORD_LOCK_TIME   (specified time after user account is locked
because of failed login attempts exceeded)
PASSWORD_VERIFY_FUNCTION  (this allows setting a strong password verify
function - min characters, password complexity)
Has PostgreSQL got any capability like this except LDAP, kerberos or PAM
authentication ?


The only part of the above that I know of is VALID UNTIL 
(PASSWORD_LIFE_TIME) from below:


http://www.postgresql.org/docs/9.2/interactive/sql-createrole.html


Regards,
Murat KOC



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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Password Security Standarts on PostgreSQL

[MURAT KOÇ]

Hi list,

In Oracle, it could be created a user profile called "PROFILE" and this
profile could have below specifications:

PASSWORD_LIFE_TIME (that describes when password will expire)
FAILED_LOGIN_ATTEMPTS (specifies number of failed login attempts before
locking user account)
PASSWORD_LOCK_TIME   (specified time after user account is locked because
of failed login attempts exceeded)
PASSWORD_VERIFY_FUNCTION  (this allows setting a strong password verify
function - min characters, password complexity)

Has PostgreSQL got any capability like this except LDAP, kerberos or PAM
authentication ?

Regards,
Murat KOC

Re: [GENERAL] password help

[Guillermo Echevarria Quintana-Gurt]

Im contacting them tomorrow for sure. My issue is that I uninstalled the 
postgresql system from my computer and now I cant get it installed again 
because of the password issue. Thats all im trying to solve, getting the 
postgresql installed again in my laptop and like said because of being really 
really clulesss im having issues with that and im really sorry for taking your 
time on helping me.
 > Date: Mon, 30 Jul 2012 13:49:03 +0800
> From: ring...@ringerc.id.au
> To: guie...@hotmail.com; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] password help
> 
> Please don't reply directly to me, reply via the mailing list (use 
> "Reply all").
> 
> On 07/30/2012 01:35 PM, Guillermo Echevarria Quintana-Gurt wrote:
> > Hi Craig, I really appreciate your answer. My situation is the one I 
> > described you in the first email. I guess i should've add that i really 
> > have no clue about how to use/run or do anything related with postgresql, i 
> > downloaded it because i was told I had to have it in order for a software i 
> > was going to use to work (holdem manager). I installed it and things worked 
> > fine, dont remember anytime doing anything with or to postgres. My problem 
> > comes now that I upgraded to holdem manager 2 and cant get to open it, i 
> > was told that it COULD be related to something to postgres and i could try 
> > uninstalling and reinstalling it again to see if things worked then. I 
> > tried that but as told encountered the password problem.
> This is a Holdem Manager issue. They've done a silent installation of 
> PostgreSQL, and their upgrade tool clearly doesn't do its job right.
> 
> Please contact their technical support for assistance.
> 
> --
> Craig Ringer
  

Re: [GENERAL] password help

[Craig Ringer]

On 07/30/2012 02:00 PM, Guillermo Echevarria Quintana-Gurt wrote:
Im contacting them tomorrow for sure. My issue is that I uninstalled 
the postgresql system from my computer and now I cant get it installed 
again because of the password issue. Thats all im trying to solve, 
getting the postgresql installed again in my laptop and like 
said because of being really really clulesss im having issues with 
that and im really sorry for taking your time on helping me.

A google search for "windows 7 administrator command prompt" reveals:

http://technet.microsoft.com/en-us/library/cc947813(v=ws.10).aspx 



and

http://www.howtogeek.com/howto/windows-vista/run-a-command-as-administrator-from-the-windows-vista-run-box/

both of which explain in detail how to open a command prompt as 
administrator. It's similarly easy to find instructions on how to get to 
the administrative tools in the control panel.


The best way to be less "clueless" - as you call yourself - about 
computers is to actively learn by trying to research things when you run 
into problems. These days Google will often find the answer if you try a 
few different ways to ask the question.


Try re-reading Dave's blog post slowly. When you encounter something you 
don't understand, look it up. It will take longer that way, but you will 
learn more and most importantly you will be more able to solve your own 
problems in future.


--
Craig Ringer

Re: [GENERAL] password help

[Craig Ringer]

Please don't reply directly to me, reply via the mailing list (use 
"Reply all").


On 07/30/2012 01:35 PM, Guillermo Echevarria Quintana-Gurt wrote:

Hi Craig, I really appreciate your answer. My situation is the one I described 
you in the first email. I guess i should've add that i really have no clue 
about how to use/run or do anything related with postgresql, i downloaded it 
because i was told I had to have it in order for a software i was going to use 
to work (holdem manager). I installed it and things worked fine, dont remember 
anytime doing anything with or to postgres. My problem comes now that I 
upgraded to holdem manager 2 and cant get to open it, i was told that it COULD 
be related to something to postgres and i could try uninstalling and 
reinstalling it again to see if things worked then. I tried that but as told 
encountered the password problem.
This is a Holdem Manager issue. They've done a silent installation of 
PostgreSQL, and their upgrade tool clearly doesn't do its job right.


Please contact their technical support for assistance.

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password help

[Craig Ringer]

On 07/30/2012 02:30 AM, Guillermo Echevarria Quintana-Gurt wrote:


Hi, I just uninstalled version 8.4 and tried installing version 9.1 in 
my computer (my system is Windows 7). The issue Im facing was that 
when I clicked run to install the new version one of the steps was 
requiring me the following:
"please provide a password for the database superuser (postgres) and 
service account (postgress). If the service account already exists in 
windows, you must enter the current password for the account. If the 
account doesnt exist, it will be created when you click "next""


As per the blog you linked to, open an administrator command prompt by 
right-clicking on the command prompt entry it in the start menu and 
choosing "Run as administrator", then in the command prompt run:


   net user postgres *

and enter a new password when prompted.

If you can't find the command prompt in the start menu, just type 
"command" into the Start menu search box and it will appear in the list.


Alternately, try completely uninstalling all PostgreSQL versions, then 
using the system management console to delete the "postgres" user. You 
cannot do this via the "users" control panel, it must be done via the 
usermgmt mmc snapin, which you can find called User Management within 
Administrative Tools in the control panel.


It seems the 9.2 installers will use a different setup that won't 
require a service password anymore. (Thanks Dave!).


--
Craig Ringer

[GENERAL] password help

[Guillermo Echevarria Quintana-Gurt]

Hi, I just uninstalled version 8.4 and tried installing version 9.1 in my 
computer (my system is Windows 7). The issue Im facing was that when I clicked 
run to install the new version one of the steps was requiring me the following:
"please provide a password for the database superuser (postgres) and service 
account (postgress). If the service account already exists in windows, you must 
enter the current password for the account. If the account doesnt exist, it 
will be created when you click "next""
 
I typed many possible passwords I could have but all of them tell me its 
incorrect. So I would like to know if there is a password I had related to my 
account or computer with the previous version I had? Dont even think i had an 
account in the past because i tried creating one now with this email that is my 
primary and was able to create a new account.I also read this blogpost 
http://pgsnake.blogspot.com/2010/07/postgresql-passwords-and-installers.html 
but still havent been able to figure out how to solve that password issue. I 
would really appreciate if you could help me out with this issue. Thanks a lot 
for your time.
Guillermo
 
> Date: Sat, 28 Jul 2012 18:04:50 +
> Subject: Your new postgresql.org community account
> To: guie...@hotmail.com
> From: webmas...@postgresql.org
> 
> You are receiving this e-mail because you requested a new
> PostgreSQL community account.
> 
> Please go to the following page and choose a new password:
> 
> https://www.postgresql.org/account/reset/24q-39e-6458006552e65a88da70/
> 
> Your username, in case you've forgotten, is guieche.

  

Re: [GENERAL] PASSWORD vs. md5('somepass')

[Alexander Reichstadt]

Thanks, I was here 
.

Am 20.03.2012 um 16:55 schrieb Josh Kupershmidt:

> On Tue, Mar 20, 2012 at 8:28 AM, Alexander Reichstadt  wrote:
>> Hi,
>> 
>> I look for a way to reproduce the encrypted string stored as a password by 
>> means other than using the CREATE ROLE command.
>> 
>> When using CREATE ROLEPASSWORD 'somepass' the resulting string for 
>> rolpassword in pg_authid always starts with md5, suggesting it would create 
>> some md5 string. So I thought to use SELECT md5('somepass') to get the same.
>> 
>> But the two strings differ. Is there a function that does that outside the 
>> create role context?
> 
> See pg_authid's explanation of the rolpassword column:
>  http://www.postgresql.org/docs/9.1/static/catalog-pg-authid.html
> 
> which you can reproduce via:
>  SELECT 'md5' || MD5(role_password_here || role_name_here);
> 
> Josh
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PASSWORD vs. md5('somepass')

[Josh Kupershmidt]

On Tue, Mar 20, 2012 at 8:28 AM, Alexander Reichstadt  wrote:
> Hi,
>
> I look for a way to reproduce the encrypted string stored as a password by 
> means other than using the CREATE ROLE command.
>
> When using CREATE ROLEPASSWORD 'somepass' the resulting string for 
> rolpassword in pg_authid always starts with md5, suggesting it would create 
> some md5 string. So I thought to use SELECT md5('somepass') to get the same.
>
> But the two strings differ. Is there a function that does that outside the 
> create role context?

See pg_authid's explanation of the rolpassword column:
  http://www.postgresql.org/docs/9.1/static/catalog-pg-authid.html

which you can reproduce via:
  SELECT 'md5' || MD5(role_password_here || role_name_here);

Josh

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PASSWORD vs. md5('somepass')

[Alexander Reichstadt]

Hi,

I look for a way to reproduce the encrypted string stored as a password by 
means other than using the CREATE ROLE command.

When using CREATE ROLEPASSWORD 'somepass' the resulting string for 
rolpassword in pg_authid always starts with md5, suggesting it would create 
some md5 string. So I thought to use SELECT md5('somepass') to get the same.

But the two strings differ. Is there a function that does that outside the 
create role context?

Thanks
Alex

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password issue

[Albe Laurenz]

Mahmoud wrote:
> I am trying to create a database by passing arguments to createdb.exe
> but createdb always asks me about the password although I passed  -W 123
> to it.
>
> How can I override password request?
>
> PS
> This my test for creating the database
> createdb.exe -U postgres -W 123 -O admin -e test

As has been mentioned, -W takes no arguments and prompts you for a password.

If you want a password, but don't want the prompt (e.g. because you are
writing a script), you could:

- Not use -W, then the superuser will have no password initially.
- Start the server.
- Using "trust" authentication, connect to a database.
- Issue "ALTER ROLE ... PASSWORD '...'" to set a password.

Yours,
Laurenz Albe



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password issue

[hubert depesz lubaczewski]

On Thu, May 19, 2011 at 05:53:11PM +0300, Mahmoud wrote:
> Hi all
> I am trying to create a database by passing arguments to
> createdb.exe but createdb always asks me about the password although
> I passed  -W 123 to it.

please check docs for createdb
http://www.postgresql.org/docs/current/interactive/app-createdb.html

-W is not used to provide password at command line.

for providing password check
http://www.postgresql.org/docs/current/interactive/libpq-envars.html
and/or
http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.com/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password issue

[Ashesh Vashi]

'-W' option is there to prompt the password.

Use PGPASSWORD environment variable.
http://www.postgresql.org/docs/8.3/static/libpq-envars.html

*createdb creates a PostgreSQL database.*
*
*
*Usage:*
*  createdb [OPTION]... [DBNAME] [DESCRIPTION]*
*
*
*Options:*
*  -D, --tablespace=TABLESPACE  default tablespace for the database*
*  -e, --echo   show the commands being sent to the server*
*  -E, --encoding=ENCODING  encoding for the database*
*  -l, --locale=LOCALE  locale settings for the database*
*  --lc-collate=LOCALE  LC_COLLATE setting for the database*
*  --lc-ctype=LOCALELC_CTYPE setting for the database*
*  -O, --owner=OWNERdatabase user to own the new database*
*  -T, --template=TEMPLATE  template database to copy*
*  --help   show this help, then exit*
*  --versionoutput version information, then exit*
*
*
*Connection options:*
*  -h, --host=HOSTNAME  database server host or socket directory*
*  -p, --port=PORT  database server port*
*  -U, --username=USERNAME  user name to connect as*
*  -w, --no-passwordnever prompt for password*
*  -W, --password   force password prompt*

On Thu, May 19, 2011 at 8:23 PM, Mahmoud  wrote:

> Hi all
> I am trying to create a database by passing arguments to createdb.exe but
> createdb always asks me about the password although I passed  -W 123 to it.
>
> How can I override password request?
>
> PS
> This my test for creating the database
> createdb.exe -U postgres -W 123 -O admin -e test
>
> Cheers.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
--

Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise PostgreSQL Company



*http://www.linkedin.com/in/asheshvashi*

[GENERAL] Password issue

[Mahmoud]

Hi all
I am trying to create a database by passing arguments to createdb.exe 
but createdb always asks me about the password although I passed  -W 123 
to it.


How can I override password request?

PS
This my test for creating the database
createdb.exe -U postgres -W 123 -O admin -e test

Cheers.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password

[Roedy Green]

On Mon, 21 Feb 2011 10:45:14 +0530, sachin.srivast...@enterprisedb.com
(Sachin Srivastava) wrote, quoted or indirectly quoted someone who
said :

>Hello,
>
>Installation of postgresql requires you to enter a password for the user 
>'postgres'.
>
> i) If the user 'postgres' is not there, it will create it and set the 
> password to whatever you have provided,
> ii) If the user 'postgres' is already existing, then you have to give its 
> password to move further in the installation.
>
>You can use any account other that 'postgres' by giving CLI option 
>'---serviceaccount '. See --help for more details.
>
>In case you dont remember the password you set for user 'postgres' then you 
>can change the same via "Right Click My Computer"-->"Manage"-> Users.. 
>
>You said it rejects the password.  When ? 
>
>And windows user are most welcome here.
>
>
>On Feb 21, 2011, at 7:40 AM, Roedy Green wrote:
>
>> I gave Postgre a password during install.  However, it always rejects
>> it. I tried uninstalling, deleting all files, and reinstalling. Same
>> thing.  It complains about user "roedy" (my windows id). It seems to
>> me the default user is supposed to be postgres not Roedy.  Perhaps
>> that is the source of the problem. I can't find anything relevant in
>> the docs.
>> 
>> The docs talk about installing on Unix by compiling C source.  I have
>> Windows 7 64 bit. I get the feeling Windows users are unwelcome.
>> -- 
>> Roedy Green Canadian Mind Products
>> http://mindprod.com
>> Refactor early. If you procrastinate, you will have
>> even more code to adjust based on the faulty design.
>> .
>> 
>> 
>> -- 
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general

This did not help.  However, I have got it going.  Part of my problem
came from expecting it to work identically to MySQL.

I have posted my notes at
http://mindprod.com/jgloss/postgresql.html#GOTCHAS

The key was setting up ENV parms to get it to default to postgres as
the user id.  It was defaulting to roedy, my windows id.  I later
discovered I could force it to use postgres with the -U option. At
first this did not appear to work.

-- 
Roedy Green Canadian Mind Products
http://mindprod.com
Refactor early. If you procrastinate, you will have
even more code to adjust based on the faulty design.
.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password

[Roedy Green]

On Sun, 20 Feb 2011 21:44:22 -0800, pie...@hogranch.com (John R
Pierce) wrote, quoted or indirectly quoted someone who said :

>when you initially connect to postgres wtih psql or pgadmin-III, specify 
>the user as `postgres` and then once connected,

It would not let me in even once.
-- 
Roedy Green Canadian Mind Products
http://mindprod.com
Refactor early. If you procrastinate, you will have
even more code to adjust based on the faulty design.
.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password

[John R Pierce]

On 02/20/11 9:15 PM, Sachin Srivastava wrote:
In case you dont remember the password you set for user 'postgres' 
then you can change the same via "Right Click My 
Computer"-->"Manage"-> Users.. 


note that if you change it here, you also need to change it in the 
postgres service descriptor, in Control Panel->Administration 
Tools->Services



when you initially connect to postgres wtih psql or pgadmin-III, specify 
the user as `postgres` and then once connected,


CREATE USER yourname WITH PASSWORD 'somepass' createdb createrole;

and this will create a SQL account for you with that sql password, and 
give this user permission to create databases and roles (users).




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password

[Sachin Srivastava]

Hello,

Installation of postgresql requires you to enter a password for the user 
'postgres'.

 i) If the user 'postgres' is not there, it will create it and set the password 
to whatever you have provided,
 ii) If the user 'postgres' is already existing, then you have to give its 
password to move further in the installation.

You can use any account other that 'postgres' by giving CLI option 
'---serviceaccount '. See --help for more details.

In case you dont remember the password you set for user 'postgres' then you can 
change the same via "Right Click My Computer"-->"Manage"-> Users.. 

You said it rejects the password.  When ? 

And windows user are most welcome here.


On Feb 21, 2011, at 7:40 AM, Roedy Green wrote:

> I gave Postgre a password during install.  However, it always rejects
> it. I tried uninstalling, deleting all files, and reinstalling. Same
> thing.  It complains about user "roedy" (my windows id). It seems to
> me the default user is supposed to be postgres not Roedy.  Perhaps
> that is the source of the problem. I can't find anything relevant in
> the docs.
> 
> The docs talk about installing on Unix by compiling C source.  I have
> Windows 7 64 bit. I get the feeling Windows users are unwelcome.
> -- 
> Roedy Green Canadian Mind Products
> http://mindprod.com
> Refactor early. If you procrastinate, you will have
> even more code to adjust based on the faulty design.
> .
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Regards,
Sachin Srivastava
EnterpriseDB, the Enterprise PostgreSQL company.

[GENERAL] password

[Roedy Green]

I gave Postgre a password during install.  However, it always rejects
it. I tried uninstalling, deleting all files, and reinstalling. Same
thing.  It complains about user "roedy" (my windows id). It seems to
me the default user is supposed to be postgres not Roedy.  Perhaps
that is the source of the problem. I can't find anything relevant in
the docs.

The docs talk about installing on Unix by compiling C source.  I have
Windows 7 64 bit. I get the feeling Windows users are unwelcome.
-- 
Roedy Green Canadian Mind Products
http://mindprod.com
Refactor early. If you procrastinate, you will have
even more code to adjust based on the faulty design.
.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password management

[Christophe Dore]

Hi

 

IMHO, you should never store password in clear

 

If you store the last 5 crypted passwords, then you can make it  comparing the 
new password, crypted, to those 5 strings.

 

Regards

 

-- 

Christophe Doré 
Implementation Product Manager 

3 rue Marcel Allegot 
92190 Meudon, France 
+33 1 46 90 21 00 office 
+33 6 1379 2910 mobile 
CAST, Leader in Automated Application Intelligence 
Achieve Insight. Deliver Excellence. 

www.castsoftware.com   | Gain visibility into 
application quality to proactively manage risk and improve team performance.

From: akp geek [mailto:akpg...@gmail.com] 
Sent: jeudi 6 mai 2010 20:31
To: pgsql-general
Subject: password management

 

Dear all -

 

   I am writing function to handle the passwords. Currently the 
crypt is being used to store the password in the database. what I need to do 
is, when the user wants to change the password, I need to check if that 
password is not being used before up to 5 times, If not then then records 
should be inserted to the database.

 

  The problem where i am running into, when I capture the password 
that user entered, I can't compare to the one in database , because each time 
the function crypt gives different one. Is there any way that I can achieve 
this?

 

  Appreciate your help

 

Regards

Re: [GENERAL] password management

[Craig Ringer]

On 7/05/2010 12:01 PM, Craig Ringer wrote:


craig=> create or replace function extract_salt(text) returns text as $$
craig$> select (regexp_matches($1, E'^(\\$[^\\$]+\\$[^\\$]+)\\$'))[1];
craig$> $$ language sql immutable;


Upon re-reading the pgcrypto documentation I see that this is unnecessary.

Just pass the password hash as the salt. Pgcrypto will extract the salt 
part of the hash its self. (otherwise, how could you check passwords?)


So - just as if you were testing authentication, crypt the user's new 
password plaintext against each of the old password hashes using the old 
password hash as salt, and see if the output hash is the same as the old 
password hash. If it is, they've re-used the password.


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password management

[Craig Ringer]

On 7/05/2010 2:31 AM, akp geek wrote:

Dear all -

I am writing function to handle the passwords. Currently
the crypt is being used to store the password in the database. what I
need to do is, when the user wants to change the password, I need to
check if that password is not being used before up to 5 times, If not
then then records should be inserted to the database.

   The problem where i am running into, when I capture the
password that user entered, I can't compare to the one in database ,
because each time the function crypt gives different one. Is there any
way that I can achieve this?


Extract the salt from each stored password and re-encrypt the new 
password with the same salt when comparing it to the old one.


eg:


craig=> create table password_history ( password text not null );
CREATE TABLE
craig=> insert into password_history(password) values ( crypt('fred', 
gen_salt('md5')) );

INSERT 0 1
craig=> insert into password_history(password) values ( crypt('bob', 
gen_salt('md5')) );

INSERT 0 1
craig=> insert into password_history(password) values ( 
crypt('smeghead', gen_salt('md5')) );

INSERT 0 1
craig=> create or replace function extract_salt(text) returns text as $$
craig$> select (regexp_matches($1, E'^(\\$[^\\$]+\\$[^\\$]+)\\$'))[1];
craig$> $$ language sql immutable;
CREATE FUNCTION
craig=> select extract_salt(password), password from password_history;
 extract_salt |  password
--+
 $1$p3AMpr5s  | $1$p3AMpr5s$BtNTSXwIJbHrdnJEZ4NFg.
 $1$FKySMIXg  | $1$FKySMIXg$xFM5osjqclTuaJIUiGvU3.
 $1$MUwd2dGt  | $1$MUwd2dGt$w06IEIvJ1lROXw7WGb3dw.
(3 rows)

craig=> select exists (select 1 from password_history where 
crypt('fred', extract_salt(password)) = password);

 ?column?
--
 t
(1 row)

craig=> select exists (select 1 from password_history where crypt('bob', 
extract_salt(password)) = password);

 ?column?
--
 t
(1 row)

craig=> select exists (select 1 from password_history where 
crypt('nosuch', extract_salt(password)) = password);

 ?column?
--
 f
(1 row)



Make sure to generate a new salt value if you accept the password and 
want to store it, though.



( Perhaps pgcrypto needs a function to extract the salt? )


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] password management

[akp geek]

Dear all -

   I am writing function to handle the passwords. Currently the
crypt is being used to store the password in the database. what I need to do
is, when the user wants to change the password, I need to check if that
password is not being used before up to 5 times, If not then then records
should be inserted to the database.

  The problem where i am running into, when I capture the
password that user entered, I can't compare to the one in database , because
each time the function crypt gives different one. Is there any way that I
can achieve this?

  Appreciate your help

Regards

Re: [GENERAL] [Password?]

[Ms swati chande]

Hi,
 
Thank you all for your kind responses.
 
Things however aren't falling in place. 
 
Will take a short break, rework, and get back.
Probably, with a new problem!
 
Thanks again,
Regards
Swati


  

Re: [GENERAL] Password?

[Adrian Klaver]

On Wednesday 08 July 2009 11:48:08 am Ms swati chande wrote:
> Hi,
>  
> I started everything again from scratch.
> 1. Created a new user("Swati"), with limited/ restricted rights.
>     Ensured that no password is set anywhere.

How limited? Can this user do administrative tasks, i.e create database,create 
user?

>  
> 2. Ran initdb from the new user.
>     c:\postgresql\bin>initdb -D c:\postgresql\data2
>    It displayed the DEBUG: start transaction and commit transaction
> states etc. and ended with DEBUG: exit(0)
>    A warning with the following statement was also displayed:
>      WARNING: enabling "trust" authentication foe local connections
>      you can change this by editing "pg_hba.conf" or by initdb -A.
>  
> 3. After this I executed pg_ctl:
>      c:\postgresql\bin>pg_ctl -D c:\postgresql\data2 -l logfile
> start got the the message:server starting
>  and the logfile contained the following:
>  LOG:  could not bind IPv4 socket: No error
>  HINT:  Is another postmaster already running on port 5432?
> If not, wait a few seconds and retry.
>      WARNING:  could not create listen socket for "localhost"
>      FATAL:  could not create any TCP/IP sockets
>      LOG:  could not bind IPv4 socket: No error
>  HINT:  Is another postmaster already running on port 5432?
> If not, wait a few seconds and retry.
>  WARNING:  could not create listen socket for "localhost"
>      FATAL:  could not create any TCP/IP sockets
>  
> 4. To take care of the above issues,
>      Made the following change in the postgresql.conf file:
>  listen_addresses = 'xxx.xxx.x.x' (my current ip)
>      and in pg_hba:
>      host all all 'xxx.xxx.x.x' trust

Can you show the complete pg_hba.conf file? Or to put it another way is the 
above line the only uncommented line in the file?

>  
> 5.Then issued
>  c:\postgresql\bin>pg_ctl -D c:\postgresql\data2 -l logfile start
>    again.
>    Now got the following in logfile:
>      LOG: database system was shut down at 2009-07-08 22:34:50
>      LOG: database system is ready to accept connections
>      LOG:  autovacuum launcher started
>  
> 6.Opened another command window.
>    Now when I write in the new window (or even in the same),
>      c:\postgresql\bin>createdb demo
>    OR
>      c:\postgresql\bin>createuser -S -d -R svc
>    I am prompted for password, I don't know what to enter here.

Are you running this as 'Swati' user?

>  
> I think I am making some mistake in pg_hba.conf. Can't make out.
> Must be some brainless blunder some where.
>  
> Thanks a ton for sparing your time and bearing with me.
>  
> Please guide.
>  
> Regards
> Swati



-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password?

[John R Pierce]

Ms swati chande wrote:



 Made the following change in the postgresql.conf file:
 listen_addresses = 'xxx.xxx.x.x' (my current ip)
 and in pg_hba:
 host all all 'xxx.xxx.x.x' trust
 
...

6.Opened another command window.
   Now when I write in the new window (or even in the same),
 c:\postgresql\bin>createdb demo
   OR
 c:\postgresql\bin>createuser -S -d -R svc
   I am prompted for password, I don't know what to enter here.
 



try ...
   createuser -h xxx.xxx.x.x -S -d -R svc

by default, its connecting to localhost (127.0.0.1) rather than your IP.

(note this behavior is different than on Unix/Linux type systems, where 
by default it connects to a 'unix domain socket', which doesn't exist on 
MS Windows).





If you only want to connect to this database from the same computer, I'd 
suggest using 127.0.0.1/localhost rather than xxx.xxx.x.x in both the 
listen_address and pg_hba...




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password?

[Andreas Wenk]

Ms swati chande schrieb:

Hi,
 
I started everything again from scratch.

1. Created a new user("Swati"), with limited/ restricted rights.
Ensured that no password is set anywhere.
 
2. Ran initdb from the new user.

c:\postgresql\bin>initdb -D c:\postgresql\data2
   It displayed the DEBUG: start transaction and commit
transaction states etc.
   and ended with DEBUG: exit(0)
   A warning with the following statement was also displayed:
 WARNING: enabling "trust" authentication foe local
connections
 you can change this by editing "pg_hba.conf" or by
initdb -A.
 
3. After this I executed pg_ctl:

 c:\postgresql\bin>pg_ctl -D c:\postgresql\data2 -l
logfile start
 got the the message:server starting
 and the logfile contained the following:
 LOG:  could not bind IPv4 socket: No error
 HINT:  Is another postmaster already running on
port 5432? If not,
 wait a few seconds and retry.
 WARNING:  could not create listen socket for
"localhost"
 FATAL:  could not create any TCP/IP sockets
 LOG:  could not bind IPv4 socket: No error
 HINT:  Is another postmaster already running on
port 5432? If not, wait
 a few seconds and retry.
 WARNING:  could not create listen socket for
"localhost"
 FATAL:  could not create any TCP/IP sockets
 
4. To take care of the above issues,

 Made the following change in the postgresql.conf file:
 listen_addresses = 'xxx.xxx.x.x' (my current ip)
 and in pg_hba:
 host all all 'xxx.xxx.x.x' trust
 
5.Then issued

 c:\postgresql\bin>pg_ctl -D c:\postgresql\data2 -l logfile
start
   again.
   Now got the following in logfile:
 LOG: database system was shut down at 2009-07-08
22:34:50
 LOG: database system is ready to accept connections
 LOG:  autovacuum launcher started
 
6.Opened another command window.

   Now when I write in the new window (or even in the same),
 c:\postgresql\bin>createdb demo
   OR
 c:\postgresql\bin>createuser -S -d -R svc
   I am prompted for password, I don't know what to enter here.
 
I think I am making some mistake in pg_hba.conf. Can't make out.

Must be some brainless blunder some where.
 
Thanks a ton for sparing your time and bearing with me.
 
Please guide.
 
Regards

Swati


Swati, sorry to say - but I got no solution as I cannot try to simulate 
this. I do not have a Windows machine ... hopefully someone else can help.


One thing anyway ...  Step 4. seems to be correct. Actually, is there a 
user postgres on your system? Why not give postgres then a password (in 
the windows user administration) and use


c:\postgresql\bin>createuser -U postgres -S -d -R svc

But this is really vague ...

Cheers

Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password?

[Ms swati chande]

Hi,
 
I started everything again from scratch.
1. Created a new user("Swati"), with limited/ restricted rights.
    Ensured that no password is set anywhere.
 
2. Ran initdb from the new user.
    c:\postgresql\bin>initdb -D c:\postgresql\data2
   It displayed the DEBUG: start transaction and commit transaction states 
etc.
   and ended with DEBUG: exit(0)
   A warning with the following statement was also displayed:
     WARNING: enabling "trust" authentication foe local connections
     you can change this by editing "pg_hba.conf" or by initdb -A.
 
3. After this I executed pg_ctl:
     c:\postgresql\bin>pg_ctl -D c:\postgresql\data2 -l logfile start
 got the the message:server starting
 and the logfile contained the following:
 LOG:  could not bind IPv4 socket: No error
 HINT:  Is another postmaster already running on port 5432? If 
not,
 wait a few seconds and retry.
     WARNING:  could not create listen socket for "localhost"
     FATAL:  could not create any TCP/IP sockets
     LOG:  could not bind IPv4 socket: No error
 HINT:  Is another postmaster already running on port 5432? If 
not, wait
     a few seconds and retry.
 WARNING:  could not create listen socket for "localhost"
     FATAL:  could not create any TCP/IP sockets
 
4. To take care of the above issues,
     Made the following change in the postgresql.conf file:
 listen_addresses = 'xxx.xxx.x.x' (my current ip)
     and in pg_hba:
     host all all 'xxx.xxx.x.x' trust
 
5.Then issued
 c:\postgresql\bin>pg_ctl -D c:\postgresql\data2 -l logfile start
   again.
   Now got the following in logfile:
     LOG: database system was shut down at 2009-07-08 22:34:50
     LOG: database system is ready to accept connections
     LOG:  autovacuum launcher started
 
6.Opened another command window.
   Now when I write in the new window (or even in the same),
     c:\postgresql\bin>createdb demo
   OR
     c:\postgresql\bin>createuser -S -d -R svc
   I am prompted for password, I don't know what to enter here. 
 
I think I am making some mistake in pg_hba.conf. Can't make out.
Must be some brainless blunder some where.
 
Thanks a ton for sparing your time and bearing with me.
 
Please guide.
 
Regards
Swati



  

Re: [Re: [GENERAL] Password?]

[Ms swati chande]

Yes,
Its the currently logged on user.


--- On Wed, 7/8/09, Andreas Wenk  wrote:


From: Andreas Wenk 
Subject: [Re: [GENERAL] Password?]
To: "PG-General Mailing List" 
Date: Wednesday, July 8, 2009, 3:54 PM


Serge Fonville schrieb:
>> *argh* - more detailed to avoid confusion. The auth method 'password' in
>> pg_hba.conf means, that you will be asked for a password for the user you
>> try to create a db with. If no user is given (with createdb -U [username]),
>> this user is postgres ...
> 
> Wasn't it that it uses the currently logged on user is used if no user
> is specified?

correct - so this will be postgres because other users are not allowed to use 
these
programs ...

/var/lib/postgresql/8.4/bin$ ./createdb test -p 5433
createdb: could not connect to database postgres: FATAL:  role "duke" does not 
exist

$ sudo su postgres
postg...@duke-linux:~/8.4/bin$ ./createdb test -p 5433
postg...@duke-linux:~/8.4/bin$

auth method in pg_hba.conf is trust in this case.

-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



  

Re: [GENERAL] Password?

[Abbas]

On Wed, Jul 8, 2009 at 4:12 PM, Andreas Wenk  wrote:

> Ms swati chande schrieb:
>
>> --- On *Wed, 7/8/09, Andreas Wenk //*
>> wrote:
>>
>>
>>From: Andreas Wenk 
>>Subject: Re: [GENERAL] Password?
>>To: "Ms swati chande" , "PG-General Mailing List"
>>
>>Date: Wednesday, July 8, 2009, 3:47 PM
>>
>>Ms swati chande schrieb:
>> > Thanks Andy,
>> >  I am working on Windows XP. Have built from source using Visual
>>Studio 2005.
>> >  I have made a change in pg_hba.conf to include the ipconfig of
>>my system.
>> > # TYPE  DATABASEUSERCIDR-ADDRESS  METHOD
>> >  *hostall all   trust*
>> >  # IPv4 local connections:
>> > hostall all 127.0.0.1/32  trust
>> > # IPv6 local connections:
>> > #hostall all ::1/128   trust
>>
>
Yes, the * sign should removed and have to mention listen_addresses = ' * '
in Postgresql.conf file.


>
>> >
>> >  This was to take care of the following problem:
>> >  LOG: could not bind IPv4 socket: Address already in use
>> > HINT: Is another postmaster already running on port 5432? If not,
>>wait a few seconds and retry.
>> > WARNING: could not create listen socket for "*"
>> > FATAL: could not create any TCP/IP sockets
>> >  For this I changed the listen_addresses to my current ip. and
>>made the same change in pg_hba.conf.
>> >  Thanks
>> >  Regards
>> > Swati
>> >
>>So does it work now ? Why is there a * sign before host? This seems
>>to be incorrect ...
>>
>>P.S.: dont' forget to reply also to the mailinglist (reply to all)
>>
>>
>>  >
> > No its still not working.
> > The * doesn't exist in pg_hba. It was probably in the mail as I had
> > formatted that line to be 'bold'.
>
> ah ok ..
>
> Actually it should work if you set listen_addresses to '*' in
> postgresql.conf. Did you change anything else in postgresql.conf or
> pg_hba.conf?
>
> I am not too experienced with Windows so maybe someone with more knowledge
> is able to find the trick (I installed 8.4 once with the one click installer
> ...no problems at all). But as far as I understand something is wrong with:
>
> > WARNING: could not create listen socket for "*"
> > FATAL: could not create any TCP/IP sockets
>
> I understand correct, that you fixed this? Then it should work as I
> mentioned earlier ...
>
> Cheers Andy
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Password?

[Ms swati chande]

>ah ok ..

>Actually it should work if you set listen_addresses to '*' in postgresql.conf. 
>Did you >change anything else in postgresql.conf or pg_hba.conf?

>I am not too experienced with Windows so maybe someone with more knowledge is 
>able >to find the trick (I installed 8.4 once with the one click installer 
>...no problems at all). But >as far as I understand something is wrong with:

>> WARNING: could not create listen socket for "*"
>>FATAL: could not create any TCP/IP sockets

>I understand correct, that you fixed this? Then it should work as I mentioned 
>earlier ...

Cheers Andy

 
 
Ok. Will check the '*' part of it and then get back.
Infact it was to move ahead with it that I changes the listen addresses in 
postgresql.conf.
But will take another look into it.
Thanks,
 
Regards
Swati




  

Re: [GENERAL] Password?

[Andreas Wenk]

Ms swati chande schrieb:

Thanks Andy,
 
I am working on Windows XP. Have built from source using Visual Studio 2005.
 
I have made a change in pg_hba.conf to include the ipconfig of my system. 


# TYPE  DATABASEUSERCIDR-ADDRESS  METHOD
 
*hostall all   trust*
 
# IPv4 local connections:

hostall all 127.0.0.1/32  trust
# IPv6 local connections:
#hostall all ::1/128   trust

 
This was to take care of the following problem:
 
LOG: could not bind IPv4 socket: Address already in use
HINT: Is another postmaster already running on port 5432? If not, wait a 
few seconds and retry.

WARNING: could not create listen socket for "*"
FATAL: could not create any TCP/IP sockets
 
For this I changed the listen_addresses to my current ip. and made the 
same change in pg_hba.conf.
 
Thanks
 
Regards

Swati
 


So does it work now ? Why is there a * sign before host? This seems to be 
incorrect ...

P.S.: dont' forget to reply also to the mailinglist (reply to all)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password?

[Andreas Wenk]

Ms swati chande schrieb:

--- On *Wed, 7/8/09, Andreas Wenk //* wrote:


From: Andreas Wenk 
Subject: Re: [GENERAL] Password?
To: "Ms swati chande" , "PG-General Mailing List"

Date: Wednesday, July 8, 2009, 3:47 PM

Ms swati chande schrieb:
 > Thanks Andy,
 >  I am working on Windows XP. Have built from source using Visual
Studio 2005.
 >  I have made a change in pg_hba.conf to include the ipconfig of
my system.
 > # TYPE  DATABASEUSERCIDR-ADDRESS  METHOD
 >  *hostall all   trust*
 >  # IPv4 local connections:
 > hostall all 127.0.0.1/32  trust
 > # IPv6 local connections:
 > #hostall all ::1/128   trust
 >
 >  This was to take care of the following problem:
 >  LOG: could not bind IPv4 socket: Address already in use
 > HINT: Is another postmaster already running on port 5432? If not,
wait a few seconds and retry.
 > WARNING: could not create listen socket for "*"
 > FATAL: could not create any TCP/IP sockets
 >  For this I changed the listen_addresses to my current ip. and
made the same change in pg_hba.conf.
 >  Thanks
 >  Regards
 > Swati
 > 


So does it work now ? Why is there a * sign before host? This seems
to be incorrect ...

P.S.: dont' forget to reply also to the mailinglist (reply to all)



>
> No its still not working.
> The * doesn't exist in pg_hba. It was probably in the mail as I had
> formatted that line to be 'bold'.

ah ok ..

Actually it should work if you set listen_addresses to '*' in postgresql.conf. Did you 
change anything else in postgresql.conf or pg_hba.conf?


I am not too experienced with Windows so maybe someone with more knowledge is able to find 
the trick (I installed 8.4 once with the one click installer ...no problems at all). But 
as far as I understand something is wrong with:


> WARNING: could not create listen socket for "*"
> FATAL: could not create any TCP/IP sockets

I understand correct, that you fixed this? Then it should work as I mentioned 
earlier ...

Cheers Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password?

[Abbas]

On Wed, Jul 8, 2009 at 3:22 PM, Andreas Wenk  wrote:

> Andreas Wenk schrieb:
>
>> Ms swati chande schrieb:
>>
>>> Hi
>>>  When I write the following commands at the prompt,
>>>
>>>  >createuser -S -d -R user1
>>>  > createdb sample
>>>
>>> I am asked to enter a password. I have not set any password anywhere.
>>> Which password is it asking for?
>>> Please help.
>>> I have built from source on Windows XP.
>>>
>>> Thanks is advance,
>>>
>>> Regards
>>> Swati
>>>
>> Hi Swati,
>>
>> what are the setting of your pg_hba.conf? I assume that there is a entry
>> like this:
>>
>> # TYPE  DATABASEUSERCIDR-ADDRESS  METHOD
>>
>> # "local" is for Unix domain socket connections only
>> local   all all   password
>>
>>
>> That means that the password you are asked is the password of the standard
>> user for your cluster - commonly postgres.
>>
>> Cheers
>>
>> Andy
>>
>
> *argh* - more detailed to avoid confusion. The auth method 'password' in
> pg_hba.conf means, that you will be asked for a password for the user you
> try to create a db with. If no user is given (with createdb -U [username]),
> this user is postgres ...
>
> see also createdb --help for options ...
>
>
> Cheers
>
> Andy
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

  If you don't need the password authentication you have to edit the
pg_conf file and replace "password" with "trust", after this reload the
cluster. It won't prompt you for password.

Thanks,
Abbas.

[Re: [GENERAL] Password?]

[Andreas Wenk]

Serge Fonville schrieb:

*argh* - more detailed to avoid confusion. The auth method 'password' in
pg_hba.conf means, that you will be asked for a password for the user you
try to create a db with. If no user is given (with createdb -U [username]),
this user is postgres ...


Wasn't it that it uses the currently logged on user is used if no user
is specified?


correct - so this will be postgres because other users are not allowed to use 
these
programs ...

/var/lib/postgresql/8.4/bin$ ./createdb test -p 5433
createdb: could not connect to database postgres: FATAL:  role "duke" does not 
exist

$ sudo su postgres
postg...@duke-linux:~/8.4/bin$ ./createdb test -p 5433
postg...@duke-linux:~/8.4/bin$

auth method in pg_hba.conf is trust in this case.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password?

[Andreas Wenk]

Andreas Wenk schrieb:

Ms swati chande schrieb:

Hi
 
When I write the following commands at the prompt,


 >createuser -S -d -R user1
 > createdb sample

I am asked to enter a password. I have not set any password anywhere. 
Which password is it asking for?

Please help.
I have built from source on Windows XP.

Thanks is advance,

Regards
Swati

Hi Swati,

what are the setting of your pg_hba.conf? I assume that there is a entry 
like this:


# TYPE  DATABASEUSERCIDR-ADDRESS  METHOD

# "local" is for Unix domain socket connections only
local   all all   password


That means that the password you are asked is the password of the 
standard user for your cluster - commonly postgres.


Cheers

Andy


*argh* - more detailed to avoid confusion. The auth method 'password' in pg_hba.conf 
means, that you will be asked for a password for the user you try to create a db with. If 
no user is given (with createdb -U [username]), this user is postgres ...


see also createdb --help for options ...

Cheers

Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password?

[Andreas Wenk]

Ms swati chande schrieb:

Hi
 
When I write the following commands at the prompt,


 >createuser -S -d -R user1
 > createdb sample

I am asked to enter a password. I have not set any password anywhere. 
Which password is it asking for?

Please help.
I have built from source on Windows XP.

Thanks is advance,

Regards
Swati

Hi Swati,

what are the setting of your pg_hba.conf? I assume that there is a entry like 
this:

# TYPE  DATABASEUSERCIDR-ADDRESS  METHOD

# "local" is for Unix domain socket connections only
local   all all   password


That means that the password you are asked is the password of the standard user for your 
cluster - commonly postgres.


Cheers

Andy


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Password?

[Ms swati chande]

Hi
 
When I write the following commands at the prompt,

>createuser -S -d -R user1

> createdb sample

I am asked to enter a password. I have not set any password anywhere. Which 
password is it asking for? 
Please help.

I have built from source on Windows XP.

Thanks is advance,

Regards
Swati



  

Re: [GENERAL] password for postgres

[Jasen Betts]

On 2009-02-13, Kusuma Pabba  wrote:
> i don't  know y am i getting this problem
>
> when i try to start off postgres
> it asks me for password:

what OS.

what command are you using?


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password for postgres

[Sim Zacks]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

You do not want to use the keyword encrypted.
To get in, go to your pg_hba file and set the security level to trust
for your account. Then go in as postgres without a password and change
it by:
alter role postgres with password 'welcome';


> ALTER USER postgres with encrypted password 'your_password';
> ALTER USER postgres with encrypted password 'welcome';
> 
> but it is not accepting both the passwords
> i am getting incorrect password after three trials it is returning back
> to command prompt

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkmXsSQACgkQjDX6szCBa+poGACgxKo0aHk+7+XzsdlOLVnPD0zY
aJ8An0ArIlUvz19M3um4HS7wS1BW6ZC3
=sbx/
-END PGP SIGNATURE-

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password for postgres

[Adrian Klaver]

On Friday 13 February 2009 2:18:32 am Kusuma Pabba wrote:
> i don't  know y am i getting this problem
>
> when i try to start off postgres
> it asks me for password:

Are trying to start the Postgres program or are you trying to connect to an 
already running server?

>
>
> i did not set any password as such
>
> except that when the first day i used template, i ahve used the below
> two statements
> ALTER USER postgres with encrypted password 'your_password';
> ALTER USER postgres with encrypted password 'welcome';

If you did it that order then your password for connecting should be 'welcome'.
User/role information is cluster wide. If you entered the above to access the 
template then it is in effect for all databases in the cluster.

Are you connecting as the user postgres or another user? 

>
> but it is not accepting both the passwords
> i am getting incorrect password after three trials it is returning back
> to command prompt

What is the error message that you are getting?
Have you set up the pg_hba.conf file correctly?
See http://www.postgresql.org/docs/8.3/interactive/client-authentication.html 
for more information.


>
> when i have used
> select * from pg_shadow;
> then i got
>
> md5d31faa0b92fad4e2d8e4af34a30f890b

I am assuming this is for the user postgres.

>
> though i use this i am not able to acess i don't  know what to do with
> this issue
> can any one shed light on me by explaining me what was the mistake i did
> or which password to use
> thanks for any help
>
>
> Regards
> kusuma.p



-- 
Adrian Klaver
akla...@comcast.net

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password for postgres

[Abdul Rahman]

Kindly let me know two things to answer you. First, Are you trying to start 
postgres service or psql prompt? second, what OS is in your use.



  

[GENERAL] password for postgres

[Kusuma Pabba]

i don't  know y am i getting this problem

when i try to start off postgres
it asks me for password:


i did not set any password as such

except that when the first day i used template, i ahve used the below 
two statements

ALTER USER postgres with encrypted password 'your_password';
ALTER USER postgres with encrypted password 'welcome';

but it is not accepting both the passwords
i am getting incorrect password after three trials it is returning back 
to command prompt


when i have used
select * from pg_shadow;
then i got

md5d31faa0b92fad4e2d8e4af34a30f890b

though i use this i am not able to acess i don't  know what to do with 
this issue
can any one shed light on me by explaining me what was the mistake i did 
or which password to use

thanks for any help


Regards
kusuma.p


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password and Installation

[Dave Page]

Hi Andrew,

On Mon, Nov 24, 2008 at 12:10 AM, Andrew Maeng <[EMAIL PROTECTED]> wrote:
> Thanks Dave. I can't seem to find the SQL user in the user accounts though.
> All i can see is the asp.net machine account.

Look for a user called 'postgres', not SQL.

> I'm guessing that this means that PostgreSQL is uninstalled, but I'm still
> unable to install PostgreSQL because I'm putting in the "wrong password".

The uninstaller doesn't remove the postgres user account because it
doesn't have any way of knowing if you're using it for other tools or
different versions of PostgreSQL. If the installer is reporting that
the password is incorrect, that's because there's an existing account
and Windows is telling us the password is wrong. If you can't find the
account for whatever reason, another way of removing it is to use the
command line tools. From a command prompt with administrator
privileges, try:

net user postgres /delete

It *should* be shown in the computer management applet though - but
the user accounts tool in Control Panel will hide service accounts (I
assume that applies to Vista as well as XP).

Regards, Dave.


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password and Installation

[Dave Page]

On Fri, Nov 21, 2008 at 10:30 PM, Andrew Maeng <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I recently uninstalled PostgreSQL, and now am attempting to reinstall it on
> a Windows Vista OS. However, I don't remember the password that was used to
> install PostgreSQL before, and am prompted with "The password specified was
> incorrect. Please enter the correct password for the postgres windows user
> account."
>
> I'm guessing that PostgreSQL wasn't fully uninstalled previously, and there
> are still some registry files or data files somewhere? Can my old password
> somehow be retrieved?

No - it's a Windows user account, so the password cannot be retrieved
any more than your Administrator password can.

I don't know what the equivalent on Vista is, but on XP, open the
Computer Management tool under Administrative Tools, and you can reset
the password under the users section.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Password and Installation

[Andrew Maeng]

Hi,

I recently uninstalled PostgreSQL, and now am attempting to reinstall it on a 
Windows Vista OS. However, I don't remember the password that was used to 
install PostgreSQL before, and am prompted with "The password specified was 
incorrect. Please enter the correct password for the postgres windows user 
account."

I'm guessing that PostgreSQL wasn't fully uninstalled previously, and there are 
still some registry files or data files somewhere? Can my old password somehow 
be retrieved?

Thanks,

- Andrew

_

Re: [GENERAL] Password safe web application with postgre

[Steve Manes]

Bohdan Linda wrote:

On Thu, May 15, 2008 at 05:40:49PM +0200, Steve Manes wrote:
I keep the user's login credentials in a TripleDES-encrypted, 
non-persistent cookie, separate from session data.


This is the approach I am/will be heading to. Having the cookie with login
and password encrypted on user side, HTTPS connection, and what was said
in previous emails about not storing credentials in cookies any ideas of
weak sides?  Moreover if parts of decryption keys will be unique to the
sessions and stored in session on a server?


No security is 100% and neither is my solution.  Given enough time, 
interest and computer time it could be hacked.


But we used similar tamper-proof credentials security on three large, 
hacker-infested community web sites which together logged up to .75 
billion page views/month.  Everything else under the sun got hacked but 
this encrypted cookie never was (we had watchdogs sniffing for mangled 
cred cookies).  It was just too much work.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password safe web application with postgre

[Bohdan Linda]

Hello,

thank you everyone for the answers. I went through and I forgot add one
thing. The web-app is frontend, thus basically PL/PGSQL launcher and all
changes are audited, so common login is unwelcome.  

On Thu, May 15, 2008 at 05:40:49PM +0200, Steve Manes wrote:
> I keep the user's login credentials in a TripleDES-encrypted, 
> non-persistent cookie, separate from session data.
> 

This is the approach I am/will be heading to. Having the cookie with login
and password encrypted on user side, HTTPS connection, and what was said
in previous emails about not storing credentials in cookies any ideas of
weak sides?  Moreover if parts of decryption keys will be unique to the
sessions and stored in session on a server?

PS. Appologies for going slightly OT as this is becoming more general than
pgsql.

Thank you,
Bohdan 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password safe web application with postgre

[Craig Ringer]

Steve Crawford wrote:

You can make some modest security improvements by storing things such as 
the browser identification and IP address in the session data and 
verifying it on each request but IP verification fails if the user is 
behind a proxy like AOL's where each request may come from a different IP.


It'll also break with IPv6 Privacy Extensions (RFC3041), especially with 
fairly short connection keepalive intervals.


With Windows Vista supporting IPv6 and enabling it by default that's a 
significant concern. Its resolver doesn't appear to prefer IPv6 despite 
early documentation indicating that it would (eg: http://kame.org will 
prefer IPv4 to IPv6 on Vista) so it's not an urgent issue, but it bears 
thinking about.


It's great that PostgreSQL supports IPv6 so well, by the way. It 
provides me with transparent access to databases on my testing 
workstation from many of the networks I use day to day.


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password safe web application with postgre

[Steve Manes]

Bohdan Linda wrote:

The frontend is web based so it is stateless; it is connecting to database
on every get/post. There is also a requirement that the user is
transparently logged in for some period of time.

Tha most easy way is to store login credentials into the session. The
drawback is that session is stored in file, so the credentials are
readable. I want to avoid it. 


I keep the user's login credentials in a TripleDES-encrypted, 
non-persistent cookie, separate from session data.


I believe you said you were using PHP.  Here are the encrypt/decrypt 
functions I use:


function encrypt_mcrypt($str, $key = null)
{
$key = ($key === null) ? DEFAULT_MCRYPT_KEY : $key;

// Note: requires libmcrypt 2.4 or greater

$td = mcrypt_module_open(MCRYPT_TripleDES, "", MCRYPT_MODE_CFB, 
"");


$iv = mcrypt_create_iv(mcrypt_enc_get_iv_size($td), MCRYPT_RAND);

mcrypt_generic_init($td, $key, $iv);

$encrypted = mcrypt_generic($td, $str);

mcrypt_generic_deinit($td);

$encrypted  = rawurlencode($encrypted);
$iv = rawurlencode($iv);

return join(",", array (md5($str), $iv, $encrypted));
}


function decrypt_mcrypt($enc_str, $key = null)
{
$key = ($key === null) ? DEFAULT_MCRYPT_KEY : $key;

list ($hash_value, $iv, $encrypted) = explode(",", $enc_str);

$encrypted  = rawurldecode($encrypted);
$iv = rawurldecode($iv);

// Note: requires libmcrypt 2.4 or greater

$td = mcrypt_module_open(MCRYPT_TripleDES, "", MCRYPT_MODE_CFB, 
"");


mcrypt_generic_init($td, $key, $iv);

$plaintext = mdecrypt_generic($td, $encrypted);

mcrypt_generic_deinit($td);

// Compare hash values.  If not equal, return a null.

if (md5($plaintext) != $hash_value)  {
return null;
}

return $plaintext;
}
}

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password safe web application with postgre*s*

[Steve Crawford]

[EMAIL PROTECTED] wrote:

...

By the way, this is an *intra*net-solution, and we don't have hackers 
in our staff, I hope...

Cross your fingers - most compromises come from inside the firewall.

Cheers,
Steve


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password safe web application with postgre

[Steve Crawford]

Bohdan Linda wrote:

Hello,

I have the following problem. A multiuser app has authentization and
authorization done based on pgsql.

The frontend is web based so it is stateless; it is connecting to database
on every get/post. There is also a requirement that the user is
transparently logged in for some period of time.

Tha most easy way is to store login credentials into the session. The
drawback is that session is stored in file, so the credentials are
readable. I want to avoid it.


Don't store login info in the session - just the user's ID and whatever 
other session data is appropriate for your application. The basic idea is:


1. User makes a request.

2. Server looks for the session cookie (require cookies - storing 
session info in the URI means that links to off-site locations will leak 
the session ID via the referrer information).


2a. If it exists, grab the user's ID from the session data and use it 
for authorization.


2b. If it doesn't exist or if it exists but the session is no longer 
valid, route user to login page. The user enters their username and 
password. The server authenticates the information and establishes a 
session tied to a cookie. The value of the cookie must be non-guessable 
or your app is vulnerable - Google around and you'll find some papers 
about some major websites that have stupidly stored login data in the 
cookie. Base the cookie vaue on a good random number generator. The MD5 
of a long random number is often used - I'm not a crypto guy so I can't 
pass judgment on how "random" that is.


From here on, the browser/server is just passing that random token back 
and forth. It contains no username or password info. Since it is the 
temporary pass to the system, it still needs to be protected. That's why 
cookie-based sessions are preferred to URI based ones and HTTPS is 
preferred to HTTP. And avoid the mistake of having a login that sits on 
an HTTP page but posts to an HTTPS page. It's vulnerable. One of my 
banks still does this so I always just click "login" which fails but 
takes me to the HTTPS login page where I do my actual login.


The session info on the server end only needs the ID of the user 
associated with the session for authorization purposes. The user's name 
and password need not be stored in the session - just enough info to be 
able to determine access rights.


You can make some modest security improvements by storing things such as 
the browser identification and IP address in the session data and 
verifying it on each request but IP verification fails if the user is 
behind a proxy like AOL's where each request may come from a different IP.


Cheers,
Steve


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Password safe web application with postgre*s*

[ludwig]

In our web-based-solution (PHP)  the database credentials (username and password) are encrypted and stored by PHP as session-Variables.Yes, there is the risk, they could be read by someone, who has access to the apache-sessions-directory, but this user also must have access to the php-scripts with the encrypt-functions to get the unencryption-keys and he must be able to work with these informations.But I think, this solution is much more save then storing or comitting the credentials as clear-text in cookies, hidden formular-elements or as sessions. But
when you try to login to the database, somehow the credentials must be cleartext, so you can't get rid of this lack of security in my opinion.By the way, this is an *intra*net-solution, and we don't have hackers in our staff, I hope...Ludwig

Re: [GENERAL] Password safe web application with postgre

[Fernando]

You could try to have a function in your application that encrypts the 
connection string and store it in a session variable.  When you need it 
you decrypted from the session variables.  Session variables are stored 
as files on the server, therefore the risk is not as high.


Just a thought.

Fernando.

Bohdan Linda wrote:

Hello,

I have the following problem. A multiuser app has authentization and
authorization done based on pgsql.

The frontend is web based so it is stateless; it is connecting to database
on every get/post. There is also a requirement that the user is
transparently logged in for some period of time.

Tha most easy way is to store login credentials into the session. The
drawback is that session is stored in file, so the credentials are
readable. I want to avoid it. 


My first step was hashing the password with the same mechanizm as pgsql
does, but I am not able to pass it to the server. I did some research with
mighty google and found reply by Tom Lane:

"No, you need to put the plain text of the password into the connInfo.
Knowing the md5 doesn't prove you know the password. "

Thus the next logical step is keeping sessions in servers memory rather
than files. Memory dump could compromise it, but this is acceptable risk.

I would like to ask you, if someone had solved this problem is some more
elegant way.

Thank you,
Bohdan 

  

Re: [GENERAL] Password safe web application with postgre

[Allan Kamau]

Hi Bohdan,
Is your web applications for use with PostgreSQL server administration 
where you would like users to supply their login credentials for 
PostgreSQL so that their actions within the db can be limited by the 
fine gain privileges assigned to them?


If it is not then you may want to maybe remodel your solution so that 
your uses may share a common DB login whose login details 
(username,password and server host name etc) are stored/contained within 
your web application hosted on the server.
Then you supply your users with other username/password which will only 
be known by your web application and not the PostgreSQL login. When your 
users wish to use your web application, they will login with their 
username/password for the web application which your web application 
should verify (by means you see fit). The web application can now login 
(using the PostgreSQL credentials) to the DB on behalf of the user(s).
Using a shared login has the following advantages, you only need only 
one login for all your users. Which means you only need administer one 
login. And this gives you the option to use DB connection pooling (this 
is an application solution). Creating connections is an expensive 
process and should be done only when necessary.


Allan.

Bohdan Linda wrote:

Hello,

I have the following problem. A multiuser app has authentization and
authorization done based on pgsql.

The frontend is web based so it is stateless; it is connecting to database
on every get/post. There is also a requirement that the user is
transparently logged in for some period of time.

Tha most easy way is to store login credentials into the session. The
drawback is that session is stored in file, so the credentials are
readable. I want to avoid it. 


My first step was hashing the password with the same mechanizm as pgsql
does, but I am not able to pass it to the server. I did some research with
mighty google and found reply by Tom Lane:

"No, you need to put the plain text of the password into the connInfo.
Knowing the md5 doesn't prove you know the password. "

Thus the next logical step is keeping sessions in servers memory rather
than files. Memory dump could compromise it, but this is acceptable risk.

I would like to ask you, if someone had solved this problem is some more
elegant way.

Thank you,
Bohdan 

  



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Password safe web application with postgre

[Bohdan Linda]

Hello,

I have the following problem. A multiuser app has authentization and
authorization done based on pgsql.

The frontend is web based so it is stateless; it is connecting to database
on every get/post. There is also a requirement that the user is
transparently logged in for some period of time.

Tha most easy way is to store login credentials into the session. The
drawback is that session is stored in file, so the credentials are
readable. I want to avoid it. 

My first step was hashing the password with the same mechanizm as pgsql
does, but I am not able to pass it to the server. I did some research with
mighty google and found reply by Tom Lane:

"No, you need to put the plain text of the password into the connInfo.
Knowing the md5 doesn't prove you know the password. "

Thus the next logical step is keeping sessions in servers memory rather
than files. Memory dump could compromise it, but this is acceptable risk.

I would like to ask you, if someone had solved this problem is some more
elegant way.

Thank you,
Bohdan 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] password option in pg_dumpall

[Tom Lane]

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> is there anyway of specifying password in the command line interface
> of pg_dumpall??

No, and you wouldn't want to use it if there was (hint: putting a
password on a command line is insecure).

The recommended procedure to avoid a lot of password prompts is to
set up a ~/.pgpass file:
http://www.postgresql.org/docs/8.2/static/libpq-pgpass.html

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] password option in pg_dumpall

[[EMAIL PROTECTED]]

is there anyway of specifying password in the command line interface
of pg_dumpall??

this my script, and it asks for password for every host...
thanks

'''
#!/bin/sh
for line in `cat /home/mark/work/infrastructure/farm_all`
do
pg_dumpall -h $line -U postgres | bzip2 > "$line.bz2"
done
'''

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Password as a command line argument to createuser

[Andrew Sullivan]

On Wed, Dec 19, 2007 at 10:38:52AM -0500, Tom Lane wrote:
> reading the password from /dev/tty, so if you want to script this, you'd
> be stuck with making a special-purpose program that didn't.

But given that passwords are sort of awful in this way anyway, why not use
something designed not to have this problem, like Kerberos?  Especially now
that someone has been doing the work to make Kerberos play nicely in the
latest and greatest ways?

A

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Password as a command line argument to createuser

[Tom Lane]

Greg Smith <[EMAIL PROTECTED]> writes:
> In your typical shell nowadays the echo command is a built-in one--it 
> executes directly rather than calling a separate echo binary, so it won't 
> leak what you tell it onto a command line.  That means this line in a 
> script would be simplest way to do this that's not completely insecure:

> echo "create user foo password 'secret'" | psql ...

And if we haven't given you a headache yet:

There's a similar risk even after you've securely sent the command
to the database server: it will be transiently exposed in
pg_stat_activity, and perhaps permanently logged in the postmaster log.
Now the audience that can see either of those things is hopefully
smaller than "everyone on the machine", but still it's not very nice
if you don't want anyone else to know the cleartext of your password.

The way to deal with this is to pre-encrypt the password before you send
it over to the server.  Both the createuser program and psql's \password
command do it that way.  Unfortunately it looks like they both insist on
reading the password from /dev/tty, so if you want to script this, you'd
be stuck with making a special-purpose program that didn't.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Password as a command line argument to createuser

[Greg Smith]

On Wed, 19 Dec 2007, A. Kretschmer wrote:


psql -U ... database -c "create user foo password 'secret';"


This seems like a reasonable example, but it will also show the password 
you're assigning on the command line to anybody who happens to run ps, 
which is the reason why this isn't allowed by createuser in the first 
place.


In your typical shell nowadays the echo command is a built-in one--it 
executes directly rather than calling a separate echo binary, so it won't 
leak what you tell it onto a command line.  That means this line in a 
script would be simplest way to do this that's not completely insecure:


echo "create user foo password 'secret'" | psql ...

This is not recommended on the command line (I think other people can 
still see the whole thing), but in a script I believe others just see the 
psql executing against standard input.


Of course you need the surrounding script to not do the wrong thing 
either, where the wrong thing includes any approach where you put the 
password on the command line.  Last time I had to do a batch creation of a 
bunch of accounts I put them into a file with the format 
"username:password", read that directly from the shell (a good sample to 
borrow from for that part is 
http://www.askdavetaylor.com/how_do_i_read_lines_of_data_in_a_shell_script.html 
) and used echo | psql as above to create them.  This is not an approach 
I'd want to use as a long-term tool, but for hacking something together 
it's not an awful way to do it.


Like all questions with security implications, I highly recommend you 
believe nothing I said above and confirm each suggestion through your own 
research and testing.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Password as a command line argument to createuser

[A. Kretschmer]

am  Tue, dem 18.12.2007, um 22:04:13 -0800 mailte Jane Ren folgendes:
> Hi,
> 
> I need to write a script that creates a new user with a password
> automatically.
> 
> Is there a way I can specify the password as a command line argument to
> createuser?

>From a unix shell? You can call psql with -c "your command".

Try this:

psql -U ... database -c "create user foo password 'secret';"



Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Password as a command line argument to createuser

[Tom Lane]

"Jane Ren" <[EMAIL PROTECTED]> writes:
> Is there a way I can specify the password as a command line argument to
> createuser?

No, and it would be a really bad idea if you could, as the password
would be exposed to everyone else on the machine (via "ps") while
createuser runs.

There are various ways to do this securely, but putting the password
on a program's command line isn't one of them.  I'd suggest looking
at how psql's \password command does it.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Password as a command line argument to createuser

[Joshua D. Drake]

Jane Ren wrote:

Hi,

I need to write a script that creates a new user with a password
automatically.

Is there a way I can specify the password as a command line argument to
createuser?



Since you have access to the shell use psql -U user -c "create role ..."

Joshua D. Drake


It looks like postgres does not read from stdin, but from /dev/tty.

Thanks

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/




---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Password as a command line argument to createuser

[Jane Ren]

Hi,

I need to write a script that creates a new user with a password
automatically.

Is there a way I can specify the password as a command line argument to
createuser?

It looks like postgres does not read from stdin, but from /dev/tty.

Thanks

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Password authentication failed

[Jan Bilek]

Connect to PostgreSql as Postgres user (default database user):

psql yourdb -U Postgres

then you will be asked for password selected during the installation.

Hope this will help.

JB

  - Original Message - 
  From: Suresh Nimbalkar 
  To: pgsql-general@postgresql.org 
  Sent: Tuesday, May 01, 2007 1:29 PM
  Subject: [GENERAL] Password authentication failed


  Hi!

  I am a complete newbee to Postgres. Have installed Postgres on Windows 2003 
server SP1 a week back. When I try to log-in to the server (by writting psql 
mydb at command prompt in postgres/bin directory), I keep getting a message 
"psal: FATAL: password authentication failed for user"Administrator". 

  I have installed Postgres as an Administrator and log-in to the server as 
administrator. I don't think I am making mistake in entering the password. 

  It's quite frustrating. Will someone please help?

  Thanks and regards
  Vedsur



--
  Ahhh...imagining that irresistible "new car" smell?
  Check out new cars at Yahoo! Autos. 

[GENERAL] Password authentication failed

[Suresh Nimbalkar]

Hi!

I am a complete newbee to Postgres. Have installed Postgres on Windows 2003 
server SP1 a week back. When I try to log-in to the server (by writting psql 
mydb at command prompt in postgres/bin directory), I keep getting a message 
"psal: FATAL: password authentication failed for user"Administrator". 

I have installed Postgres as an Administrator and log-in to the server as 
administrator. I don't think I am making mistake in entering the password. 

It's quite frustrating. Will someone please help?

Thanks and regards
Vedsur

   
-
Ahhh...imagining that irresistible "new car" smell?
 Check outnew cars at Yahoo! Autos.

Re: [GENERAL] Password for postgres

[Shoaib Mir]

The command goes through fine, but the next time I log into postgres using

the command,
>>psql mydb postgres
>>it logs in, and doesnt ask me for a password at all. If I issue a command
like,

This is because you have trust in pg_hba.conf file, change it to md5 so it
asks for password every time you log in :)


psql mydb postgres mypwd


That is not the correct way, just as i mentioned change it to md5 and now
when you do

psql -d mydb -U postgres

It will ask for a password.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 3/8/07, George Heller <[EMAIL PROTECTED]> wrote:


Hi all,

I am using a postgres database, and want to set a password for the
account. The default user name is 'postgres' and in order to set a password,
I did an alter user as below,

alter user postgres with password 'mypwd';

The command goes through fine, but the next time I log into postgres using
the command,

psql mydb postgres

it logs in, and doesnt ask me for a password at all. If I issue a command
like,

psql mydb postgres mypwd

it says, extra argument!!! I have checked the pg_shadow table, and it
looks like the password has been set. I also have a trust entry for the
database server that I am accessing it through, in the pg_hba.conf file.

Any suggestions to get around this problem?

Thanks!
George

--
Need Mail bonding?
Go to the Yahoo! Mail 
Q&Afor
 great
tips from Yahoo! 
Answersusers.

[GENERAL] Password for postgres

[George Heller]

Hi all,
   
  I am using a postgres database, and want to set a password for the account. 
The default user name is 'postgres' and in order to set a password, I did an 
alter user as below,
   
  alter user postgres with password 'mypwd';
   
  The command goes through fine, but the next time I log into postgres using 
the command,
   
  psql mydb postgres
   
  it logs in, and doesnt ask me for a password at all. If I issue a command 
like,
   
  psql mydb postgres mypwd 
   
  it says, extra argument!!! I have checked the pg_shadow table, and it looks 
like the password has been set. I also have a trust entry for the database 
server that I am accessing it through, in the pg_hba.conf file. 
   
  Any suggestions to get around this problem?
   
  Thanks!
  George

 
-
Need Mail bonding?
Go to the Yahoo! Mail Q&A for great tips from Yahoo! Answers users.

Re: [GENERAL] Password issue revisited

[Bruce Momjian]

I assume this is not a TODO.

---

Magnus Hagander wrote:
> >>> The default on *all* windows versions since NT 4.0 (which is when the
> >>> directory we use was added) will put this file in a protected directory.
> >>> The only case when it's not protected by default is if you're usnig FAT
> >>> filesystem, in which case there is nothing you can do about it anyway.
> >>> On unix, the file will often be created in outside-readable mode by
> >>> default, depending on how your OS is set up.
> > 
> > I believe that .pgpass on *nix won't be used if it is readable by anyone
> > except the current user.
> 
> No, root can always read it. On unix, there is one "root". On windows,
> the concept of administrator is less clear.
> 
> 
> > From the docs -
> > The permissions on .pgpass must disallow any access to world or group;
> > achieve this by the command chmod 0600 ~/.pgpass. If the permissions are
> > less strict than this, the file will be ignored. (The file permissions
> > are not currently checked on Microsoft Windows, however.)
> > 
> > I would think that if they are using FAT filesystem (which is only
> > partially supported for developers benefit) then they can't use pgpass.
> 
> If they are using FAT, the obviously don't care about the security of
> the system anyway, so it's not a problem, IMHO. So we only have to care
> about people who use NTFS.
> 
> 
> >>> So to reach a situation where the file lives in an unprotected
> >>> directory, you must actively open up the directory in question. Which is
> >>> hidden from default view, so you really need to know what you're
> >>> doing to
> >>> get there.
> >>>
> >>> Not to mention it's a pain to define what permissions are ok and what
> >>> are not. We're talking ACLs and not filemodes - so how do you decide
> >>> which accounts are ok to have access, and which are not?
> > 
> > I would say the same as the *nix version - if it is readable or writable
> > by anyone except the current user it is potentially at risk, the current
> > user connecting to pgsql is the only use for this file.
> > Which I believe is the whole point of the TODO entry, stop anyone using
> > the pgpass file without proper security.
> 
> Again, it's a lot harder to actually define it on Windows. What if your
> user has access only through a group? What about DENY permissions.
> Things like that.
> 
> 
> > The other thing to consider is that pgpass is the file referenced by
> > PGPASSFILE - the user can set this to point to a file anywhere on any
> > drive available.
> 
> That's a very valid point though, didn't think about that.
> 
> Still doesn't take away the "how" part, though, but it does take away
> part of the "why" part.
> 
> //Magnus
> 
> 
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>  http://momjian.us
  EnterpriseDB   http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Password issue revisited

[Dave Page]

Magnus Hagander wrote:
> Dave Page wrote:
>> Magnus Hagander wrote:
>>
>>> Just to make things clear, this wouldn't be about another auth method.
>>> Windows has an API to store arbitrary passwords in a "secure way". At
>>> least it does in XP+, not sure if it was in 2000.
>> Would it really solve Tony's problem though? I'm not familiar with the
>> API you're thinking of, but do be useful to us it must be able to give
>> the unencrypted passwords back to us, and therefore anything else
>> pretending to be us.
> 
> yeah, but it pops up a GUI notification for you. It's what IE uses to
> store things like passports. It's also used, IIRC, by the new RDP client
> that's available, and a few more.
> Did a quick check, and it's XP/2003 only. See
> http://msdn2.microsoft.com/en-us/library/aa302353.aspx.

That would break all the non-interactive apps that we recommend using
pgpass with to prevent storing passwords in even less secure places.

Regards, Dave.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Password issue revisited

[Magnus Hagander]

Dave Page wrote:
> Magnus Hagander wrote:
> 
>> Just to make things clear, this wouldn't be about another auth method.
>> Windows has an API to store arbitrary passwords in a "secure way". At
>> least it does in XP+, not sure if it was in 2000.
> 
> Would it really solve Tony's problem though? I'm not familiar with the
> API you're thinking of, but do be useful to us it must be able to give
> the unencrypted passwords back to us, and therefore anything else
> pretending to be us.

yeah, but it pops up a GUI notification for you. It's what IE uses to
store things like passports. It's also used, IIRC, by the new RDP client
that's available, and a few more.
Did a quick check, and it's XP/2003 only. See
http://msdn2.microsoft.com/en-us/library/aa302353.aspx.

//Magnus

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Password issue revisited

[Dave Page]

Magnus Hagander wrote:

> Just to make things clear, this wouldn't be about another auth method.
> Windows has an API to store arbitrary passwords in a "secure way". At
> least it does in XP+, not sure if it was in 2000.

Would it really solve Tony's problem though? I'm not familiar with the
API you're thinking of, but do be useful to us it must be able to give
the unencrypted passwords back to us, and therefore anything else
pretending to be us.

Regards, Dave.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Password issue revisited

[Magnus Hagander]

Tom Lane wrote:
> Magnus Hagander <[EMAIL PROTECTED]> writes:
>> Tony Caduto wrote:
>>> What about having a wallet type system where the user can create a pass
>>> phrase to protect a generated key that would get
>>> loaded once per session.  That is how KDE allows users to store passwords.
> 
>> If we wanted to do that, we could use the Windows API that's available
>> to do this. The idea with the pgpass flie is to have it compatible with
>> the unix version.
> 
> More to the point, that's far outside the scope of this project.  Use a
> PAM auth module that you like, or Kerberos or whatever.  I'm way past
> tired of "let's put yet another authentication technology in libpq" requests.

Just to make things clear, this wouldn't be about another auth method.
Windows has an API to store arbitrary passwords in a "secure way". At
least it does in XP+, not sure if it was in 2000.

Not saying it's a good idea, but it's not another auth tech for libpq.

//Magnus

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Password issue revisited

[Tom Lane]

Magnus Hagander <[EMAIL PROTECTED]> writes:
> Tony Caduto wrote:
>> What about having a wallet type system where the user can create a pass
>> phrase to protect a generated key that would get
>> loaded once per session.  That is how KDE allows users to store passwords.

> If we wanted to do that, we could use the Windows API that's available
> to do this. The idea with the pgpass flie is to have it compatible with
> the unix version.

More to the point, that's far outside the scope of this project.  Use a
PAM auth module that you like, or Kerberos or whatever.  I'm way past
tired of "let's put yet another authentication technology in libpq" requests.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Password issue revisited

[Magnus Hagander]

Tony Caduto wrote:
> Magnus Hagander wrote:
>> Are we sure we want to do this? (Sorry, didn't notice this thread last
>> time)
>>
>> The default on *all* windows versions since NT 4.0 (which is when the
>> directory we use was added) will put this file in a protected directory.
>>   
> Is there truly such a thing on a windows PC?  All it takes is one Virus
> or Malware to gain access to the PC and anything stored in the
> user profile is easy picking.
> The virus and malware creators may not know about the pg_pass file now,
> but they will eventually.
> What about having a wallet type system where the user can create a pass
> phrase to protect a generated key that would get
> loaded once per session.  That is how KDE allows users to store passwords.
> 
> I work at a large financial institution and if the auditors knew about
> the pg_pass being plain text, they would pretty much ban
> it's use.
> 
> Anytime a password is sitting on a non encrypted file system, regardless
> of it's permissions it is potentially at risk.

If we wanted to do that, we could use the Windows API that's available
to do this. The idea with the pgpass flie is to have it compatible with
the unix version.

//Magnus

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Password issue revisited

[Tony Caduto]

Magnus Hagander wrote:

Are we sure we want to do this? (Sorry, didn't notice this thread last
time)

The default on *all* windows versions since NT 4.0 (which is when the
directory we use was added) will put this file in a protected directory.
  
Is there truly such a thing on a windows PC?  All it takes is one Virus 
or Malware to gain access to the PC and anything stored in the

user profile is easy picking.
The virus and malware creators may not know about the pg_pass file now, 
but they will eventually.
What about having a wallet type system where the user can create a pass 
phrase to protect a generated key that would get

loaded once per session.  That is how KDE allows users to store passwords.

I work at a large financial institution and if the auditors knew about 
the pg_pass being plain text, they would pretty much ban

it's use.

Anytime a password is sitting on a non encrypted file system, regardless 
of it's permissions it is potentially at risk.


--
Tony 



---(end of broadcast)---
TIP 6: explain analyze is your friend