Skip to content

Commit c6367df

Browse files
tglsfdctglsfdc
committed
Change default privileges for languages and functions to be PUBLIC USAGE
and PUBLIC EXECUTE, respectively. Per discussion about easing updates from prior versions.
1 parent e92bec2 commit c6367df

File tree

8 files changed

+71
-36
lines changed

8 files changed

+71
-36
lines changed

‎doc/src/sgml/ref/grant.sgml

Lines changed: 25 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v 1.29 2002/09/03 22:17:34 tgl Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/grant.sgml,v 1.30 2002/09/24 23:14:25 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -62,15 +62,27 @@ GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
6262
</para>
6363

6464
<para>
65-
Users other than the creator of an object do not have any access privileges
66-
to the object unless the creator grants permissions.
6765
There is no need to grant privileges to the creator of an object,
68-
as the creator automatically holds all privileges.
66+
as the creator has all privileges by default.
6967
(The creator could, however, choose to revoke
70-
some of his own privileges for safety. Note that the ability to
68+
some of his own privileges for safety.) Note that the ability to
7169
grant and revoke privileges is inherent in the creator and cannot
72-
be lost. The right to drop the object is likewise inherent in the
73-
creator, and cannot be granted or revoked.)
70+
be lost. The right to drop an object, or to alter it in any way
71+
not described by a grantable right, is likewise inherent in the
72+
creator, and cannot be granted or revoked.
73+
</para>
74+
75+
<para>
76+
Depending on the type of object, the initial default privileges may
77+
include granting some privileges to <literal>PUBLIC</literal>.
78+
The default is no public access for tables and schemas;
79+
<literal>TEMP</> table creation privilege for databases;
80+
<literal>EXECUTE</> privilege for functions; and
81+
<literal>USAGE</> privilege for languages.
82+
The object creator may of course revoke these privileges. (For maximum
83+
security, issue the <command>REVOKE</> in the same transaction that
84+
creates the object; then there is no window in which another user
85+
may use the object.)
7486
</para>
7587

7688
<para>
@@ -137,9 +149,9 @@ GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
137149
<term>REFERENCES</term>
138150
<listitem>
139151
<para>
140-
To create a table with a foreign key constraint, it is
141-
necessary to have this privilege on the table with the referenced
142-
key.
152+
To create a foreign key constraint, it is
153+
necessary to have this privilege on both the referencing and
154+
referenced tables.
143155
</para>
144156
</listitem>
145157
</varlistentry>
@@ -292,10 +304,9 @@ GRANT SELECT,UPDATE,INSERT ON mytable TO GROUP todos;
292304
<para>
293305
If the <quote>Access privileges</> column is empty for a given object,
294306
it means the object has default privileges (that is, its privileges field
295-
is NULL). Currently, default privileges are interpreted as <quote>all
296-
privileges for the owner and no privileges for anyone else</quote>, except
297-
for databases: the default privilege settings for a database allow anyone
298-
to create temporary tables in it. The first <command>GRANT</> or
307+
is NULL). Default privileges always include all privileges for the owner,
308+
and may include some privileges for <literal>PUBLIC</> depending on the
309+
object type, as explained above. The first <command>GRANT</> or
299310
<command>REVOKE</> on an object
300311
will instantiate the default privileges (producing, for example,
301312
<literal>{=,miriam=arwdRxt}</>) and then modify them per the specified request.

‎src/backend/catalog/aclchk.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/catalog/aclchk.c,v 1.77 2002/09/04 20:31:13 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/catalog/aclchk.c,v 1.78 2002/09/24 23:14:25 tgl Exp $
1212
*
1313
* NOTES
1414
* See acl.h.
@@ -481,7 +481,7 @@ ExecuteGrantStmt_Language(GrantStmt *stmt)
481481
elog(ERROR, "language \"%s\" not found", langname);
482482
pg_language_tuple = (Form_pg_language) GETSTRUCT(tuple);
483483

484-
if (!pg_language_tuple->lanpltrusted)
484+
if (!pg_language_tuple->lanpltrusted && stmt->is_grant)
485485
elog(ERROR, "language \"%s\" is not trusted", langname);
486486

