DefaultACL

From PostgreSQL wiki
Jump to navigationJump to search

This project has been mostly completed in PostgreSQL 9.0. See also:


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.

Retrieved from "https://wiki.postgresql.org/index.php?title=DefaultACL&oldid=12187"