Thread: Database migration to RDS issues permissions

On 11/19/2016 05:21 AM, Fran ... wrote:
> Hi,
>
>
> I have to migrate a production database to RDS. This is the size and info:
>
>
>  database      | owneruser | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> =Tc/owneruser            +| 32 GB   | pg_default |
>
>
> Origin database:
>
> 1 database
>
> 1 owneruser with superuser permission
>
>
> Backup archived size is 2G and it takes less than a minute.
>
>
> I am trying to running the following steps in a DEV environment and I am
> having problems with destination permissions.
>
>
> 1º-Creating RDS instance
>
> Done and I can connect to.
>
>
> 2º-Making backup:
>
> pg_dump -F c database > backup_db.dump
>
> 3º-Creating user,database and grant permissions in RDS.
> Create database database;
> CREATE USER owneruser WITH PASSWORD 'owneruser';
> create database database;
> grant all privileges on database to ownerdatabase;
>
> 4º-Restoring backup
> pg_restore -d database -h hostname -U postgres -F c -f log_file.log
> backup.db.dump
>
> While restoring is working it prints a lot of permissions errors.

The permissions errors are ?

A sampling will suffice for now.

>
> It's being hard to find "how to" and documentations about right permissions.
>
> Regards.
>


--
Adrian Klaver
adrian.klaver@aklaver.com

"Fran ..." <Bryan691@hotmail.com> writes:
> these are some of them:

These look to be cascading damage from some earlier failure.  I'd advise
looking at the first one or two errors and solving them, then repeat
as necessary.

In general, though, pg_dump of a single database is not a complete
representation of where you were: it lacks any information about
global objects (roles and tablespaces).  I suspect your problems
ultimately trace back to not having created the right roles in
the target installation before starting the restore.  You might
find "pg_dumpall -g" to be helpful.

            regards, tom lane

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

"Fran ..." <Bryan691@hotmail.com> writes:
> I think I would solve the problem granting "superuser" permission but this is not possible in RDS.

It looks like your other errors are also due to doing the restore as
a non-superuser.  Not sure if you have any good alternatives here ---
you could just ignore the errors relating to plpgsql, but if you have
C-language functions that you need to migrate, there is no way to
install those without superuser privileges.

            regards, tom lane

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@aklaver.com> 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@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
>
> 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@aklaver.com


--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/19/2016 11:12 AM, Fran ... wrote:
> Hi,
>
>
> You were right and I have tried to grant that role to user and I get
> following errors..

GRANT what role to what user?

>
>
> /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;/
>
> I run the dump from origin with postgres user.
>

Pretty sure the issue is less where it is coming from then where it is going.
RDS has constraints on what a user can do. As I said before I do not use it,
so I cannot be of much help other then to point you at the docs:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts

Using the rds_superuser Role


Seems to be you need to use the above role to do your restore.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/19/2016 11:12 AM, Fran ... wrote:
> 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;/
>
> I run the dump from origin with postgres user.
>

Alright, I bit the bullet and set up a test Postgres RDS instance. The only way I
could get anything to load was to follow the instructions here:

http://dba.stackexchange.com/questions/66372/moving-a-postgres-database-from-standalone-local-db-to-amazon-rds

and that was only after finding a database that did not have plpythonu
installed as that is uninstallable.

So I ended up with:

pg_restore -C -d test -h testdb.xxxxxxxxxxx.rds.amazonaws.com -p 5432 -U rds_user --no-owner --no-privileges b_app.out

I think I will stick with my policy of not using RDS.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 11/21/2016 03:34 PM, Fran ... wrote:
> Hi Adrian,
>
>
> I followed you link and I had again errors:

What was the command you used?

>
>
> /pg_restore: [archiver (db)] Error from TOC entry 4368; 2606 151317 FK
> CONSTRAINT type_id_3940becf ownersuser/
> /pg_restore: [archiver (db)] could not execute query: ERROR:  constraint
> "type_id_3940becf" of relation "store" does not exist/
> /    Command was: ALTER TABLE ONLY public.store DROP CONSTRAINT
> type_id_3940becf;/

Can't DROP what does not exist. The end result is the same anyway. You
can avoid this type of error with --if-exists.
> /
> /
> /pg_restore: [archiver (db)] Error from TOC entry 4273; 1259 1179680
> INDEX profile_id owneruser/
> /pg_restore: [archiver (db)] could not execute query: ERROR:  index
> "profile_id" does not exist/
> /    Command was: DROP INDEX public.profile_id;/

See above.

> /
> /
> /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';/

Not adding a COMMENT, not necessarily fatal. Best guess plpgsql is
actually installed, have you checked?

> /
> /
>
> /pg_restore: [archiver (db)] Error from TOC entry 4756; 0 0 USER MAPPING
> USER MAPPING dwhuser SERVER pg_rest postgres/
> /pg_restore: [archiver (db)] could not execute query: ERROR:  role
> "user" does not exist/
> /    Command was: CREATE USER MAPPING FOR user SERVER pg_rest OPTIONS (/
> /    password 'XXXXX',/
> /    "user" 'user'/
> /);/

This is probably because you could not import the global roles from your
original database.

>
> Regards.
>

--
Adrian Klaver
adrian.klaver@aklaver.com