Skip site navigation (1) Skip section navigation (2)

[Patch Review] TRUNCATE Permission

From: "Ryan Bradetich" <rbradetich(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: [Patch Review] TRUNCATE Permission
Date: 2008-09-01 06:55:25
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
Hello all,

Robert Haas submitted the TRUNCATE permissions patch for the September
commit fest:

I had some extra time tonight, so I downloaded, installed and reviewed this
Here are my findings:

1. I found the patch style to be consistent with the surrounding code.
2. The patch provides documentation updates and regression test updates.
3. The patch applies (with some fuzz) to the latest GIT tree.

Three issues I found with the patch via code reading and verified via

1. File: src/backend/catalog/aclchk.c:
    Function: pg_class_aclmask():

I believe the ACL_TRUNCATE trigger should be added to this check and mask.

         * Deny anyone permission to update a system catalog unless
         * pg_authid.rolcatupdate is set.       (This is to let superusers
         * themselves from themselves.)  Also allow it if
         * As of 7.4 we have some updatable system views; those shouldn't be
         * protected in this way.  Assume the view rules can take care of
         * themselves.  ACL_USAGE is if we ever have system sequences.
        if ((mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_USAGE)) &&
                IsSystemClass(classForm) &&
                classForm->relkind != RELKIND_VIEW &&
                !has_rolcatupdate(roleid) &&
                elog(DEBUG2, "permission denied for system catalog update");
                mask &= ~(ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_USAGE);

Here is where it is visible via the psql interface:

template1=# select rolname, rolcatupdate from pg_authid;
 rolname | rolcatupdate
 rbrad   | t
(1 row)

template1=# select has_table_privilege('pg_class', 'delete');
(1 row)

template1=# select has_table_privilege('pg_class', 'truncate');
(1 row)

template1=# update pg_authid set rolcatupdate = false;

template1=# select has_table_privilege('pg_class', 'delete');
(1 row)

template1=# select has_table_privilege('pg_class', 'truncate');
(1 row)

I do not believe this is a huge issue since truncate is prohibited on the
system catalogs
by the truncate_check_rel().

template1=# truncate pg_authid;
ERROR:  permission denied: "pg_authid" is a system catalog

  2. The src/test/regress/sql/privileges.sql regression test has tests for
      the has_table_privilege() function.  It looks like all the other
      are tested in this function, but there is not a test for the TRUNCATE

  3.  I believe you missed a documentation update in the ddl.sgml file:

   There are several different privileges: <literal>SELECT</>,
   <literal>INSERT</>, <literal>UPDATE</>, <literal>DELETE</>,
   <literal>REFERENCES</>, <literal>TRIGGER</>,
   <literal>CREATE</>, <literal>CONNECT</>, <literal>TEMPORARY</>,
   <literal>EXECUTE</>, and <literal>USAGE</>.

I played around with the patch for an hour or so tonight and I did not
observer any other unusual behaviors.

Hopefully this review is useful.  It is my first patch review for a
I will update the wiki with a link to this email message for my review.


- Ryan


pgsql-hackers by date

Next:From: Martijn van OosterhoutDate: 2008-09-01 07:21:47
Subject: Re: Our CLUSTER implementation is pessimal
Previous:From: Jaime CasanovaDate: 2008-09-01 06:50:48
Subject: Re: Extending grant insert on tables to sequences

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group