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

permissionspostgresqlrole

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?

Best Answer

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.

Related Solutions

MySQL – How to Allow User to Query View but Not Underlying Table

I figured it out myself.

First, I created an account that ONLY had permissions to certain columns (not all). I then created a view for those columns and used the user as the definer.

Then, I created another test account and ONLY gave them permissions to that view.

So the test user could not see the underlying table but only the view and columns.

Works great.

PostgreSQL Index – How Can Unique Index Allow Duplicate Rows?

The answer can be found in the amazing documentation .. it LOOKS like you have NULL values in your tables.. When the DB checks for uniqueness, it says "does NULL equal NULL? NOPE!" and allows it.

The important bit below (emphasis mine):

are not allowed. Null values are not considered equal. A multicolumn unique

If you want to keep the uniqueness across ALL THREE COLUMNS and, at the same time, treat nulls as equal, then you have to get creative with your UNIQUE indexes by making them partial indexes..

CREATE UNIQUE INDEX ix1 ON table (col1, col2wNull, col3wNull) WHERE col2wNull is not NULL and col3wNull is not Null;
CREATE UNIQUE INDEX ix2 ON table (col1, col2wNull) WHERE col2wNull is not Null and col3wNull is Null;
CREATE UNIQUE INDEX ix3 ON table (col1, col3wNull) WHERE col2wNull is Null and col3wNull is not Null;
CREATE UNIQUE INDEX ix4 ON table (col1) WHERE col2wNull is NULL and col3wNull is NULL;

As you can see .. it can easily get a bit crazy.

A different alternative would be to make col2wNull and col3wNull be defined as NOT NULL and provide some default value for when nothing is supplied. THIS MAY OR MAY NOT BE A GOOD IDEA depending on what you're doing. "Magic values" have a tendency to give you lots of problems later.

With regard to your edit and the two strings appearing to be equal, but the database reporting that they are not - I can only imagine that there are some "invisible" characters (UTF-8?) that are in the string. Or it could be something as simple as one string has an additional space on the end. It partially depends on how you are saving it into the database. (Are you performing a trim() on them, lower(), etc..)

You could try comparing the strings in various other ways (such as looking at md5 hash). I believe you can ask postgres to convert the column value into hex to view, as well, but how to go about doing that is escaping me at the moment (my apologies).

Best Answer

Related Solutions

MySQL – How to Allow User to Query View but Not Underlying Table

PostgreSQL Index – How Can Unique Index Allow Duplicate Rows?

Related Question