Skip to content

Instantly share code, notes, and snippets.

@ipmb

ipmb/pg_chown.py

Last active August 18, 2021 22:12
Show Gist options
  • Save ipmb/bf176278e1a37d07efd9f7b62927cab5 to your computer and use it in GitHub Desktop.
Save ipmb/bf176278e1a37d07efd9f7b62927cab5 to your computer and use it in GitHub Desktop.
Download ZIP
change ownership of all items in public schema using Python/psycopg2
Raw
pg_chown.py
import psycopg2
NEW_USER = "fred"
types = {"c": "TABLE", "t": "TABLE", "r": "TABLE", "S": "SEQUENCE", "i": "INDEX", "v": "VIEW"}
with psycopg2.connect(**kwargs) as conn:
conn.autocommit = True
with conn.cursor() as curs:
for kind, name in types.items():
curs.execute(
f"SELECT format('ALTER {name} %%I.%%I OWNER TO %%I', n.nspname, c.relname, %s) "
"FROM pg_class c, pg_namespace n "
"WHERE n.oid = c.relnamespace AND n.nspname = 'public' AND c.relkind = %s",
(NEW_USER, kind),
)
for s in curs.fetchall():
print(f" + {s[0]}")
curs.execute(s[0])
# Functions
curs.execute(
"SELECT format('ALTER FUNCTION %%I.%%I(%%I) OWNER TO %%I', p.proname, n.nspname, pg_catalog.pg_get_function_identity_arguments(p.oid), %s) "
"FROM pg_catalog.pg_namespace n "
"JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid "
"WHERE n.nspname = 'public'",
(NEW_USER,),
)
for s in curs.fetchall():
print(f" + {s[0]}")
curs.execute(s[0])
# Text Search Dictionaries
curs.execute(
f"SELECT format('ALTER TEXT SEARCH DICTIONARY %%I.%%I OWNER TO %%I', n.nspname, d.dictname, %s) "
"FROM pg_catalog.pg_namespace n "
"JOIN pg_catalog.pg_ts_dict d ON d.dictnamespace = n.oid "
"WHERE n.nspname = 'public'",
(NEW_USER,),
)
for s in curs.fetchall():
print(f" + {s[0]}")
curs.execute(s[0])
@ipmb
Copy link
Author

ipmb commented Aug 18, 2021

A pure SQL version

\set ECHO queries
\pset pager off
-- change ownership of all objects in a single database
-- dynamically generate all the queries and then execute them using \gexec
-- BE SURE TO UPDATE THE USERNAME BELOW BEFORE EXECUTING
WITH objects AS (
  SELECT
    *
  FROM
    (
      VALUES
        ('t', 'TABLE'),
        ('r', 'TABLE'),
        ('S', 'SEQUENCE'),
        ('i', 'INDEX'),
        ('v', 'VIEW')
    ) AS t (abbr, name)
)
SELECT
  format(
    'ALTER %s %I.%I OWNER TO %s',
    o.name, n.nspname, c.relname, 'new_username'  -- change username to match the environment here
  )
FROM
  pg_class c,
  pg_namespace n,
  objects o
WHERE
  n.oid = c.relnamespace
  AND n.nspname = 'public'
  AND c.relkind = o.abbr; \gexec

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment