Re: WITH SYSID feature dropped

----- Original Message -----
From: "Alvaro Herrera"

> Donald Fraser wrote:
>
> > We developed and have been using since 7.1, and currently running 7.4,
> > bespoke client / database software. The ability to manage users and
security
> > was of high priority and we therefore developed a much more elaborate
user
> > definition where by the information about users was held in our own
tables
> > and we could create a postgresql database user from this table at any
time.
> > To simplify things we controlled the SYSID and used this as the key for
> > mapping a postgresql user to a user defined in our table.
>
> Why not do it the other way around? Create the Postgres user first,
> grab its Oid, then use that as primary key in your user table.

Read my previous posting to answer that one.
The bottom line is there are several solutions to work around our problem:
1) Add a new column to our table that will hold the OID of a postgresql
user.
2) Use the postgresql user name to map to a record in our table (the easy
solution).

I was really just asking, in the first instance, why the changes had taken
place - I appreciate everyones thoughts and inputs for help on our problem.

> On the other hand, it's possible that the new role management in 8.1,
> which has brought much more elaborate privilege handling, could have
> rendered your user handling unnecessary. Have you considered exploring
> that?

Roles are a great improvement to postgresql, but we also talking security
issues here:
Our module controls things like:
1) how long before a user must change their password (daily, weekly, monthly
etc)
2) password rotation - for example a user cannot use the same password
within the last three changes
3) Password semantics: length of password, dictionary word checks and so
on...
4) Restricting a users rights until they have changed their password (they
cannot use the system until they change the password set by the
administrator)

That should give you an insight into why we created such a module as those
are just some of the things it does...

Thanks again for the input.
Regards,
Donald Fraser