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.

Answer 2

The pg_dump approach is nice and simple, however, it doesn't work with tables in other schemas, as the output doesn't qualify the table with schema name. Instead it generates:

SET search_path = foo, pg_catalog;
...
GRANT SELECT ON foo_table to foo_user;

and will fail to grant privileges to an nonexistent public.foo_table relation.

Also, if you have relations with the same name in different schemas, you need to ensure that you only rename the table in the specified schema. I began to hack a bash script base on the above to take care of this but it started to become a bit unwieldy, so I switched to perl.

Usage: transfer-acl old-qualified-relation=new-qualified-relation

e.g. transfer-acl foo.foo_table=foo.bar_table will apply the grants on foo.foo_table to the foo.bar_table. I didn't implement any REVOKE rewriting because I wasn't able to get a dump to emit any.

#! /usr/bin/perl

use strict;
use warnings;

my %rename = map {(split '=')} @ARGV;

open my $dump, '-|', qw(pg_dump customer -s), map {('-t', $_)} keys %rename
    or die "Cannot open pipe from pg_dump: $!\n";

my $schema = 'public';
while (<$dump>) {
    if (/^SET search_path = (\w+)/) {
        $schema = $1;
    }
    elsif (/^(GRANT .*? ON TABLE )(\w+)( TO (?:[^;]+);)$/) {
        my $fq_table = "$schema." . $2; # fully-qualified schema.table
        print "$1$rename{$fq_table}$3\n" if exists $rename{$fq_table};
    }
}

Pipe the results of this to psql and you're set.