Обсуждение: type privileges and default privileges

During the closing days of the 9.1 release, we had discussed that we
should add privileges on types (and domains), so that owners can prevent
others from using their types because that would prevent the owners from
changing them in certain ways.  (Collations have similar issues and work
quite similar to types, so we could include them in this consideration.)

As I'm plotting to write code for this, I wonder about how to handle
default privileges.  For compatibility and convenience, we would still
want to have types with public privileges by default.  Should we
continue to hardcode this, as we have done in the past with functions,
for example, or should we use the new default privileges facility to
register the public default privileges in the template database?

On Sat, Nov 5, 2011 at 10:35 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> During the closing days of the 9.1 release, we had discussed that we
> should add privileges on types (and domains), so that owners can prevent
> others from using their types because that would prevent the owners from
> changing them in certain ways.  (Collations have similar issues and work
> quite similar to types, so we could include them in this consideration.)
>
> As I'm plotting to write code for this, I wonder about how to handle
> default privileges.  For compatibility and convenience, we would still
> want to have types with public privileges by default.  Should we
> continue to hardcode this, as we have done in the past with functions,
> for example, or should we use the new default privileges facility to
> register the public default privileges in the template database?

I think it would make sense to follow the model of default privileges,
but I'm a bit confused by the rest of this, because pg_default_acl is
normally empty - you only make an entry there when a schema has
different defaults than the, uh, default defaults.  So you shouldn't
need to "register" anything, I wouldn't think.

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

On mån, 2011-11-07 at 12:21 -0500, Robert Haas wrote:
> > As I'm plotting to write code for this, I wonder about how to handle
> > default privileges.  For compatibility and convenience, we would
> still
> > want to have types with public privileges by default.  Should we
> > continue to hardcode this, as we have done in the past with
> functions,
> > for example, or should we use the new default privileges facility to
> > register the public default privileges in the template database?
> 
> I think it would make sense to follow the model of default privileges,
> but I'm a bit confused by the rest of this, because pg_default_acl is
> normally empty - you only make an entry there when a schema has
> different defaults than the, uh, default defaults.  So you shouldn't
> need to "register" anything, I wouldn't think.

Let me put this differently.  Should we either continue to hardcode the
default privileges in the acldefault() function, or should we instead
initialize the system catalogs with an entry in pg_default_acl as though
ALTER DEFAULT PRIVILEGES GRANT USAGE ON TYPES TO PUBLIC; had been
executed?

Peter Eisentraut <peter_e@gmx.net> writes:
> Let me put this differently.  Should we either continue to hardcode the
> default privileges in the acldefault() function, or should we instead
> initialize the system catalogs with an entry in pg_default_acl as though
> ALTER DEFAULT PRIVILEGES GRANT USAGE ON TYPES TO PUBLIC; had been
> executed?

If you're proposing to replace acldefault() with a catalog lookup,
I vote no.  I think that's a performance hit with little redeeming
social value.
        regards, tom lane

On ons, 2011-11-09 at 00:21 -0500, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Let me put this differently.  Should we either continue to hardcode the
> > default privileges in the acldefault() function, or should we instead
> > initialize the system catalogs with an entry in pg_default_acl as though
> > ALTER DEFAULT PRIVILEGES GRANT USAGE ON TYPES TO PUBLIC; had been
> > executed?
> 
> If you're proposing to replace acldefault() with a catalog lookup,
> I vote no.  I think that's a performance hit with little redeeming
> social value.

No, I'm pondering having pg_default_acl initialized so that newly
created types have explicit USAGE privileges in their typacl column, so
acldefault() wouldn't be needed.  (And builtin types would have their
typacl initialized analogously.)  I suppose this is how we might have
done it if we had invented ALTER DEFAULT PRIVILEGES first.

On Thu, Nov 10, 2011 at 3:17 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On ons, 2011-11-09 at 00:21 -0500, Tom Lane wrote:
>> Peter Eisentraut <peter_e@gmx.net> writes:
>> > Let me put this differently.  Should we either continue to hardcode the
>> > default privileges in the acldefault() function, or should we instead
>> > initialize the system catalogs with an entry in pg_default_acl as though
>> > ALTER DEFAULT PRIVILEGES GRANT USAGE ON TYPES TO PUBLIC; had been
>> > executed?
>>
>> If you're proposing to replace acldefault() with a catalog lookup,
>> I vote no.  I think that's a performance hit with little redeeming
>> social value.
>
> No, I'm pondering having pg_default_acl initialized so that newly
> created types have explicit USAGE privileges in their typacl column, so
> acldefault() wouldn't be needed.  (And builtin types would have their
> typacl initialized analogously.)  I suppose this is how we might have
> done it if we had invented ALTER DEFAULT PRIVILEGES first.

