Juju Charms Collection
postgresql package

Database creation fails with syntax error

Bug #1172086 reported by Sidnei da Silva
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
postgresql (Juju Charms Collection)
Fix Released
Undecided
Sidnei da Silva

Bug Description

Current code in the charm doesn't do any quoting around the database name:

postgres=# CREATE DATABASE u1-app-be;
ERROR: syntax error at or near "-"
LINE 1: CREATE DATABASE u1-app-be;

Tried changing to pass params to psycopg2, but that also fails in a slightly different way:

postgres=# CREATE DATABASE 'u1-app-be';
ERROR: syntax error at or near "'u1-app-be'"
LINE 1: CREATE DATABASE 'u1-app-be';
                        ^

Using double quotes seems to work around it, but not sure how to do this in the right way with psycopg2 and params:

postgres=# CREATE DATABASE "u1-app-be";
CREATE DATABASE

Related branches

lp:~sidnei/charms/precise/postgresql/trunk
Marco Ceppi (community): Approve
Diff: 760 lines (+216/-137)
5 files modified
config.yaml (+4/-0)
hooks/hooks.py (+210/-136)
revision (+0/-1)
templates/pg_hba.conf.tmpl (+1/-0)
templates/postgres.cron.tmpl (+1/-0)
Revision history for this message
Sidnei da Silva (sidnei) wrote :

Linked branch changes to use params but doesn't fix the issue mentioned above.

Revision history for this message
Stuart Bishop (stub) wrote :

SQL identifiers, such as schema names, table names, role names etc., need to be quoted with double quotes (per the SQL standard). Unfortunately, psycopg2 doesn't provide a helper to do this. Steal quote_identifier() code from http://bazaar.launchpad.net/~canonical-launchpad-branches/lazr-postgresql/trunk/view/head:/src/lazr/postgresql/quoting.py, or just replace " with "" and wrap it in double quotes if you don't care about unicode.

Revision history for this message
Stuart Bishop (stub) wrote :

In addition, if you want to pass an SQL identifier through as a parameter to excute(), you need to use the AsIs() helper to avoid psycopg2 quoting your already quoted value:

 from psycopg2.extensions import AsIs

cur.execute("SELECT TRUE FROM %s", (AsIs(quote_identifier('foo'),))

Revision history for this message
Sidnei da Silva (sidnei) wrote :

Implemented as suggested. Also had to change generate_postgresql_hba to pass user/schema_user/database around and -changed hook to default to generating a user if one is not provided from the relation, just like the -joined hook does.

Stuart Bishop (stub)
Changed in postgresql (Juju Charms Collection):
assignee: nobody → Sidnei da Silva (sidnei)
status: New → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.