PostgreSQL copy permissions from another table

Question

Is it possible to copy the user permissions from one table in a PostgreSQL database to another table? Is it just a matter of updating the pg_class.relacl column value for the target table to the value for the source table, as in:

UPDATE pg_class SET relacl=(SELECT relacl FROM pg_class WHERE relname='source_table') WHERE relname='target_table';

This seems to work, but am I missing anything else that may need to be done or other 'gotchas' with this method?

Thanks in advance for any replies.

Answer 1

If you can use command-line instead of SQL then a safer approach would be to use pg_dump:

pg_dump dbname -t oldtablename -s \
| egrep '^(GRANT|REVOKE)' \
| sed 's/oldtablename/newtablename/' \
| psql dbname

I assume a unix server. On Windows I'd use pg_dump -s to a file, manually edit it and then import it to a database.

Maybe you'll also need to copy permissions to sequences owned by this table - pg_dump will work.