por - usuarios y roles en postgresql

Modifique OWNER en todas las tablas simultáneamente en PostgreSQL

Question

¿Cómo modifico el propietario de todas las tablas en una base de datos PostgreSQL?

Intenté ALTER TABLE * OWNER TO new_owner pero no admite la sintaxis de asterisco.

Answer 1

Docker: Modificar propietario de todas las tablas + secuencias

export user="your_new_owner"
export dbname="your_db_name"

cat <<EOF | docker run -i --rm --link postgres:postgres postgres sh -c "psql -h /$POSTGRES_PORT_5432_TCP_ADDR -p /$POSTGRES_PORT_5432_TCP_PORT -U postgres -d $dbname" | grep ALTER | docker run -i --rm --link postgres:postgres postgres sh -c "psql -h /$POSTGRES_PORT_5432_TCP_ADDR -p /$POSTGRES_PORT_5432_TCP_PORT -U postgres -d $dbname"
SELECT ''ALTER TABLE ''||schemaname||''.''||tablename||'' OWNER TO $user;'' FROM pg_tables WHERE schemaname = ''public'';
SELECT ''ALTER SEQUENCE ''||relname||'' OWNER TO $user;'' FROM pg_class WHERE relkind = ''S'';
EOF

Answer 2

Ver el comando REASSIGN OWNED

Nota: como @trygvis menciona en la respuesta a continuación , el comando REASSIGN OWNED está disponible desde al menos la versión 8.2, y es un método mucho más fácil.

Como está cambiando la propiedad de todas las tablas, es probable que también desee vistas y secuencias. Esto es lo que hice:

Mesas:

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = ''public'';" YOUR_DB` ; do  psql -c "alter table /"$tbl/" owner to NEW_OWNER" YOUR_DB ; done

Secuencias:

for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = ''public'';" YOUR_DB` ; do  psql -c "alter table /"$tbl/" owner to NEW_OWNER" YOUR_DB ; done

Puntos de vista:

for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = ''public'';" YOUR_DB` ; do  psql -c "alter table /"$tbl/" owner to NEW_OWNER" YOUR_DB ; done

Probablemente podrías DRY eso un poco, ya que las declaraciones de alteración son idénticas para las tres.

Answer 3

A partir de PostgreSQL 9.0, tiene la capacidad de GRANT [priv name] ON ALL [object type] IN SCHEMA donde [priv name] es el típico SELECT, INSERT, UPDATE, DELETE, etc y [object type] puede ser uno de los siguientes:

• TABLES
• SEQUENCES
• FUNCTIONS

Los documentos de PostgreSQL en GRANT y GRANT entran en más detalles al respecto. En algunas situaciones aún es necesario usar trucos que involucren los catálogos del sistema ( pg_catalog.pg_* ) pero no es tan común. Con frecuencia hago lo siguiente:

1. BEGIN una transacción para modificar los privs
2. Cambiar la propiedad de DATABASES a un "rol de DBA"
3. Cambiar la propiedad de SCHEMAS al "rol DBA"
4. REVOKE ALL privilegios en todas las TABLES , SEQUENCES y FUNCTIONS de todos los roles
5. GRANT SELECT, INSERT, UPDATE, DELETE en tablas relevantes / apropiadas para los roles apropiados
6. COMMIT la transacción DCL.

Answer 4

Basado en la share , aquí hay una solución para múltiples esquemas:

DO $$
DECLARE 
  r record;
  i int;
  v_schema text[] := ''{public,schema1,schema2,schema3}'';
  v_new_owner varchar := ''my_new_owner'';
