Re: [HACKERS] type privileges and default privileges
On Thu, Nov 10, 2011 at 11:17 PM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.com) wrote: On Thu, Nov 10, 2011 at 10:52 PM, Stephen Frost sfr...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] type privileges and default privileges
* Robert Haas (robertmh...@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 signature.asc Description: Digital signature
Re: [HACKERS] type privileges and default privileges
On ons, 2011-11-09 at 00:21 -0500, Tom Lane wrote: Peter Eisentraut pete...@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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] type privileges and default privileges
On Thu, Nov 10, 2011 at 3:17 PM, Peter Eisentraut pete...@gmx.net wrote: On ons, 2011-11-09 at 00:21 -0500, Tom Lane wrote: Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] type privileges and default privileges
Robert Haas robertmh...@gmail.com writes: On Thu, Nov 10, 2011 at 3:17 PM, Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] type privileges and default privileges
* Tom Lane (t...@sss.pgh.pa.us) wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Nov 10, 2011 at 3:17 PM, Peter Eisentraut pete...@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 signature.asc Description: Digital signature
Re: [HACKERS] type privileges and default privileges
Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] type privileges and default privileges
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@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 signature.asc Description: Digital signature
Re: [HACKERS] type privileges and default privileges
On Thu, Nov 10, 2011 at 10:52 PM, Stephen Frost sfr...@snowman.net wrote: * Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] type privileges and default privileges
* Robert Haas (robertmh...@gmail.com) wrote: On Thu, Nov 10, 2011 at 10:52 PM, Stephen Frost sfr...@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 signature.asc Description: Digital signature
Re: [HACKERS] type privileges and default privileges
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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] type privileges and default privileges
Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] type privileges and default privileges
On Sat, Nov 5, 2011 at 10:35 AM, Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
