1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
|
<?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 16.3 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 <a href="#DDL-PRIV" class="id_link">#</a></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 inherit the privileges of the
owning role) and able to <code class="literal">SET ROLE</code> to 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 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>
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 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>
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 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>
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 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>
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 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>
Allows <code class="command">TRUNCATE</code> on a table.
</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>
Allows creation of a foreign key constraint referencing a
table, or specific column(s) of a table.
</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>
Allows creation of a trigger on a table, view, etc.
</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>
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 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>
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 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>
Allows temporary tables to be created while using the database.
</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>
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 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>
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 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>
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 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>
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, each having the
format:
</p><pre class="synopsis">
<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>
</pre><p>
Each <code class="type">aclitem</code> lists all the permissions of one grantee that
have been granted by a particular grantor. Specific privileges are
represented by one-letter abbreviations from
<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>
appended if the privilege was granted with grant option. 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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 5.8. Row Security Policies</td></tr></table></div></body></html>
|
