From 1826987a46d079458007b7b6bbcbbd852353adbb Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Tue, 23 Dec 2014 10:22:09 -0300 Subject: Use a bitmask to represent role attributes MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit The previous representation using a boolean column for each attribute would not scale as well as we want to add further attributes. Extra auxilliary functions are added to go along with this change, to make up for the lost convenience of access of the old representation. Catalog version bumped due to change in catalogs and the new functions. Author: Adam Brightwell, minor tweaks by Álvaro Reviewed by: Stephen Frost, Andres Freund, Álvaro Herrera --- doc/src/sgml/catalogs.sgml | 127 ++++++++++++++++++++++++++++++--------------- doc/src/sgml/func.sgml | 127 +++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 213 insertions(+), 41 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 9ceb96b54c7..947091627fd 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1391,89 +1391,134 @@ - rolsuper - bool + rolattr + bigint + + Role attributes; see and + for details + + + + + rolconnlimit + int4 + + For roles that can log in, this sets maximum number of concurrent + connections this role can make. -1 means no limit. + + + + + rolpassword + text + + Password (possibly encrypted); null if none. If the password + is encrypted, this column will begin with the string md5 + followed by a 32-character hexadecimal MD5 hash. The MD5 hash + will be of the user's password concatenated to their user name. + For example, if user joe has password xyzzy, + PostgreSQL will store the md5 hash of + xyzzyjoe. A password that does not follow that + format is assumed to be unencrypted. + + + + + rolvaliduntil + timestamptz + Password expiry time (only used for password authentication); + null if no expiration + + + + + + + Attributes in <structfield>rolattr</> + + + + + Attribute + CREATE ROLE Option + Description + Position + + + + + + Superuser + SUPERUSER Role has superuser privileges + 0 - rolinherit - bool - Role automatically inherits privileges of roles it is a - member of + Inherit + INHERIT + + Role automatically inherits privileges of roles it is a member of + + 1 - rolcreaterole - bool + Create Role + CREATEROLE Role can create more roles + 2 - rolcreatedb - bool + Create DB + CREATEDB Role can create databases + 3 - rolcatupdate - bool + Catalog Update + CATUPDATE Role can update system catalogs directly. (Even a superuser cannot do this unless this column is true) + 4 - rolcanlogin - bool + Can Login + LOGIN Role can log in. That is, this role can be given as the initial session authorization identifier + 5 - rolreplication - bool + Replication + REPLICATION Role is a replication role. That is, this role can initiate streaming replication (see ) and set/unset the system backup mode using pg_start_backup and pg_stop_backup + 6 - rolconnlimit - int4 + Bypass Row Level Security + BYPASSRLS - For roles that can log in, this sets maximum number of concurrent - connections this role can make. -1 means no limit. - - - - - rolpassword - text - - Password (possibly encrypted); null if none. If the password - is encrypted, this column will begin with the string md5 - followed by a 32-character hexadecimal MD5 hash. The MD5 hash - will be of the user's password concatenated to their user name. - For example, if user joe has password xyzzy, - PostgreSQL will store the md5 hash of - xyzzyjoe. A password that does not follow that - format is assumed to be unencrypted. + Role can bypass row level security policies when row_security + is set off + 7 - - rolvaliduntil - timestamptz - Password expiry time (only used for password authentication); - null if no expiration -
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 24c64b7187f..2a37e65eb9a 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -15139,6 +15139,133 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute'); are immediately available without doing SET ROLE. + + lists functions that + allow the user to query role attribute information programmatically. + + + + Role Attribute Inquiry Functions + + + Name Return Type Description + + + + pg_has_role_attribute(role, attribute) + boolean + does role have the permissions allowed by named attribute + + + pg_check_role_attribute(role, attribute) + boolean + does role have the named attribute + + + pg_check_role_attribute(role_attributes, attribute) + boolean + is attribute set in bitmap of role attributes + + + pg_all_role_attributes(role_attributes) + text[] + convert bitmap of role attribute representation to text[] + + + +
+ + + pg_has_role_attribute + + + pg_check_role_attribute + + + pg_all_role_attributes + + + + pg_has_role_attribute checks the attribute permissions + given to a role. It will always return true for roles + with superuser privileges unless the attribute being checked is + CATUPDATE (superuser cannot bypass + CATUPDATE permissions). The role can be specified by name + and by OID. The attribute is specified by a text string which must evaluate + to one of the following role attributes: + SUPERUSER, + INHERIT, + CREATEROLE, + CREATEDB, + CATUPDATE, + CANLOGIN, + REPLICATION, or + BYPASSRLS. See for more + information. For example: + +SELECT pg_has_role_attribute('joe', 'SUPERUSER'); + pg_has_role_attribute +----------------------- + f +(1 row) + +SELECT rolname, pg_has_role_attribute(oid, 'INHERIT') AS rolinherit FROM pg_roles; + rolname | rolinherit +----------+------------ + postgres | t + joe | t +(2 rows) + + + + + pg_check_role_attribute checks the attribute value given + to a role. The role can be specified by name and by OID. The attribute is + specified by a text string which must evaluate to a valid role attribute (see + pg_has_role_attribute). A third variant of this function + allows for a bitmap representation (bigint) of attributes + to be given instead of a role. + Example: + +SELECT pg_check_role_attribute('joe', 'SUPERUSER'); + pg_check_role_attribute +------------------------- + f +(1 row) + +SELECT rolname, pg_check_role_attribute(oid, 'INHERIT') as rolinherit FROM pg_roles; + rolname | rolinherit +----------+------------ + postgres | t + joe | t +(2 rows) + t +(1 row) + + +SELECT rolname, pg_check_role_attribute(rolattr, 'SUPERUSER') AS rolsuper FROM pg_authid; + rolname | rolsuper +----------+---------- + postgres | t + joe | f +(2 rows) + + + + + pg_all_role_attributes convert a set of role attributes + represented by an bigint bitmap to a text array. + Example: + +SELECT rolname, pg_all_role_attributes(rolattr) AS attributes FROM pg_authid; + rolname | attributes +----------+----------------------------------------------------------------------------------------------- + postgres | {Superuser,Inherit,"Create Role","Create DB","Catalog Update",Login,Replication,"Bypass RLS"} + joe | {Inherit,Login} +(2 rows) + + + shows functions that determine whether a certain object is visible in the -- cgit v1.2.3