Обсуждение: Why can't I change a password

Hi all,

I've somehow messed up something.

psql super
psql (8.4.5)
Type "help" for help.

super=> \du+
                   List of roles
 Role name | Attributes  | Member of | Description
-----------+-------------+-----------+-------------
 bobo      |             | {}        |
 junk      |             | {}        |
 myuid     |             | {}        |
 postgres  | Superuser   | {}        |
           : Create role
           : Create DB
 slitt     | Create DB   | {}        |
 super     | Superuser   | {}        |
           : Create role
           : Create DB

super=>
super=> alter user myuid password 'mypass';
ERROR:  permission denied
super=>

I don't get it. User super is listed as attributes Superuser, create role and
createdb, so why the lack of permission? This also happened when I was in as
"postgres".

Interestingly, earlier tonight I was changing passwords just fine. I don't
know what happened. Obviously there's a chunk of information I'm missing.

What diagnostic tests can I do to narrow this down?

Thanks

SteveT

Steve Litt
Recession Relief Package
http://www.recession-relief.US
Twitter: http://www.twitter.com/stevelitt

Thanks Dmitriy,

It turns out the solution I used was to su to postgres in Linux, and then run
the command psql without arguments, at which time I could have my way with any
object.

More in my responses to you...

On Sunday 16 January 2011 06:21:28 Dmitriy Igrishin wrote:
> Hey Steve,
>
> 2011/1/16 Steve Litt <slitt@troubleshooters.com>
>
> > Hi all,
> >
> > I've somehow messed up something.
> >
> > psql super
>
> psql's synopsis is
>        psql [option...] [dbname [username]]
> Thus, the call "psql super" connects psql to a database
> "super" but since username unspecified it is connected
> with current Unix user (which is returned by whois(1)).
>
> So, you should call psql like that
>   psql super super
slitt@mydesk:~$ psql super super
psql: FATAL:  Ident authentication failed for user "super"
slitt@mydesk:~$ psql postgres postgres
psql: FATAL:  Ident authentication failed for user "postgres"
slitt@mydesk:~$

> or like that
>   psql -U super
slitt@mydesk:~$ psql -U super
psql: FATAL:  Ident authentication failed for user "super"
slitt@mydesk:~$ psql -U postgres
psql: FATAL:  Ident authentication failed for user "postgres"
slitt@mydesk:~$

Now watch this:
slitt@mydesk:~$ su - postgres
Password:
postgres@mydesk:~$ psql -U postgres
psql (8.4.5)
Type "help" for help.

postgres=#

Luckily for me, needing to be Linux user postgres isn't at all an
inconvenience, so the problem is pretty much solved, although some curiosity
remains.

Thanks

SteveT

Steve Litt
Recession Relief Package
http://www.recession-relief.US
Twitter: http://www.twitter.com/stevelitt

On 16/01/2011 20:56, Steve Litt wrote:
> Thanks Dmitriy,
>
> It turns out the solution I used was to su to postgres in Linux, and then run
> the command psql without arguments, at which time I could have my way with any
> object.
>
> More in my responses to you...
>
> On Sunday 16 January 2011 06:21:28 Dmitriy Igrishin wrote:
>> Hey Steve,
>>
>> 2011/1/16 Steve Litt<slitt@troubleshooters.com>
>>
>>> Hi all,
>>>
>>> I've somehow messed up something.
>>>
>>> psql super
>>
>> psql's synopsis is
>>         psql [option...] [dbname [username]]
>> Thus, the call "psql super" connects psql to a database
>> "super" but since username unspecified it is connected
>> with current Unix user (which is returned by whois(1)).
>>
>> So, you should call psql like that
>>    psql super super
> slitt@mydesk:~$ psql super super
> psql: FATAL:  Ident authentication failed for user "super"
> slitt@mydesk:~$ psql postgres postgres
> psql: FATAL:  Ident authentication failed for user "postgres"
> slitt@mydesk:~$

If you have configured PG to listen on a TCP/IP port (5432 by default),
you can also do:

   psql -U postgres -h localhost super

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On Sunday 16 January 2011 16:02:12 Raymond O'Donnell wrote:

> If you have configured PG to listen on a TCP/IP port (5432 by default),
> you can also do:
>
>    psql -U postgres -h localhost super
>
> Ray.

Thanks Ray,

My psql seems a lot different from others. Loook what happened:

slitt@mydesk:~$ psql -U postgres -h localhost super
Password for user postgres:
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"
slitt@mydesk:~$ psql -U postgres -h 127.0.0.1 super
Password for user postgres:
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"
slitt@mydesk:~$

My postgresql.conf configures the port at 5433 instead of 5432, so I also
tried this:

slitt@mydesk:~$ psql -U postgres -h localhost -p 5433 super
Password for user postgres:
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"
slitt@mydesk:~$

Thanks

Steve

