Re: Database migration to RDS issues permissions - Mailing list pgsql-general
From | Fran ... |
---|---|
Subject | Re: Database migration to RDS issues permissions |
Date | |
Msg-id | AM4PR03MB17488FF22F0527775417F6F797B30@AM4PR03MB1748.eurprd03.prod.outlook.com Whole thread Raw |
In response to | Re: Database migration to RDS issues permissions (Adrian Klaver <adrian.klaver@aklaver.com>) |
Responses |
Re: Database migration to RDS issues permissions
(Tom Lane <tgl@sss.pgh.pa.us>)
Re: Database migration to RDS issues permissions (Adrian Klaver <adrian.klaver@aklaver.com>) |
List | pgsql-general |
Hi,
I run "pg_dumpall" command and there are the permissions por the user:
CREATE ROLE dlapuser;
ALTER ROLE dlapuser WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md5XXXXXXXXXXXXXXXXXXXXXXafac';
I also saw the first errors and they are weird.
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4751; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
pg_restore: [archiver (db)] Error from TOC entry 4752; 0 0 COMMENT EXTENSION pg_stat_statements
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension pg_stat_statements
Command was: COMMENT ON EXTENSION pg_stat_statements IS 'track execution statistics of all SQL statements executed';
pg_restore: [archiver (db)] Error from TOC entry 4753; 0 0 COMMENT EXTENSION postgres_fdw
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension postgres_fdw
Command was: COMMENT ON EXTENSION postgres_fdw IS 'foreign-data wrapper for remote PostgreSQL servers';
pg_restore: [archiver (db)] Error from TOC entry 408; 1255 563407 FUNCTION cksum2(text) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c
Command was: CREATE FUNCTION cksum2(text) RETURNS smallint
LANGUAGE c
AS '$libdir/pgc_checksum', 'text_checksum2';
pg_restore: [archiver (db)] could not execute query: ERROR: function public.cksum2(text) does not exist
Command was: ALTER FUNCTION public.cksum2(text) OWNER TO postgres;
pg_restore: [archiver (db)] Error from TOC entry 411; 1255 563408 FUNCTION cksum4(text) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c
Command was: CREATE FUNCTION cksum4(text) RETURNS integer
LANGUAGE c
AS '$libdir/pgc_checksum', 'text_checksum4';
pg_restore: [archiver (db)] could not execute query: ERROR: function public.cksum4(text) does not exist
Command was: ALTER FUNCTION public.cksum4(text) OWNER TO postgres;
@Adrian Klaver what others permissions do you suggest?
Thanks in advance.
De: Adrian Klaver <adrian.klaver@aklaver.com>
Enviado: sábado, 19 de noviembre de 2016 18:24
Para: Fran ...; pgsql-general@postgresql.org
Asunto: Re: [GENERAL] Database migration to RDS issues permissions
Enviado: sábado, 19 de noviembre de 2016 18:24
Para: Fran ...; pgsql-general@postgresql.org
Asunto: Re: [GENERAL] Database migration to RDS issues permissions
On 11/19/2016 07:21 AM, Fran ... wrote:
> Hi Adrian,
>
>
> these are some of them:
>
>
> /pg_restore: [archiver (db)] Error from TOC entry 4997; 0 0 SEQUENCE SET
> account_id_seq owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR: permission
> denied for sequence account_id_seq/
> / Command was: SELECT pg_catalog.setval('account_id_seq', 26738, true);/
> /
> /
>
> /pg_restore: [archiver (db)] Error from TOC entry 4548; 0 106491 TABLE
> DATA account owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR: permission
> denied for relation account/
> / Command was: COPY account (id, user_id, test, picture, status) FROM
> stdin;/
> /
> /
>
> /pg_restore: [archiver (db)] Error from TOC entry 3763; 2604 1179420
> DEFAULT id owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR: must be
> owner of relation trix_venue/
> / Command was: ALTER TABLE ONLY venue ALTER COLUMN id SET DEFAULT
> eval('venue_id_seq'::regclass);/
>
> Thanks in advance.
In addition to what Tom said:
create database database;
grant all privileges on database to ownerdatabase;
is probably not doing what you think it is or want.
A GRANT on a database only grants connect privileges and the ability to
create schemas in the database. It does not allow creating of objects
within the schema. For more details see:
https://www.postgresql.org/docs/9.5/static/sql-grant.html
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
> Hi Adrian,
>
>
> these are some of them:
>
>
> /pg_restore: [archiver (db)] Error from TOC entry 4997; 0 0 SEQUENCE SET
> account_id_seq owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR: permission
> denied for sequence account_id_seq/
> / Command was: SELECT pg_catalog.setval('account_id_seq', 26738, true);/
> /
> /
>
> /pg_restore: [archiver (db)] Error from TOC entry 4548; 0 106491 TABLE
> DATA account owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR: permission
> denied for relation account/
> / Command was: COPY account (id, user_id, test, picture, status) FROM
> stdin;/
> /
> /
>
> /pg_restore: [archiver (db)] Error from TOC entry 3763; 2604 1179420
> DEFAULT id owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR: must be
> owner of relation trix_venue/
> / Command was: ALTER TABLE ONLY venue ALTER COLUMN id SET DEFAULT
> eval('venue_id_seq'::regclass);/
>
> Thanks in advance.
In addition to what Tom said:
create database database;
grant all privileges on database to ownerdatabase;
is probably not doing what you think it is or want.
A GRANT on a database only grants connect privileges and the ability to
create schemas in the database. It does not allow creating of objects
within the schema. For more details see:
https://www.postgresql.org/docs/9.5/static/sql-grant.html
www.postgresql.org GRANT on Database Objects. This variant of the GRANT command gives specific privileges on a database object to one or more roles. These privileges are added to those ... |
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
pgsql-general by date: