You do likely need log_bin_trust_function_creators
= 1 on RDS but that isn't the issue, here.
You can specify a DEFINER
value other than your own account only if you have the SUPER
privilege.
— http://dev.mysql.com/doc/refman/5.6/en/stored-programs-security.html
When a stored program (proc, function, event, or trigger) is running, everything it does has the permissions of the user who defined it, or of the user explicitly stated with a DEFINER
declaration. This allows, among other things, for stored programs to permit other users to do things to data they don't directly have permission to manipulate, as long as they have permission to use the stored program itself.
It would be a serious vulnerability, then, if a non-SUPER
user could create a procedure with an arbitrary definer, because the user could escalate his or her privileges at will.
This is also true of views, of course, when the definer security context is used, as in the example you posted.
One of the biggest complaints I have with RDS is that you can't have SUPER
... and now it can be one of yours, too :) because that fact is the cause of the problem you are having.
Of course, if I were running a managed MySQL service, I wouldn't give anybody SUPER
, either, so their security model makes sense, even if it is sometimes unwieldy.
If all of your objects have the same definer, a workaround would be to restore the dump using that account instead of the one you're using now, but that seems unlikely.
Deleting just the line with the DEFINER
declaration should make the dumpfile work in cases where it appears on a line by itself, or you could use sed or perl to modify the file... an idea that I already know you're not fond of, but it really is a nice thing about MySQL that such hackery is quite legitimate, and not really all that far afield from the kinds of things I have to do as a DBA even in a non-RDS envirnoment.
perl -pe 's/\sDEFINER=`[^`]+`@`[^`]+`//' < oldfile.sql > newfile.sql
...possibly not the answer you hoped for, but you could run that against your dumpfile and should end up with a slightly more usable file.
As of now, RDS does allow changing configurations. So you can
- See the list of configurations that your RDS is using.
- You can change these parameters. I believe you can not change all
the parameters though. You can change those that are listed in the
RDS reference page.
As usual there are some static (or fixed) configuration parameters for which you have to restart you DB server and then there are dynamic options for which you dont need a restart.
Here is what the RDS reference manual says:
PostgreSQL parameters that you would set for a local PostgreSQL
instance in the postgresql.conf file are maintained in the DB
parameter group for your DB instance. If you create a DB instance
using the default parameter group, the parameter settings are in the
parameter group called default.postgres9.3.
When you create a DB instance, the parameters in the associated DB
parameter group are loaded. You can modify parameter values by
changing values in the parameter group. You can also change parameter
values, if you have the security privileges to do so, by using the
ALTER DATABASE, ALTER ROLE, and the SET commands. Note that you cannot
use the command line postgres command nor the env PGOPTIONS command
because you will have no access to the host.
Here is the official amazon RDS documentation for postgres:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.Parameters
Best Answer
I think your best approach is using the Database Migration Service AWS launched past year.
https://aws.amazon.com/dms/
This service is specifically designed for your use case. It is "simply" a managed appliance running on top of an EC2 instance in your VPC. Once deployed and assuming no connectivity problems, you submit the details for source and target databases, configure some transformations if needed and then the service manages the initial load and further replication, in an asynchronous way.
I have used it already both for OnPremises-to-AWS and RDS-to-RDS for cloud migrations and engine upgrades, and achieved nearly zero downtime in all cases. It works just great.