Re: pre-proposal: permissions made easier

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?

2. Does it violate the SQL standard?

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

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)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(at)j-davis(dot)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(at)j-davis(dot)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(at)j-davis(dot)com> writes:
> On Sun, 2009-06-28 at 14:32 -0400, Tom Lane wrote:
>> Jeff Davis <pgsql(at)j-davis(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)j-davis(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)fetter(dot)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(at)fetter(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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

Andrew Dunstan <andrew(at)dunslane(dot)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

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> Can you refer me to a general prospective-permissions scheme that is
> more widely accepted?

Well, the point of my post was that nothing's gotten to the point of
being "widely accepted". But there are people working on a "default
ACLs" scheme that would cover some of that territory.
http://wiki.postgresql.org/wiki/DefaultACL

regards, tom lane

Jeff Davis wrote:
> 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.
>
>
>

Think of MySQL's wildcard permissions. They apply to any object whether
that object is created before or after the rule is set, AIUI. That means
the wildcard pattern is applied at the time the permission rule is
referenced, rather than when the rule is created, thus applying it
prospectively.

It's a feature many users would like to have, although, as Tom rightly
points out, it can be a bit of a footgun if used carelessly.

cheers

andrew

All,

First, let me talk about the problem: it's been my observation that the
majority of users, including public commercial web sites, which I run
into in the field do not employ permissions in any useful way to protect
their data. An awful lot of these applications are running as the
superuser or the database owner, partly because the company can't deal
with object permissions management, especially when the application is
under continuous development.

The pgAdmin widget doesn't really help much in this respect.

I want to avoid anything which requires an additional permissions check
or any other check at runtime. Instead, we need two tools:

1) ALTER SCHEMA SET DEFAULT PRIVILEGES statements which sets default
permissions, by ROLE and object type, on new objects.

2) a statement to set privs on all existing objects by type and role
within a schema.

These two tools would make it vastly easier for admins to manage ROLE
privileges without needing any additional runtime checks or limiting
flexibility in object permissions assignment. Further, they would make
it very simple to build the kind of very simple ROLE-based permissions
management Jeff is talking about on top of it (which is, BTW, very
popular; it's one of MSSQL's major selling points to small businesses).

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

On Mon, 2009-06-29 at 10:52 -0700, Josh Berkus wrote:
> 1) ALTER SCHEMA SET DEFAULT PRIVILEGES statements which sets default
> permissions, by ROLE and object type, on new objects.
>
> 2) a statement to set privs on all existing objects by type and role
> within a schema.

I don't see why either of these things should be properties of the
schema. It seems to make much more sense for these defaults to be a
property of the user who creates the objects.

If #1 and #2 are both implemented as properties of the user, I think
that solves the use case I brought up. It would still be difficult to
see the overall scheme at a glance, but I don't think that's a major
problem.

Regards,
Jeff Davis

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Mon, 2009-06-29 at 10:52 -0700, Josh Berkus wrote:
>> 1) ALTER SCHEMA SET DEFAULT PRIVILEGES statements which sets default
>> permissions, by ROLE and object type, on new objects.
>>
>> 2) a statement to set privs on all existing objects by type and role
>> within a schema.

> I don't see why either of these things should be properties of the
> schema. It seems to make much more sense for these defaults to be a
> property of the user who creates the objects.

That seems fairly backwards to me. I agree that tying it to schemas
is a bit less flexible than one could wish, but that doesn't make
attaching it to the user the right thing.

regards, tom lane

Jeff,

> I don't see why either of these things should be properties of the
> schema. It seems to make much more sense for these defaults to be a
> property of the user who creates the objects.

The main reason is existing practice. Currently, most applications I
see in the field which bother with having several ROLES have all
database objects belonging to one ROLE ("db_owner"). So for most people
setting permissions for all objects belonging to a specific user would
amount to setting permissions for all objects of that type in a given
database.

There's also the fact that SCHEMAs currently have their own visibility
rules and permissions, which seems to me to dovetail nicely with the ACLs.

This is, of course, assuming that we are talking about setting
permissions in saved objects, that is, all the object belonging to a
particular user.

The approach I could see as valuable in vastly simplyfying things would
be to set the permission on the user regardless of object properties;
that is, the user is defined as WITH SELECT, INSERT, UPDATE ON ALL
TABLES. These user permissions would supercede any object permissions
for that role.

This would make DBA's lives vastly simpler and make them more likely to
use permissions. But would it actually benefit security?

The problem I see with this approach is that in 95% of the applications
I run across there are a few tables which really need to be "locked
down" and restricted from most user access (maybe accessed only by an
SRF). In large development shops where more than one person has their
hands on the DB, I can easily see one developer accidentally bypassing
object-level security set up by another DBA through this mechanism.

The second, and bigger problem I can see is that this opens a whole new
set of security holes by allowing an end-run around the existing access
control structure with attackers can try to exploit.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> The second, and bigger problem I can see is that this opens a whole new
> set of security holes by allowing an end-run around the existing access
> control structure with attackers can try to exploit.

Yeah. I'm very concerned about any scheme that invents additional
sources of permissions that aren't visible in the object's own ACL list.
Even if it's secure in its own terms, it'll blindside people and
programs who are used to the existing ways of doing things.

From what I recall of prior discussions, there is rough consensus that
the two types of facilities you mentioned (setting up default ACLs to be
applied at creation of objects created later, and providing a way to
change multiple objects' permissions with one GRANT) are desirable,
though there is plenty of argument about the details. Neither of these
result in creating any new sources of permissions --- a given object's
ACL is still the whole truth.

regards, tom lane

Tom,

> From what I recall of prior discussions, there is rough consensus that
> the two types of facilities you mentioned (setting up default ACLs to be
> applied at creation of objects created later, and providing a way to
> change multiple objects' permissions with one GRANT) are desirable,
> though there is plenty of argument about the details. Neither of these
> result in creating any new sources of permissions --- a given object's
> ACL is still the whole truth.

yeah, that's why I've been working on that approach. It doesn't
simplify things as much as some DBAs might want, but it's the most
side-effect-free approach.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

On Mon, Jun 29, 2009 at 7:41 PM, Josh Berkus<josh(at)agliodbs(dot)com> wrote:
> The main reason is existing practice.

I haven't followed the entire conversation so i'm not sure who I'm
going to be disagreeing with or agreeing with here. But I wanted to
mention that existing practice may not be a very useful place to start
here. Whatever mechanism we invent is going to change the calculus of
people deciding how to set up their schemas and roles since they'll
want to arrange things to take advantage of this new mechanism.

In particular, one early question was whether to use wildcard patterns
or schema names. People were saying wildcard patterns would be more
flexible because people don't always set up their objects in different
schemas. But if we had a mechanism someone wanted to use which
depended on schemas they would be far more likely to choose to set up
schemas for objects which belong in different security classes.

--
greg
http://mit.edu/~gsstark/resume.pdf

Greg,

> In particular, one early question was whether to use wildcard patterns
> or schema names. People were saying wildcard patterns would be more
> flexible because people don't always set up their objects in different
> schemas. But if we had a mechanism someone wanted to use which
> depended on schemas they would be far more likely to choose to set up
> schemas for objects which belong in different security classes.

What I'm saying is that there are many users currently using schema for
security classes. I personally haven't ever encountered a DBA who used
role ownership of objects as a mechanism for security context. There's
nothing conceptually invalid about the latter approach, but it would be
hard for DBAs to grasp, and as a result less of them would use it.

Mainly that's because the concept of schema easily maps (even if
inaccurately) to the concept of directories, whose permissions IT staff
are used to managing. So it's more intuitive for a DBA to say "This is
sensitive data so I'm going to put it in the SENSITIVE schema" than to
say "this is sensitive data so I'm going to have the table belong to the
SENSITIVE role".

Further, it's common practice on other DBMSes to have a "database owner"
role which owns all database objects. So DBA who learn Postgres second
are going to do that.

If we were going for a theoretically pure approach, we'd actually have a
"security context" concept which would include a bundle of permissions
and each object would belong to one.

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

On Mon, Jun 29, 2009 at 9:02 PM, Josh Berkus<josh(at)agliodbs(dot)com> wrote:
> What I'm saying is that there are many users currently using schema for
> security classes.  I personally haven't ever encountered a DBA who used role
> ownership of objects as a mechanism for security context.  There's nothing
> conceptually invalid about the latter approach, but it would be hard for
> DBAs to grasp, and as a result less of them would use it.
>
> Mainly that's because the concept of schema easily maps (even if
> inaccurately) to the concept of directories, whose permissions IT staff are
> used to managing.  So it's more intuitive for a DBA to say "This is
> sensitive data so I'm going to put it in the SENSITIVE schema" than to say
> "this is sensitive data so I'm going to have the table belong to the
> SENSITIVE role".
>
> Further, it's common practice on other DBMSes to have a "database owner"
> role which owns all database objects.  So DBA who learn Postgres second are
> going to do that.

