-<!-- $PostgreSQL: pgsql/doc/src/sgml/user-manag.sgml,v 1.34 2006/03/10 19:10:49 momjian Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/user-manag.sgml,v 1.40 2008/09/08 00:47:40 tgl Exp $ -->
<chapter id="user-manag">
<title>Database Roles and Privileges</title>
</para>
<para>
- The set of database roles a given client connection may connect as
+ The set of database roles a given client connection can connect as
is determined by the client authentication setup, as explained in
<xref linkend="client-authentication">. (Thus, a client is not
necessarily limited to connect as the role with the same name as
<title>Role Attributes</title>
<para>
- A database role may have a number of attributes that define its
+ A database role can have a number of attributes that define its
privileges and interact with the client authentication system.
<variablelist>
as the initial role name for a database connection. A role with
the <literal>LOGIN</> attribute can be considered the same thing
as a <quote>database user</>. To create a role with login privilege,
- use either
+ use either:
<programlisting>
CREATE ROLE <replaceable>name</replaceable> LOGIN;
CREATE USER <replaceable>name</replaceable>;
configuration settings described in <xref
linkend="runtime-config">. For example, if for some reason you
want to disable index scans (hint: not a good idea) anytime you
- connect, you can use
+ connect, you can use:
<programlisting>
ALTER ROLE myname SET enable_indexscan TO off;
</programlisting>
granted.
There are several different kinds of privilege: <literal>SELECT</>,
<literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
- <literal>RULE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
- <literal>CREATE</>, <literal>TEMPORARY</>, <literal>EXECUTE</>,
- and <literal>USAGE</>. For more
- information on the different types of privileges supported by
+ <literal>TRUNCATE</>, <literal>REFERENCES</>, <literal>TRIGGER</>,
+ <literal>CREATE</>, <literal>CONNECT</>, <literal>TEMPORARY</>,
+ <literal>EXECUTE</>, and <literal>USAGE</>.
+ For more information on the different types of privileges supported by
<productname>PostgreSQL</productname>, see the
<xref linkend="sql-grant" endterm="sql-grant-title"> reference page.
</para>
To assign privileges, the <command>GRANT</command> command is
used. So, if <literal>joe</literal> is an existing role, and
<literal>accounts</literal> is an existing table, the privilege to
- update the table can be granted with
+ update the table can be granted with:
<programlisting>
GRANT UPDATE ON accounts TO joe;
</programlisting>
</synopsis>
You can grant membership to other group roles, too (since there isn't
really any distinction between group roles and non-group roles). The
- only restriction is that you can't set up circular membership loops.
+ database will not let you set up circular membership loops. Also,
+ it is not permitted to grant membership in a role to
+ <literal>PUBLIC</literal>.
</para>
<para>
considered owned by the group role not the login role. Second, member
roles that have the <literal>INHERIT</> attribute automatically have use of
privileges of roles they are members of. As an example, suppose we have
- done
+ done:
<programlisting>
CREATE ROLE joe LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
granted to <literal>wheel</> are not available, because even though
<literal>joe</> is indirectly a member of <literal>wheel</>, the
membership is via <literal>admin</> which has the <literal>NOINHERIT</>
- attribute. After
+ attribute. After:
<programlisting>
SET ROLE admin;
</programlisting>
the session would have use of only those privileges granted to
- <literal>admin</>, and not those granted to <literal>joe</>. After
+ <literal>admin</>, and not those granted to <literal>joe</>. After:
<programlisting>
SET ROLE wheel;
</programlisting>
the session would have use of only those privileges granted to
<literal>wheel</>, and not those granted to either <literal>joe</>
or <literal>admin</>. The original privilege state can be restored
- with any of
+ with any of:
<programlisting>
SET ROLE joe;
SET ROLE NONE;
<para>
Functions and triggers allow users to insert code into the backend
- server that other users may execute unintentionally. Hence, both
+ server that other users might execute unintentionally. Hence, both
mechanisms permit users to <quote>Trojan horse</quote>
others with relative ease. The only real protection is tight
control over who can define functions.