The Linux Page

Table owner in PostgreSQL

System tables and how to edit them is just not defined in the SQL language, unfortunately. So different database systems have different solutions to handle their table declarations. PostgreSQL is no different.

In most cases, MySQL uses some special instruction to update system information. In PostgreSQL, you have internal tables that the administrator can access and tweak as required.

In my case, I often create a table as myself (alexis) and then hand it over to the Apache server (www-data by default under Debian based platforms.) That does not generally work because then the Apache server is not the owner of the resulting table. And that's a problem because it will look as if the table was protected. The Apache server is likely to not be able to SELECT, INSERT, UPDATE, etc. on that table.

The common solution is to use the GRANT command.

       [,...] | ALL [ PRIVILEGES ] }
       ON [ TABLE ] tablename [, ...]
       TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

That generally works, up until you run your Uninstall command (to test and make sure that the table does indeed get deleted.) At that point, it breaks. Then trying to re-install the module breaks too since the table already exists.

So the best way is to change the owner of the table. But there are no option to do so. The only SQL way is to create a new table with the right owner (i.e. create the table as www-data user.) Then copy the existing table in the new table, and finally ask the onwer to delete his table so you can properly name the new table. Tedious! and you have several weak spots in the process.

The best solution, to my point of view, is to fix the ownership of the table. To do so, you want to update a cell in a table defined in the PostgreSQL system as follow:

   UPDATE pg_class SET relowner = <user ID> WHERE relname = '<table name>';

Use a SELECT order to get a list of the existing tables (relations) and see the existing user identifiers.

   SELECT * FROM pg_class;
   SELECT relname, relowner FROM pg_class;

Note that in general it is enough to change the owner of the main table. If you want to really change them all, there are indexes and keys to update too.