BEGIN
    FOR r IN 
        select ''ALTER TABLE "'' || table_schema || ''"."'' || table_name || ''" OWNER TO '' || v_new_owner || '';'' as a from information_schema.tables where table_schema = ANY (v_schema)
        union all
        select ''ALTER TABLE "'' || sequence_schema || ''"."'' || sequence_name || ''" OWNER TO '' || v_new_owner || '';'' as a from information_schema.sequences where sequence_schema = ANY (v_schema)
        union all
        select ''ALTER TABLE "'' || table_schema || ''"."'' || table_name || ''" OWNER TO '' || v_new_owner || '';'' as a from information_schema.views where table_schema = ANY (v_schema)
        union all
        select ''ALTER FUNCTION "''||nsp.nspname||''"."''||p.proname||''"(''||pg_get_function_identity_arguments(p.oid)||'') OWNER TO '' || v_new_owner || '';'' as a from pg_proc p join pg_namespace nsp ON p.pronamespace = nsp.oid where nsp.nspname = ANY (v_schema)
        union all
        select ''ALTER DATABASE "'' || current_database() || ''" OWNER TO '' || v_new_owner 
    LOOP
        EXECUTE r.a;
    END LOOP;
    FOR i IN array_lower(v_schema,1) .. array_upper(v_schema,1)
    LOOP
        EXECUTE ''ALTER SCHEMA "'' || v_schema[i] || ''" OWNER TO '' || v_new_owner ;
    END LOOP;
END
$$;

Answer 5

El siguiente script de shell más simple funcionó para mí.

#!/bin/bash
for i in  `psql -U $1  -qt -c  "select tablename from pg_tables where schemaname=''$2''"`
do
psql -U $1 -c  "alter table $2.$i set schema $3"
done

Donde ingrese $ 1 - nombre de usuario (base de datos) $ 2 = esquema existente $ 3 = a nuevo esquema.

Answer 6

Esto: http://archives.postgresql.org/pgsql-bugs/2007-10/msg00234.php también es una solución agradable y rápida, y funciona para múltiples esquemas en una base de datos:

Mesas

SELECT ''ALTER TABLE ''|| schemaname || ''.'' || tablename ||'' OWNER TO my_new_owner;''
FROM pg_tables WHERE NOT schemaname IN (''pg_catalog'', ''information_schema'')
ORDER BY schemaname, tablename;

Secuencias

SELECT ''ALTER SEQUENCE ''|| sequence_schema || ''.'' || sequence_name ||'' OWNER TO my_new_owner;''
FROM information_schema.sequences WHERE NOT sequence_schema IN (''pg_catalog'', ''information_schema'')
ORDER BY sequence_schema, sequence_name;

Puntos de vista

SELECT ''ALTER VIEW ''|| table_schema || ''.'' || table_name ||'' OWNER TO my_new_owner;''
FROM information_schema.views WHERE NOT table_schema IN (''pg_catalog'', ''information_schema'')
ORDER BY table_schema, table_name;

Vistas materializadas

Basado en esta respuesta

SELECT ''ALTER TABLE ''|| oid::regclass::text ||'' OWNER TO my_new_owner;''
FROM pg_class WHERE relkind = ''m''
ORDER BY oid;

Esto genera todas las declaraciones ALTER TABLE / ALTER SEQUENCE / ALTER VIEW requeridas, cópielas y péguelas nuevamente en plsql para ejecutarlas.

Revisa tu trabajo en psql haciendo:

/dt *.*
/ds *.*
/dv *.*

Answer 7

Hace poco tuve que cambiar la propiedad de todos los objetos en una base de datos. Aunque las tablas, las vistas, los activadores y las secuencias se modificaron con facilidad, el enfoque anterior falló para las funciones, ya que la firma forma parte del nombre de la función. Por supuesto, tengo antecedentes en MySQL y no estoy muy familiarizado con Postgres.

Sin embargo, pg_dump le permite volcar solo el esquema y esto contiene ALTER xxx OWNER TO yyy; declaraciones que necesita. Aquí está mi pedazo de magia de concha sobre el tema

pg_dump -s YOUR_DB | grep -i ''owner to'' | sed -e ''s/OWNER TO .*;/OWNER TO NEW_OWNER;/i'' | psqL YOUR_DB

Answer 8

He creado un script conveniente para eso; pg_change_db_owner.sh . Este script cambia la propiedad de todas las tablas, vistas, secuencias y funciones en un esquema de base de datos y también del propietario del esquema en sí.

