From 7762619e95272974f90a38d8d85aafbe0e94add5 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 28 Jun 2005 05:09:14 +0000 Subject: Replace pg_shadow and pg_group by new role-capable catalogs pg_authid and pg_auth_members. There are still many loose ends to finish in this patch (no documentation, no regression tests, no pg_dump support for instance). But I'm going to commit it now anyway so that Alvaro can make some progress on shared dependencies. The catalog changes should be pretty much done. --- doc/src/sgml/catalogs.sgml | 760 +++++++++++++++++++++++++++++++-------------- doc/src/sgml/func.sgml | 12 +- 2 files changed, 536 insertions(+), 236 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 2b1318b05e4..7358dfb5122 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,6 +1,6 @@ @@ -78,6 +78,16 @@ table columns (attributes) + + pg_authid + authorization identifiers (roles) + + + + pg_auth_members + authorization identifier membership relationships + + pg_cast casts (data type conversions) @@ -113,11 +123,6 @@ descriptions or comments on database objects - - pg_group - groups of database users - - pg_index additional index information @@ -168,11 +173,6 @@ query rewrite rules - - pg_shadow - database users - - pg_statistic planner statistics @@ -902,6 +902,201 @@ + + <structname>pg_authid</structname> + + + pg_authid + + + + The catalog pg_authid contains information about + database authorization identifiers (roles). A role subsumes the concepts + of users and groups. A user is essentially just a + role with the rolcanlogin flag set. Any role (with or + without rolcanlogin) may have other roles as members; see + pg_auth_members. + + + + Since this catalog contains passwords, it must not be publicly readable. + pg_roles + is a publicly readable view on + pg_authid that blanks out the password field. + + + + contains detailed information about user and + privilege management. + + + + Because user identities are cluster-wide, + pg_authid + is shared across all databases of a cluster: there is only one + copy of pg_authid per cluster, not + one per database. + + + + <structname>pg_authid</> Columns + + + + + Name + Type + References + Description + + + + + + rolname + name + + Role name + + + + rolsuper + bool + + Role has superuser privileges + + + + rolcreaterole + bool + + Role may create more roles + + + + rolcreatedb + bool + + Role may create databases + + + + rolcatupdate + bool + + + Role may update system catalogs directly. (Even a superuser may not do + this unless this column is true.) + + + + + rolcanlogin + bool + + + Role may log in, that is, this role can be given as the initial + session authorization identifier. + + + + + rolpassword + text + + Password (possibly encrypted); NULL if none + + + + rolvaliduntil + timestamptz + + Password expiry time (only used for password authentication); + NULL if no expiration + + + + rolconfig + text[] + + Session defaults for run-time configuration variables + + + +
+ + + + + + <structname>pg_auth_members</structname> + + + pg_auth_members + + + + The catalog pg_auth_members shows the membership + relations between roles. Any non-circular set of relationships is allowed. + + + + Because user identities are cluster-wide, + pg_auth_members + is shared across all databases of a cluster: there is only one + copy of pg_auth_members per cluster, not + one per database. + + + + <structname>pg_auth_members</> Columns + + + + + Name + Type + References + Description + + + + + + roleid + oid + pg_authid.oid + ID of a role that has a member + + + + member + oid + pg_authid.oid + ID of a role that is a member of roleid + + + + grantor + oid + pg_authid.oid + ID of the role that granted this membership + + + + admin_option + bool + + True if member may grant membership in + roleid to others + + + +
+ + + + <structname>pg_cast</structname> @@ -1065,8 +1260,8 @@ relowner - int4 - pg_shadow.usesysid + oid + pg_authid.oid Owner of the relation @@ -1492,8 +1687,8 @@ conowner - int4 - pg_shadow.usesysid + oid + pg_authid.oid Owner of the conversion @@ -1576,8 +1771,8 @@ datdba - int4 - pg_shadow.usesysid + oid + pg_authid.oid Owner of the database, usually the user who created it @@ -1917,69 +2112,6 @@ - - <structname>pg_group</structname> - - - pg_group - - - - The catalog pg_group defines groups and stores what users belong to what - groups. Groups are created with the CREATE - GROUP command. Consult for information - about user privilege management. - - - - Because user and group identities are cluster-wide, - pg_group - is shared across all databases of a cluster: there is only one - copy of pg_group per cluster, not - one per database. - - - - <structname>pg_group</> Columns - - - - - Name - Type - References - Description - - - - - - groname - name - - Name of the group - - - - grosysid - int4 - - An arbitrary number to identify this group - - - - grolist - int4[] - pg_shadow.usesysid - An array containing the IDs of the users in this group - - - -
- - - - <structname>pg_index</structname> @@ -2437,8 +2569,8 @@ nspowner - int4 - pg_shadow.usesysid + oid + pg_authid.oid Owner of the namespace @@ -2517,9 +2649,9 @@ opcowner - int4 - pg_shadow.usesysid - Operator class owner + oid + pg_authid.oid + Owner of the operator class @@ -2606,8 +2738,8 @@ oprowner - int4 - pg_shadow.usesysid + oid + pg_authid.oid Owner of the operator @@ -2786,8 +2918,8 @@ proowner - int4 - pg_shadow.usesysid + oid + pg_authid.oid Owner of the function @@ -3066,149 +3198,41 @@ - - <structname>pg_shadow</structname> + + <structname>pg_statistic</structname> - - pg_shadow + + pg_statistic - The catalog pg_shadow contains information about - database users. The name stems from the fact that this table - should not be readable by the public since it contains passwords. - pg_user - is a publicly readable view on - pg_shadow that blanks out the password field. + The catalog pg_statistic stores statistical data + about the contents of the database. Entries are created by + ANALYZE and subsequently used by the query planner. + There is one entry for each table column that has been analyzed. + Note that all the statistical data is inherently approximate, + even assuming that it is up-to-date. - contains detailed information about user and - privilege management. + pg_statistic also stores statistical data about + the values of index expressions. These are described as if they were + actual data columns; in particular, starelid + references the index. No entry is made for an ordinary non-expression + index column, however, since it would be redundant with the entry + for the underlying table column. - Because user identities are cluster-wide, - pg_shadow - is shared across all databases of a cluster: there is only one - copy of pg_shadow per cluster, not - one per database. - - - - <structname>pg_shadow</> Columns - - - - - Name - Type - References - Description - - - - - - usename - name - - User name - - - - usesysid - int4 - - User ID (arbitrary number used to reference this user) - - - - usecreatedb - bool - - User may create databases - - - - usesuper - bool - - User is a superuser - - - - usecatupd - bool - - - User may update system catalogs. (Even a superuser may not do - this unless this column is true.) - - - - - passwd - text - - Password (possibly encrypted) - - - - valuntil - abstime - - Password expiry time (only used for password authentication) - - - - useconfig - text[] - - Session defaults for run-time configuration variables - - - -
- - - - - - <structname>pg_statistic</structname> - - - pg_statistic - - - - The catalog pg_statistic stores statistical data - about the contents of the database. Entries are created by - ANALYZE and subsequently used by the query planner. - There is one entry for each table column that has been analyzed. - Note that all the statistical data is inherently approximate, - even assuming that it is up-to-date. - - - - pg_statistic also stores statistical data about - the values of index expressions. These are described as if they were - actual data columns; in particular, starelid - references the index. No entry is made for an ordinary non-expression - index column, however, since it would be redundant with the entry - for the underlying table column. - - - - Since different kinds of statistics may be appropriate for different - kinds of data, pg_statistic is designed not - to assume very much about what sort of statistics it stores. Only - extremely general statistics (such as nullness) are given dedicated - columns in pg_statistic. Everything else - is stored in slots, which are groups of associated columns - whose content is identified by a code number in one of the slot's columns. - For more information see - src/include/catalog/pg_statistic.h. + Since different kinds of statistics may be appropriate for different + kinds of data, pg_statistic is designed not + to assume very much about what sort of statistics it stores. Only + extremely general statistics (such as nullness) are given dedicated + columns in pg_statistic. Everything else + is stored in slots, which are groups of associated columns + whose content is identified by a code number in one of the slot's columns. + For more information see + src/include/catalog/pg_statistic.h. @@ -3374,8 +3398,8 @@ spcowner - int4 - pg_shadow.usesysid + oid + pg_authid.oid Owner of the tablespace, usually the user who created it @@ -3586,8 +3610,8 @@ typowner - int4 - pg_shadow.usesysid + oid + pg_authid.oid Owner of the type @@ -3922,6 +3946,11 @@ + + pg_group + groups of database users + + pg_indexes indexes @@ -3937,6 +3966,11 @@ currently prepared transactions + + pg_roles + database roles + + pg_rules rules @@ -3947,6 +3981,11 @@ parameter settings + + pg_shadow + database users + + pg_stats planner statistics @@ -3972,6 +4011,62 @@ + + <structname>pg_group</structname> + + + pg_group + + + + The view pg_group exists for backwards + compatibility: it emulates a catalog that existed in + PostgreSQL before version 8.1. + It shows the names and members of all roles that are marked as not + rolcanlogin, which is an approximation to the set + of roles that are being used as groups. + + + + <structname>pg_group</> Columns + + + + + Name + Type + References + Description + + + + + + groname + name + pg_authid.rolname + Name of the group + + + + grosysid + oid + pg_authid.oid + ID of this group + + + + grolist + oid[] + pg_authid.oid + An array containing the IDs of the roles in this group + + + +
+ + + <structname>pg_indexes</structname> @@ -4332,7 +4427,7 @@ owner name - pg_shadow.usename + pg_authid.rolname Name of the user that executed the transaction @@ -4361,6 +4456,110 @@ + + <structname>pg_roles</structname> + + + pg_roles + + + + The view pg_roles provides access to + information about database roles. This is simply a publicly + readable view of + pg_authid + that blanks out the password field. + + + + <structname>pg_roles</> Columns + + + + + Name + Type + References + Description + + + + + + rolname + name + + Role name + + + + rolsuper + bool + + Role has superuser privileges + + + + rolcreaterole + bool + + Role may create more roles + + + + rolcreatedb + bool + + Role may create databases + + + + rolcatupdate + bool + + + Role may update system catalogs directly. (Even a superuser may not do + this unless this column is true.) + + + + + rolcanlogin + bool + + + Role may log in, that is, this role can be given as the initial + session authorization identifier. + + + + + rolpassword + text + + Not the password (always reads as ********) + + + + rolvaliduntil + timestamptz + + Password expiry time (only used for password authentication); + NULL if no expiration + + + + rolconfig + text[] + + Session defaults for run-time configuration variables + + + +
+ + + <structname>pg_rules</structname> @@ -4534,6 +4733,107 @@ + + <structname>pg_shadow</structname> + + + pg_shadow + + + + The view pg_shadow exists for backwards + compatibility: it emulates a catalog that existed in + PostgreSQL before version 8.1. + It shows properties of all roles that are marked as + rolcanlogin. + + + + The name stems from the fact that this table + should not be readable by the public since it contains passwords. + pg_user + is a publicly readable view on + pg_shadow that blanks out the password field. + + + + <structname>pg_shadow</> Columns + + + + + Name + Type + References + Description + + + + + + usename + name + pg_authid.rolname + User name + + + + usesysid + oid + pg_authid.oid + ID of this user + + + + usecreatedb + bool + + User may create databases + + + + usesuper + bool + + User is a superuser + + + + usecatupd + bool + + + User may update system catalogs. (Even a superuser may not do + this unless this column is true.) + + + + + passwd + text + + Password (possibly encrypted) + + + + valuntil + abstime + + Password expiry time (only used for password authentication) + + + + useconfig + text[] + + Session defaults for run-time configuration variables + + + +
+ + + <structname>pg_stats</structname> @@ -4720,7 +5020,7 @@ tableowner name - pg_shadow.usename + pg_authid.rolname name of table's owner @@ -4764,7 +5064,7 @@ The view pg_user provides access to information about database users. This is simply a publicly readable view of - pg_shadow + pg_shadow that blanks out the password field. @@ -4885,7 +5185,7 @@ viewowner name - pg_shadow.usename + pg_authid.rolname name of view's owner diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index d7430f1ccf8..734686bf4a4 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ @@ -8443,8 +8443,8 @@ SET search_path TO schema , schema, .. has_table_privilege checks whether a user can access a table in a particular way. The user can be - specified by name or by ID - (pg_user.usesysid), or if the argument is + specified by name or by OID + (pg_authid.oid), or if the argument is omitted current_user is assumed. The table can be specified by name or by OID. (Thus, there are actually six variants of @@ -8756,9 +8756,9 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); in it refer to the relation indicated by the second parameter - pg_get_userbyid(userid) + pg_get_userbyid(roleid) name - get user name with given ID + get role name with given ID pg_get_serial_sequence(table_name, column_name) @@ -8805,7 +8805,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); pg_get_userbyid - extracts a user's name given a user ID number. + extracts a role's name given its OID. pg_get_serial_sequence fetches the name of the sequence associated with a serial or bigserial column. The name is suitably formatted -- cgit v1.2.3