Обсуждение: Password strength requirements
I'm working on a project which needs to satisfy some legal requirements for password strength. But any postgresql user can do; alter role [session_user] password 'foo'; Is there any way to disallow self changing of password by ordinary users? Or force password strength in any other 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
On Dec 21, 2006, at 7:08 AM, Tomasz Ostrowski wrote: > I'm working on a project which needs to satisfy some legal > requirements for password strength. But any postgresql user can do; > alter role [session_user] password 'foo'; > > 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. One of those should satisfy your needs - PAM in particular allows you to plugin a huge variety of backends to provide a lot of different behaviors. Cheers, Steve
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
On Thu, Dec 21, 2006 at 21:04:33 +0100, Tomasz Ostrowski <tometzky@batory.org.pl> wrote: > > But I need an ability to change passwords. The easiest way to do it But do you have to use the native passwords in Postgres? If you use ldap or pam, you could use passwords maintained somewhere else that had more strict requirements.
On Thu, 21 Dec 2006, Bruno Wolff III wrote: > > But I need an ability to change passwords. > > But do you have to use the native passwords in Postgres? If you use > ldap or pam, you could use passwords maintained somewhere else that > had more strict requirements. As I've written earlier I'd have to: - learn and understand this LDAP/PAM - how to use it, how to change passwords remotely, how to define password strength requirements, etc. - setup and maintain this services, creating another single point of failure, - document it for the future system administrator, - implement password change function in application, using secure transport. And everything I need would be very simple to do if there was an option to disable self-change of passwords for ordinary users. I'm writing here, because I have a problem with PostgreSQL, for which I can see a simple solution if PostgreSQL would have one more simple feature. I hoped I've overlooked something in the documentation and this feature is present. If not, then maybe someone else would also need this, and it'll perhaps make it to the TODO list. 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
Tomasz Ostrowski <tometzky@batory.org.pl> writes: > And everything I need would be very simple to do if there was an > option to disable self-change of passwords for ordinary users. If you are using PAM authentication, the password recorded by Postgres is irrelevant, so I'm not seeing what the problem is. regards, tom lane
On Thu, Dec 21, 2006 at 23:43:06 +0100, Tomasz Ostrowski <tometzky@batory.org.pl> wrote: > > And everything I need would be very simple to do if there was an > option to disable self-change of passwords for ordinary users. That seems like a feature not many other people are going to want. You have the soruce, and it probably wouldn't be too hard to put in a hack to do that.
On Fri, 2006-12-22 at 01:20 -0600, Bruno Wolff III wrote: > On Thu, Dec 21, 2006 at 23:43:06 +0100, > Tomasz Ostrowski <tometzky@batory.org.pl> wrote: > > > > And everything I need would be very simple to do if there was an > > option to disable self-change of passwords for ordinary users. > > That seems like a feature not many other people are going to want. > You have the soruce, and it probably wouldn't be too hard to put > in a hack to do that. I must say, that I was tempted to try. Even though I'm at all fit to. In my case, it is not because of blocking of self-password change, but on quite a similar token I need: 1. password expiration - which works on the database level in such a way, that when account/password expire, only "alter... password .." statement is allowed for such a user. *all* other SQL statements should result in "RAISE EXCEPTION... " - that is: transaction aborted. 2. I also need some additional *per*session* fields (of the "client_encoding" or "search_path" variaty) in the SET/SHOW environment. Still, I have *never* hacked the postgres, so I'm a bit reluctant here - this may be more then a little project I fear. But if I try, could you pls hint me on which source files and/or functions should I start with? Or may be the there is a "quick start for hackers" HOWTO somwhere around? BTW: One of the reasons I need the hack for password change/expiry is that neither of the two 'possible' alternatives: PAM or LDAP, do not allow for "CREATE USER ..." as per documentation in: "http://www.postgresql.org/docs/8.2/static/auth-methods.html#AUTH-LDAP" (but may be I'm missinterpreting the docs?) And LDAP, although most atractive, is further unnecesarly constrained by a depencecy on SASL. ....and putting more oil into the fire. LDAP is not actually a database - it's an '...Access Protocol', so we may choose freely the 'LDAP backend database' ... a good relational database like postgres is an option here .... and this is a real mass. So I think postgres should have more extensive *native* support for password authentication, and I'm willing to hack .... but provided my lack of experience - pointers to HOWTOs apreciated :) -- -R