487487
/*

‎src/backend/utils/adt/acl.c

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/acl.c,v 1.79 2002/09/04 20:31:27 momjian Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/acl.c,v 1.80 2002/09/24 23:14:25 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -417,11 +417,13 @@ acldefault(GrantObjectType objtype, AclId ownerid)
417417
owner_default = ACL_ALL_RIGHTS_DATABASE;
418418
break;
419419
case ACL_OBJECT_FUNCTION:
420-
world_default = ACL_NO_RIGHTS;
420+
/* Grant EXECUTE by default, for now */
421+
world_default = ACL_EXECUTE;
421422
owner_default = ACL_ALL_RIGHTS_FUNCTION;
422423
break;
423424
case ACL_OBJECT_LANGUAGE:
424-
world_default = ACL_NO_RIGHTS;
425+
/* Grant USAGE by default, for now */
426+
world_default = ACL_USAGE;
425427
owner_default = ACL_ALL_RIGHTS_LANGUAGE;
426428
break;
427429
case ACL_OBJECT_NAMESPACE:

‎src/bin/initdb/initdb.sh

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -27,7 +27,7 @@
2727
# Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
2828
# Portions Copyright (c) 1994, Regents of the University of California
2929
#
30-
# $Header: /cvsroot/pgsql/src/bin/initdb/Attic/initdb.sh,v 1.174 2002/09/18 21:35:23 tgl Exp $
30+
# $Header: /cvsroot/pgsql/src/bin/initdb/Attic/initdb.sh,v 1.175 2002/09/24 23:14:25 tgl Exp $
3131
#
3232
#-------------------------------------------------------------------------
3333

@@ -1034,6 +1034,8 @@ $ECHO_N "setting privileges on built-in objects... "$ECHO_C
10341034
WHERE proacl IS NULL;
10351035
UPDATE pg_language SET lanacl = '{"=U"}' \
10361036
WHERE lanpltrusted;
1037+
UPDATE pg_language SET lanacl = '{"="}' \
1038+
WHERE NOT lanpltrusted;
10371039
EOF
10381040
) \
10391041
| "$PGPATH"/postgres $PGSQL_OPT template1 > /dev/null || exit_nicely

‎src/bin/pg_dump/pg_dump.c

Lines changed: 25 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -22,7 +22,7 @@
2222
*
2323
*
2424
* IDENTIFICATION
25-
* $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v 1.300 2002/09/22 20:57:20 petere Exp $
25+
* $Header: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v 1.301 2002/09/24 23:14:25 tgl Exp $
2626
*
2727
*-------------------------------------------------------------------------
2828
*/
@@ -4839,16 +4839,18 @@ GetPrivileges(Archive *AH, const char *s, const char *type)
48394839
}
48404840

48414841