I'm not convinced.  That's a lot of catalog clutter for no benefit.

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

Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Nov 10, 2011 at 3:17 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
>> No, I'm pondering having pg_default_acl initialized so that newly
>> created types have explicit USAGE privileges in their typacl column, so
>> acldefault() wouldn't be needed.  (And builtin types would have their
>> typacl initialized analogously.)  I suppose this is how we might have
>> done it if we had invented ALTER DEFAULT PRIVILEGES first.

> I'm not convinced.  That's a lot of catalog clutter for no benefit.

To actually get rid of acldefault, we'd have to do that not only for
types but for all objects with ACLs.  That's a LOT of catalog bulk,
and like Robert I'm not seeing much benefit.  It's not unreasonable
to want the typical case to be small and fast.
        regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Thu, Nov 10, 2011 at 3:17 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
> >> No, I'm pondering having pg_default_acl initialized so that newly
> >> created types have explicit USAGE privileges in their typacl column, so
> >> acldefault() wouldn't be needed.  (And builtin types would have their
> >> typacl initialized analogously.)  I suppose this is how we might have
> >> done it if we had invented ALTER DEFAULT PRIVILEGES first.
>
> > I'm not convinced.  That's a lot of catalog clutter for no benefit.
>
> To actually get rid of acldefault, we'd have to do that not only for
> types but for all objects with ACLs.  That's a LOT of catalog bulk,
> and like Robert I'm not seeing much benefit.  It's not unreasonable
> to want the typical case to be small and fast.

A LOT of catalog bulk..?  Am I missing something here?  Aren't we just
talking about 16 bytes, or so, per catalog entry?  On one of my larger
databases, with ~250k catalog records, we're talking about 4MB.  That
catalog is currently 130MB.  An empty database has, what, 5000 objects?
That'd be ~80KB?

My thought would be to have a place where we keep what the 'default
default' ACL is for each object type (yes, with the idea that it could
be modified, I'd see that as a nice feature and I don't think we need
to worry, as much, about performance of DDL..) and then we populate the
ACL for each created object with the 'default default' ACL.  Perhaps we
integrate this with the existing default permissions system..  This
isn't a new idea to me, to be honest..
Thanks,
    Stephen

Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> To actually get rid of acldefault, we'd have to do that not only for
>> types but for all objects with ACLs.  That's a LOT of catalog bulk,
>> and like Robert I'm not seeing much benefit.  It's not unreasonable
>> to want the typical case to be small and fast.

> A LOT of catalog bulk..?  Am I missing something here?

What I'm missing is what actual benefit we get from spending the extra
space.  (No, I don't believe that changing the defaults is something
that users commonly will or should do; it's certainly not the case to
optimize for.)
        regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > A LOT of catalog bulk..?  Am I missing something here?
>
> What I'm missing is what actual benefit we get from spending the extra
> space.  (No, I don't believe that changing the defaults is something
> that users commonly will or should do; it's certainly not the case to
> optimize for.)

Typical database *users*?  No.  A DBA or SA?  Certainly, and we already
provide a way to do that, in part.  Supporting it for the 'default
defaults' would be nice as would support for default privileges for
schemas (rather than just objects that go *in* schemas).

Certainly a big one that people get caught by is our default of execute
to public on functions..  Most of our privileges are set up as minimal
access to others, functions are an oddity in that regard.  Rather than
fight the battle of what the default *should* be for functions, we could
just give the DBA the ability to configure it for their database.
Thanks,
    Stephen

On Thu, Nov 10, 2011 at 10:52 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Stephen Frost <sfrost@snowman.net> writes:
>> > A LOT of catalog bulk..?  Am I missing something here?
>>
>> What I'm missing is what actual benefit we get from spending the extra
>> space.  (No, I don't believe that changing the defaults is something
>> that users commonly will or should do; it's certainly not the case to
>> optimize for.)
>
> Typical database *users*?  No.  A DBA or SA?  Certainly, and we already
> provide a way to do that, in part.  Supporting it for the 'default
> defaults' would be nice as would support for default privileges for
> schemas (rather than just objects that go *in* schemas).
>
> Certainly a big one that people get caught by is our default of execute
> to public on functions..  Most of our privileges are set up as minimal
> access to others, functions are an oddity in that regard.  Rather than
> fight the battle of what the default *should* be for functions, we could
> just give the DBA the ability to configure it for their database.

