On Thu, 21 Dec 2006, Steve Atkins wrote:
> >Is there any way to disallow self changing of password by ordinary
> >users? Or force password strength in any other way?
>
> If you check http://www.postgresql.org/docs/8.2/static/client-authentication.html
> you'll see a bunch of different ways to authenticate users. Most of
> them are external to the database, so don't allow changing the
> password from within the database.
But I need an ability to change passwords. The easiest way to do it
would be to create a "security definer" function owned by a role with
"create role" privilege that checks password strength and changes
password, for example:
create or replace function change_password(_password text)
returns void
language plpgsql
volatile
security definer
as $function$
begin
if (
char_length(_password)<8
or _password !~ '[A-Z]'
or _password !~ '[a-z]'
or _password !~ '[0-9]'
) then
raise exception 'Password too weak, because ...';
end if;
execute 'alter user ' || quote_ident(session_user)
|| ' password ' || quote_literal(_password)
|| ' valid until ' || quote_literal(current_timestamp+'60 days'::interval);
return;
end;
$function$;
But any user can change his password using 'alter user ... password
...'. If there was any way of preventing users without "create role"
privilege from changing their own passwords this would be good enough.
I'll just need to ensure encrypted connections to the database.
If I had to use external authentication it'd need a lot of work - I'd
need to learn, setup, document and maintain this external
authentication, provide a way of changing passwords securely other
than using a database, create one more single point of failure,
etc...
Or I'll just ignore this possibility of choosing weak password. It
would not reset account validity time anyway. Also nobody would
notice - application interface will use this change_password function
- it's just not the right way.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh