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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com>
> *Enviado:* sábado, 19 de noviembre de 2016 18:24
> *Para:* Fran ...; pgsql-general(at)postgresql(dot)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(dot)klaver(at)aklaver(dot)com

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com