Tenga en cuenta que si solo desea cambiar la propiedad de todos los objetos, en una base de datos en particular, que pertenezca a un rol de base de datos en particular, puede usar el comando REASSIGN OWNED lugar.

Answer 9

Igual que el enfoque de @ AlexSoto para las funciones:

IFS=$''/n''  
for fnc in `psql -qAt -c "SELECT  ''/"'' || p.proname||''/"'' || ''('' || pg_catalog.pg_get_function_identity_arguments(p.oid) || '')'' FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE n.nspname = ''public'';" YOUR_DB` ; do  psql -c "alter function $fnc owner to NEW_OWNER" YOUR_DB; done

Answer 10

La respuesta de @Alex Soto es la correcta y la esencia cargada por @Yoav Aner también funciona siempre que no haya caracteres especiales en los nombres de tabla / vista (que son legales en postgres).

Necesitas escapar de ellos para trabajar y he subido una idea para eso: https://gist.github.com/2911117

Answer 11

La solución aceptada no se encarga de la propiedad de la función, ya que la solución se encarga de todo (al revisar, noté que es similar a @magiconair arriba)

echo "Database: ${DB_NAME}"
echo "Schema: ${SCHEMA}"
echo "User: ${NEW_OWNER}"

pg_dump -s -c -U postgres ${DB_NAME} | egrep "${SCHEMA}/..*OWNER TO"| sed -e "s/OWNER TO.*;$/OWNER TO ${NEW_OWNER};/" | psql -U postgres -d ${DB_NAME}
# do following as last step to allow recovery
psql -U postgres -d postgres -c "ALTER DATABASE ${DB_NAME} OWNER TO ${NEW_OWNER};"

Answer 12

Me gusta este, ya que modifica tablas , vistas , secuencias y funciones de un determinado esquema de una sola vez (en una sola instrucción SQL), sin crear una función y puede usarla directamente en PgAdmin III y psql :

(Probado en PostgreSql v9.2)

DO $$DECLARE r record;
DECLARE
    v_schema varchar := ''public'';
    v_new_owner varchar := ''<NEW_OWNER>'';
BEGIN
    FOR r IN 
        select ''ALTER TABLE "'' || table_schema || ''"."'' || table_name || ''" OWNER TO '' || v_new_owner || '';'' as a from information_schema.tables where table_schema = v_schema
        union all
        select ''ALTER TABLE "'' || sequence_schema || ''"."'' || sequence_name || ''" OWNER TO '' || v_new_owner || '';'' as a from information_schema.sequences where sequence_schema = v_schema
        union all
        select ''ALTER TABLE "'' || table_schema || ''"."'' || table_name || ''" OWNER TO '' || v_new_owner || '';'' as a from information_schema.views where table_schema = v_schema
        union all
        select ''ALTER FUNCTION "''||nsp.nspname||''"."''||p.proname||''"(''||pg_get_function_identity_arguments(p.oid)||'') OWNER TO '' || v_new_owner || '';'' as a from pg_proc p join pg_namespace nsp ON p.pronamespace = nsp.oid where nsp.nspname = v_schema
    LOOP
        EXECUTE r.a;
    END LOOP;
END$$;

Basado en las respuestas proporcionadas por @rkj, @AlannaRose, @SharoonThomas, @ user3560574 y esta respuesta por @a_horse_with_no_name

Muchas gracias.

Mejor aún: también cambiar la base de datos y el propietario del esquema .

DO $$DECLARE r record;
DECLARE
    v_schema varchar := ''public'';
    v_new_owner varchar := ''admin_ctes'';
