CODEWITHSUNDEEP
sqlpostgresqlprivileges
user425270
user425270

Reputation: 83

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

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"?

Upvotes: 7

Views: 8759

Answers (3)

user292283
user292283

Reputation: 242

A table DDL and its permissions can be dumped to a file using

pg_dump --schema-only --table=my_old_table --schema=my_schema --dbname=my_db --file=my_old_table_ddl.sql ...

Next my_old_table_ddl.sql can be edited to replace old_table_name by new_ table_name and
the DLL executed using psql command.

Executing GRANT statements only allows copying just the permissions.

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

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.

Upvotes: 9

Robert M. Lefkowitz
Robert M. Lefkowitz

Reputation: 1495

Information about tables in postgresql are stored in the pg_class table. The field containing table privileges is relacl.

So something like the following would work:

update pg_class set relacl = (select relacl from pg_class where relname = 'from_table') where relname='to_table';

Note that pg_class has metadata for all tables -- so you should also take care to make sure you are using the right schema (relnamespace) in case there are tables of the same name in multiple schemas.

Upvotes: 2

Related Questions