Copy permissions from another table

database-permissions, postgresql

Solution

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.

Problem

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?

Original source