Thread: WITH SYSID feature dropped

"Donald Fraser" <postgres@kiwi-fraser.net> writes:
> 1) Was there a good reason for dropping this feature?

Users/groups are identified by OIDs now, not by sysids.

The original motivation for allowing manual assignment of userids was to
allow recovery if a user was dropped while still owning objects --- you
could recreate the user with the same sysid and then the objects' owner
fields would match up.  8.1 now does reference counting and prevents you
from dropping users who still own anything, so AFAICS that use-case is
gone.  Do you have another one?

            regards, tom lane

----- Original Message -----
From: "Tom Lane"


> "Donald Fraser"  writes:
> > 1) Was there a good reason for dropping this feature?

> Users/groups are identified by OIDs now, not by sysids.
>
> The original motivation for allowing manual assignment of userids was to
> allow recovery if a user was dropped while still owning objects --- you
> could recreate the user with the same sysid and then the objects' owner
> fields would match up.  8.1 now does reference counting and prevents you
> from dropping users who still own anything, so AFAICS that use-case is
> gone.  Do you have another one?

Yes we do have another use.
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.
Obviously we have the information in our table to go from a postgresql user
name back to the original SYSID but that process is messy and requires
string manipulation etc.
The ability to controll the SYSID, now labelled OID, basically allows for a
more elaborate third-party managment of postgresql users.
It is not impossible to use the postgresql user name to map into the table
we have created but it would be nicer and cleaner to be able to continue
using the OID.

I take it then that the patching of that feature would cause problems
because the OID is controlled by postgreql and we could therefore be trying
to create a user with an OID that could already be in use.

Regards
Donald Fraser

"Donald Fraser" <postgres@kiwi-fraser.net> writes:
> Yes we do have another use.
> 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.

It's not apparent to me why you have to control the userID in order to
have an auxiliary table defining a user.  You could do something like
keeping the currently assigned OID in the aux table, setting the
field to null or zero if the user doesn't currently exist in pg_authid.

> I take it then that the patching of that feature would cause problems
> because the OID is controlled by postgreql and we could therefore be trying
> to create a user with an OID that could already be in use.

You ran that risk already with the SYSID scheme, no?

            regards, tom lane

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.

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?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

----- Original Message -----
From: "Tom Lane"


> It's not apparent to me why you have to control the userID in order to
> have an auxiliary table defining a user.  You could do something like

When ever a postgresql user does anything to a record in a table we
have a trigger function to check at the beginning of each transaction
whether they currently have sufficient privallages to perform what ever
action they are attempting. (These are things that go beyond what
GROUPS and ROLES can achieve).
In order to do this effeciently we used the session id of the postgresql
user to look up our "user" record for the check and that is where the
link originally arose from.
At the time the documentation informed that we could set the SYSID
ourselves, and by doing that we created the logical link between our
primary key and a postgresql user - it seemed the most logical and
effecient means of managing things at the time.

> keeping the currently assigned OID in the aux table, setting the
> field to null or zero if the user doesn't currently exist in pg_authid.

Our "user" table stores information about the user before, during and
after the user is a postgresql user so that we can effectively hold an
audit of who has created, edited, deleted what and when. Therefore
the primary key (SYSID) in this table, is the foreign key in many
other tables that users can access for record creation and
modification. Therefore the primary key cannot be null when the
user is no longer a postgresql user and idealy we don't want it
changing as this could potentially mean 100's if not 1000's of
foreign key cascading updates.

Our "user" table holds security privilage and access rights
information which can change at the drop of a hat. Our approach to
managing the mapping of our privillages to postgresql GROUPS
etc has been one of brute force. It is much easier to DROP a user
and recreate them when their access rights change, as apposed to
figuring out what GROUPS they currently belong to and what
GROUPS they should now belong to. Therefore under the new
scheme of things dropping and recreating a new user would cause
a change in the OID and this is not desireable as described above.

Yes we could create a new column that mapps to the postgresql
OID, but the management of this seems like a lot more work
especially since the creation of postgresql users, controlled by our
"user" table, are handled in AFTER trigger events.

So going back to my original questions, you have answered them,
OIDs are now used because 8.1 now handles dependency
information on user owned objects.

Hence the bottom line for us is that we can no longer use the old
SYSID approach  for mapping a postgresql user to the same user
in our "user" table.
Its a case of we have heavy reliance of the internal functions:
GetSessionUserId() and GetUserNameFromId()
Now we will simpy have to create our own versions of these
functions that do the correct mapping of the postgresql user to
the same user in our "user" table.

> > I take it then that the patching of that feature would cause problems
> > because the OID is controlled by postgreql and we could therefore be
trying
> > to create a user with an OID that could already be in use.

> You ran that risk already with the SYSID scheme, no?

So far we haven't had a problem with it - we reserved the first 20 numbers
for "system" users (e.g. postgres = 1) and started our sequence numbering
from 21. It is a long time since I looked at that code (7.1) and my
understanding at the time was that SYSID was only used by postgresql for
identifying a  postgresql user by id rather than by name and was only
created and  stored in pg_shadow.

A "nice" solution for backward compatibility would have been to put the
original SYSID column, formally from pg_shadow table, into the
pg_authid table. Its only an integer value, so hardly takes up much
space, and leave the creation logic of this column value as before.
Then all you needed to do was add your own functions that use the
OID side of things.
For example GetSessionUserId() and GetUserNameFromId()
could have been left as is and then create very similar functions for use
on the OID side of things e.g.
GetSessionUserOID() and GetUserNameFromOID()

Could have, would have, should have...
but nobody thought of it and probably a bit late now though....

I hope I haven't sounded negative, I do appreciate all the work that goes
on from the postgreql team and I think the new features in 8.1 far out
way the minor inconveniences that it will cause us in upgrading....

