Re: Copying Permissions

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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Tue, Nov 8, 2016 at 9:48 AM, Stephen Frost <sfrost(at)snowman(dot)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
>