DefaultACL

This project has been mostly completed in PostgreSQL 9.0.
See also:
* GRANT / REVOKE IN SCHEMA
* ALTER DEFAULT PRIVILEGES 
* pg_default_acl
== Description ==
On the TODO we have:
Allow GRANT/REVOKE permissions to be inherited by objects based on schema permissions
This is a proposal for implementation of default ACLs/permissions for newly created objects.
This will only be at object creation time (there is a separate TODO item for doing grant/revoke
on multiple objects at once using globbing or similar; if I make a proposal on that it'll be
another wiki page).
== Why to Do This ==
Managing permissions for a large complex database is very labor-intensive. DBAs are forced to use GUI tools or ad-hoc scripts which are awkward and or error-prone. As a result, many DBAs avoid creating and maintaining proper role setups at all and run insecure databases. We get requests for better permissions management for new databases at least 5 times a week on IRC.
Allowing DBAs to set up default permissions for new objects would greatly simplify the task of implementing a database role setup. It's also a baby step towards having more sophisticated role-based management tools.
== People ==
Folks currently involved in design and implementation:
 Stephen Frost - Project lead, developed catalog changes patch
 Emilie Giustozzi - Working on grammar changes
 Josh Berkus - Consulting on design & grammar, helping with the wiki
 Petr Jelinek - Initial gram.y patch, most of coding
 Simon Riggs - usability review
== Catalog ==
New pg_catalog table: [ patch ] [ Unified patch ]
 pg_namespace_default_acl (names can be changed to protect the innocent)
 defaclnamespace - OID of the schema
 defaclgrantobjtype - Grantable object kind, char(1) similar to relkind but for all
 types of grantable objects; this is already defined in an enum
 in parsenodes.h: GrantObjectType or some such
 'r' - table
 'v' - view
 'f' - function
 'S' - sequence
 'C' - column
 -- Probably can't support this, to be honest. It would imply knowing
 that all tables created have the column(s) referenced, and would cause
 errors most likely if the column wasn't there, which isn't good. 
 'l' - language
 'W' - foreign-data wrapper
 'F' - foreign server
 defacllist[] - array of acls that the object should have on creation
 this is NOT a mask, it's exactly what the object will get, in
 other words, the default PG perms are ignored if this is
 a def_acl in here for that schema/obj_kind
 This will look like a regular list of perms, eg:
 {role1=arwd/sfrost,role2=r/sfrost}
 Full row examples:
 2200, 'r', '{role1=arwd/sfrost,role2=r/sfrost}'
 2200, 'v', '{role1=r/sfrost,role2=r/sfrost}'
== Syntax ==
Current (as of 2009-07-14) wip patch: [ Media:Defaultacls.diff.gz ]
Current syntax is implemented based on Josh's ideas with few minor adjustments.
Simple syntax:
 ALTER SCHEMA blah SET DEFAULT PRIVILEGES ON TABLE SELECT TO public;
 ALTER SCHEMA blah SET DEFAULT PRIVILEGES ON TABLE SELECT TO webuser,admin AND UPDATE to admin AND INSERT to admin AND DELETE to admin;
 ALTER SCHEMA blah ADD DEFAULT PRIVILEGES ON TABLE INSERT TO webuser;
 ALTER SCHEMA blab DROP DEFAULT PRIVILEGES ON TABLE DELETE FROM admin;
Multiple privileges to multiple users:
 ALTER SCHEMA blah SET DEFAULT PRIVILEGES ON TABLE SELECT to webuser,admin AND UPDATE,INSERT,DELETE to admin;
And most complex syntax with multiple different privileges on different objects to multiple users:
 ALTER SCHEMA blah SET DEFAULT PRIVILEGES 
 ON TABLE SELECT TO webuser,admin AND UPDATE,INSERT,DELETE TO admin
 ON VIEW SELECT TO webuser,admin;
== Syntax suggestions ==
New syntax added to ALTER SCHEMA to support this
Note:
Not using grant/revoke terms (or those commands for that matter)
because it implies something is actually going to happen for existing
objects, which isn't true. This is only for new objects which are
being created
All of this is subject to acceptance by gram.y, of course.
Suggestion #1:
 ALTER SCHEMA blah SET DEFAULT ACL TABLE select to role1,role2
 TABLE insert to role2
 VIEW select to role1;
Suggestion #2: Probably better
 Force types to be done separately and reorder things:
 ALTER SCHEMA blah SET DEFAULT TABLE ACL select to role1,role2
 ACL insert to role2;
 ALTER SCHEMA blah SET DEFAULT VIEW ACL select to role1;
 Also allow using all for either side (priv list or role list):
 ALTER SCHEMA blah DROP DEFAULT VIEW ACL all from role1;
 ALTER SCHEMA blah DROP DEFAULT TABLE ACL select from all;
 ALTER SCHEMA blah ADD DEFAULT TABLE ACL all TO role1;
 All for 'ADD' implies public, below mean the same
 ALTER SCHEMA blah ADD DEFAULT TABLE ACL SELECT TO all;
 ALTER SCHEMA blah ADD DEFAULT TABLE ACL SELECT TO public;
Suggestion #3:
(Josh Berkus) I'd prefer a syntax which is more natural language, provided we can get gram.y to support it. Also, I think "ACL" as a keyword is confusing, compared to "permissions" which is both clearer and less likely to be the name of a user DB object.
 ALTER SCHEMA blah SET DEFAULT PERMISSIONS ON TABLE SELECT TO public;
 ALTER SCHEMA blah SET DEFAULT PERMISSIONS ON TABLE SELECT TO webuser,admin AND UPDATE to admin AND INSERT to admin AND DELETE to admin;
 ALTER SCHEMA blah ADD DEFAULT PERMISSIONS ON TABLE INSERT TO webuser;
 ALTER SCHEMA blab DROP DEFAULT PERMISSIONS ON TABLE DELETE FROM admin;
Punctuation could vary according to what gram.y will tolerate. If it's happy with the below, this would be even better:
 ALTER SCHEMA blah SET DEFAULT PERMISSIONS ON TABLE SELECT to webuser,admin AND UPDATE,INSERT,DELETE to admin;
We might also be able to do:
 ALTER SCHEMA blah SET DEFAULT PERMISSIONS 
 ON TABLE SELECT TO webuser,admin AND UPDATE,INSERT,DELETE TO admin
 AND ON VIEW SELECT TO webuser,admin;
Suggestion #4:
(What we're really going to do)
Basically, Josh's suggestion is where we're headed at the moment. Emilie Giustozzi is working on the gram.y changes to implement it.
As she correctly pointed out, we will be using PRIVILEGES instead of PERMISSIONS, as PRIVILEGES is already either a key or semi-key word
in PG.
Suggestion #5:
(gsmet) I find the "ON TABLE SELECT" part of the proposed syntaxes confusing.
I would prefer a syntax which mimics the GRANT syntax:
 ALTER SCHEMA blah SET DEFAULT PRIVILEGES GRANT SELECT ON TABLE TO webuser;
 ALTER SCHEMA blah SET DEFAULT PRIVILEGES GRANT UPDATE, INSERT ON TABLE TO admin, superadmin;
Or without the GRANT keyword:
 ALTER SCHEMA blah SET DEFAULT PRIVILEGES SELECT ON TABLE TO webuser;
 ALTER SCHEMA blah SET DEFAULT PRIVILEGES UPDATE, INSERT ON TABLE TO admin, superadmin;
Simon says: we should use PRIVILEGE rather than plural if we do this
Suggestion #6
If we have an ALTER SCHEMA command for this, then users will need to issue both a GRANT to change existing tables and an ALTER SCHEMA to change new tables. It seems more natural to seek a single command that will change the privilege for new and existing tables. 
So, I suggest
GRANT .... ON SCHEMA ..... [WITH DEFAULT OPTION];
So we set existing tables with a GRANT and we set existing *and* new tables with a GRANT ON ALL ... WITH DEFAULT OPTION.
That is easier to use since it is a single command, few extra syntax to learn and easier docs/more centralised. It also extends GRANT in a similar way to the extensions suggested for GRANT ALL.
The same concept can then be easily extended to other object types, if need be.
We also need the ability to set the default back to "no default" when required.
REVOKE DEFAULT OPTION FOR ..... ON SCHEMA ....
PJMODOS says:@Simon I remember Tom saying GRANT should not affect any future privileges, that was the whole reason why we used ALTER SCHEMA. Also your way does not allow us to grant only on new objects without affecting existing ones.
== Additional Ideas ==
Suggestion #3:
Maybe also support syntax to implement the default ACL for an
object, this would wholesale replace the existing perms, eg:
 ALTER TABLE blah SET DEFAULT ACL;
 Alternatively, for the arguments above, perhaps have GRANT syntax
 support this general capability, eg:
 GRANT DEFAULT ON TABLE blah;
Also support ADD/DROP default acl to allow adding to and deleting from the
def_acls[] list so you don't always have to specify the entire list. If
'set' is used, then everything will be replaced by whatever is provided.
Possible syntax for ownership
 This will probably be implemented separately as there is some
 concern about security. Intent is to implement this as
 essentially a 'ALTER TABLE blah OWNER TO role1;' as the user
 creating the table, so they will need the same perms they
 need to do that to begin with. This is just a convenience
 mechanism, not a change in what people are allowed to do.
 Should mean that you need to be a member of the role you're
 changing the ownership to, and that role need CREATE rights
 on the schema.
 ALTER SCHEMA blah ADD DEFAULT TABLE OWNER role1;
Suggestion #4:
 Support defaults attached to the creating user rather than the 
 schema that the object is created in. For instance:
 ALTER USER webuser_admin SET DEFAULT PRIVILEGES SELECT ON TABLE TO webuser_read_only;
 would mean that all tables created by webuser_admin would 
 grant privileges for SELECT to webuser_read_only.
== Privilege ==
Setting the default privilege for a Schema may require a separate privilege also. This will allow DBAs to grant the ability for users to create tables yet prevent them from changing the underlying administrative structure.
We would need to define a default owner for new objects. This would then be equivalent to running REASSIGN OWNED BY current_role TO default_role; immediately after a table is created.
Category:PostgreSQL 9.0