summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-grant.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/sql-grant.html
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/sql-grant.html')
-rw-r--r--doc/src/sgml/html/sql-grant.html318
1 files changed, 318 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-grant.html b/doc/src/sgml/html/sql-grant.html
new file mode 100644
index 0000000..9d4a136
--- /dev/null
+++ b/doc/src/sgml/html/sql-grant.html
@@ -0,0 +1,318 @@
+<?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>GRANT</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="sql-fetch.html" title="FETCH" /><link rel="next" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">GRANT</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-fetch.html" title="FETCH">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-GRANT"><div class="titlepage"></div><a id="id-1.9.3.150.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">GRANT</span></h2><p>GRANT — define access privileges</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
+GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+ [, ...] | ALL [ PRIVILEGES ] }
+ ON { [ TABLE ] <em class="replaceable"><code>table_name</code></em> [, ...]
+ | ALL TABLES IN SCHEMA <em class="replaceable"><code>schema_name</code></em> [, ...] }
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( <em class="replaceable"><code>column_name</code></em> [, ...] )
+ [, ...] | ALL [ PRIVILEGES ] ( <em class="replaceable"><code>column_name</code></em> [, ...] ) }
+ ON [ TABLE ] <em class="replaceable"><code>table_name</code></em> [, ...]
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { { USAGE | SELECT | UPDATE }
+ [, ...] | ALL [ PRIVILEGES ] }
+ ON { SEQUENCE <em class="replaceable"><code>sequence_name</code></em> [, ...]
+ | ALL SEQUENCES IN SCHEMA <em class="replaceable"><code>schema_name</code></em> [, ...] }
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
+ ON DATABASE <em class="replaceable"><code>database_name</code></em> [, ...]
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { USAGE | ALL [ PRIVILEGES ] }
+ ON DOMAIN <em class="replaceable"><code>domain_name</code></em> [, ...]
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { USAGE | ALL [ PRIVILEGES ] }
+ ON FOREIGN DATA WRAPPER <em class="replaceable"><code>fdw_name</code></em> [, ...]
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { USAGE | ALL [ PRIVILEGES ] }
+ ON FOREIGN SERVER <em class="replaceable"><code>server_name</code></em> [, ...]
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { EXECUTE | ALL [ PRIVILEGES ] }
+ ON { { FUNCTION | PROCEDURE | ROUTINE } <em class="replaceable"><code>routine_name</code></em> [ ( [ [ <em class="replaceable"><code>argmode</code></em> ] [ <em class="replaceable"><code>arg_name</code></em> ] <em class="replaceable"><code>arg_type</code></em> [, ...] ] ) ] [, ...]
+ | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA <em class="replaceable"><code>schema_name</code></em> [, ...] }
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { USAGE | ALL [ PRIVILEGES ] }
+ ON LANGUAGE <em class="replaceable"><code>lang_name</code></em> [, ...]
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
+ ON LARGE OBJECT <em class="replaceable"><code>loid</code></em> [, ...]
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { { SET | ALTER SYSTEM } [, ... ] | ALL [ PRIVILEGES ] }
+ ON PARAMETER <em class="replaceable"><code>configuration_parameter</code></em> [, ...]
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
+ ON SCHEMA <em class="replaceable"><code>schema_name</code></em> [, ...]
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { CREATE | ALL [ PRIVILEGES ] }
+ ON TABLESPACE <em class="replaceable"><code>tablespace_name</code></em> [, ...]
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT { USAGE | ALL [ PRIVILEGES ] }
+ ON TYPE <em class="replaceable"><code>type_name</code></em> [, ...]
+ TO <em class="replaceable"><code>role_specification</code></em> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+GRANT <em class="replaceable"><code>role_name</code></em> [, ...] TO <em class="replaceable"><code>role_specification</code></em> [, ...]
+ [ WITH ADMIN OPTION ]
+ [ GRANTED BY <em class="replaceable"><code>role_specification</code></em> ]
+
+<span class="phrase">where <em class="replaceable"><code>role_specification</code></em> can be:</span>
+
+ [ GROUP ] <em class="replaceable"><code>role_name</code></em>
+ | PUBLIC
+ | CURRENT_ROLE
+ | CURRENT_USER
+ | SESSION_USER
+</pre></div><div class="refsect1" id="SQL-GRANT-DESCRIPTION"><h2>Description</h2><p>
+ The <code class="command">GRANT</code> command has two basic variants: one
+ that grants privileges on a database object (table, column, view,
+ foreign table, sequence, database, foreign-data wrapper, foreign server,
+ function, procedure, procedural language, large object, configuration
+ parameter, schema, tablespace, or type), and one that grants
+ membership in a role. These variants are similar in many ways, but
+ they are different enough to be described separately.
+ </p><div class="refsect2" id="SQL-GRANT-DESCRIPTION-OBJECTS"><h3>GRANT on Database Objects</h3><p>
+ This variant of the <code class="command">GRANT</code> command gives specific
+ privileges on a database object to
+ one or more roles. These privileges are added
+ to those already granted, if any.
+ </p><p>
+ The key word <code class="literal">PUBLIC</code> indicates that the
+ privileges are to be granted to all roles, including those that might
+ be created later. <code class="literal">PUBLIC</code> can be thought of as an
+ implicitly defined group that always includes all roles.
+ Any particular role will have the sum
+ of privileges granted directly to it, privileges granted to any role it
+ is presently a member of, and privileges granted to
+ <code class="literal">PUBLIC</code>.
+ </p><p>
+ If <code class="literal">WITH GRANT OPTION</code> is specified, the recipient
+ of the privilege can in turn grant it to others. Without a grant
+ option, the recipient cannot do that. Grant options cannot be granted
+ to <code class="literal">PUBLIC</code>.
+ </p><p>
+ If <code class="literal">GRANTED BY</code> is specified, the specified grantor must
+ be the current user. This clause is currently present in this form only
+ for SQL compatibility.
+ </p><p>
+ There is no need to grant privileges to the owner of an object
+ (usually the user that created it),
+ as the owner has all privileges by default. (The owner could,
+ however, choose to revoke some of their own privileges for safety.)
+ </p><p>
+ The right to drop an object, or to alter its definition in any way, is
+ not treated as a grantable privilege; it is inherent in the owner,
+ and cannot be granted or revoked. (However, a similar effect can be
+ obtained by granting or revoking membership in the role that owns
+ the object; see below.) The owner implicitly has all grant
+ options for the object, too.
+ </p><p>
+ The possible privileges are:
+
+ </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">SELECT</code><br /></span><span class="term"><code class="literal">INSERT</code><br /></span><span class="term"><code class="literal">UPDATE</code><br /></span><span class="term"><code class="literal">DELETE</code><br /></span><span class="term"><code class="literal">TRUNCATE</code><br /></span><span class="term"><code class="literal">REFERENCES</code><br /></span><span class="term"><code class="literal">TRIGGER</code><br /></span><span class="term"><code class="literal">CREATE</code><br /></span><span class="term"><code class="literal">CONNECT</code><br /></span><span class="term"><code class="literal">TEMPORARY</code><br /></span><span class="term"><code class="literal">EXECUTE</code><br /></span><span class="term"><code class="literal">USAGE</code><br /></span><span class="term"><code class="literal">SET</code><br /></span><span class="term"><code class="literal">ALTER SYSTEM</code></span></dt><dd><p>
+ Specific types of privileges, as defined in <a class="xref" href="ddl-priv.html" title="5.7. Privileges">Section 5.7</a>.
+ </p></dd><dt><span class="term"><code class="literal">TEMP</code></span></dt><dd><p>
+ Alternative spelling for <code class="literal">TEMPORARY</code>.
+ </p></dd><dt><span class="term"><code class="literal">ALL PRIVILEGES</code></span></dt><dd><p>
+ Grant all of the privileges available for the object's type.
+ The <code class="literal">PRIVILEGES</code> key word is optional in
+ <span class="productname">PostgreSQL</span>, though it is required by
+ strict SQL.
+ </p></dd></dl></div><p>
+ </p><p>
+ The <code class="literal">FUNCTION</code> syntax works for plain functions,
+ aggregate functions, and window functions, but not for procedures;
+ use <code class="literal">PROCEDURE</code> for those.
+ Alternatively, use <code class="literal">ROUTINE</code> to refer to a function,
+ aggregate function, window function, or procedure regardless of its
+ precise type.
+ </p><p>
+ There is also an option to grant privileges on all objects of the same
+ type within one or more schemas. This functionality is currently supported
+ only for tables, sequences, functions, and procedures. <code class="literal">ALL
+ TABLES</code> also affects views and foreign tables, just like the
+ specific-object <code class="command">GRANT</code> command. <code class="literal">ALL
+ FUNCTIONS</code> also affects aggregate and window functions, but not
+ procedures, again just like the specific-object <code class="command">GRANT</code>
+ command. Use <code class="literal">ALL ROUTINES</code> to include procedures.
+ </p></div><div class="refsect2" id="SQL-GRANT-DESCRIPTION-ROLES"><h3>GRANT on Roles</h3><p>
+ This variant of the <code class="command">GRANT</code> command grants membership
+ in a role to one or more other roles. Membership in a role is significant
+ because it conveys the privileges granted to a role to each of its
+ members.
+ </p><p>
+ If <code class="literal">WITH ADMIN OPTION</code> is specified, the member can
+ in turn grant membership in the role to others, and revoke membership
+ in the role as well. Without the admin option, ordinary users cannot
+ do that. A role is not considered to hold <code class="literal">WITH ADMIN
+ OPTION</code> on itself. Database superusers can grant or revoke
+ membership in any role to anyone. Roles having
+ <code class="literal">CREATEROLE</code> privilege can grant or revoke membership
+ in any role that is not a superuser.
+ </p><p>
+ If <code class="literal">GRANTED BY</code> is specified, the grant is recorded as
+ having been done by the specified role. Only database superusers may
+ use this option, except when it names the same role executing the command.
+ </p><p>
+ Unlike the case with privileges, membership in a role cannot be granted
+ to <code class="literal">PUBLIC</code>. Note also that this form of the command
+ does not allow the noise word <code class="literal">GROUP</code>
+ in <em class="replaceable"><code>role_specification</code></em>.
+ </p></div></div><div class="refsect1" id="SQL-GRANT-NOTES"><h2>Notes</h2><p>
+ The <a class="link" href="sql-revoke.html" title="REVOKE"><code class="command">REVOKE</code></a> command is used
+ to revoke access privileges.
+ </p><p>
+ Since <span class="productname">PostgreSQL</span> 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 <code class="literal">GROUP</code>
+ to identify whether a grantee is a user or a group. <code class="literal">GROUP</code>
+ is still allowed in the command, but it is a noise word.
+ </p><p>
+ A user may perform <code class="command">SELECT</code>, <code class="command">INSERT</code>, etc. on a
+ column if they hold that privilege for either the specific column or
+ its whole table. Granting the privilege at the table level and then
+ revoking it for one column will not do what one might wish: the
+ table-level grant is unaffected by a column-level operation.
+ </p><p>
+ When a non-owner of an object attempts to <code class="command">GRANT</code> privileges
+ on the object, the command will fail outright if the user has no
+ privileges whatsoever on the object. As long as some privilege is
+ available, the command will proceed, but it will grant only those
+ privileges for which the user has grant options. The <code class="command">GRANT ALL
+ PRIVILEGES</code> forms will issue a warning message if no grant options are
+ held, while the other forms will issue a warning if grant options for
+ any of the privileges specifically named in the command are not held.
+ (In principle these statements apply to the object owner as well, but
+ since the owner is always treated as holding all grant options, the
+ cases can never occur.)
+ </p><p>
+ It should be noted that database superusers can access
+ all objects regardless of object privilege settings. This
+ is comparable to the rights of <code class="literal">root</code> in a Unix system.
+ As with <code class="literal">root</code>, it's unwise to operate as a superuser
+ except when absolutely necessary.
+ </p><p>
+ If a superuser chooses to issue a <code class="command">GRANT</code> or <code class="command">REVOKE</code>
+ command, the command is performed as though it were issued by the
+ owner of the affected object. In particular, privileges granted via
+ such a command will appear to have been granted by the object owner.
+ (For role membership, the membership appears to have been granted
+ by the containing role itself.)
+ </p><p>
+ <code class="command">GRANT</code> and <code class="command">REVOKE</code> can also be done by a role
+ that is not the owner of the affected object, but is a member of the role
+ that owns the object, or is a member of a role that holds privileges
+ <code class="literal">WITH GRANT OPTION</code> on the object. In this case the
+ privileges will be recorded as having been granted by the role that
+ actually owns the object or holds the privileges
+ <code class="literal">WITH GRANT OPTION</code>. For example, if table
+ <code class="literal">t1</code> is owned by role <code class="literal">g1</code>, of which role
+ <code class="literal">u1</code> is a member, then <code class="literal">u1</code> can grant privileges
+ on <code class="literal">t1</code> to <code class="literal">u2</code>, but those privileges will appear
+ to have been granted directly by <code class="literal">g1</code>. Any other member
+ of role <code class="literal">g1</code> could revoke them later.
+ </p><p>
+ If the role executing <code class="command">GRANT</code> holds the required privileges
+ indirectly via more than one role membership path, it is unspecified
+ which containing role will be recorded as having done the grant. In such
+ cases it is best practice to use <code class="command">SET ROLE</code> to become the
+ specific role you want to do the <code class="command">GRANT</code> as.
+ </p><p>
+ Granting permission on a table does not automatically extend
+ permissions to any sequences used by the table, including
+ sequences tied to <code class="type">SERIAL</code> columns. Permissions on
+ sequences must be set separately.
+ </p><p>
+ See <a class="xref" href="ddl-priv.html" title="5.7. Privileges">Section 5.7</a> for more information about specific
+ privilege types, as well as how to inspect objects' privileges.
+ </p></div><div class="refsect1" id="SQL-GRANT-EXAMPLES"><h2>Examples</h2><p>
+ Grant insert privilege to all users on table <code class="literal">films</code>:
+
+</p><pre class="programlisting">
+GRANT INSERT ON films TO PUBLIC;
+</pre><p>
+ </p><p>
+ Grant all available privileges to user <code class="literal">manuel</code> on view
+ <code class="literal">kinds</code>:
+
+</p><pre class="programlisting">
+GRANT ALL PRIVILEGES ON kinds TO manuel;
+</pre><p>
+
+ Note that while the above will indeed grant all privileges if executed by a
+ superuser or the owner of <code class="literal">kinds</code>, when executed by someone
+ else it will only grant those permissions for which the someone else has
+ grant options.
+ </p><p>
+ Grant membership in role <code class="literal">admins</code> to user <code class="literal">joe</code>:
+
+</p><pre class="programlisting">
+GRANT admins TO joe;
+</pre></div><div class="refsect1" id="SQL-GRANT-COMPATIBILITY"><h2>Compatibility</h2><p>
+ According to the SQL standard, the <code class="literal">PRIVILEGES</code>
+ key word in <code class="literal">ALL PRIVILEGES</code> is required. The
+ SQL standard does not support setting the privileges on more than
+ one object per command.
+ </p><p>
+ <span class="productname">PostgreSQL</span> allows an object owner to revoke their
+ own ordinary privileges: for example, a table owner can make the table
+ read-only to themselves by revoking their own <code class="literal">INSERT</code>,
+ <code class="literal">UPDATE</code>, <code class="literal">DELETE</code>, and <code class="literal">TRUNCATE</code>
+ privileges. This is not possible according to the SQL standard. The
+ reason is that <span class="productname">PostgreSQL</span> treats the owner's
+ privileges as having been granted by the owner to themselves; therefore they
+ can revoke them too. In the SQL standard, the owner's privileges are
+ granted by an assumed entity <span class="quote">“<span class="quote">_SYSTEM</span>”</span>. Not being
+ <span class="quote">“<span class="quote">_SYSTEM</span>”</span>, the owner cannot revoke these rights.
+ </p><p>
+ According to the SQL standard, grant options can be granted to
+ <code class="literal">PUBLIC</code>; PostgreSQL only supports granting grant options
+ to roles.
+ </p><p>
+ The SQL standard allows the <code class="literal">GRANTED BY</code> option to
+ specify only <code class="literal">CURRENT_USER</code> or
+ <code class="literal">CURRENT_ROLE</code>. The other variants are PostgreSQL
+ extensions.
+ </p><p>
+ The SQL standard provides for a <code class="literal">USAGE</code> privilege
+ on other kinds of objects: character sets, collations,
+ translations.
+ </p><p>
+ In the SQL standard, sequences only have a <code class="literal">USAGE</code>
+ privilege, which controls the use of the <code class="literal">NEXT VALUE FOR</code>
+ expression, which is equivalent to the
+ function <code class="function">nextval</code> in PostgreSQL. The sequence
+ privileges <code class="literal">SELECT</code> and <code class="literal">UPDATE</code> are
+ PostgreSQL extensions. The application of the
+ sequence <code class="literal">USAGE</code> privilege to
+ the <code class="literal">currval</code> function is also a PostgreSQL extension (as
+ is the function itself).
+ </p><p>
+ Privileges on databases, tablespaces, schemas, languages, and
+ configuration parameters are
+ <span class="productname">PostgreSQL</span> extensions.
+ </p></div><div class="refsect1" id="id-1.9.3.150.9"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-revoke.html" title="REVOKE"><span class="refentrytitle">REVOKE</span></a>, <a class="xref" href="sql-alterdefaultprivileges.html" title="ALTER DEFAULT PRIVILEGES"><span class="refentrytitle">ALTER DEFAULT PRIVILEGES</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-fetch.html" title="FETCH">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA">Next</a></td></tr><tr><td width="40%" align="left" valign="top">FETCH </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> IMPORT FOREIGN SCHEMA</td></tr></table></div></body></html> \ No newline at end of file