Copying Permissions

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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;
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@sss.pgh.pa.us> 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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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