Steve Litt
Recession Relief Package
http://www.recession-relief.US
Twitter: http://www.twitter.com/stevelitt

On 16/01/2011 21:39, Steve Litt wrote:
> On Sunday 16 January 2011 16:02:12 Raymond O'Donnell wrote:
>
>> If you have configured PG to listen on a TCP/IP port (5432 by default),
>> you can also do:
>>
>>     psql -U postgres -h localhost super
>>
>> Ray.
>
> Thanks Ray,
>
> My psql seems a lot different from others. Loook what happened:
>
> slitt@mydesk:~$ psql -U postgres -h localhost super
> Password for user postgres:
> psql: FATAL:  password authentication failed for user "postgres"
> FATAL:  password authentication failed for user "postgres"
> slitt@mydesk:~$ psql -U postgres -h 127.0.0.1 super
> Password for user postgres:
> psql: FATAL:  password authentication failed for user "postgres"
> FATAL:  password authentication failed for user "postgres"
> slitt@mydesk:~$
>
> My postgresql.conf configures the port at 5433 instead of 5432, so I also
> tried this:
>
> slitt@mydesk:~$ psql -U postgres -h localhost -p 5433 super
> Password for user postgres:
> psql: FATAL:  password authentication failed for user "postgres"
> FATAL:  password authentication failed for user "postgres"
> slitt@mydesk:~$

Well that's interesting - some instance of PostgreSQL is listening on
port 5432, as well as 5433.

However, you're apparently supplying an incorrect password for user
"postgres", as per the error message. Your psql is no different to
anyone else's; that's a normal error message.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On Sunday 16 January 2011 16:02:12 Raymond O'Donnell wrote:

> If you have configured PG to listen on a TCP/IP port (5432 by default),
> you can also do:
>
>    psql -U postgres -h localhost super
>
> Ray.

Thanks Ray,

You were sooooo close! The command that works is this:

psql -U super -h localhost super

That's because the super database is owned by the super user, in this
particular case. Interestingly enough, even though I've set my port to be 5433
instead of 5432, it wasn't necessary for me to add -p 5433.

Your method has the big benefit of being able to supervise from a Postgres
Superuser who doesn't have a Linux account (super, in this case). Thanks for
the info!

SteveT

Steve Litt
Recession Relief Package
http://www.recession-relief.US
Twitter: http://www.twitter.com/stevelitt

On Sunday 16 January 2011 1:49:43 pm Steve Litt wrote:
> On Sunday 16 January 2011 16:02:12 Raymond O'Donnell wrote:
> > If you have configured PG to listen on a TCP/IP port (5432 by default),
> > you can also do:
> >
> >    psql -U postgres -h localhost super
> >
> > Ray.
>
> Thanks Ray,
>
> You were sooooo close! The command that works is this:
>
> psql -U super -h localhost super
>
> That's because the super database is owned by the super user, in this
> particular case. Interestingly enough, even though I've set my port to be
> 5433 instead of 5432, it wasn't necessary for me to add -p 5433.

The change will not take effect until you restart the server. At that point you
will need to specify the port.

>
> Your method has the big benefit of being able to supervise from a Postgres
> Superuser who doesn't have a Linux account (super, in this case). Thanks
> for the info!

See here for a more in depth look at client authentication:
http://www.postgresql.org/docs/9.0/interactive/client-authentication.html

>
> SteveT
>
> Steve Litt
> Recession Relief Package
> http://www.recession-relief.US
> Twitter: http://www.twitter.com/stevelitt



--
Adrian Klaver
adrian.klaver@gmail.com

On Sunday 16 January 2011 1:49:43 pm Steve Litt wrote:
> On Sunday 16 January 2011 16:02:12 Raymond O'Donnell wrote:
> > If you have configured PG to listen on a TCP/IP port (5432 by default),
> > you can also do:
> >
> >    psql -U postgres -h localhost super
> >
> > Ray.
>
> Thanks Ray,
>
> You were sooooo close! The command that works is this:
>
> psql -U super -h localhost super
>
> That's because the super database is owned by the super user, in this
> particular case. Interestingly enough, even though I've set my port to be
> 5433 instead of 5432, it wasn't necessary for me to add -p 5433.

Just realized you do have a server running at 5433 per this from one of your
previous posts:

slitt@mydesk:~$ psql -U postgres -h localhost -p 5433 super
Password for user postgres:
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"
slitt@mydesk:~$

There would have been a different error if there was no server listening on that
port.

It would seem you have more than one Postgres server running.


>
> Your method has the big benefit of being able to supervise from a Postgres
> Superuser who doesn't have a Linux account (super, in this case). Thanks
> for the info!
>
> SteveT
>
> Steve Litt
> Recession Relief Package
> http://www.recession-relief.US
> Twitter: http://www.twitter.com/stevelitt



--
Adrian Klaver
adrian.klaver@gmail.com