postgresqldatabase-permissions

Copy permissions from another table

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?

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.