diff options
Diffstat (limited to 'doc/src/sgml/html/ddl-priv.html')
| -rw-r--r-- | doc/src/sgml/html/ddl-priv.html | 307 |
1 files changed, 307 insertions, 0 deletions
diff --git a/doc/src/sgml/html/ddl-priv.html b/doc/src/sgml/html/ddl-priv.html new file mode 100644 index 0000000..02fd289 --- /dev/null +++ b/doc/src/sgml/html/ddl-priv.html @@ -0,0 +1,307 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>5.7. Privileges</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="ddl-alter.html" title="5.6. Modifying Tables" /><link rel="next" href="ddl-rowsecurity.html" title="5.8. Row Security Policies" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">5.7. Privileges</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl-alter.html" title="5.6. Modifying Tables">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><th width="60%" align="center">Chapter 5. Data Definition</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ddl-rowsecurity.html" title="5.8. Row Security Policies">Next</a></td></tr></table><hr /></div><div class="sect1" id="DDL-PRIV"><div class="titlepage"><div><div><h2 class="title" style="clear: both">5.7. Privileges</h2></div></div></div><a id="id-1.5.4.9.2" class="indexterm"></a><a id="id-1.5.4.9.3" class="indexterm"></a><a id="id-1.5.4.9.4" class="indexterm"></a><a id="id-1.5.4.9.5" class="indexterm"></a><a id="id-1.5.4.9.6" class="indexterm"></a><a id="id-1.5.4.9.7" class="indexterm"></a><p> + When an object is created, it is assigned an owner. The + owner is normally the role that executed the creation statement. + For most kinds of objects, the initial state is that only the owner + (or a superuser) can do anything with the object. To allow + other roles to use it, <em class="firstterm">privileges</em> must be + granted. + </p><p> + There are different kinds of privileges: <code class="literal">SELECT</code>, + <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, <code class="literal">DELETE</code>, + <code class="literal">TRUNCATE</code>, <code class="literal">REFERENCES</code>, <code class="literal">TRIGGER</code>, + <code class="literal">CREATE</code>, <code class="literal">CONNECT</code>, <code class="literal">TEMPORARY</code>, + <code class="literal">EXECUTE</code>, <code class="literal">USAGE</code>, <code class="literal">SET</code> + and <code class="literal">ALTER SYSTEM</code>. + The privileges applicable to a particular + object vary depending on the object's type (table, function, etc.). + More detail about the meanings of these privileges appears below. + The following sections and chapters will also show you how + these privileges are used. + </p><p> + The right to modify or destroy an object is inherent in being the + object's owner, and cannot be granted or revoked in itself. + (However, like all privileges, that right can be inherited by + members of the owning role; see <a class="xref" href="role-membership.html" title="22.3. Role Membership">Section 22.3</a>.) + </p><p> + An object can be assigned to a new owner with an <code class="command">ALTER</code> + command of the appropriate kind for the object, for example +</p><pre class="programlisting"> +ALTER TABLE <em class="replaceable"><code>table_name</code></em> OWNER TO <em class="replaceable"><code>new_owner</code></em>; +</pre><p> + Superusers can always do this; ordinary roles can only do it if they are + both the current owner of the object (or a member of the owning role) and + a member of the new owning role. + </p><p> + To assign privileges, the <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a> command is + used. For example, if <code class="literal">joe</code> is an existing role, and + <code class="literal">accounts</code> is an existing table, the privilege to + update the table can be granted with: +</p><pre class="programlisting"> +GRANT UPDATE ON accounts TO joe; +</pre><p> + Writing <code class="literal">ALL</code> in place of a specific privilege grants all + privileges that are relevant for the object type. + </p><p> + The special <span class="quote">“<span class="quote">role</span>”</span> name <code class="literal">PUBLIC</code> can + be used to grant a privilege to every role on the system. Also, + <span class="quote">“<span class="quote">group</span>”</span> roles can be set up to help manage privileges when + there are many users of a database — for details see + <a class="xref" href="user-manag.html" title="Chapter 22. Database Roles">Chapter 22</a>. + </p><p> + To revoke a previously-granted privilege, use the fittingly named + <a class="xref" href="sql-revoke.html" title="REVOKE"><span class="refentrytitle">REVOKE</span></a> command: +</p><pre class="programlisting"> +REVOKE ALL ON accounts FROM PUBLIC; +</pre><p> + </p><p> + Ordinarily, only the object's owner (or a superuser) can grant or + revoke privileges on an object. However, it is possible to grant a + privilege <span class="quote">“<span class="quote">with grant option</span>”</span>, which gives the recipient + the right to grant it in turn to others. If the grant option is + subsequently revoked then all who received the privilege from that + recipient (directly or through a chain of grants) will lose the + privilege. For details see the <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a> and + <a class="xref" href="sql-revoke.html" title="REVOKE"><span class="refentrytitle">REVOKE</span></a> reference pages. + </p><p> + An object's owner can choose to revoke their own ordinary privileges, + for example to make a table read-only for themselves as well as others. + But owners are always treated as holding all grant options, so they + can always re-grant their own privileges. + </p><p> + The available privileges are: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">SELECT</code></span></dt><dd><p> + Allows <code class="command">SELECT</code> from + any column, or specific column(s), of a table, view, materialized + view, or other table-like object. + Also allows use of <code class="command">COPY TO</code>. + This privilege is also needed to reference existing column values in + <code class="command">UPDATE</code>, <code class="command">DELETE</code>, + or <code class="command">MERGE</code>. + For sequences, this privilege also allows use of the + <code class="function">currval</code> function. + For large objects, this privilege allows the object to be read. + </p></dd><dt><span class="term"><code class="literal">INSERT</code></span></dt><dd><p> + Allows <code class="command">INSERT</code> of a new row into a table, view, + etc. Can be granted on specific column(s), in which case + only those columns may be assigned to in the <code class="command">INSERT</code> + command (other columns will therefore receive default values). + Also allows use of <code class="command">COPY FROM</code>. + </p></dd><dt><span class="term"><code class="literal">UPDATE</code></span></dt><dd><p> + Allows <code class="command">UPDATE</code> of any + column, or specific column(s), of a table, view, etc. + (In practice, any nontrivial <code class="command">UPDATE</code> command will + require <code class="literal">SELECT</code> privilege as well, since it must + reference table columns to determine which rows to update, and/or to + compute new values for columns.) + <code class="literal">SELECT ... FOR UPDATE</code> + and <code class="literal">SELECT ... FOR SHARE</code> + also require this privilege on at least one column, in addition to the + <code class="literal">SELECT</code> privilege. For sequences, this + privilege allows use of the <code class="function">nextval</code> and + <code class="function">setval</code> functions. + For large objects, this privilege allows writing or truncating the + object. + </p></dd><dt><span class="term"><code class="literal">DELETE</code></span></dt><dd><p> + Allows <code class="command">DELETE</code> of a row from a table, view, etc. + (In practice, any nontrivial <code class="command">DELETE</code> command will + require <code class="literal">SELECT</code> privilege as well, since it must + reference table columns to determine which rows to delete.) + </p></dd><dt><span class="term"><code class="literal">TRUNCATE</code></span></dt><dd><p> + Allows <code class="command">TRUNCATE</code> on a table. + </p></dd><dt><span class="term"><code class="literal">REFERENCES</code></span></dt><dd><p> + Allows creation of a foreign key constraint referencing a + table, or specific column(s) of a table. + </p></dd><dt><span class="term"><code class="literal">TRIGGER</code></span></dt><dd><p> + Allows creation of a trigger on a table, view, etc. + </p></dd><dt><span class="term"><code class="literal">CREATE</code></span></dt><dd><p> + For databases, allows new schemas and publications to be created within + the database, and allows trusted extensions to be installed within + the database. + </p><p> + For schemas, allows new objects to be created within the schema. + To rename an existing object, you must own the + object <span class="emphasis"><em>and</em></span> have this privilege for the containing + schema. + </p><p> + For tablespaces, allows tables, indexes, and temporary files to be + created within the tablespace, and allows databases to be created that + have the tablespace as their default tablespace. + </p><p> + Note that revoking this privilege will not alter the existence or + location of existing objects. + </p></dd><dt><span class="term"><code class="literal">CONNECT</code></span></dt><dd><p> + Allows the grantee to connect to the database. This + privilege is checked at connection startup (in addition to checking + any restrictions imposed by <code class="filename">pg_hba.conf</code>). + </p></dd><dt><span class="term"><code class="literal">TEMPORARY</code></span></dt><dd><p> + Allows temporary tables to be created while using the database. + </p></dd><dt><span class="term"><code class="literal">EXECUTE</code></span></dt><dd><p> + Allows calling a function or procedure, including use of + any operators that are implemented on top of the function. This is the + only type of privilege that is applicable to functions and procedures. + </p></dd><dt><span class="term"><code class="literal">USAGE</code></span></dt><dd><p> + For procedural languages, allows use of the language for + the creation of functions in that language. This is the only type + of privilege that is applicable to procedural languages. + </p><p> + For schemas, allows access to objects contained in the + schema (assuming that the objects' own privilege requirements are + also met). Essentially this allows the grantee to <span class="quote">“<span class="quote">look up</span>”</span> + objects within the schema. Without this permission, it is still + possible to see the object names, e.g., by querying system catalogs. + Also, after revoking this permission, existing sessions might have + statements that have previously performed this lookup, so this is not + a completely secure way to prevent object access. + </p><p> + For sequences, allows use of the + <code class="function">currval</code> and <code class="function">nextval</code> functions. + </p><p> + For types and domains, allows use of the type or domain in the + creation of tables, functions, and other schema objects. (Note that + this privilege does not control all <span class="quote">“<span class="quote">usage</span>”</span> of the + type, such as values of the type appearing in queries. It only + prevents objects from being created that depend on the type. The + main purpose of this privilege is controlling which users can create + dependencies on a type, which could prevent the owner from changing + the type later.) + </p><p> + For foreign-data wrappers, allows creation of new servers using the + foreign-data wrapper. + </p><p> + For foreign servers, allows creation of foreign tables using the + server. Grantees may also create, alter, or drop their own user + mappings associated with that server. + </p></dd><dt><span class="term"><code class="literal">SET</code></span></dt><dd><p> + Allows a server configuration parameter to be set to a new value + within the current session. (While this privilege can be granted + on any parameter, it is meaningless except for parameters that would + normally require superuser privilege to set.) + </p></dd><dt><span class="term"><code class="literal">ALTER SYSTEM</code></span></dt><dd><p> + Allows a server configuration parameter to be configured to a new + value using the <a class="xref" href="sql-altersystem.html" title="ALTER SYSTEM"><span class="refentrytitle">ALTER SYSTEM</span></a> command. + </p></dd></dl></div><p> + + The privileges required by other commands are listed on the + reference page of the respective command. + </p><p> + PostgreSQL grants privileges on some types of objects to + <code class="literal">PUBLIC</code> by default when the objects are created. + No privileges are granted to <code class="literal">PUBLIC</code> by default on + tables, + table columns, + sequences, + foreign data wrappers, + foreign servers, + large objects, + schemas, + tablespaces, + or configuration parameters. + For other types of objects, the default privileges + granted to <code class="literal">PUBLIC</code> are as follows: + <code class="literal">CONNECT</code> and <code class="literal">TEMPORARY</code> (create + temporary tables) privileges for databases; + <code class="literal">EXECUTE</code> privilege for functions and procedures; and + <code class="literal">USAGE</code> privilege for languages and data types + (including domains). + The object owner can, of course, <code class="command">REVOKE</code> + both default and expressly granted privileges. (For maximum + security, issue the <code class="command">REVOKE</code> in the same transaction that + creates the object; then there is no window in which another user + can use the object.) + Also, these default privilege settings can be overridden using the + <a class="xref" href="sql-alterdefaultprivileges.html" title="ALTER DEFAULT PRIVILEGES"><span class="refentrytitle">ALTER DEFAULT PRIVILEGES</span></a> command. + </p><p> + <a class="xref" href="ddl-priv.html#PRIVILEGE-ABBREVS-TABLE" title="Table 5.1. ACL Privilege Abbreviations">Table 5.1</a> shows the one-letter + abbreviations that are used for these privilege types in + <em class="firstterm">ACL</em> (Access Control List) values. + You will see these letters in the output of the <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a> + commands listed below, or when looking at ACL columns of system catalogs. + </p><div class="table" id="PRIVILEGE-ABBREVS-TABLE"><p class="title"><strong>Table 5.1. ACL Privilege Abbreviations</strong></p><div class="table-contents"><table class="table" summary="ACL Privilege Abbreviations" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /></colgroup><thead><tr><th>Privilege</th><th>Abbreviation</th><th>Applicable Object Types</th></tr></thead><tbody><tr><td><code class="literal">SELECT</code></td><td><code class="literal">r</code> (<span class="quote">“<span class="quote">read</span>”</span>)</td><td> + <code class="literal">LARGE OBJECT</code>, + <code class="literal">SEQUENCE</code>, + <code class="literal">TABLE</code> (and table-like objects), + table column + </td></tr><tr><td><code class="literal">INSERT</code></td><td><code class="literal">a</code> (<span class="quote">“<span class="quote">append</span>”</span>)</td><td><code class="literal">TABLE</code>, table column</td></tr><tr><td><code class="literal">UPDATE</code></td><td><code class="literal">w</code> (<span class="quote">“<span class="quote">write</span>”</span>)</td><td> + <code class="literal">LARGE OBJECT</code>, + <code class="literal">SEQUENCE</code>, + <code class="literal">TABLE</code>, + table column + </td></tr><tr><td><code class="literal">DELETE</code></td><td><code class="literal">d</code></td><td><code class="literal">TABLE</code></td></tr><tr><td><code class="literal">TRUNCATE</code></td><td><code class="literal">D</code></td><td><code class="literal">TABLE</code></td></tr><tr><td><code class="literal">REFERENCES</code></td><td><code class="literal">x</code></td><td><code class="literal">TABLE</code>, table column</td></tr><tr><td><code class="literal">TRIGGER</code></td><td><code class="literal">t</code></td><td><code class="literal">TABLE</code></td></tr><tr><td><code class="literal">CREATE</code></td><td><code class="literal">C</code></td><td> + <code class="literal">DATABASE</code>, + <code class="literal">SCHEMA</code>, + <code class="literal">TABLESPACE</code> + </td></tr><tr><td><code class="literal">CONNECT</code></td><td><code class="literal">c</code></td><td><code class="literal">DATABASE</code></td></tr><tr><td><code class="literal">TEMPORARY</code></td><td><code class="literal">T</code></td><td><code class="literal">DATABASE</code></td></tr><tr><td><code class="literal">EXECUTE</code></td><td><code class="literal">X</code></td><td><code class="literal">FUNCTION</code>, <code class="literal">PROCEDURE</code></td></tr><tr><td><code class="literal">USAGE</code></td><td><code class="literal">U</code></td><td> + <code class="literal">DOMAIN</code>, + <code class="literal">FOREIGN DATA WRAPPER</code>, + <code class="literal">FOREIGN SERVER</code>, + <code class="literal">LANGUAGE</code>, + <code class="literal">SCHEMA</code>, + <code class="literal">SEQUENCE</code>, + <code class="literal">TYPE</code> + </td></tr><tr><td><code class="literal">SET</code></td><td><code class="literal">s</code></td><td><code class="literal">PARAMETER</code></td></tr><tr><td><code class="literal">ALTER SYSTEM</code></td><td><code class="literal">A</code></td><td><code class="literal">PARAMETER</code></td></tr></tbody></table></div></div><br class="table-break" /><p> + <a class="xref" href="ddl-priv.html#PRIVILEGES-SUMMARY-TABLE" title="Table 5.2. Summary of Access Privileges">Table 5.2</a> summarizes the privileges + available for each type of SQL object, using the abbreviations shown + above. + It also shows the <span class="application">psql</span> command + that can be used to examine privilege settings for each object type. + </p><div class="table" id="PRIVILEGES-SUMMARY-TABLE"><p class="title"><strong>Table 5.2. Summary of Access Privileges</strong></p><div class="table-contents"><table class="table" summary="Summary of Access Privileges" border="1"><colgroup><col class="col1" /><col class="col2" /><col class="col3" /><col class="col4" /></colgroup><thead><tr><th>Object Type</th><th>All Privileges</th><th>Default <code class="literal">PUBLIC</code> Privileges</th><th><span class="application">psql</span> Command</th></tr></thead><tbody><tr><td><code class="literal">DATABASE</code></td><td><code class="literal">CTc</code></td><td><code class="literal">Tc</code></td><td><code class="literal">\l</code></td></tr><tr><td><code class="literal">DOMAIN</code></td><td><code class="literal">U</code></td><td><code class="literal">U</code></td><td><code class="literal">\dD+</code></td></tr><tr><td><code class="literal">FUNCTION</code> or <code class="literal">PROCEDURE</code></td><td><code class="literal">X</code></td><td><code class="literal">X</code></td><td><code class="literal">\df+</code></td></tr><tr><td><code class="literal">FOREIGN DATA WRAPPER</code></td><td><code class="literal">U</code></td><td>none</td><td><code class="literal">\dew+</code></td></tr><tr><td><code class="literal">FOREIGN SERVER</code></td><td><code class="literal">U</code></td><td>none</td><td><code class="literal">\des+</code></td></tr><tr><td><code class="literal">LANGUAGE</code></td><td><code class="literal">U</code></td><td><code class="literal">U</code></td><td><code class="literal">\dL+</code></td></tr><tr><td><code class="literal">LARGE OBJECT</code></td><td><code class="literal">rw</code></td><td>none</td><td><code class="literal">\dl+</code></td></tr><tr><td><code class="literal">PARAMETER</code></td><td><code class="literal">sA</code></td><td>none</td><td><code class="literal">\dconfig+</code></td></tr><tr><td><code class="literal">SCHEMA</code></td><td><code class="literal">UC</code></td><td>none</td><td><code class="literal">\dn+</code></td></tr><tr><td><code class="literal">SEQUENCE</code></td><td><code class="literal">rwU</code></td><td>none</td><td><code class="literal">\dp</code></td></tr><tr><td><code class="literal">TABLE</code> (and table-like objects)</td><td><code class="literal">arwdDxt</code></td><td>none</td><td><code class="literal">\dp</code></td></tr><tr><td>Table column</td><td><code class="literal">arwx</code></td><td>none</td><td><code class="literal">\dp</code></td></tr><tr><td><code class="literal">TABLESPACE</code></td><td><code class="literal">C</code></td><td>none</td><td><code class="literal">\db+</code></td></tr><tr><td><code class="literal">TYPE</code></td><td><code class="literal">U</code></td><td><code class="literal">U</code></td><td><code class="literal">\dT+</code></td></tr></tbody></table></div></div><br class="table-break" /><p> + <a id="id-1.5.4.9.23.1" class="indexterm"></a> + The privileges that have been granted for a particular object are + displayed as a list of <code class="type">aclitem</code> entries, where each + <code class="type">aclitem</code> describes the permissions of one grantee that + have been granted by a particular grantor. For example, + <code class="literal">calvin=r*w/hobbes</code> specifies that the role + <code class="literal">calvin</code> has the privilege + <code class="literal">SELECT</code> (<code class="literal">r</code>) with grant option + (<code class="literal">*</code>) as well as the non-grantable + privilege <code class="literal">UPDATE</code> (<code class="literal">w</code>), both granted + by the role <code class="literal">hobbes</code>. If <code class="literal">calvin</code> + also has some privileges on the same object granted by a different + grantor, those would appear as a separate <code class="type">aclitem</code> entry. + An empty grantee field in an <code class="type">aclitem</code> stands + for <code class="literal">PUBLIC</code>. + </p><p> + As an example, suppose that user <code class="literal">miriam</code> creates + table <code class="literal">mytable</code> and does: +</p><pre class="programlisting"> +GRANT SELECT ON mytable TO PUBLIC; +GRANT SELECT, UPDATE, INSERT ON mytable TO admin; +GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw; +</pre><p> + Then <span class="application">psql</span>'s <code class="literal">\dp</code> command + would show: +</p><pre class="programlisting"> +=> \dp mytable + Access privileges + Schema | Name | Type | Access privileges | Column privileges | Policies +--------+---------+-------+-----------------------+-----------------------+---------- + public | mytable | table | miriam=arwdDxt/miriam+| col1: +| + | | | =r/miriam +| miriam_rw=rw/miriam | + | | | admin=arw/miriam | | +(1 row) +</pre><p> + </p><p> + If the <span class="quote">“<span class="quote">Access privileges</span>”</span> column is empty for a given + object, it means the object has default privileges (that is, its + privileges entry in the relevant system catalog is null). Default + privileges always include all privileges for the owner, and can include + some privileges for <code class="literal">PUBLIC</code> depending on the object + type, as explained above. The first <code class="command">GRANT</code> + or <code class="command">REVOKE</code> on an object will instantiate the default + privileges (producing, for + example, <code class="literal">miriam=arwdDxt/miriam</code>) and then modify them + per the specified request. Similarly, entries are shown in <span class="quote">“<span class="quote">Column + privileges</span>”</span> only for columns with nondefault privileges. + (Note: for this purpose, <span class="quote">“<span class="quote">default privileges</span>”</span> always means + the built-in default privileges for the object's type. An object whose + privileges have been affected by an <code class="command">ALTER DEFAULT + PRIVILEGES</code> command will always be shown with an explicit + privilege entry that includes the effects of + the <code class="command">ALTER</code>.) + </p><p> + Notice that the owner's implicit grant options are not marked in the + access privileges display. A <code class="literal">*</code> will appear only when + grant options have been explicitly granted to someone. + </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ddl-alter.html" title="5.6. Modifying Tables">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ddl.html" title="Chapter 5. Data Definition">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ddl-rowsecurity.html" title="5.8. Row Security Policies">Next</a></td></tr><tr><td width="40%" align="left" valign="top">5.6. Modifying Tables </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.8. Row Security Policies</td></tr></table></div></body></html>
\ No newline at end of file |