4842-
/*
4842+
/*----------
48434843
* Write out grant/revoke information
48444844
*
4845-
* 'type' must be TABLE, FUNCTION, LANGUAGE, or SCHEMA. 'name' is the
4846-
* formatted name of the object. Must be quoted etc. already.
4845+
* 'type' must be TABLE, FUNCTION, LANGUAGE, or SCHEMA.
4846+
* 'name' is the formatted name of the object. Must be quoted etc. already.
4847+
* 'tag' is the tag for the archive entry (typ. unquoted name of object).
48474848
* 'nspname' is the namespace the object is in (NULL if none).
48484849
* 'usename' is the owner, NULL if there is no owner (for languages).
48494850
* 'acls' is the string read out of the fooacl system catalog field;
48504851
* it will be parsed here.
48514852
* 'objoid' is the OID of the object for purposes of ordering.
4853+
*----------
48524854
*/
48534855
static void
48544856
dumpACL(Archive *fout, const char *type, const char *name,
@@ -4867,6 +4869,14 @@ dumpACL(Archive *fout, const char *type, const char *name,
48674869

48684870
sql = createPQExpBuffer();
48694871

4872+
/*
4873+
* Always start with REVOKE ALL FROM PUBLIC, so that we don't have to
4874+
* wire-in knowledge about the default public privileges for different
4875+
* kinds of objects.
4876+
*/
4877+
appendPQExpBuffer(sql, "REVOKE ALL ON %s %s FROM PUBLIC;\n",
4878+
type, name);
4879+
48704880
/* Make a working copy of acls so we can use strtok */
48714881
aclbuf = strdup(acls);
48724882

@@ -4938,18 +4948,21 @@ dumpACL(Archive *fout, const char *type, const char *name,
49384948
else
49394949
{
49404950
/* No privileges. Issue explicit REVOKE for safety. */
4941-
appendPQExpBuffer(sql, "REVOKE ALL ON %s %s FROM ",
4942-
type, name);
49434951
if (eqpos == tok)
49444952
{
4945-
/* Empty left-hand side means "PUBLIC" */
4946-
appendPQExpBuffer(sql, "PUBLIC;\n");
4953+
/* Empty left-hand side means "PUBLIC"; already did it */
49474954
}
49484955
else if (strncmp(tok, "group ", strlen("group ")) == 0)
4949-
appendPQExpBuffer(sql, "GROUP %s;\n",
4956+
{
4957+
appendPQExpBuffer(sql, "REVOKE ALL ON %s %s FROM GROUP %s;\n",
4958+
type, name,
49504959
fmtId(tok + strlen("group ")));
4960+
}
49514961
else
4952-
appendPQExpBuffer(sql, "%s;\n", fmtId(tok));
4962+
{
4963+
appendPQExpBuffer(sql, "REVOKE ALL ON %s %s FROM %s;\n",
4964+
type, name, fmtId(tok));
4965+
}
49534966
}
49544967
free(priv);
49554968
}
@@ -4960,9 +4973,8 @@ dumpACL(Archive *fout, const char *type, const char *name,
49604973
*/
49614974
if (!found_owner_privs && usename)
49624975
{
4963-
appendPQExpBuffer(sql, "REVOKE ALL ON %s %s FROM ",
4964-
type, name);
4965-
appendPQExpBuffer(sql, "%s;\n", fmtId(usename));
4976+
appendPQExpBuffer(sql, "REVOKE ALL ON %s %s FROM %s;\n",
4977+
type, name, fmtId(usename));
49664978
}
49674979

49684980
ArchiveEntry(fout, objoid, tag, nspname, usename ? usename : "",

‎src/bin/scripts/createlang.sh

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
# Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
88
# Portions Copyright (c) 1994, Regents of the University of California
99
#
10-
# $Header: /cvsroot/pgsql/src/bin/scripts/Attic/createlang.sh,v 1.38 2002/08/22 00:01:47 tgl Exp $
10+
# $Header: /cvsroot/pgsql/src/bin/scripts/Attic/createlang.sh,v 1.39 2002/09/24 23:14:25 tgl Exp $
1111
#
1212
#-------------------------------------------------------------------------
1313

@@ -291,8 +291,13 @@ if [ "$?" -ne 0 ]; then
291291
exit 1
292292
fi
293293

294-
if test -n "$trusted"; then
295-
sqlcmd="GRANT USAGE ON LANGUAGE \"$langname\" TO PUBLIC;"
294+
# ----------
295+
# Grant privileges. As of 7.3 the default privileges for a language include
296+
# public USAGE, so we need not change them for a trusted language; but it
297+
# seems best to disable public USAGE for an untrusted one.
298+
# ----------
299+
if test -z "$trusted"; then
300+
sqlcmd="REVOKE ALL ON LANGUAGE \"$langname\" FROM PUBLIC;"
296301
if [ "$showsql" = yes ]; then
297302
echo "$sqlcmd"
298303
fi
@@ -302,4 +307,5 @@ if test -n "$trusted"; then
302307
exit 1
303308
fi
304309
fi
310+
305311
exit 0

‎src/test/regress/expected/privileges.out

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -229,6 +229,7 @@ GRANT USAGE ON LANGUAGE sql TO regressuser2; -- fail
229229
ERROR: permission denied
230230
CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql;
231231
CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
232+
REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int) FROM PUBLIC;
232233
GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regressuser2;
233234
GRANT USAGE ON FUNCTION testfunc1(int) TO regressuser3; -- semantic error
234235
ERROR: invalid privilege type USAGE for function object

‎src/test/regress/sql/privileges.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -156,6 +156,7 @@ GRANT USAGE ON LANGUAGE sql TO regressuser2; -- fail
156156
CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql;
157157
CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
158158

159+
REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int) FROM PUBLIC;
159160
GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regressuser2;
160161
GRANT USAGE ON FUNCTION testfunc1(int) TO regressuser3; -- semantic error
161162
GRANT ALL PRIVILEGES ON FUNCTION testfunc1(int) TO regressuser4;

0 commit comments

Comments
 (0)