Re: Copying Permissions
| От | Corey Huinker |
|---|---|
| Тема | Re: Copying Permissions |
| Дата | |
| Msg-id | CADkLM=e4hJcCiN1WBKjc0668KtLeUHJgLvkN+KxCdTXnfqf9Pw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Copying Permissions (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: Copying Permissions
Re: Copying Permissions |
| Список | pgsql-hackers |
> 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
В списке pgsql-hackers по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: postgres_fdw : altering foreign table not invalidating prepare statement execution plan.