Can someone help me with this? I need to find out all the objects in a database using a script. The reason why I need this is, I am asked to change the owner of all database objects so for that I need to first list down all objects.
Add a comment
|
4 Answers
I think you probably want REASSIGN OWNED instead. No need to identify all the objects, just the users.
Comments
If you're changing every object from role A to role B, you might like REASSIGN OWNED:
REASSIGN OWNED BY A TO B
5 Comments
Arun
I am trying below query but it is throwing a syntax error. Am I missing something? reassign owned postgres to "Arun";
Adam Wright
What specific query are you using? Also, what version of PostgreSQL?
Arun
The query I have used is: reassign owned postgres to "Arun"; (using pgadmin). I am using version 8.3
Peter Eisentraut
It's
REASSIGN OWNED BY A TO B.
Erwin Brandstetter
Please link to the current version of the manual - or to a matching version if required. v8.2 is outdated. I fixed the link (and the format) - Arun has declared version 8.3.
In PostgreSQL (or almost any other RDBMS for that matter), I would recommend to take a look at metadata tables (system catalog).
Example. You want all tables:
db => \d pg_tables
View "pg_catalog.pg_tables"
Column | Type | Modifiers
-------------+---------+-----------
schemaname | name |
tablename | name |
tableowner | name |
tablespace | name |
hasindexes | boolean |
hasrules | boolean |
hastriggers | boolean |
db => select tablename from pg_tables;
Will get you a list of all tables. You can use a query to build a script to change ownership of the tables you want.
Similarly, you can query other views/tables in the catalog to get other object types (sequences, indexes, you name it).
answered Jan 17, 2013 at 13:22
Pablo Santa Cruz
182k33 gold badges250 silver badges300 bronze badges
2 Comments
Arun
Thanks a lot for your reply. This will give me all tables, but what about other objects like schema, constraints, indexes, triggers etc? Is there any way I can list them?
Pablo Santa Cruz
I have just updated the answer. Take a look at the link I posted.
pg_class and/or pg_index is probably what you need for indexes. pg_proc for triggers.If you can do a pg_dump and pg_restore to create a new database the adding the --no-owner flag on the pg_restore and running the pg_restore as the user you wish to set ownership to should work for this.
Comments