Keep up the good work,
and cheers for the feed back.

Regards
Donald Fraser

"Donald Fraser" <postgres@kiwi-fraser.net> writes:
> From: "Tom Lane"
>> keeping the currently assigned OID in the aux table, setting the
>> field to null or zero if the user doesn't currently exist in pg_authid.

> Our "user" table stores information about the user before, during and
> after the user is a postgresql user so that we can effectively hold an
> audit of who has created, edited, deleted what and when. Therefore
> the primary key (SYSID) in this table, is the foreign key in many
> other tables that users can access for record creation and
> modification. Therefore the primary key cannot be null when the
> user is no longer a postgresql user and idealy we don't want it
> changing as this could potentially mean 100's if not 1000's of
> foreign key cascading updates.

I didn't say it should be the primary key.  You can still use a notional
sysid as your pkey, you just need an additional column that records the
current OID (if any) of the user as a PG user.  This is essentially the
reverse of your proposal to add an unused sysid column to pg_authid ...

            regards, tom lane

----- 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

On Wed, Dec 21, 2005 at 04:15:35PM -0000, Donald Fraser wrote:
> Our "user" table holds security privilege and access rights
> information which can change at the drop of a hat. Our approach to
> managing the mapping of our privileges to PostgreSQL GROUPS
> etc has been one of brute force. It is much easier to DROP a user
> and recreate them when their access rights change, as apposed to
> figuring out what GROUPS they currently belong to and what
> GROUPS they should now belong to. Therefore under the new
> scheme of things dropping and recreating a new user would cause
> a change in the OID and this is not desireable as described above.

I think it would be a useful addition to PostgreSQL to have the ability
to explicitly set a set of permissions on a role. IE, be able to
atomically define exactly what permissions/roles a role has. This would
effectively take the place of a set of GRANT and DROP statements.

I realize that you could probably get the same effect by
BEGIN
REVOKE * FROM ROLE
GRANT ... TO ROLE
GRANT ... TO ROLE
...
COMMIT;

But that's rather ugly, especially since REVOKE * doesn't actually
exist. Of course you could probably codify all that into a function, but
I suspect it would still be much uglier than supporting it directly in
the grammar.

BTW, from http://www.postgresql.org/docs/8.1/static/sql-alteruser.html

"ALTER USER is now an alias for ALTER ROLE, which see for more
information."

Someone want to commit a quick grammar fix to that? Maybe "which you
should see for more..."?

> Could have, would have, should have...
> but nobody thought of it and probably a bit late now though....

BTW, this is why it's good to keep an eye on things that are being
changed and if possible test on HEAD occasionally. Doesn't guarantee
that OID support would have been maintained, but it's always good to
know how people are using PostgreSQL, especially when it comes to the
more 'off the wall' features.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

On Wed, Dec 21, 2005 at 04:35:00PM -0000, Donald Fraser wrote:
> 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)

Can you release any of that code under a BSD license? Some of those
should arguably be built-in. If nothing else, it would be good reference
code for others. Though, you can set a 'valid until' limit on roles
right now, but I suppose that's not exactly the same as what you have.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> "ALTER USER is now an alias for ALTER ROLE, which see for more
> information."

> Someone want to commit a quick grammar fix to that? Maybe "which you
> should see for more..."?

It's perfectly good English as-is.

            regards, tom lane

> On Wed, Dec 21, 2005 at 04:35:00PM -0000, Donald Fraser wrote:
> > 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)

From: "Jim C. Nasby"
> Can you release any of that code under a BSD license? Some of those
> should arguably be built-in. If nothing else, it would be good reference
> code for others. Though, you can set a 'valid until' limit on roles
> right now, but I suppose that's not exactly the same as what you have.

I was afraid someone might ask that question...
The short answer is no :-(
The long answer is there is too much of our company specific code embedded
within it, which would make for a lot of changes and un-tested code if I
were to sit down and strip all the unnecessary gook out. Additionally there
are implied protocols to adhere to (which means writing additional
documentation), a java automation engine, which in turn uses a modified
postgresql JDBC driver, of which is only at the 7.4 version level. To top it
off the code now doesn't work with version 8.1.x.
As I am a one man band, I have problems keeping on top of everything. I
would on the first instance like to merge some of my JDBC driver
enhancements so that I could then release to the public my automation engine
which would then allow me to release further code... As we are still running
postgresql 7.4 I will have to pull my finger out and get some of these
issues sorted so that we can upgrade to at least 8.0. So may be sometime
next year you'll see some of it in the public domain.... No promises but I
have released other stuff in the past - for example
(http://gborg.postgresql.org/project/citext/projdisplay.php)

Regards
Donald Fraser

* Donald Fraser (postgres@kiwi-fraser.net) wrote:
> > On Wed, Dec 21, 2005 at 04:35:00PM -0000, Donald Fraser wrote:
> > > 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)
>
> From: "Jim C. Nasby"
> > Can you release any of that code under a BSD license? Some of those
> > should arguably be built-in. If nothing else, it would be good reference
> > code for others. Though, you can set a 'valid until' limit on roles
> > right now, but I suppose that's not exactly the same as what you have.

I definitely agree that it'd be nice to have some of these built in.  On
the other hand, PAM can do much of this but that requires appropriate
access to the various system files.  SASL is another thought but I'm not
sure if it can do even most of that (without having it using PAM
underneath which I understand is rather ugly).

I'd really like to get rid of the rather hackish PAM solution I've got
right now and so I wouldn't mind spending time looking into implementing
some of these things as built-ins.  The part that makes me nervous about
that is if it would require protocol changes and/or additional library
dependencies that some might not care for.

    Thanks,

        Stephen