Sure, let's do.  But that hardly means that we need to store useless
catalog records in every database with the DBA doesn't do that.

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

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Thu, Nov 10, 2011 at 10:52 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > Certainly a big one that people get caught by is our default of execute
> > to public on functions..  Most of our privileges are set up as minimal
> > access to others, functions are an oddity in that regard.  Rather than
> > fight the battle of what the default *should* be for functions, we could
> > just give the DBA the ability to configure it for their database.
>
> Sure, let's do.  But that hardly means that we need to store useless
> catalog records in every database with the DBA doesn't do that.

Fair enough, so the direction would be to add 'IN DATABASE' options to
'ALTER DEFAULT PRIVILEGES' and have all the same options there, plus
flags for schema (and any other schema-level/entire-database things)
options?  I presume that the 'IN SCHEMA' / 'FOR USER' options would be
used, where those exist, and we'd only fall back to the higher ones if
those don't exist?
Thanks,
    Stephen

On Thu, Nov 10, 2011 at 11:17 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Robert Haas (robertmhaas@gmail.com) wrote:
>> On Thu, Nov 10, 2011 at 10:52 PM, Stephen Frost <sfrost@snowman.net> wrote:
>> > Certainly a big one that people get caught by is our default of execute
>> > to public on functions..  Most of our privileges are set up as minimal
>> > access to others, functions are an oddity in that regard.  Rather than
>> > fight the battle of what the default *should* be for functions, we could
>> > just give the DBA the ability to configure it for their database.
>>
>> Sure, let's do.  But that hardly means that we need to store useless
>> catalog records in every database with the DBA doesn't do that.
>
> Fair enough, so the direction would be to add 'IN DATABASE' options to
> 'ALTER DEFAULT PRIVILEGES' and have all the same options there, plus
> flags for schema (and any other schema-level/entire-database things)
> options?  I presume that the 'IN SCHEMA' / 'FOR USER' options would be
> used, where those exist, and we'd only fall back to the higher ones if
> those don't exist?

Oh, I didn't realize that you were proposing a database-wide setting;
my point was just that the way the feature looks to the user doesn't
have to dictate the catalog representation.

I'm not entirely certain whether a database-wide setting is useful
enough to justify the additional complexity.  I'm not saying it isn't,
just wondering out loud.  To need this rather than just a per-schema
facility, you'd need to be using enough schemas (or creating new ones
frequently enough) that setting the privileges one schema at a time
would be inconvenient.  How common is that?

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

* Robert Haas (robertmhaas@gmail.com) wrote:
> Oh, I didn't realize that you were proposing a database-wide setting;
> my point was just that the way the feature looks to the user doesn't
> have to dictate the catalog representation.

I agree that the features don't have to define the catalog
representation.  On the other hand, I don't think we'd want a change to
the 'default defaults' to affect existing objects in the system.

> I'm not entirely certain whether a database-wide setting is useful
> enough to justify the additional complexity.  I'm not saying it isn't,
> just wondering out loud.  To need this rather than just a per-schema
> facility, you'd need to be using enough schemas (or creating new ones
> frequently enough) that setting the privileges one schema at a time
> would be inconvenient.  How common is that?

I don't really see it as a frequency thing, to be honest..  I see it as
dependent on the user base and what the users are allowed to do.  I run
quite a few different databases with different roles.  I've also got
lots of users that aren't DBAs but need to be able to create tables,
sometimes create schemas, etc.  What typically ends up happening is that
they'll create a schema and be running along happily, until one day
they're out, and someone else needs to do something with that schema.
Then it's a support request to me, even though that schema should have
had 'usage' to public from the get-go because there's nothing sensetive
in that database.  I've got cases where the user who created the schema
doesn't know how or doesn't think they can change the permissions
themselves too, more calls to me.

Being able to set up the defaults on the database when we create it
(that's not something we let non-admins do, at least.. :), depending on
what the role of the new database is, would be quite nice.  Being able
to do it for certain objects in schemas has certainly helped.  Doing it
for more objects (like schemas) seems like a natural next-step to me.
Doing it for schemas is inherently "database-wide", as that's the
container which schemas exist inside of.  Thinking on it a bit more,
the 'database-wide' options for objects which exist inside schemas could
just be what new schemas are initialized with for their defaults using
the existing system.  If schemas are created before that's done, there'd
be a bit of pain going back and updating them, but that's not a big
deal, imv.

I do still think it'd be nice if we displayed the *actual* ACLs in \dp
instead of just 'blank'.  Also, do we really not sure a function's ACL
in any \df*..?  :/
Thanks,
    Stephen