postgres: how can I allow index creation but no table mutations or table drops by the same user?

Question

I am setting up a postgres server with what is going to be a (mostly) read-only workload.

I would like to allow myself and colleagues to create/drop indices as we see fit, without allowing ourselves to drop tables or mutate the tables.

I spent time reading the grant/revoke documentation as well as role documentation pages, but it seems the table owner roles get both powers.

One thing I found was that, supposedly, grant create on tablespace allows index creation, but it didn't work ( note, I did not create any other tablespaces).

to be specific, I tried:

mydb=# grant create on tablespace pg_default to bob;
GRANT

... then switched over to bob:

mydb=> create index on foo(a);
ERROR:  must be owner of relation foo

I also tried making bob an owner, but then bob gets the right to drop tables, and there is seemingly no way to revoke that privilege. So I'm stuck with both or none.

Any ideas?

Answer 1

At the SQL level you can't, since all those tasks are governed by table ownership.

The CREATE on a tablespace is required but not sufficient to create an index on a table and store the index in that tablespace. If you don't have the CREATE right on the tablespace you want to put the index in then you cannot CREATE INDEX that index. However, having that right is not enough; otherwise anybody could create indexes on any table if they had the right to create anything in any tablespace, and we don't want that. Indexes have a performance cost, take heavy locks during creation, and perhaps most importantly an expression index can leak data about the table via a malicious function or operator. So you must also own the table the index is to be created on.

Support for a separate INDEX right on a table could be added to PostgreSQL, but has not been, and might not get accepted if submitted. For now, you're stuck with having to own the table.

You could write a C extension that installs a ProcessUtility_hook that checks what operations are being performed and the current user identity, then rejects or permits them as appropriate. You can find examples of ProcessUtility_hook use in contrib/sepgsql and externally in the bdr_commandfilter.c file in the BDR project source code. You have to compile the extension, install it into the file system, then add it to shared_preload_libraries to install it, so you need full filesystem level access to the server, and usually root access.

A more practical approach is to use a SECURITY DEFINER function as a wrapper. Write a PL/PgSQL function that runs as the table owner and accepts as arguments the table to index, the column(s) to index, etc. Have it create the CREATE INDEX expression using format(...) then pass it to EXECUTE. Do not allow the user to pass arbitrary SQL expressions as arguments, or you're basically giving them full access via SQL injection. Want multiple columns? You'll have to accept colname text[] as an argument and quote_ident each one. And so on. Search for "dynamic SQL plpgsql" to learn more about this approach.

Answer 2

Even though plpgsql event triggers are pretty limited in current versions, you can achieve the particular result of disallowing only DROP TABLE with them.

Consider this:

CREATE OR REPLACE FUNCTION no_drop() RETURNS event_trigger AS $$
BEGIN
    IF (tg_tag = 'DROP TABLE') THEN
      RAISE exception 'DROP TABLE is not allowed';
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER nodrop_trigger ON sql_drop
   EXECUTE PROCEDURE no_drop();

As user postgres (superuser):

test=# create table foo(bar int);
CREATE TABLE

test=# drop table foo;
ERROR:  DROP TABLE is not allowed

On the other hand CREATE INDEX does work:

test=# create index idx on foo(bar);
CREATE INDEX