From: Jim C. Nasby <jnasby@pervasive.com>
To: Donald Fraser <postgres@kiwi-fraser.net>
Cc: [ADMIN] <pgsql-admin@postgresql.org>
Subject: Re: WITH SYSID feature dropped
Date: Wed, 21 Dec 2005 16:12:07 -0600
Message-ID: <20051221221207.GI72143@pervasive.com> (raw)
In-Reply-To: <016201c60649$c6254b70$0264a8c0@demolish1>
References: <011501c60578$47642e40$0264a8c0@demolish1>
<29845.1135094224@sss.pgh.pa.us>
<012201c60583$4a832080$0264a8c0@demolish1>
<525.1135097989@sss.pgh.pa.us>
<016201c60649$c6254b70$0264a8c0@demolish1>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: jnasby@pervasive.com, postgres@kiwi-fraser.net
Subject: Re: WITH SYSID feature dropped
In-Reply-To: <20051221221207.GI72143@pervasive.com>
References: <011501c60578$47642e40$0264a8c0@demolish1> <29845.1135094224@sss.pgh.pa.us> <012201c60583$4a832080$0264a8c0@demolish1> <525.1135097989@sss.pgh.pa.us> <016201c60649$c6254b70$0264a8c0@demolish1> <20051221221207.GI72143@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox