1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!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.8. 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.7. Modifying Tables" /><link rel="next" href="ddl-rowsecurity.html" title="5.9. 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.8. Privileges</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ddl-alter.html" title="5.7. 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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ddl-rowsecurity.html" title="5.9. 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.8. Privileges <a href="#DDL-PRIV" class="id_link">#</a></h2></div></div></div><a id="id-1.5.4.10.2" class="indexterm"></a><a id="id-1.5.4.10.3" class="indexterm"></a><a id="id-1.5.4.10.4" class="indexterm"></a><a id="id-1.5.4.10.5" class="indexterm"></a><a id="id-1.5.4.10.6" class="indexterm"></a><a id="id-1.5.4.10.7" class="indexterm"></a><a id="id-1.5.4.10.8" class="indexterm"></a><p>
3 When an object is created, it is assigned an owner. The
4 owner is normally the role that executed the creation statement.
5 For most kinds of objects, the initial state is that only the owner
6 (or a superuser) can do anything with the object. To allow
7 other roles to use it, <em class="firstterm">privileges</em> must be
10 There are different kinds of privileges: <code class="literal">SELECT</code>,
11 <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, <code class="literal">DELETE</code>,
12 <code class="literal">TRUNCATE</code>, <code class="literal">REFERENCES</code>, <code class="literal">TRIGGER</code>,
13 <code class="literal">CREATE</code>, <code class="literal">CONNECT</code>, <code class="literal">TEMPORARY</code>,
14 <code class="literal">EXECUTE</code>, <code class="literal">USAGE</code>, <code class="literal">SET</code>,
15 <code class="literal">ALTER SYSTEM</code>, and <code class="literal">MAINTAIN</code>.
16 The privileges applicable to a particular
17 object vary depending on the object's type (table, function, etc.).
18 More detail about the meanings of these privileges appears below.
19 The following sections and chapters will also show you how
20 these privileges are used.
22 The right to modify or destroy an object is inherent in being the
23 object's owner, and cannot be granted or revoked in itself.
24 (However, like all privileges, that right can be inherited by
25 members of the owning role; see <a class="xref" href="role-membership.html" title="21.3. Role Membership">Section 21.3</a>.)
27 An object can be assigned to a new owner with an <code class="command">ALTER</code>
28 command of the appropriate kind for the object, for example
29 </p><pre class="programlisting">
30 ALTER TABLE <em class="replaceable"><code>table_name</code></em> OWNER TO <em class="replaceable"><code>new_owner</code></em>;
32 Superusers can always do this; ordinary roles can only do it if they are
33 both the current owner of the object (or inherit the privileges of the
34 owning role) and able to <code class="literal">SET ROLE</code> to the new owning role.
36 To assign privileges, the <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a> command is
37 used. For example, if <code class="literal">joe</code> is an existing role, and
38 <code class="literal">accounts</code> is an existing table, the privilege to
39 update the table can be granted with:
40 </p><pre class="programlisting">
41 GRANT UPDATE ON accounts TO joe;
43 Writing <code class="literal">ALL</code> in place of a specific privilege grants all
44 privileges that are relevant for the object type.
46 The special <span class="quote">“<span class="quote">role</span>”</span> name <code class="literal">PUBLIC</code> can
47 be used to grant a privilege to every role on the system. Also,
48 <span class="quote">“<span class="quote">group</span>”</span> roles can be set up to help manage privileges when
49 there are many users of a database — for details see
50 <a class="xref" href="user-manag.html" title="Chapter 21. Database Roles">Chapter 21</a>.
52 To revoke a previously-granted privilege, use the fittingly named
53 <a class="xref" href="sql-revoke.html" title="REVOKE"><span class="refentrytitle">REVOKE</span></a> command:
54 </p><pre class="programlisting">
55 REVOKE ALL ON accounts FROM PUBLIC;
58 Ordinarily, only the object's owner (or a superuser) can grant or
59 revoke privileges on an object. However, it is possible to grant a
60 privilege <span class="quote">“<span class="quote">with grant option</span>”</span>, which gives the recipient
61 the right to grant it in turn to others. If the grant option is
62 subsequently revoked then all who received the privilege from that
63 recipient (directly or through a chain of grants) will lose the
64 privilege. For details see the <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a> and
65 <a class="xref" href="sql-revoke.html" title="REVOKE"><span class="refentrytitle">REVOKE</span></a> reference pages.
67 An object's owner can choose to revoke their own ordinary privileges,
68 for example to make a table read-only for themselves as well as others.
69 But owners are always treated as holding all grant options, so they
70 can always re-grant their own privileges.
72 The available privileges are:
74 </p><div class="variablelist"><dl class="variablelist"><dt id="DDL-PRIV-SELECT"><span class="term"><code class="literal">SELECT</code></span> <a href="#DDL-PRIV-SELECT" class="id_link">#</a></dt><dd><p>
75 Allows <code class="command">SELECT</code> from
76 any column, or specific column(s), of a table, view, materialized
77 view, or other table-like object.
78 Also allows use of <code class="command">COPY TO</code>.
79 This privilege is also needed to reference existing column values in
80 <code class="command">UPDATE</code>, <code class="command">DELETE</code>,
81 or <code class="command">MERGE</code>.
82 For sequences, this privilege also allows use of the
83 <code class="function">currval</code> function.
84 For large objects, this privilege allows the object to be read.
85 </p></dd><dt id="DDL-PRIV-INSERT"><span class="term"><code class="literal">INSERT</code></span> <a href="#DDL-PRIV-INSERT" class="id_link">#</a></dt><dd><p>
86 Allows <code class="command">INSERT</code> of a new row into a table, view,
87 etc. Can be granted on specific column(s), in which case
88 only those columns may be assigned to in the <code class="command">INSERT</code>
89 command (other columns will therefore receive default values).
90 Also allows use of <code class="command">COPY FROM</code>.
91 </p></dd><dt id="DDL-PRIV-UPDATE"><span class="term"><code class="literal">UPDATE</code></span> <a href="#DDL-PRIV-UPDATE" class="id_link">#</a></dt><dd><p>
92 Allows <code class="command">UPDATE</code> of any
93 column, or specific column(s), of a table, view, etc.
94 (In practice, any nontrivial <code class="command">UPDATE</code> command will
95 require <code class="literal">SELECT</code> privilege as well, since it must
96 reference table columns to determine which rows to update, and/or to
97 compute new values for columns.)
98 <code class="literal">SELECT ... FOR UPDATE</code>
99 and <code class="literal">SELECT ... FOR SHARE</code>
100 also require this privilege on at least one column, in addition to the
101 <code class="literal">SELECT</code> privilege. For sequences, this
102 privilege allows use of the <code class="function">nextval</code> and
103 <code class="function">setval</code> functions.
104 For large objects, this privilege allows writing or truncating the
106 </p></dd><dt id="DDL-PRIV-DELETE"><span class="term"><code class="literal">DELETE</code></span> <a href="#DDL-PRIV-DELETE" class="id_link">#</a></dt><dd><p>
107 Allows <code class="command">DELETE</code> of a row from a table, view, etc.
108 (In practice, any nontrivial <code class="command">DELETE</code> command will
109 require <code class="literal">SELECT</code> privilege as well, since it must
110 reference table columns to determine which rows to delete.)
111 </p></dd><dt id="DDL-PRIV-TRUNCATE"><span class="term"><code class="literal">TRUNCATE</code></span> <a href="#DDL-PRIV-TRUNCATE" class="id_link">#</a></dt><dd><p>
112 Allows <code class="command">TRUNCATE</code> on a table.
113 </p></dd><dt id="DDL-PRIV-REFERENCES"><span class="term"><code class="literal">REFERENCES</code></span> <a href="#DDL-PRIV-REFERENCES" class="id_link">#</a></dt><dd><p>
114 Allows creation of a foreign key constraint referencing a
115 table, or specific column(s) of a table.
116 </p></dd><dt id="DDL-PRIV-TRIGGER"><span class="term"><code class="literal">TRIGGER</code></span> <a href="#DDL-PRIV-TRIGGER" class="id_link">#</a></dt><dd><p>
117 Allows creation of a trigger on a table, view, etc.
118 </p></dd><dt id="DDL-PRIV-CREATE"><span class="term"><code class="literal">CREATE</code></span> <a href="#DDL-PRIV-CREATE" class="id_link">#</a></dt><dd><p>
119 For databases, allows new schemas and publications to be created within
120 the database, and allows trusted extensions to be installed within
123 For schemas, allows new objects to be created within the schema.
124 To rename an existing object, you must own the
125 object <span class="emphasis"><em>and</em></span> have this privilege for the containing
128 For tablespaces, allows tables, indexes, and temporary files to be
129 created within the tablespace, and allows databases to be created that
130 have the tablespace as their default tablespace.
132 Note that revoking this privilege will not alter the existence or
133 location of existing objects.
134 </p></dd><dt id="DDL-PRIV-CONNECT"><span class="term"><code class="literal">CONNECT</code></span> <a href="#DDL-PRIV-CONNECT" class="id_link">#</a></dt><dd><p>
135 Allows the grantee to connect to the database. This
136 privilege is checked at connection startup (in addition to checking
137 any restrictions imposed by <code class="filename">pg_hba.conf</code>).
138 </p></dd><dt id="DDL-PRIV-TEMPORARY"><span class="term"><code class="literal">TEMPORARY</code></span> <a href="#DDL-PRIV-TEMPORARY" class="id_link">#</a></dt><dd><p>
139 Allows temporary tables to be created while using the database.
140 </p></dd><dt id="DDL-PRIV-EXECUTE"><span class="term"><code class="literal">EXECUTE</code></span> <a href="#DDL-PRIV-EXECUTE" class="id_link">#</a></dt><dd><p>
141 Allows calling a function or procedure, including use of
142 any operators that are implemented on top of the function. This is the
143 only type of privilege that is applicable to functions and procedures.
144 </p></dd><dt id="DDL-PRIV-USAGE"><span class="term"><code class="literal">USAGE</code></span> <a href="#DDL-PRIV-USAGE" class="id_link">#</a></dt><dd><p>
145 For procedural languages, allows use of the language for
146 the creation of functions in that language. This is the only type
147 of privilege that is applicable to procedural languages.
149 For schemas, allows access to objects contained in the
150 schema (assuming that the objects' own privilege requirements are
151 also met). Essentially this allows the grantee to <span class="quote">“<span class="quote">look up</span>”</span>
152 objects within the schema. Without this permission, it is still
153 possible to see the object names, e.g., by querying system catalogs.
154 Also, after revoking this permission, existing sessions might have
155 statements that have previously performed this lookup, so this is not
156 a completely secure way to prevent object access.
158 For sequences, allows use of the
159 <code class="function">currval</code> and <code class="function">nextval</code> functions.
161 For types and domains, allows use of the type or domain in the
162 creation of tables, functions, and other schema objects. (Note that
163 this privilege does not control all <span class="quote">“<span class="quote">usage</span>”</span> of the
164 type, such as values of the type appearing in queries. It only
165 prevents objects from being created that depend on the type. The
166 main purpose of this privilege is controlling which users can create
167 dependencies on a type, which could prevent the owner from changing
170 For foreign-data wrappers, allows creation of new servers using the
171 foreign-data wrapper.
173 For foreign servers, allows creation of foreign tables using the
174 server. Grantees may also create, alter, or drop their own user
175 mappings associated with that server.
176 </p></dd><dt id="DDL-PRIV-SET"><span class="term"><code class="literal">SET</code></span> <a href="#DDL-PRIV-SET" class="id_link">#</a></dt><dd><p>
177 Allows a server configuration parameter to be set to a new value
178 within the current session. (While this privilege can be granted
179 on any parameter, it is meaningless except for parameters that would
180 normally require superuser privilege to set.)
181 </p></dd><dt id="DDL-PRIV-ALTER-SYSTEM"><span class="term"><code class="literal">ALTER SYSTEM</code></span> <a href="#DDL-PRIV-ALTER-SYSTEM" class="id_link">#</a></dt><dd><p>
182 Allows a server configuration parameter to be configured to a new
183 value using the <a class="xref" href="sql-altersystem.html" title="ALTER SYSTEM"><span class="refentrytitle">ALTER SYSTEM</span></a> command.
184 </p></dd><dt id="DDL-PRIV-MAINTAIN"><span class="term"><code class="literal">MAINTAIN</code></span> <a href="#DDL-PRIV-MAINTAIN" class="id_link">#</a></dt><dd><p>
185 Allows <code class="command">VACUUM</code>, <code class="command">ANALYZE</code>,
186 <code class="command">CLUSTER</code>, <code class="command">REFRESH MATERIALIZED VIEW</code>,
187 <code class="command">REINDEX</code>, <code class="command">LOCK TABLE</code>,
188 and database object statistics manipulation functions
189 (see <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-STATSMOD" title="Table 9.105. Database Object Statistics Manipulation Functions">Table 9.105</a>) on a relation.
190 </p></dd></dl></div><p>
192 The privileges required by other commands are listed on the
193 reference page of the respective command.
194 </p><p id="DDL-PRIV-DEFAULT">
195 PostgreSQL grants privileges on some types of objects to
196 <code class="literal">PUBLIC</code> by default when the objects are created.
197 No privileges are granted to <code class="literal">PUBLIC</code> by default on
201 foreign data wrappers,
206 or configuration parameters.
207 For other types of objects, the default privileges
208 granted to <code class="literal">PUBLIC</code> are as follows:
209 <code class="literal">CONNECT</code> and <code class="literal">TEMPORARY</code> (create
210 temporary tables) privileges for databases;
211 <code class="literal">EXECUTE</code> privilege for functions and procedures; and
212 <code class="literal">USAGE</code> privilege for languages and data types
214 The object owner can, of course, <code class="command">REVOKE</code>
215 both default and expressly granted privileges. (For maximum
216 security, issue the <code class="command">REVOKE</code> in the same transaction that
217 creates the object; then there is no window in which another user
219 Also, these default privilege settings can be overridden using the
220 <a class="xref" href="sql-alterdefaultprivileges.html" title="ALTER DEFAULT PRIVILEGES"><span class="refentrytitle">ALTER DEFAULT PRIVILEGES</span></a> command.
222 <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
223 abbreviations that are used for these privilege types in
224 <em class="firstterm"><acronym class="acronym">ACL</acronym></em> values.
225 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>
226 commands listed below, or when looking at <acronym class="acronym">ACL</acronym> columns
228 </p><div class="table" id="PRIVILEGE-ABBREVS-TABLE"><p class="title"><strong>Table 5.1. <acronym class="acronym">ACL</acronym> 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>
229 <code class="literal">LARGE OBJECT</code>,
230 <code class="literal">SEQUENCE</code>,
231 <code class="literal">TABLE</code> (and table-like objects),
233 </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>
234 <code class="literal">LARGE OBJECT</code>,
235 <code class="literal">SEQUENCE</code>,
236 <code class="literal">TABLE</code>,
238 </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>
239 <code class="literal">DATABASE</code>,
240 <code class="literal">SCHEMA</code>,
241 <code class="literal">TABLESPACE</code>
242 </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>
243 <code class="literal">DOMAIN</code>,
244 <code class="literal">FOREIGN DATA WRAPPER</code>,
245 <code class="literal">FOREIGN SERVER</code>,
246 <code class="literal">LANGUAGE</code>,
247 <code class="literal">SCHEMA</code>,
248 <code class="literal">SEQUENCE</code>,
249 <code class="literal">TYPE</code>
250 </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><tr><td><code class="literal">MAINTAIN</code></td><td><code class="literal">m</code></td><td><code class="literal">TABLE</code></td></tr></tbody></table></div></div><br class="table-break" /><p>
251 <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
252 available for each type of SQL object, using the abbreviations shown
254 It also shows the <span class="application">psql</span> command
255 that can be used to examine privilege settings for each object type.
256 </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">arwdDxtm</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>
257 <a id="id-1.5.4.10.24.1" class="indexterm"></a>
258 The privileges that have been granted for a particular object are
259 displayed as a list of <code class="type">aclitem</code> entries, each having the
261 </p><pre class="synopsis">
262 <em class="replaceable"><code>grantee</code></em><code class="literal">=</code><em class="replaceable"><code>privilege-abbreviation</code></em>[<span class="optional"><code class="literal">*</code></span>]...<code class="literal">/</code><em class="replaceable"><code>grantor</code></em>
264 Each <code class="type">aclitem</code> lists all the permissions of one grantee that
265 have been granted by a particular grantor. Specific privileges are
266 represented by one-letter abbreviations from
267 <a class="xref" href="ddl-priv.html#PRIVILEGE-ABBREVS-TABLE" title="Table 5.1. ACL Privilege Abbreviations">Table 5.1</a>, with <code class="literal">*</code>
268 appended if the privilege was granted with grant option. For example,
269 <code class="literal">calvin=r*w/hobbes</code> specifies that the role
270 <code class="literal">calvin</code> has the privilege
271 <code class="literal">SELECT</code> (<code class="literal">r</code>) with grant option
272 (<code class="literal">*</code>) as well as the non-grantable
273 privilege <code class="literal">UPDATE</code> (<code class="literal">w</code>), both granted
274 by the role <code class="literal">hobbes</code>. If <code class="literal">calvin</code>
275 also has some privileges on the same object granted by a different
276 grantor, those would appear as a separate <code class="type">aclitem</code> entry.
277 An empty grantee field in an <code class="type">aclitem</code> stands
278 for <code class="literal">PUBLIC</code>.
280 As an example, suppose that user <code class="literal">miriam</code> creates
281 table <code class="literal">mytable</code> and does:
282 </p><pre class="programlisting">
283 GRANT SELECT ON mytable TO PUBLIC;
284 GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
285 GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
287 Then <span class="application">psql</span>'s <code class="literal">\dp</code> command
289 </p><pre class="programlisting">
292 Schema | Name | Type | Access privileges | Column privileges | Policies
293 --------+---------+-------+------------------------+-----------------------+----------
294 public | mytable | table | miriam=arwdDxtm/miriam+| col1: +|
295 | | | =r/miriam +| miriam_rw=rw/miriam |
296 | | | admin=arw/miriam | |
300 If the <span class="quote">“<span class="quote">Access privileges</span>”</span> column is empty for a given
301 object, it means the object has default privileges (that is, its
302 privileges entry in the relevant system catalog is null). Default
303 privileges always include all privileges for the owner, and can include
304 some privileges for <code class="literal">PUBLIC</code> depending on the object
305 type, as explained above. The first <code class="command">GRANT</code>
306 or <code class="command">REVOKE</code> on an object will instantiate the default
307 privileges (producing, for
308 example, <code class="literal">miriam=arwdDxt/miriam</code>) and then modify them
309 per the specified request. Similarly, entries are shown in <span class="quote">“<span class="quote">Column
310 privileges</span>”</span> only for columns with nondefault privileges.
311 (Note: for this purpose, <span class="quote">“<span class="quote">default privileges</span>”</span> always means
312 the built-in default privileges for the object's type. An object whose
313 privileges have been affected by an <code class="command">ALTER DEFAULT
314 PRIVILEGES</code> command will always be shown with an explicit
315 privilege entry that includes the effects of
316 the <code class="command">ALTER</code>.)
318 Notice that the owner's implicit grant options are not marked in the
319 access privileges display. A <code class="literal">*</code> will appear only when
320 grant options have been explicitly granted to someone.
322 The <span class="quote">“<span class="quote">Access privileges</span>”</span> column
323 shows <code class="literal">(none)</code> when the object's privileges entry is
324 non-null but empty. This means that no privileges are granted at all,
325 even to the object's owner — a rare situation. (The owner still
326 has implicit grant options in this case, and so could re-grant her own
327 privileges; but she has none at the moment.)
328 </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.7. 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.9. Row Security Policies">Next</a></td></tr><tr><td width="40%" align="left" valign="top">5.7. Modifying Tables </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.9. Row Security Policies</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob