Re: WITH SYSID feature dropped

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461