pre-proposal: permissions made easier

Jeff Davis <pgsql@j-davis.com> wrote:

I'd like some brief feedback on this idea before I try to make a real
proposal.

The use case is this:

You have an application with several roles:
* admin user - owns all the objects related to that application
* normal user - INSERT/UPDATE/DELETE plus sequence usage
* read-only user - for reporting

The problem is managing all the permissions requires a lot of care, and
it's difficult to easily verify that all the permissions are set as you
expect on all of the objects. Because it's more difficult to manage, I
think many people just have a single user for all of these things.

My idea is to have a "GRANT mask":

CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo;

[syntax not meant as a real proposal yet, just for illustration]

And foo_ro would get the SELECT ON TABLE and USAGE ON SCHEMA privileges
from foo, but not any INSERT privileges. That way, you can add roles
without having to do a GRANT on each object separately. And, more
importantly, you can pretty easily observe that the privileges are what
you expect without inspecting the objects individually.

This idea is meant to be a better solution than the "GRANT ... *" that
MySQL offers.

Questions:

1. Is this a reasonable approach from a security standpoint, or is it
fundamentally flawed?

It seems to me that you're duplicating the functionality that is already
possible by using groups. i.e. grant the permissions to the group and
add users to the group as appropriate.

--
Bill Moran
http://www.potentialtech.com

On Sun, 2009-06-28 at 14:12 -0400, Bill Moran wrote:

It seems to me that you're duplicating the functionality that is already
possible by using groups. i.e. grant the permissions to the group and
add users to the group as appropriate.

Take the use case in my email. You would have to grant a specific set of
permissions to each of 3 groups for every object created.

The problem is not having many users that all share the exact same
permissions; the problem is having to assign separate permissions on a
per-object basis.

Regards,
Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:

My idea is to have a "GRANT mask":
CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo;

You haven't really explained what "foo" is here. If it's a single
object then I don't think this offers any leverage. If it's a
placeholder or class representative of some kind, then maybe, but
in that case you've entirely failed to convey the point ...

regards, tom lane

On Sun, 2009-06-28 at 14:32 -0400, Tom Lane wrote:

Jeff Davis <pgsql@j-davis.com> writes:

My idea is to have a "GRANT mask":
CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo;

You haven't really explained what "foo" is here. If it's a single
object then I don't think this offers any leverage. If it's a
placeholder or class representative of some kind, then maybe, but
in that case you've entirely failed to convey the point ...

I meant for "foo" to be a user. "foo_ro" would be the read-only version,
who has a strict subset of foo's permissions.

Regards,
Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:

On Sun, 2009-06-28 at 14:32 -0400, Tom Lane wrote:

Jeff Davis <pgsql@j-davis.com> writes:

My idea is to have a "GRANT mask":
CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo;

You haven't really explained what "foo" is here.

I meant for "foo" to be a user. "foo_ro" would be the read-only version,
who has a strict subset of foo's permissions.

I see. It seems like rather a complicated (and expensive) mechanism
for a pretty narrow use-case. It'd only help for the cases where you
could define your permissions requirements that way. I agree that
there are some such cases, but I think real-world problems tend to be
a bit more complicated than that. I fear people would soon want
exceptions to the "strict subset" rule; and once you put that in,
the conceptual simplicity disappears, as does the ability to easily
verify what the set of GRANTs is doing.

regards, tom lane

On Sun, 2009-06-28 at 14:56 -0400, Tom Lane wrote:

I meant for "foo" to be a user. "foo_ro" would be the read-only version,
who has a strict subset of foo's permissions.

I see. It seems like rather a complicated (and expensive) mechanism
for a pretty narrow use-case. It'd only help for the cases where you
could define your permissions requirements that way. I agree that
there are some such cases, but I think real-world problems tend to be
a bit more complicated than that. I fear people would soon want
exceptions to the "strict subset" rule; and once you put that in,
the conceptual simplicity disappears, as does the ability to easily
verify what the set of GRANTs is doing.

As soon as the permissions scheme gets more complicated than what I
suggest, I agree that the user is better off just using GRANTs on a
per-object basis. You could still GRANT directly to the user foo_ro --
for instance if your reporting user needs to join against some other
table -- but that could get complicated if you take it too far.

The users I'm targeting with my idea are:
* Users who have a fairly simple set of users and permissions, and who
want a simple picture of the permissions in their system for
reassurance/verification.
* Users who come from MySQL every once in a while, annoyed that we
don't support "GRANT ... *" syntax.
* Users who are savvy enough to use access control, but don't have
rigorous procedures for making DDL changes. Some of these users
depend on an ORM or similar to make DDL changes for them, and this
idea gives them a workaround.
* Users who don't currently use separate permissions, but might start
if it's simpler to do simple things.

Maybe I should shop this idea on -general and see how many people's
problems would actually be solved?

The performance issue is something to consider, but I think it would
just be an extra catalog lookup (for each level), and the users of this
feature would probably be willing to pay that cost.

Regards,
Jeff Davis

On Sun, Jun 28, 2009 at 12:52:54PM -0700, Jeff Davis wrote:

On Sun, 2009-06-28 at 14:56 -0400, Tom Lane wrote:

I meant for "foo" to be a user. "foo_ro" would be the read-only
version, who has a strict subset of foo's permissions.

I see. It seems like rather a complicated (and expensive)
mechanism for a pretty narrow use-case. It'd only help for the
cases where you could define your permissions requirements that
way. I agree that there are some such cases, but I think
real-world problems tend to be a bit more complicated than that.
I fear people would soon want exceptions to the "strict subset"
rule; and once you put that in, the conceptual simplicity
disappears, as does the ability to easily verify what the set of
GRANTs is doing.

As soon as the permissions scheme gets more complicated than what I
suggest, I agree that the user is better off just using GRANTs on a
per-object basis. You could still GRANT directly to the user foo_ro
-- for instance if your reporting user needs to join against some
other table -- but that could get complicated if you take it too
far.

The users I'm targeting with my idea are: * Users who have a fairly
simple set of users and permissions, and who want a simple picture
of the permissions in their system for reassurance/verification.

I don't know of a case that started simple and stayed there without a
lot of design up front. In other words, those who'd benefit by such a
thing are generally not those who'd want a shortcut.

* Users who come from MySQL every once in a while, annoyed that we
don't support "GRANT ... *" syntax.

I'm missing what's wrong with a wild-card GRANT syntax for this case.

* Users who are savvy enough to use access control, but don't have
rigorous procedures for making DDL changes. Some of these users
depend on an ORM or similar to make DDL changes for them, and this
idea gives them a workaround.

Such ORMs are a problem, and accommodating them only aggravates it. :)

* Users who don't currently use separate permissions, but might
start if it's simpler to do simple things.

This is a matter of education, not tools. The problem here is not
that permissions are unavailable, but that people are failing to use
them.

Maybe I should shop this idea on -general and see how many people's
problems would actually be solved?

There are a few issues at hand here, some of which could get addressed
by polling on -general:

