Re: VALID UNTIL

Hi,

I have a hard time figuring out why my replication stopped with a message like

FATAL: password authentication failed for user "foo"

in the logs. I thought it was some pg_hba.conf change, a pgpass modification,
or NOLOGIN option, it wasn't. I was out of options when I remembered to check
if there is a VALID OPTION option set. For my surprise, it wasn't exposed by
\du or even \du+.

euler=# \du
List of roles
-[ RECORD 1 ]----------------------------------------------
Role name | euler
Attributes | Superuser, Create role, Create DB, Replication
Member of | {}
-[ RECORD 2 ]----------------------------------------------
Role name | foo
Attributes |
Member of | {}

euler=# \du+
List of roles
-[ RECORD 1 ]-----------------------------------------------
Role name | euler
Attributes | Superuser, Create role, Create DB, Replication
Member of | {}
Description |
-[ RECORD 2 ]-----------------------------------------------
Role name | foo
Attributes |
Member of | {}
Description |

but after checking in the catalog I got it.

test=# select rolname,rolvaliduntil from pg_authid;
rolname | rolvaliduntil
---------+------------------------
euler |
foo | 2012-03-01 00:00:00-03
(2 rows)

Is there any reason why it is not exposed? What about exposing that
information in attributes or even in a separate column? It could help
troubleshooting quickly on this case.

--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

On Wed, Mar 14, 2012 at 11:20:05AM -0300, Euler Taveira de Oliveira wrote:
> Hi,
>
> I have a hard time figuring out why my replication stopped with a message like
>
> FATAL: password authentication failed for user "foo"
>
> in the logs. I thought it was some pg_hba.conf change, a pgpass modification,
> or NOLOGIN option, it wasn't. I was out of options when I remembered to check
> if there is a VALID OPTION option set. For my surprise, it wasn't exposed by
> \du or even \du+.
>
> Is there any reason why it is not exposed?

Oversight.

> What about exposing that information in attributes or even in a
> separate column? It could help troubleshooting quickly on this case.

I don't know how frequently people use VALID UNTIL, but I'm guessing
it's not terribly often because yours is the first comment about how
it's not exposed, so I'd tend toward putting it in attributes rather
than a separate column.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

2012/3/14 David Fetter <david(at)fetter(dot)org>

>
> I don't know how frequently people use VALID UNTIL, but I'm guessing
> it's not terribly often because yours is the first comment about how
> it's not exposed, so I'd tend toward putting it in attributes rather
> than a separate column.
>
>
If it's desired I can write a patch to put "Valid until" into attributes
column.

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

2012/3/14 David Fetter <david(at)fetter(dot)org>

>
> I don't know how frequently people use VALID UNTIL, but I'm guessing
> it's not terribly often because yours is the first comment about how
> it's not exposed, so I'd tend toward putting it in attributes rather
> than a separate column.
>
>
The attached patch put VALID UNTIL into attributes column in verbose mode
like example above.

bdteste=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bucardo | Superuser, Create role, Create DB, Replication | {}
postgres | Superuser, Create role, Create DB, Replication | {}

bdteste=# \du+
List of roles
Role name | Attributes
| Member of | Description
-----------+-------------------------------------------------------------------------------------------+-----------+--------------
bucardo | Superuser, Create role, Create DB, Valid until 2012-12-31
23:59:59.999999-02, Replication | {} | bucardo role
postgres | Superuser, Create role, Create DB, Replication
| {} |

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

=?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)com> writes:
> The attached patch put VALID UNTIL into attributes column in verbose mode
> like example above.

Why would you confine it to verbose mode? For most people it won't
matter, but for people who are using the feature, it seems like
important information. Per the OP's complaint, it's particularly
important for those who have forgotten they're using the feature
(and hence would not think to specify "+" ...)

regards, tom lane

2012/3/14 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

>
> Why would you confine it to verbose mode?

Because I did not want to change the current behavior of this psql
command... but...

For most people it won't
> matter, but for people who are using the feature, it seems like
> important information. Per the OP's complaint, it's particularly
> important for those who have forgotten they're using the feature
> (and hence would not think to specify "+" ...)
>
>
You' re right, then I attached a new patch with your suggestion.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

=?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?= <fabriziomello(at)gmail(dot)com> writes:
> 2012/3/14 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>> For most people it won't
>> matter, but for people who are using the feature, it seems like
>> important information. Per the OP's complaint, it's particularly
>> important for those who have forgotten they're using the feature
>> (and hence would not think to specify "+" ...)

> You' re right, then I attached a new patch with your suggestion.

Applied with minor corrections (mostly, ensuring the displayed text
is translatable).

regards, tom lane