BEGIN
    FOR r IN 
        select ''ALTER TABLE "'' || table_schema || ''"."'' || table_name || ''" OWNER TO '' || v_new_owner || '';'' as a from information_schema.tables where table_schema = v_schema
        union all
        select ''ALTER TABLE "'' || sequence_schema || ''"."'' || sequence_name || ''" OWNER TO '' || v_new_owner || '';'' as a from information_schema.sequences where sequence_schema = v_schema
        union all
        select ''ALTER TABLE "'' || table_schema || ''"."'' || table_name || ''" OWNER TO '' || v_new_owner || '';'' as a from information_schema.views where table_schema = v_schema
        union all
        select ''ALTER FUNCTION "''||nsp.nspname||''"."''||p.proname||''"(''||pg_get_function_identity_arguments(p.oid)||'') OWNER TO '' || v_new_owner || '';'' as a from pg_proc p join pg_namespace nsp ON p.pronamespace = nsp.oid where nsp.nspname = v_schema
        union all
        select ''ALTER SCHEMA "'' || v_schema || ''" OWNER TO '' || v_new_owner 
        union all
        select ''ALTER DATABASE "'' || current_database() || ''" OWNER TO '' || v_new_owner 
    LOOP
        EXECUTE r.a;
    END LOOP;
END$$;

Answer 13

No hay tal comando en PostgreSQL. Pero puede evitarlo utilizando el método que described algún tiempo para los GRANT.

Answer 14

Puede utilizar el comando REASSIGN OWNED .

Sinopsis:

REASSIGN OWNED BY old_role [, ...] TO new_role

Esto cambia todos los objetos propiedad de old_role al nuevo rol. No tiene que pensar en qué tipo de objetos tiene el usuario, todos serán cambiados. Tenga en cuenta que solo se aplica a objetos dentro de una sola base de datos. Tampoco altera el propietario de la base de datos.

Está disponible de nuevo a al menos 8.2. Su documentación en línea sólo va tan lejos.

Answer 15

Puedes probar lo siguiente en PostgreSQL 9

DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = ''public''
    LOOP
        EXECUTE ''alter table ''|| r.tablename ||'' owner to newowner;'';
    END LOOP;
END$$;

Answer 16

Si desea hacerlo en una declaración de SQL, debe definir una función exec () como se menciona en http://wiki.postgresql.org/wiki/Dynamic_DDL

CREATE FUNCTION exec(text) returns text language plpgsql volatile
  AS $f$
    BEGIN
      EXECUTE $1;
      RETURN $1;
    END;
$f$;

Luego puedes ejecutar esta consulta, cambiará el propietario de las tablas, secuencias y vistas:

SELECT exec(''ALTER TABLE '' || quote_ident(s.nspname) || ''.'' ||
            quote_ident(s.relname) || '' OWNER TO $NEWUSER'')
  FROM (SELECT nspname, relname
          FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
         WHERE nspname NOT LIKE E''pg//_%'' AND 
               nspname <> ''information_schema'' AND 
               relkind IN (''r'',''S'',''v'') ORDER BY relkind = ''S'') s;

$ NEWUSER es el nuevo nombre postgresql del nuevo propietario.

En la mayoría de las circunstancias, necesitas ser superusuario para ejecutar esto. Puede evitarlo cambiando el propietario de su propio usuario a un grupo de roles del que sea miembro.

Gracias a RhodiumToad en #postgresql por ayudar con esto.

Answer 17

es muy simple

1. su - postgres
2. psql
3. REASIGNADO POR [old_user] TO [new_user];
4. / c [su base de datos]
5. REASIGNADO POR [old_user] TO [new_user];

hecho.

Answer 18

muy simple, inténtalo ...

 select ''ALTER TABLE '' || table_name || '' OWNER TO myuser;'' from information_schema.tables where table_schema = ''public'';

Answer 19

pg_dump as insert statements 
pg_dump -d -O database filename
-d ( data as inserts ) -O ( capital O is no owner )

Luego, vuelva a colocar el archivo de respaldo en PostgreSQL usando:

psql -d database -U username -h hostname < filename

Como no se incluye un propietario, todas las tablas, esquemas, etc. creados se crean bajo el usuario de inicio de sesión que especifique.

He leído que esto también podría ser un buen enfoque para migrar entre las versiones de PostgreSQL.