|
Hi,
You were right and I have tried to grant that role to user and I get following errors..
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4335; 2606 151422 FK CONSTRAINT worker_id_refs_id_6fd8ce95 owneruser
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.worker_status" does not exist
Command was: ALTER TABLE ONLY public.worker_status DROP CONSTRAINT worker_id_refs_id_6fd8ce95;
pg_restore: [archiver (db)] Error from TOC entry 4079; 1259 151046 INDEX id_e owneruser
pg_restore: [archiver (db)] could not execute query: ERROR: index "id_e" does not exist
Command was: DROP INDEX public.id_e;
De: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Enviado: sábado, 19 de noviembre de 2016 18:41 Para: Fran ...; pgsql-general@xxxxxxxxxxxxxx; tgl@xxxxxxxxxxxxx Asunto: Re: Database migration to RDS issues permissions On 11/19/2016 09:33 AM, Fran ... wrote:
> 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 think I would solve the problem granting "superuser" permission but > this is not possible in RDS. I don't use RDS, but from what I gather the above is not strictly true: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html
> > > I also saw the first errors and they are weird. > Indications that you are not running the restore as a user with sufficient privileges. Is the database you are dumping from an RDS instance or a regular Postgres database? > > @Adrian Klaver <mailto:adrian.klaver@xxxxxxxxxxx> what others > permissions do you suggest? > > Origin and target are the same version of course. PostgreSQL 9.4.1 > > Thanks in advance. > > ------------------------------------------------------------------------ > *De:* Adrian Klaver <adrian.klaver@xxxxxxxxxxx> > *Enviado:* sábado, 19 de noviembre de 2016 18:24 > *Para:* Fran ...; pgsql-general@xxxxxxxxxxxxxx > *Asunto:* Re: 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 > PostgreSQL: Documentation: 9.5: GRANT > <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@xxxxxxxxxxx -- Adrian Klaver adrian.klaver@xxxxxxxxxxx | ||