* SQL standards compliance (clearly not a -general issue)
* Utility to current users (might be addressable on -general)
* Utility to future users (not on -general)
* Trade-offs such a solution would impose (possibly on -general

and the ever-popular

* Stuff I didn't think of ;)

The performance issue is something to consider, but I think it would
just be an extra catalog lookup (for each level), and the users of
this feature would probably be willing to pay that cost.

Where did this come up?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Sun, Jun 28, 2009 at 2:07 PM, Jeff Davis<pgsql@j-davis.com> wrote:

I'd like some brief feedback on this idea before I try to make a real
proposal.

The use case is this:

You have an application with several roles:
 * admin user - owns all the objects related to that application
 * normal user - INSERT/UPDATE/DELETE plus sequence usage
 * read-only user - for reporting

The problem is managing all the permissions requires a lot of care, and
it's difficult to easily verify that all the permissions are set as you
expect on all of the objects. Because it's more difficult to manage, I
think many people just have a single user for all of these things.

My idea is to have a "GRANT mask":

CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo;

[syntax not meant as a real proposal yet, just for illustration]

And foo_ro would get the SELECT ON TABLE and USAGE ON SCHEMA privileges
from foo, but not any INSERT privileges. That way, you can add roles
without having to do a GRANT on each object separately. And, more
importantly, you can pretty easily observe that the privileges are what
you expect without inspecting the objects individually.

This idea is meant to be a better solution than the "GRANT ... *" that
MySQL offers.

Questions:

1. Is this a reasonable approach from a security standpoint, or is it
fundamentally flawed?

Reasonable.

2. Does it violate the SQL standard?

Don't know.

3. Is it completely orthogonal to "IN ROLE" and "INHERITS", or should
they be made to work together somehow?

Maybe GRANT foo (SELECT, USAGE) TO foo_ro, meaning "grant restricted
membership in role foo to foo_ro, encompassing only the SELECT and
USAGE privileges of foo"?

I do to some degree share Tom's worry that this is an idiosyncratic
solution to a tiny subset of the problem space. On the other hand, I
have to admit I've needed to do this exact thing, so I wrote a script
to issue the necessary grant statements. Then I discovered that
whenever I created any new objects (most frequently drop and recreate
of any of the relevant views) the permissions got screwed up, so I
crontabbed the script to run every 20 minutes. This works, but it
doesn't bleed elegance.

I'm not sure what the right things to do in this area are, but I'm
glad that you (and others, like Stephen Frost) are thinking about
it...

...Robert

David Fetter wrote:

* Users who come from MySQL every once in a while, annoyed that we
don't support "GRANT ... *" syntax.

I'm missing what's wrong with a wild-card GRANT syntax for this case.

Without a major change in the way we do permissions, it will not work
prospectively. We have no way ATM to store permissions for an object
that does not currently exist.

cheers

andrew

On Sun, Jun 28, 2009 at 05:27:19PM -0400, Andrew Dunstan wrote:

David Fetter wrote:

* Users who come from MySQL every once in a while, annoyed that
we don't support "GRANT ... *" syntax.

I'm missing what's wrong with a wild-card GRANT syntax for this
case.

Without a major change in the way we do permissions, it will not
work prospectively. We have no way ATM to store permissions for an
object that does not currently exist.

There have been previous discussions of prospective permissions
changes. Are we restarting them here?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Sun, 2009-06-28 at 17:23 -0400, Robert Haas wrote:

I do to some degree share Tom's worry that this is an idiosyncratic
solution to a tiny subset of the problem space.

I share the concern. However, I don't know if it's a "tiny subset" or
not; I think we'll have to get some feedback from users to really know.

On the other hand, I
have to admit I've needed to do this exact thing, so I wrote a script
to issue the necessary grant statements. Then I discovered that
whenever I created any new objects (most frequently drop and recreate
of any of the relevant views) the permissions got screwed up, so I
crontabbed the script to run every 20 minutes. This works, but it
doesn't bleed elegance.

I have written scripts to handle some of this in the past, and it's
always awkward. That's what made me start thinking about alternatives.

Regards,
Jeff Davis

* David Fetter (david@fetter.org) wrote:

On Sun, Jun 28, 2009 at 05:27:19PM -0400, Andrew Dunstan wrote:

Without a major change in the way we do permissions, it will not
work prospectively. We have no way ATM to store permissions for an
object that does not currently exist.

There have been previous discussions of prospective permissions
changes. Are we restarting them here?

Having default permissions for new objects (something a couple of us are
working towards) would help with this situation some. I don't think the
ground Jeff's proposal would cover is entirely covered by just having
default permissions though.

Stephen

On Sun, 2009-06-28 at 14:16 -0700, David Fetter wrote:

The users I'm targeting with my idea are: * Users who have a fairly
simple set of users and permissions, and who want a simple picture
of the permissions in their system for reassurance/verification.

I don't know of a case that started simple and stayed there without a
lot of design up front. In other words, those who'd benefit by such a
thing are generally not those who'd want a shortcut.

I think that the 3 user types I outlined are a fairly reasonable
permissions scheme for a significant set of applications. I have used
that in the past, and generally speaking, I didn't need to make lots of
strange exceptions.

* Users who don't currently use separate permissions, but might
start if it's simpler to do simple things.

This is a matter of education, not tools. The problem here is not
that permissions are unavailable, but that people are failing to use
them.

I don't think education is the answer. These users aren't necessarily
ignorant, but just don't want to hack up scripts to manage permissions
for what they perceive are simple schemes.

If the user imagines a well-defined but simple scheme, and it takes a
lot of awkward scripts to accomplish it, I think we've missed something.
A "reporting user" seems like a perfectly normal kind of user to create,
and yet it's very awkward to do.

The performance issue is something to consider, but I think it would
just be an extra catalog lookup (for each level), and the users of
this feature would probably be willing to pay that cost.

Where did this come up?

Tom mentioned that it might be expensive to check permissions, which I
assume was due to the extra catalog lookups required. I don't think it's
a major concern, nor would it affect normal permissions checks, unless I
missed something.

Regards,
Jeff Davis

On Sun, 2009-06-28 at 14:38 -0700, David Fetter wrote:

There have been previous discussions of prospective permissions
changes. Are we restarting them here?

I don't remember seeing anything in those discussions that really
materialized. Can you point me to something that you think is a better
alternative than my idea?

Regards,
Jeff Davis

On Sun, 2009-06-28 at 18:03 -0400, Stephen Frost wrote:

* David Fetter (david@fetter.org) wrote:

On Sun, Jun 28, 2009 at 05:27:19PM -0400, Andrew Dunstan wrote:

Without a major change in the way we do permissions, it will not
work prospectively. We have no way ATM to store permissions for an
object that does not currently exist.

There have been previous discussions of prospective permissions
changes. Are we restarting them here?

Having default permissions for new objects (something a couple of us are
working towards) would help with this situation some. I don't think the
ground Jeff's proposal would cover is entirely covered by just having
default permissions though.

One case that it would not cover is creating new roles that you would
like to have access to existing objects. Defaults may be useful
independently, though, so I think the proposals are overlapping, but
generally different.

Regards,
Jeff Davis

David Fetter wrote:

On Sun, Jun 28, 2009 at 05:27:19PM -0400, Andrew Dunstan wrote:

David Fetter wrote:

* Users who come from MySQL every once in a while, annoyed that
we don't support "GRANT ... *" syntax.

I'm missing what's wrong with a wild-card GRANT syntax for this
case.

Without a major change in the way we do permissions, it will not
work prospectively. We have no way ATM to store permissions for an
object that does not currently exist.

There have been previous discussions of prospective permissions
changes. Are we restarting them here?

*shrug*

It's not on the TODO list. I recall it being raised from time to time
but I certainly don't recall a consensus that it should be done, nor
how, so if you're implying that such a thing is a settled decision I
suspect you're not entirely correct. Of course, my memory has been known
to have errors ...

cheers

andrew

On Sun, Jun 28, 2009 at 06:28:32PM -0400, Andrew Dunstan wrote:

David Fetter wrote:

On Sun, Jun 28, 2009 at 05:27:19PM -0400, Andrew Dunstan wrote:

David Fetter wrote:

* Users who come from MySQL every once in a while, annoyed that
we don't support "GRANT ... *" syntax.

I'm missing what's wrong with a wild-card GRANT syntax for this
case.

Without a major change in the way we do permissions, it will not
work prospectively. We have no way ATM to store permissions for an
object that does not currently exist.

There have been previous discussions of prospective permissions
changes. Are we restarting them here?

*shrug*

It's not on the TODO list. I recall it being raised from time to time
but I certainly don't recall a consensus that it should be done, nor
how,

That was my recollection, too.

so if you're implying that such a thing is a settled decision I
suspect you're not entirely correct.

By no means.

Of course, my memory has been known to have errors ...

Same with mine ;)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Andrew Dunstan <andrew@dunslane.net> writes:

David Fetter wrote:

There have been previous discussions of prospective permissions
changes. Are we restarting them here?

It's not on the TODO list. I recall it being raised from time to time
but I certainly don't recall a consensus that it should be done, nor
how, so if you're implying that such a thing is a settled decision I
suspect you're not entirely correct. Of course, my memory has been known
to have errors ...

I think there's widespread agreement that SQL permissions are a pain in
the neck to manage. We haven't got a consensus on a solution to that,
but looking at possibilities is certainly reasonable.

Jeff's idea does amount to granting prospective permissions in one
sense. If you (in the future) grant some permissions to role foo,
then role foo_ro would automatically get some of those permissions too.
I think it has to be looked at in comparison to more general
prospective-permissions schemes; it clearly doesn't do everything you
could wish for in that line, and so we have to ask whether there'd be
much use-case left for it if we do implement something more general.
It also seems to me that a lot of the potential objections are shared
with more general schemes --- in particular, "ooops, I forgot this was
in place and indirectly granted some permissions I shouldn't have"...

regards, tom lane

On Mon, 2009-06-29 at 12:55 -0400, Tom Lane wrote:

I think it has to be looked at in comparison to more general
prospective-permissions schemes;

When I searched google for "prospective permissions", all I found were
links to messages in this thread ;)

Can you refer me to a general prospective-permissions scheme that is
more widely accepted? Being more widely accepted also has the benefit
that users will feel more comfortable with the behavior.

Regards,
Jeff Davis