Can you copy table privileges from one table to another in postgresql?

Question

I've got a import process that copies a table schema with the code below and then populates the table with data. However it doesn't copy over the roles granted.

CREATE TABLE TOTABLE (LIKE FROMTABLE INCLUDING INDEXES)

Is there a way I can copy privileges when the schema is copied, or I can apply the privileges afterwards from the "FROMTABLE"?

Answer 1

Be very careful when manipulating catalog tables directly. It's generally advisable to use DDL statements exclusively. Catalog tables are not meant to be written by users. If you mess this up, your DB cluster might be corrupted beyond repair. You have been warned.

Update: Turns out, the above warning is quite right. This was a bad idea to begin with. Standard GRANT / REVOKE commands (as well as the default privilege system) also make entries in pg_shdepend table to remember dependencies between objects and roles mentioned in the access control list (except for the owner, which is linked anyway). The manual:

The catalog pg_shdepend records the dependency relationships between database objects and shared objects, such as roles. This information allows PostgreSQL to ensure that those objects are unreferenced before attempting to delete them.

By manipulating the access control list (relacl for relations) directly, dependencies fall out of sync, which can lead to "strange" problems when trying to drop roles later.

There was a recent discussion about "Copying Permissions" on pgsql-hackers (Nov 2016), but nothing has been implemented, yet.

Incomplete solution (do not use!)

The query presented by @Robert has a bug (as he noted): relname is not unique. There can be any number of tables with the same name in multiple schemas of the same db. To fix:

UPDATE pg_class c_to
SET    relacl = c_from.relacl 
FROM   pg_class c_from
WHERE  c_from.oid  = 'public.from_table'::regclass
AND    c_to.oid    = 'public.to_table'::regclass

Differences

• The cast to regclass picks a table deterministically, even without schema-qualification. Details:

• How do I speed up counting rows in a PostgreSQL table?

• If one of the tables is not found, you get an exception immediately (the cast to regclass fails).
  @Robert's query would happily set relacl to NULL if from_table is not found.