It sounds to me like they *are* using the owner to provide security then.

> If we were going for a theoretically pure approach, we'd actually have a
> "security context" concept which would include a bundle of permissions and
> each object would belong to one.

It sounds like you're confounding the the owner of the objects and
roles that users have.

In the databases I've used we had a dba user which owned all the
objects. Then we had a "www" user which had DML access to most
objects, though lacked update and delete access to others. We actually
didn't need any other users but if we had a more extensive backend
interface we would have had a "www-backend" and "reports" and so on.

So I'm not sure what problem we're trying to solve here.

There's "I just created a new "www-backend" role which I want to have
access to everything "www" has and then I'll go add a few additional
privileges. We can do that already by having "www-backend" belong to
the "www" role and then add the extra privileges manually.

And there's "I just created a new table, I want "www" and
"www-backend" to get their usual privileges without thinking about it.
You want to be able to specify default grants that an object gets
based on the schema? That seems mostly reasonable though it might be a
good idea to have a WITH DEFAULT GRANTS or something like that on the
CREATE statement so that the dba has to make it explicit. It does
seems slightly silly since surely anyone creating a new object would
just paste in their grants from another object or some common source
anyways, but I suppose that's the way with convenience features.

--
greg
http://mit.edu/~gsstark/resume.pdf

Greg,

> And there's "I just created a new table, I want "www" and
> "www-backend" to get their usual privileges without thinking about it.
> You want to be able to specify default grants that an object gets
> based on the schema? That seems mostly reasonable though it might be a
> good idea to have a WITH DEFAULT GRANTS or something like that on the
> CREATE statement so that the dba has to make it explicit.

Well, the idea is *user and schema*, not schema alone. I think Jeff's
proposal for users was user alone, unmodified by schema. I'd prefer to
reverse the switch (i.e. NO DEFAULT GRANTS) just because I'd like
default grants to work with ORMs and similar.

In other words, my/stephen's proposal amounts to the idea that objects
in a schema should, by default, be able to inherit permissions from
their schema at creation time.

>It does
> seems slightly silly since surely anyone creating a new object would
> just paste in their grants from another object or some common source
> anyways, but I suppose that's the way with convenience features.

That works fine until you have 6 (or more) defined roles and a couple
hundred objects, and are in a "agile" environment where the dev team is
constantly adding objects which have the wrong permissions. That's
whose problem I'm trying to solve (because they're my clients).

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

On Tue, Jun 30, 2009 at 1:51 AM, Josh Berkus<josh(at)agliodbs(dot)com> wrote:
>
>>It does
>> seems slightly silly since surely anyone creating a new object would
>> just paste in their grants from another object or some common source
>> anyways, but I suppose that's the way with convenience features.
>
> That works fine until you have 6 (or more) defined roles and a couple
> hundred objects, and are in a "agile" environment where the dev team is
> constantly adding objects which have the wrong permissions.  That's whose
> problem I'm trying to solve (because they're my clients).

Well I don't understand how you get them wrong if you're just pasting
them from a file. I mean, sure you can pick the wrong template but
nothing can help you there. You could just as easily pick the wrong
template if it's a database feature instead of a text file.

"Agile" doesn't mean doing things without thinking about them :)

--
greg
http://mit.edu/~gsstark/resume.pdf

Greg,

> Well I don't understand how you get them wrong if you're just pasting
> them from a file. I mean, sure you can pick the wrong template but
> nothing can help you there. You could just as easily pick the wrong
> template if it's a database feature instead of a text file.

I really have to wonder if you've ever managed a production database
project.

As someone who has managed quite a few, my idea of the feature is
designed to make my life (and my clients') easier. It's *vastly* easier
to tell developers "don't touch the permissions, it will take care of
itself" and set it in a central location than to expect them to remember
to apply a set of permissions each time, or follow them around playing
catch-up on the objects they add and modify.

Oracle, MSSQL, etc. all have management solutions for this. For a good
reason.

> "Agile" doesn't mean doing things without thinking about them :)

In many companies it does. :-(

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

On Tue, Jun 30, 2009 at 4:39 AM, Josh Berkus<josh(at)agliodbs(dot)com> wrote:
>
>
>> Well I don't understand how you get them wrong if you're just pasting
>> them from a file. I mean, sure you can pick the wrong template but
>> nothing can help you there. You could just as easily pick the wrong
>> template if it's a database feature instead of a text file.
>
> I really have to wonder if you've ever managed a production database
> project.
>
> As someone who has managed quite a few, my idea of the feature is designed
> to make my life (and my clients') easier.  It's *vastly* easier to tell
> developers "don't touch the permissions, it will take care of itself" and
> set it in a central location than to expect them to remember to apply a set
> of permissions each time, or follow them around playing catch-up on the
> objects they add and modify.

But that's not what we're talking about and there's no way they can
just "take care of themselves". The database isn't a mind-reader and
can't know whether this new table is supposed to have the "public web
data" permission template or the "sensitive data" permission template.

You can put it in the wrong schema and get the wrong default
permission just as easily as you can choose the wrong text template to
paste into your database creation script.

I'm not saying it's a bad idea to have some sort of short cut for the
default permissions. Actually it sounds like it would lend itself to
the good code practice of being self-documenting which makes it easier
to see that which template's being used which is sounds like quite a
good thing. But you do still have to think carefully about that
choice.

Perhaps tieing it to the schema is wrong and we should actually
require the user to specify the template they want explicitly which
would be even better for that. So it would be something like "WITH
GRANTS LIKE sensitive_table".

--
greg
http://mit.edu/~gsstark/resume.pdf

On Tue, Jun 30, 2009 at 04:24:40AM +0100, Greg Stark wrote:
> On Tue, Jun 30, 2009 at 1:51 AM, Josh Berkus<josh(at)agliodbs(dot)com> wrote:
> >
> >> It does seems slightly silly since surely anyone creating a new
> >> object would just paste in their grants from another object or
> >> some common source anyways, but I suppose that's the way with
> >> convenience features.
> >
> > That works fine until you have 6 (or more) defined roles and a
> > couple hundred objects, and are in a "agile" environment where the
> > dev team is constantly adding objects which have the wrong
> > permissions.  That's whose problem I'm trying to solve (because
> > they're my clients).
>
> Well I don't understand how you get them wrong if you're just
> pasting them from a file. I mean, sure you can pick the wrong
> template but nothing can help you there. You could just as easily
> pick the wrong template if it's a database feature instead of a text
> file.
>
> "Agile" doesn't mean doing things without thinking about them :)

It does in a lot of shops ;)

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

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

* Greg Stark <gsstark(at)mit(dot)edu> [090630 00:18]:

> Perhaps tieing it to the schema is wrong and we should actually
> require the user to specify the template they want explicitly which
> would be even better for that. So it would be something like "WITH
> GRANTS LIKE sensitive_table".

And, not having any experience with the current permissions code, or the
code required to do that (;-]), I would *love* something like that...

*especially* if those grants remain "by reference", i.e. If I change the
GRANTS/REVOKES on sensitive_table, those are automatically "apply" to all
tables created with the "WITH GRANTS LIKE sensitive_table"...

It would simplify all the work I have to do in:
make_$PERMISSION_ROLE(table)
and make it much more "elegant", and save me having to re-run them all
if I want to change some permissions.

But I realize that since I'm as anal about my database schemas as I am
about my code, I'm probably not your typical "DB dev shop" people like
Josh are used to dealing with...

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

Aidan Van Dyk wrote:
>
> *especially* if those grants remain "by reference", i.e. If I change the
> GRANTS/REVOKES on sensitive_table, those are automatically "apply" to all
> tables created with the "WITH GRANTS LIKE sensitive_table"...
>
>
>

Isn't that exactly what Tom is objecting to, namely that the permissions
of an object would not be contained entirely in catalog entry for the
object itself?

cheers

andrew

* Andrew Dunstan <andrew(at)dunslane(dot)net> [090630 09:08]:
>
>
> Aidan Van Dyk wrote:
>>
>> *especially* if those grants remain "by reference", i.e. If I change the
>> GRANTS/REVOKES on sensitive_table, those are automatically "apply" to all
>> tables created with the "WITH GRANTS LIKE sensitive_table"...
>>
>>
>>
>
> Isn't that exactly what Tom is objecting to, namely that the permissions
> of an object would not be contained entirely in catalog entry for the
> object itself?

Well, it depends on how it's done... If one of the permissions on an
object you can assign is "look at $X", the you don't get the "hidden
permissions" problem. The object itself still contains everything you
need to "trace" the permissions of an object...

I have no idea if it's something that even half-aligns with the internal
permission model/code...

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

andrew(at)dunslane(dot)net (Andrew Dunstan) writes:

> Jeff Davis wrote:
>> 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.
>>
>>
>>
>
> Think of MySQL's wildcard permissions. They apply to any object
> whether that object is created before or after the rule is set,
> AIUI. That means the wildcard pattern is applied at the time the
> permission rule is referenced, rather than when the rule is created,
> thus applying it prospectively.
>
> It's a feature many users would like to have, although, as Tom rightly
> points out, it can be a bit of a footgun if used carelessly.

I'll point out, "for posterity", that way back in yesteryear, TOPS-10
(introduced in 1967) had a "declarative permissioning" system for file
access that resembles this.

The best description I'm aware of is the following:
http://lkml.org/lkml/1999/2/5/2

A FILDAE config file for a particular user might look like the
following:

# anything in a directory named "private" is off limits
*/private/*:*:*:*:
# people in group "foo" get full (create, delete, read, write,
# execute) access to everything in the foo project directory
~/projects/foo/*:*:foo:*:cdrwx
# people playing mygame can update the high score file
~/mygame/score.dat:*:*:
~/mygame/bin/mygame:rw
# some friends have access to the RCS files for mygame
~/mygame/src/RCS/*:dennis,kevin,josh:*:
/usr/bin/ci:rw
~/mygame/src/RCS/*:dennis,kevin,josh:*:
/usr/bin/co:rw
# I'll put stuff I want everyone to read in my ~/public directory
# I'll make the public directory 744, so no one will actually have
# to check .access_list, but I'll still put in this entry for
completeness
~/public/*:*:*:*:r# anything left over gets no access*:*:*:*:

This obviously isn't notably SQL-like, but that's not the point :-).
--
"cbbrowne","@","cbbrowne.com"
http://www3.sympatico.ca/cbbrowne/lisp.html
Ubuntu is an ancient African word, meaning "can't configure Debian"