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
|
<?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>22.2. Role Attributes</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="database-roles.html" title="22.1. Database Roles" /><link rel="next" href="role-membership.html" title="22.3. Role Membership" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">22.2. Role Attributes</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="database-roles.html" title="22.1. Database Roles">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="user-manag.html" title="Chapter 22. Database Roles">Up</a></td><th width="60%" align="center">Chapter 22. Database Roles</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="role-membership.html" title="22.3. Role Membership">Next</a></td></tr></table><hr /></div><div class="sect1" id="ROLE-ATTRIBUTES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">22.2. Role Attributes <a href="#ROLE-ATTRIBUTES" class="id_link">#</a></h2></div></div></div><p>
A database role can have a number of attributes that define its
privileges and interact with the client authentication system.
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term">login privilege<a id="id-1.6.9.6.2.1.1.1.1" class="indexterm"></a></span></dt><dd><p>
Only roles that have the <code class="literal">LOGIN</code> attribute can be used
as the initial role name for a database connection. A role with
the <code class="literal">LOGIN</code> attribute can be considered the same
as a <span class="quote">“<span class="quote">database user</span>”</span>. To create a role with login privilege,
use either:
</p><pre class="programlisting">
CREATE ROLE <em class="replaceable"><code>name</code></em> LOGIN;
CREATE USER <em class="replaceable"><code>name</code></em>;
</pre><p>
(<code class="command">CREATE USER</code> is equivalent to <code class="command">CREATE ROLE</code>
except that <code class="command">CREATE USER</code> includes <code class="literal">LOGIN</code> by
default, while <code class="command">CREATE ROLE</code> does not.)
</p></dd><dt><span class="term">superuser status<a id="id-1.6.9.6.2.1.2.1.1" class="indexterm"></a></span></dt><dd><p>
A database superuser bypasses all permission checks, except the right
to log in. This is a dangerous privilege and should not be used
carelessly; it is best to do most of your work as a role that is not a
superuser. To create a new database superuser, use <code class="literal">CREATE
ROLE <em class="replaceable"><code>name</code></em> SUPERUSER</code>. You must do
this as a role that is already a superuser.
</p></dd><dt><span class="term">database creation<a id="id-1.6.9.6.2.1.3.1.1" class="indexterm"></a></span></dt><dd><p>
A role must be explicitly given permission to create databases
(except for superusers, since those bypass all permission
checks). To create such a role, use <code class="literal">CREATE ROLE
<em class="replaceable"><code>name</code></em> CREATEDB</code>.
</p></dd><dt><span class="term" id="ROLE-CREATION">role creation<a id="id-1.6.9.6.2.1.4.1.1" class="indexterm"></a></span></dt><dd><p>
A role must be explicitly given permission to create more roles
(except for superusers, since those bypass all permission
checks). To create such a role, use <code class="literal">CREATE ROLE
<em class="replaceable"><code>name</code></em> CREATEROLE</code>.
A role with <code class="literal">CREATEROLE</code> privilege can alter and drop
roles which have been granted to the <code class="literal">CREATEROLE</code>
user with the <code class="literal">ADMIN</code> option. Such a grant occurs
automatically when a <code class="literal">CREATEROLE</code> user that is not
a superuser creates a new role, so that by default, a
<code class="literal">CREATEROLE</code> user can alter and drop the roles
which they have created.
Altering a role includes most changes that can be made using
<code class="literal">ALTER ROLE</code>, including, for example, changing
passwords. It also includes modifications to a role that can
be made using the <code class="literal">COMMENT</code> and
<code class="literal">SECURITY LABEL</code> commands.
</p><p>
However, <code class="literal">CREATEROLE</code> does not convey the ability to
create <code class="literal">SUPERUSER</code> roles, nor does it convey any
power over <code class="literal">SUPERUSER</code> roles that already exist.
Furthermore, <code class="literal">CREATEROLE</code> does not convey the power
to create <code class="literal">REPLICATION</code> users, nor the ability to
grant or revoke the <code class="literal">REPLICATION</code> privilege, nor the
ability to modify the role properties of such users. However, it does
allow <code class="literal">ALTER ROLE ... SET</code> and
<code class="literal">ALTER ROLE ... RENAME</code> to be used on
<code class="literal">REPLICATION</code> roles, as well as the use of
<code class="literal">COMMENT ON ROLE</code>,
<code class="literal">SECURITY LABEL ON ROLE</code>,
and <code class="literal">DROP ROLE</code>.
Finally, <code class="literal">CREATEROLE</code> does not
confer the ability to grant or revoke the <code class="literal">BYPASSRLS</code>
privilege.
</p></dd><dt><span class="term">initiating replication<a id="id-1.6.9.6.2.1.5.1.1" class="indexterm"></a></span></dt><dd><p>
A role must explicitly be given permission to initiate streaming
replication (except for superusers, since those bypass all permission
checks). A role used for streaming replication must
have <code class="literal">LOGIN</code> permission as well. To create such a role, use
<code class="literal">CREATE ROLE <em class="replaceable"><code>name</code></em> REPLICATION
LOGIN</code>.
</p></dd><dt><span class="term">password<a id="id-1.6.9.6.2.1.6.1.1" class="indexterm"></a></span></dt><dd><p>
A password is only significant if the client authentication
method requires the user to supply a password when connecting
to the database. The <code class="option">password</code> and
<code class="option">md5</code> authentication methods
make use of passwords. Database passwords are separate from
operating system passwords. Specify a password upon role
creation with <code class="literal">CREATE ROLE
<em class="replaceable"><code>name</code></em> PASSWORD '<em class="replaceable"><code>string</code></em>'</code>.
</p></dd><dt><span class="term">inheritance of privileges<a id="id-1.6.9.6.2.1.7.1.1" class="indexterm"></a></span></dt><dd><p>
A role inherits the privileges of roles it is a member of, by default.
However, to create a role which does not inherit privileges by
default, use <code class="literal">CREATE ROLE <em class="replaceable"><code>name</code></em>
NOINHERIT</code>. Alternatively, inheritance can be overridden
for individual grants by using <code class="literal">WITH INHERIT TRUE</code>
or <code class="literal">WITH INHERIT FALSE</code>.
</p></dd><dt><span class="term">bypassing row-level security<a id="id-1.6.9.6.2.1.8.1.1" class="indexterm"></a></span></dt><dd><p>
A role must be explicitly given permission to bypass every row-level security (RLS) policy
(except for superusers, since those bypass all permission checks).
To create such a role, use <code class="literal">CREATE ROLE <em class="replaceable"><code>name</code></em> BYPASSRLS</code> as a superuser.
</p></dd><dt><span class="term">connection limit<a id="id-1.6.9.6.2.1.9.1.1" class="indexterm"></a></span></dt><dd><p>
Connection limit can specify how many concurrent connections a role can make.
-1 (the default) means no limit. Specify connection limit upon role creation with
<code class="literal">CREATE ROLE <em class="replaceable"><code>name</code></em> CONNECTION LIMIT '<em class="replaceable"><code>integer</code></em>'</code>.
</p></dd></dl></div><p>
A role's attributes can be modified after creation with
<code class="command">ALTER ROLE</code>.<a id="id-1.6.9.6.2.3" class="indexterm"></a>
See the reference pages for the <a class="xref" href="sql-createrole.html" title="CREATE ROLE"><span class="refentrytitle">CREATE ROLE</span></a>
and <a class="xref" href="sql-alterrole.html" title="ALTER ROLE"><span class="refentrytitle">ALTER ROLE</span></a> commands for details.
</p><p>
A role can also have role-specific defaults for many of the run-time
configuration settings described in <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a>. For example, if for some reason you
want to disable index scans (hint: not a good idea) anytime you
connect, you can use:
</p><pre class="programlisting">
ALTER ROLE myname SET enable_indexscan TO off;
</pre><p>
This will save the setting (but not set it immediately). In
subsequent connections by this role it will appear as though
<code class="literal">SET enable_indexscan TO off</code> had been executed
just before the session started.
You can still alter this setting during the session; it will only
be the default. To remove a role-specific default setting, use
<code class="literal">ALTER ROLE <em class="replaceable"><code>rolename</code></em> RESET <em class="replaceable"><code>varname</code></em></code>.
Note that role-specific defaults attached to roles without
<code class="literal">LOGIN</code> privilege are fairly useless, since they will never
be invoked.
</p><p>
When a non-superuser creates a role using the <code class="literal">CREATEROLE</code>
privilege, the created role is automatically granted back to the creating
user, just as if the bootstrap superuser had executed the command
<code class="literal">GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE,
INHERIT FALSE</code>. Since a <code class="literal">CREATEROLE</code> user can
only exercise special privileges with regard to an existing role if they
have <code class="literal">ADMIN OPTION</code> on it, this grant is just sufficient
to allow a <code class="literal">CREATEROLE</code> user to administer the roles they
created. However, because it is created with <code class="literal">INHERIT FALSE, SET
FALSE</code>, the <code class="literal">CREATEROLE</code> user doesn't inherit the
privileges of the created role, nor can it access the privileges of that
role using <code class="literal">SET ROLE</code>. However, since any user who has
<code class="literal">ADMIN OPTION</code> on a role can grant membership in that
role to any other user, the <code class="literal">CREATEROLE</code> user can gain
access to the created role by simply granting that role back to
themselves with the <code class="literal">INHERIT</code> and/or <code class="literal">SET</code>
options. Thus, the fact that privileges are not inherited by default nor
is <code class="literal">SET ROLE</code> granted by default is a safeguard against
accidents, not a security feature. Also note that, because this automatic
grant is granted by the bootstrap user, it cannot be removed or changed by
the <code class="literal">CREATEROLE</code> user; however, any superuser could
revoke it, modify it, and/or issue additional such grants to other
<code class="literal">CREATEROLE</code> users. Whichever <code class="literal">CREATEROLE</code>
users have <code class="literal">ADMIN OPTION</code> on a role at any given time
can administer it.
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="database-roles.html" title="22.1. Database Roles">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="user-manag.html" title="Chapter 22. Database Roles">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="role-membership.html" title="22.3. Role Membership">Next</a></td></tr><tr><td width="40%" align="left" valign="top">22.1. Database Roles </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"> 22.3. Role Membership</td></tr></table></div></body></html>
|
