ALTER USER

ALTER USER

Post by Alvaro Herre » Mon, 17 Mar 2003 11:38:18



Hackers,

One can alter a user to set a validity timestamp.  However, unless one
uses the ugly kludge of setting a date very far into the future, there's
no way to set this validity forever.

Should I make a patch to correct this?  Should be quite trivial.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El dia que dejes de cambiar dejaras de vivir"

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

http://archives.postgresql.org

 
 
 

ALTER USER

Post by Bruno Wolff I » Mon, 17 Mar 2003 22:23:11


On Sat, Mar 15, 2003 at 22:38:13 -0400,

Quote:> Hackers,

> One can alter a user to set a validity timestamp.  However, unless one
> uses the ugly kludge of setting a date very far into the future, there's
> no way to set this validity forever.

There is an infinite time for timestamp. There currently isn't for date,
though there was some talk about doing that.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command


 
 
 

ALTER USER

Post by Alvaro Herre » Tue, 18 Mar 2003 01:37:42



> On Sat, Mar 15, 2003 at 22:38:13 -0400,

> > Hackers,

> > One can alter a user to set a validity timestamp.  However, unless one
> > uses the ugly kludge of setting a date very far into the future, there's
> > no way to set this validity forever.

> There is an infinite time for timestamp. There currently isn't for date,
> though there was some talk about doing that.

I don't know much about date/time datatypes, but valuntil is of type
abstime, and you can set it to infinity:

alvh=# alter user alvh valid until 'infinity';
ALTER USER
alvh=# select usename, valuntil from pg_shadow where usename='alvh';
 usename | valuntil
---------+----------
 alvh    | infinity
(1 row)

I see now that one can use this syntax to make a user valid forever,
though it is different than setting the value to NULL (as is when the
user hasn't got a validity defined).  This should be mentioned in the
docs, I think.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"In fact, the basic problem with Perl 5's subroutines is that they're not
crufty enough, so the cruft leaks out into user-defined code instead, by
the Conservation of Cruft Principle."  (Larry Wall, Apocalypse 6)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

ALTER USER

Post by Rod Tayl » Tue, 18 Mar 2003 02:04:26


--=-H/vVgdnZfJGi3DWf0+4c
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

Quote:> I see now that one can use this syntax to make a user valid forever,
> though it is different than setting the value to NULL (as is when the
> user hasn't got a validity defined).  This should be mentioned in the
> docs, I think.

It may be worth while to change the default for valuntil to be
'infinity'.  NULL implies they will expire, we're just not sure when.
Infinity shows that we do not intend to expire the user -- which is more
in-line with the actual implementation.=20

--=20

PGP Key: http://www.rbt.ca/rbtpub.asc

--=-H/vVgdnZfJGi3DWf0+4c
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (FreeBSD)

iD8DBQA+dK5z6DETLow6vwwRAihuAJ9BbVzIGpCVA4Z8+gAwyoJXCby8bwCcDFDo
mpwIj+LEv+CPggwB3ePFLO0=
=J917
-----END PGP SIGNATURE-----

--=-H/vVgdnZfJGi3DWf0+4c--

 
 
 

ALTER USER

Post by Tom La » Tue, 18 Mar 2003 02:36:29



> It may be worth while to change the default for valuntil to be
> 'infinity'.  NULL implies they will expire, we're just not sure when.

This is not the only place in the system catalogs where NULL is
effectively used to mean a default value that could also be spelled
out explicitly.  (ACLs behave that way, and useconfig/datconfig
do too IIRC.)

It's a bit of a hack, but it saves table space and backend code ---
without this convention the default would have to be inserted "manually"
since we have no mechanism to supply defaults when C code is forming a
new catalog tuple.

I'm inclined to leave the code alone.  But Alvaro is right that it'd be
good to point out the 'infinity' option in the CREATE USER and ALTER
USER man pages.  (Doc patch please?)

                        regards, tom lane

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

http://www.postgresql.org/docs/faqs/FAQ.html

 
 
 

ALTER USER

Post by Alvaro Herre » Tue, 18 Mar 2003 03:05:54


--jRHKVT23PllUwdXP
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit


> I'm inclined to leave the code alone.  But Alvaro is right that it'd be
> good to point out the 'infinity' option in the CREATE USER and ALTER
> USER man pages.  (Doc patch please?)

Attached.  (Please correct if it's not good english.)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Investigacin es lo que hago cuando no s lo que estoy haciendo"
(Wernher von Braun)

--jRHKVT23PllUwdXP
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="alter-user-doc.patch"

Index: doc/src/sgml/ref/alter_user.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/alter_user.sgml,v
retrieving revision 1.24
diff -c -r1.24 alter_user.sgml
*** doc/src/sgml/ref/alter_user.sgml    2003/01/19 00:13:29     1.24
--- doc/src/sgml/ref/alter_user.sgml    2003/03/16 17:57:31
***************
*** 124,130 ****
        <listitem>
         <para>
        The date (and, optionally, the time)
!       at which this user's password is to expire.
         </para>
        </listitem>
       </varlistentry>
--- 124,131 ----
        <listitem>
         <para>
        The date (and, optionally, the time)
!       at which this user's password is to expire.  To set the password
!       never to expire, use 'infinity'.
         </para>
        </listitem>
       </varlistentry>
***************
*** 229,234 ****
--- 230,242 ----
     the time zone which is one hour ahead of <acronym>UTC</>:
  <programlisting>
  ALTER USER chris VALID UNTIL 'May 4 12:00:00 1998 +1';
+ </programlisting>
+   </para>
+
+   <para>
+    Make a user valid forever:
+ <programlisting>
+ ALTER USER fred VALID UNTIL 'infinity';
  </programlisting>
    </para>

--jRHKVT23PllUwdXP
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0

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

--jRHKVT23PllUwdXP--

 
 
 

ALTER USER

Post by Bruno Wolff I » Tue, 18 Mar 2003 22:12:31


Is it just the password that expires or the account? The comment for
valid until says the password is valid until that time. However, one of
the examples says the account is valid until that time.

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

http://archives.postgresql.org

 
 
 

ALTER USER

Post by Tom La » Tue, 18 Mar 2003 23:19:54



Quote:> Is it just the password that expires or the account? The comment for
> valid until says the password is valid until that time. However, one of
> the examples says the account is valid until that time.

Given the current implementation, I think it's correct to say that
the password expires not the account:

1. the userid isn't deleted or anything like that.

2. validuntil is only checked in password authentication methods; if you
are able to connect via a non-password auth method (eg IDENT) then it's
not checked.

I've never been quite sure whether #2 is a bug or a feature, though.

                        regards, tom lane

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

http://www.postgresql.org/docs/faqs/FAQ.html

 
 
 

ALTER USER

Post by Peter Galbav » Tue, 18 Mar 2003 23:32:56


Quote:> 1. the userid isn't deleted or anything like that.

> 2. validuntil is only checked in password authentication methods; if you
> are able to connect via a non-password auth method (eg IDENT) then it's
> not checked.

> I've never been quite sure whether #2 is a bug or a feature, though.

Without knowing the history, I would have assumed that this was added to be
the start of a 'password ageing' function. Similar fields exist in GCOS
passwd files, but very few systems use them.

I got bitten by this when some of my user account (in a 6.x DB) were
invalidated after two years. Like I remembered to check...

Peter

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly

 
 
 

ALTER USER

Post by Bruce Momji » Fri, 21 Mar 2003 06:39:20


Your patch has been added to the PostgreSQL unapplied patches list at:

        http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------



> > I'm inclined to leave the code alone.  But Alvaro is right that it'd be
> > good to point out the 'infinity' option in the CREATE USER and ALTER
> > USER man pages.  (Doc patch please?)

> Attached.  (Please correct if it's not good english.)

> --
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> "Investigaci?n es lo que hago cuando no s? lo que estoy haciendo"
> (Wernher von Braun)

[ Attachment, skipping... ]

Quote:

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

--
  Bruce Momjian                        |  http://candle.pha.pa.us

  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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

http://archives.postgresql.org

 
 
 

ALTER USER

Post by Bruce Momji » Fri, 21 Mar 2003 06:39:21


I am a little disturbed by having NULL mean no expire of password, but
documenting that 'infinity' is the proper way to set no expiration.

Does that disturb anyone else?  Should we hack up the grammar to allow
VALID UNTIL NULL for consistency?

I guess I imagine someone spinning through pg_shadow and looking for
infinity and not looking at NULL as equivalent.  Maybe I should document
NULL is valid too for 'infinity'.

I will apply Alvero's documentation patch with a mention that internally
NULL is also infinity.

Comments?

---------------------------------------------------------------------------



> > It may be worth while to change the default for valuntil to be
> > 'infinity'.  NULL implies they will expire, we're just not sure when.

> This is not the only place in the system catalogs where NULL is
> effectively used to mean a default value that could also be spelled
> out explicitly.  (ACLs behave that way, and useconfig/datconfig
> do too IIRC.)

> It's a bit of a hack, but it saves table space and backend code ---
> without this convention the default would have to be inserted "manually"
> since we have no mechanism to supply defaults when C code is forming a
> new catalog tuple.

> I'm inclined to leave the code alone.  But Alvaro is right that it'd be
> good to point out the 'infinity' option in the CREATE USER and ALTER
> USER man pages.  (Doc patch please?)

>                    regards, tom lane

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

> http://www.postgresql.org/docs/faqs/FAQ.html

--
  Bruce Momjian                        |  http://candle.pha.pa.us

  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

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

http://www.postgresql.org/docs/faqs/FAQ.html

 
 
 

ALTER USER

Post by Bruce Momji » Sat, 22 Mar 2003 05:05:47


Patch applied.  Thanks.

---------------------------------------------------------------------------



> > I'm inclined to leave the code alone.  But Alvaro is right that it'd be
> > good to point out the 'infinity' option in the CREATE USER and ALTER
> > USER man pages.  (Doc patch please?)

> Attached.  (Please correct if it's not good english.)

> --
> Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
> "Investigaci?n es lo que hago cuando no s? lo que estoy haciendo"
> (Wernher von Braun)

[ Attachment, skipping... ]

Quote:

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

--
  Bruce Momjian                        |  http://candle.pha.pa.us

  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------

 
 
 

1. alter user to change user's password returns pg_shadow: Permission

According to the Postgresql manual, a user can alter their own
password.  When I try:
alter user "test" with password "zzzz";
Where test is the user id signed in with, I get the error:
ERROR:  pg_shadow: Permission denied.

What am I overlooking?

Tia,

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

2. How to make a splash from in VB3

3. Can DBMS_SQL Not Be Used to Alter Users?

4. Anyone have any feedback regarding Oracle Web Application Server for use in

5. How to alter User Defined Data Type

6. Fix for Windows setup.exe not running

7. alter user name: how ?

8. SP Escape Character???

9. How to alter User Defined Data Types

10. Alter user defined data types

11. alter user via dbms_sql

12. Oracle 8.1.6 Alter User command fails with VERIFY_FUNCTION

13. alter user problem