Обсуждение: Copying Permissions
Craig's post yesterday about exposing syntax for disabling indexes reminded me of another feature I think we're lacking in areas where we have to do table management.
The issue is to create a *something* that has the exact permissions of another *something*. Usually it's creating a table related to (but not yet inheriting) a parent, but it could also be to drop and recreate a *something*, making sure it has the same permissions it had before.
BEGIN;
CREATE VIEW dummy AS SELECT 1::text as dummy;
UPDATE pg_class
SET relacl = ( SELECT relacl FROM pg_class
WHERE oid = 'foo'::regclass)
WHERE oid = 'dummy'::regclass;
DROP VIEW foo;
CREATE VIEW foo AS <insert new view definition here>;
UPDATE pg_class
SET relacl = ( SELECT relacl FROM pg_class
WHERE oid = 'dummy'::regclass)
WHERE oid = 'foo'::regclass;
END;
UPDATE pg_class
SET relacl = ( SELECT relacl FROM pg_class
WHERE oid = 'dummy'::regclass)
WHERE oid = 'foo'::regclass;
END;
I suppose I could have learned how to store a relacl as a scalar and just saved it to a variable, but even then I'd have to twiddle with (and have the permissions to twiddle with) pg_class.
So it'd be nice to:
ALTER TABLE bar SET PERMISSIONS FROM foo;
or maybe even
GRANT SAME PERMISSIONS ON VIEW bar FROM foo;
Thoughts?
or maybe even
GRANT SAME PERMISSIONS ON VIEW bar FROM foo;
Thoughts?
Corey, * Corey Huinker (corey.huinker@gmail.com) wrote: > Craig's post yesterday about exposing syntax for disabling indexes reminded > me of another feature I think we're lacking in areas where we have to do > table management. > > The issue is to create a *something* that has the exact permissions of > another *something*. Usually it's creating a table related to (but not yet > inheriting) a parent, but it could also be to drop and recreate a > *something*, making sure it has the same permissions it had before. Agreed, that seems like a very sensible use-case. > BEGIN; > > CREATE VIEW dummy AS SELECT 1::text as dummy; > > UPDATE pg_class > SET relacl = ( SELECT relacl FROM pg_class > WHERE oid = 'foo'::regclass) > WHERE oid = 'dummy'::regclass; Yikes, let's not suggest folks go updating catalog tables, ever, please. :) > So it'd be nice to: > ALTER TABLE bar SET PERMISSIONS FROM foo; > or maybe even > GRANT SAME PERMISSIONS ON VIEW bar FROM foo; > > Thoughts? I like the general idea and suspect many others would also, but there's two big questions: Should we have a way for users to define an ACL ala the ALTER DEFAULT PERMISSIONS approach where the ACL is not attached to any particular object and is, instead, something which can be assigned to a table. Further, if we support that, what happens if that is changed later? I could see use-cases for both having that change impact all objects and for not having that happen. Second, as always, what's the syntax going to actually be? I don't think GRANT SAME PERMISSIONS is going to work out too well in the parser, and it seems a bit grotty to me anyway. I do think this should be associated with GRANT rather than ALTER TABLE- GRANT is what we use for managing privileges on an object. Thanks! Stephen
On Tue, Nov 8, 2016 at 9:48 AM, Stephen Frost <sfrost@snowman.net> wrote: > Second, as always, what's the syntax going to actually be? I don't > think GRANT SAME PERMISSIONS is going to work out too well in the > parser, and it seems a bit grotty to me anyway. I do think this should > be associated with GRANT rather than ALTER TABLE- GRANT is what we use > for managing privileges on an object. One thing to think about is that GRANT sort of implies adding privileges, but this operation would both add and remove privileges as necessary. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Nov 8, 2016 at 9:48 AM, Stephen Frost <sfrost@snowman.net> wrote:
>> Second, as always, what's the syntax going to actually be? I don't
>> think GRANT SAME PERMISSIONS is going to work out too well in the
>> parser, and it seems a bit grotty to me anyway. I do think this should
>> be associated with GRANT rather than ALTER TABLE- GRANT is what we use
>> for managing privileges on an object.
> One thing to think about is that GRANT sort of implies adding
> privileges, but this operation would both add and remove privileges as
> necessary.
Other things to think about:
1. If you can GRANT x, that generally implies that you can REVOKE x.
What would REVOKE SAME PERMISSIONS mean?
2. The GRANT/REVOKE syntax is largely governed by the SQL standard.
We risk getting boxed in by picking something that will conflict
with future spec extensions in this area.
On the whole, I suspect some sort of "ALTER TABLE x COPY PERMISSIONS
FROM y" syntax would be better.
BTW, please specify what the grantor of the resulting permissions
would be. I rather doubt that it should involve blindly copying
the source ACL if the user doing the COPY is not the original
grantor --- that feels way too much like a security problem
waiting to happen.
regards, tom lane
> SET relacl = ( SELECT relacl FROM pg_class
> WHERE oid = 'foo'::regclass)
> WHERE oid = 'dummy'::regclass;
Yikes, let's not suggest folks go updating catalog tables, ever, please.
:)
Glad you find that as icky as I do.
Should we have a way for users to define an ACL ala the ALTER DEFAULT
PERMISSIONS approach where the ACL is not attached to any particular
object and is, instead, something which can be assigned to a table.
Further, if we support that, what happens if that is changed later? I
could see use-cases for both having that change impact all objects and
for not having that happen.
I think allowing users to receive and send serialized relacl values (which is what I *think* you're asking about here) is only slightly less icky, and presents a backward compatibility issue. Those issues go away if the ACL is contained in an existing object, or exists only for the life of a statement. In which case I think you're suggesting something like this:
BEGIN;GATHER TEMPORARY DEFAULT PRIVILEGES FROM view_name;DROP VIEW view_name;CREATE VIEW view_name as ...;
COMMIT;
Which would solve the problem provided I don't want to drop dependent objects with different permissions. Once I have to do a DROP a;DROP b;CREATE b;CREATE a; and the permissions of A and B don't match, I'm sunk.
Second, as always, what's the syntax going to actually be? I don't
think GRANT SAME PERMISSIONS is going to work out too well in the
parser, and it seems a bit grotty to me anyway. I do think this should
be associated with GRANT rather than ALTER TABLE- GRANT is what we use
for managing privileges on an object.
So GRANT / REVOKE are a bit weird in this case, because they operate on an object as it pertains to 1+ roles. Here are adding in a reference to another like-typed object, and the roles aren't even mentioned.
Moreover, the operation itself would potentially do both GRANTing and REVOKEing, depending on what the target objects permissions were relative to the source object. So there's situations where an object could end up with fewer permissions after a GRANT than it had before.
Or...we could instead decide that the GRANT only adds permissions, never revokes, and if the user wants an exact copy then it's up to them to first revoke all privs on the new object before the GRANT. Either way, the syntax might be:
BEGIN;
CREATE TEMPORARY VIEW dummy AS SELECT 1 AS dummy_col;
GRANT ALL PRIVILEGES ON VIEW dummy FROM my_view;
GRANT ALL PRIVILEGES ON VIEW dummy FROM my_view;
DROP VIEW my_view;
CREATE VIEW my_view ...;
REVOKE ALL PRIVILEGES on my_view FROM public ; /* repeat for every other role you can think of ... ick */
GRANT ALL PRIVILEGES ON VIEW my_view FROM dummy;
COMMIT;
GRANT ALL PRIVILEGES ON VIEW my_view FROM dummy;
COMMIT;
That's still clumsy, but at least we've avoided having a user touch pg_class.relacl.
So after all that wrangling, i got around to where Tom got rather quickly: ALTER TABLE x COPY PERMISSIONS FROM y;
If we're worried about the ALTER-person's authority to GRANT things already granted to table y, then I suppose the best thing to do would be this:
1. Strip all permissions from x (empty relacl), so the ALTER-ing person pretty much has to be the owner.
2. Iterate over the permissions in the relacl of y, and attempt to grant them (as the ALTER-person) one by one, issuing NOTICE or WARNING whenever a grant fails.
3. The operation is judged to have succeeded if at least one permission is granted, or NO grants failed (i.e. there was nothing to grant).
I can see obvious problems with copying grants from one user to another on an existing object not of the user's creation, but in this case the ALTER-ing person already has ownership (or close) of the object, they're not compromising any previously existing object. Still, I'm sure somebody could dream up a priv escalation somehow, hence my iterate-and-spaghetti-test the individual grants approach.
On Wed, Nov 9, 2016 at 1:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, Nov 8, 2016 at 9:48 AM, Stephen Frost <sfrost@snowman.net> wrote:
>> Second, as always, what's the syntax going to actually be? I don't
>> think GRANT SAME PERMISSIONS is going to work out too well in the
>> parser, and it seems a bit grotty to me anyway. I do think this should
>> be associated with GRANT rather than ALTER TABLE- GRANT is what we use
>> for managing privileges on an object.
> One thing to think about is that GRANT sort of implies adding
> privileges, but this operation would both add and remove privileges as
> necessary.
Other things to think about:
1. If you can GRANT x, that generally implies that you can REVOKE x.
What would REVOKE SAME PERMISSIONS mean?
2. The GRANT/REVOKE syntax is largely governed by the SQL standard.
We risk getting boxed in by picking something that will conflict
with future spec extensions in this area.
On the whole, I suspect some sort of "ALTER TABLE x COPY PERMISSIONS
FROM y" syntax would be better.
BTW, please specify what the grantor of the resulting permissions
would be. I rather doubt that it should involve blindly copying
the source ACL if the user doing the COPY is not the original
grantor --- that feels way too much like a security problem
waiting to happen.
regards, tom lane
On Wed, Nov 9, 2016 at 2:54 PM, Corey Huinker <corey.huinker@gmail.com> wrote: > 3. The operation is judged to have succeeded if at least one permission is > granted, or NO grants failed (i.e. there was nothing to grant). Allow me to be skeptical. If a user types INSERT INTO blah VALUES (...), (...), (...) should we change the system to report success if at least 1 of the 3 rows got successfully inserted? I bet that wouldn't go over well. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert, * Robert Haas (robertmhaas@gmail.com) wrote: > On Wed, Nov 9, 2016 at 2:54 PM, Corey Huinker <corey.huinker@gmail.com> wrote: > > 3. The operation is judged to have succeeded if at least one permission is > > granted, or NO grants failed (i.e. there was nothing to grant). > > Allow me to be skeptical. If a user types INSERT INTO blah VALUES > (...), (...), (...) should we change the system to report success if > at least 1 of the 3 rows got successfully inserted? I bet that > wouldn't go over well. To this point, we already do this for GRANT and REVOKE, so if this is going to be based around those commands then it should perform in a similar manner. Of course, that behavior is required for SQL spec and as Tom points out that might be reason enough to avoid actually tying this in with GRANT/REVOKE since we could end up in a tough spot if the SQL committee decides to take a different direction than what we use (or use the keywords we pick for something else). Thanks! Stephen
Corey, * Corey Huinker (corey.huinker@gmail.com) wrote: > I think allowing users to receive and send serialized relacl values (which > is what I *think* you're asking about here) is only slightly less icky, and That isn't actually what I was suggesting. > presents a backward compatibility issue. Those issues go away if the ACL is > contained in an existing object, or exists only for the life of a > statement. In which case I think you're suggesting something like this: Right- an existing 'object'. What I was suggesting is that we have, for lack of a better word, 'profiles'- which are essentially complete, named, aclitem arrays. That way, we aren't tying this to an existing object in the system but rather making it a top-level object on its own, in a manner akin to how the default privileges system contains acitem arrays which are not associated with an object. Consider: CREATE PROFILE joe_select GRANT SELECT ON TABLES TO joe; ALTER DEFAULT PRIVILEGES IN SCHEMA joes PROFILE joe_select; ALTER TABLE joe SET PROFILE joe_select; etc. The other question this brings up, as I think I mentioned before, is this: is this a one-time copy of that 'profile'? What if the profile is later changed? For my 2c, I kind of like the idea that an update to the profile would cause the privileges to be effectivly changed for all objects using that profile, though that may mean we end up with a different kind of implementation than what you proposed of just copying the relacl. Generally speaking, setting a profile should be the purview of the owner of the object, imv. We would also have to consider if objects can have both a profile and independently granted accesses. I'm thinking the answer to that is probably 'yes'. Thanks! Stephen