From 249724cb014bd341cf51a8c4284fca9767a556d1 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 5 Oct 2009 19:24:49 +0000 Subject: Create an ALTER DEFAULT PRIVILEGES command, which allows users to adjust the privileges that will be applied to subsequently-created objects. Such adjustments are always per owning role, and can be restricted to objects created in particular schemas too. A notable benefit is that users can override the traditional default privilege settings, eg, the PUBLIC EXECUTE privilege traditionally granted by default for functions. Petr Jelinek --- doc/src/sgml/catalogs.sgml | 94 ++++++++++- doc/src/sgml/ref/allfiles.sgml | 3 +- doc/src/sgml/ref/alter_default_privileges.sgml | 211 +++++++++++++++++++++++++ doc/src/sgml/ref/grant.sgml | 35 ++-- doc/src/sgml/ref/psql-ref.sgml | 39 +++-- doc/src/sgml/reference.sgml | 3 +- 6 files changed, 359 insertions(+), 26 deletions(-) create mode 100644 doc/src/sgml/ref/alter_default_privileges.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index dd103573a5d..15dab71cc0d 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,4 +1,4 @@ - + @@ -113,6 +113,11 @@ databases within this database cluster + + pg_default_acl + default privileges for object types + + pg_depend dependencies between database objects @@ -2155,6 +2160,93 @@ + + <structname>pg_default_acl</structname> + + + pg_default_acl + + + + The catalog pg_default_acl stores initial + privileges to be assigned to newly created objects. + + + + <structname>pg_default_acl</> Columns + + + + + Name + Type + References + Description + + + + + + defaclrole + oid + pg_authid.oid + The OID of the role associated with this entry + + + + defaclnamespace + oid + pg_namespace.oid + The OID of the namespace associated with this entry, + or 0 if none + + + + defaclobjtype + char + + + Type of object this entry is for: + r = relation (table, view), + S = sequence, + f = function + + + + + defaclacl + aclitem[] + + + Access privileges that this type of object should have on creation + + + + +
+ + + A pg_default_acl entry shows the initial privileges to + be assigned to an object belonging to the indicated user. There are + currently two types of entry: global entries with + defaclnamespace = 0, and per-schema entries + that reference a particular schema. If a global entry is present then + it overrides the normal hard-wired default privileges + for the object type. A per-schema entry, if present, represents privileges + to be added to the global or hard-wired default privileges. + + + + Note that when an ACL entry in another catalog is NULL, it is taken + to represent the hard-wired default privileges for its object, + not whatever might be in pg_default_acl + at the moment. pg_default_acl is only consulted during + object creation. + + + + + <structname>pg_depend</structname> diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 845033b6b66..c15579c5164 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -1,5 +1,5 @@ @@ -9,6 +9,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml new file mode 100644 index 00000000000..b2054b17804 --- /dev/null +++ b/doc/src/sgml/ref/alter_default_privileges.sgml @@ -0,0 +1,211 @@ + + + + + ALTER DEFAULT PRIVILEGES + 7 + SQL - Language Statements + + + + ALTER DEFAULT PRIVILEGES + define default access privileges + + + + ALTER DEFAULT PRIVILEGES + + + + +ALTER DEFAULT PRIVILEGES + [ FOR { ROLE | USER } target_role [, ...] ] + [ IN SCHEMA schema_name [, ...] ] + abbreviated_grant_or_revoke + +where abbreviated_grant_or_revoke is one of: + +GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } + [,...] | ALL [ PRIVILEGES ] } + ON TABLE + TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + +GRANT { { USAGE | SELECT | UPDATE } + [,...] | ALL [ PRIVILEGES ] } + ON SEQUENCE + TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + +GRANT { EXECUTE | ALL [ PRIVILEGES ] } + ON FUNCTION + TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + +REVOKE [ GRANT OPTION FOR ] + { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } + [,...] | ALL [ PRIVILEGES ] } + ON TABLE + FROM { [ GROUP ] role_name | PUBLIC } [, ...] + [ CASCADE | RESTRICT ] + +REVOKE [ GRANT OPTION FOR ] + { { USAGE | SELECT | UPDATE } + [,...] | ALL [ PRIVILEGES ] } + ON SEQUENCE + FROM { [ GROUP ] role_name | PUBLIC } [, ...] + [ CASCADE | RESTRICT ] + +REVOKE [ GRANT OPTION FOR ] + { EXECUTE | ALL [ PRIVILEGES ] } + ON FUNCTION + FROM { [ GROUP ] role_name | PUBLIC } [, ...] + [ CASCADE | RESTRICT ] + + + + + Description + + + ALTER DEFAULT PRIVILEGES allows you to set the privileges + that will be applied to objects created in the future. (It does not + affect privileges assigned to already-existing objects.) Currently, + only the privileges for tables (including views), sequences, and + functions can be altered. + + + + You can change default privileges only for objects that will be created by + yourself or by roles that you are a member of. The privileges can be set + globally (i.e., for all objects created in the current database), + or just for objects created in specified schemas. Default privileges + that are specified per-schema are added to whatever the global default + privileges are for the particular object type. + + + + As explained under , + the default privileges for any object type normally grant all grantable + permissions to the object owner, and may grant some privileges to + PUBLIC as well. However, this behavior can be changed by + altering the global default privileges with + ALTER DEFAULT PRIVILEGES. + + + + Parameters + + + + target_role + + + The name of an existing role of which the current role is a member. + If FOR ROLE is omitted, the current role is assumed. + + + + + + schema_name + + + The name of an existing schema. Each target_role + must have CREATE privileges for each specified schema. + If IN SCHEMA is omitted, the global default privileges + are altered. + + + + + + role_name + + + The name of an existing role to grant or revoke privileges for. + This parameter, and all the other parameters in + abbreviated_grant_or_revoke, + act as described under + or + , + except that one is setting permissions for a whole class of objects + rather than specific named objects. + + + + + + + + + Notes + + + Use 's \ddp command + to obtain information about existing assignments of default privileges. + The meaning of the privilege values is the same as explained for + \dp under + . + + + + If you wish to drop a role that has had its global default privileges + altered, it is necessary to use DROP OWNED BY first, + to get rid of the default privileges entry for the role. + + + + + Examples + + + Grant SELECT privilege to everyone for all tables (and views) you + subsequently create in schema myschema, and allow + role webuser to INSERT into them too: + + +ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLE TO PUBLIC; +ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT INSERT ON TABLE TO webuser; + + + + + Undo the above, so that subsequently-created tables won't have any + more permissions than normal: + + +ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE SELECT ON TABLE FROM PUBLIC; +ALTER DEFAULT PRIVILEGES IN SCHEMA myschema REVOKE INSERT ON TABLE FROM webuser; + + + + + Remove the public EXECUTE permission that is normally granted on functions, + for all functions subsequently created by role admin: + + +ALTER DEFAULT PRIVILEGES FOR ROLE admin REVOKE EXECUTE ON FUNCTION FROM PUBLIC; + + + + + + Compatibility + + + There is no ALTER DEFAULT PRIVILEGES statement in the SQL + standard. + + + + + See Also + + + + + + + + diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index 4dddde27b91..2dcf4aa0f0b 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -1,5 +1,5 @@ @@ -80,14 +80,6 @@ GRANT role_name [, ...] TO - - As of PostgreSQL 8.1, the concepts of users and - groups have been unified into a single kind of entity called a role. - It is therefore no longer necessary to use the keyword GROUP - to identify whether a grantee is a user or a group. GROUP - is still allowed in the command, but it is a noise word. - - GRANT on Database Objects @@ -145,6 +137,9 @@ GRANT role_name [, ...] TO REVOKE in the same transaction that creates the object; then there is no window in which another user can use the object.) + Also, these initial default privilege settings can be changed using the + + command. @@ -388,6 +383,14 @@ GRANT role_name [, ...] TO + + Since PostgreSQL 8.1, the concepts of users and + groups have been unified into a single kind of entity called a role. + It is therefore no longer necessary to use the keyword GROUP + to identify whether a grantee is a user or a group. GROUP + is still allowed in the command, but it is a noise word. + + A user may perform SELECT, INSERT, etc. on a column if he holds that privilege for either the specific column or @@ -518,8 +521,13 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw; REVOKE on an object will instantiate the default privileges (producing, for example, {miriam=arwdDxt/miriam}) and then modify them per the - specified request. Entries are shown in Column access + specified request. Similarly, entries are shown in Column access privileges only for columns with nondefault privileges. + (Note: for this purpose, default privileges always means the + built-in default privileges for the object's type. An object whose + privileges have been affected by an ALTER DEFAULT PRIVILEGES + command will always be shown with an explicit privilege entry that + includes the effects of the ALTER.) @@ -602,9 +610,10 @@ GRANT admins TO joe; See Also - - - + + + + diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index f60c3150e9f..e689d275cb4 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1,5 +1,5 @@ @@ -978,6 +978,29 @@ testdb=> + + \ddp [ pattern ] + + + Lists default access privilege settings. An entry is shown for + each role (and schema, if applicable) for which the default + privilege settings have been changed from the built-in defaults. + If pattern is + specified, only entries whose role name or schema name matches + the pattern are listed. + + + + The command is used to set + default access privileges. The meaning of the + privilege display is explained under + . + + + + + \dD[S] [ pattern ] @@ -1142,8 +1165,8 @@ testdb=> class="parameter">pattern is specified, only those roles whose names match the pattern are listed. (This command is now effectively the same as \du). - If the form \dg+ is used, additional information - is shown about each role, including the comment for each role. + If the form \dg+ is used, additional information + is shown about each role, including the comment for each role. @@ -1235,7 +1258,9 @@ testdb=> The and - commands are used to set access privileges. + commands are used to set access privileges. The meaning of the + privilege display is explained under + . @@ -2045,12 +2070,6 @@ lo_import 152801 specified, only tables,views and sequences whose names match the pattern are listed. - - The and - - commands are used to set access privileges. - - This is an alias for \dp (display privileges). diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 48f8040541d..0e72fc5475b 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -1,4 +1,4 @@ - + Reference @@ -37,6 +37,7 @@ &alterAggregate; &alterConversion; &alterDatabase; + &alterDefaultPrivileges; &alterDomain; &alterForeignDataWrapper; &alterFunction; -- cgit v1.2.3