Postgres error on schema creation

[tarekziade]

trying to deploy with postgres we're getting an error

"must be owner of type timestamp without time zone or type bigint"

Is there anything special to do prior to running the app ?

bin/uwsgi --ini-paste config/production.ini
[uWSGI] getting INI configuration from config/production.ini
*** Starting uWSGI 2.0.9 (64bit) on [Mon Mar 16 22:44:44 2015] ***
compiled with version: 4.8.2 20140120 (Red Hat 4.8.2-16) on 16 March 2015 20:21:09
os: Linux-3.10.0-123.20.1.el7.x86_64 #1 SMP Thu Jan 29 18:05:33 UTC 2015
nodename: ip-172-31-2-202
machine: x86_64
clock source: unix
pcre jit disabled
detected number of CPU cores: 2
current working directory: /data/readinglist
detected binary path: /data/readinglist/bin/uwsgi
setgid() to 1001
setuid() to 1011
your processes number limit is 28702
your memory page size is 4096 bytes
 *** WARNING: you have enabled harakiri without post buffering. Slow upload could be rejected on post-unbuffered webservers ***
detected max file descriptor number: 1024
lock engine: pthread robust mutexes
thunder lock: disabled (you can enable it with --thunder-lock)
uWSGI http bound on 127.0.0.1:8000 fd 4
uwsgi socket 0 bound to TCP address 127.0.0.1:50321 (port auto-assigned) fd 3
Python version: 2.7.5 (default, Jun 17 2014, 18:11:42)  [GCC 4.8.2 20140120 (Red Hat 4.8.2-16)]
Set PythonHome to /data/readinglist
*** Python threads support is disabled. You can enable it with --enable-threads ***
Python main interpreter initialized at 0x1dcb8c0
your server socket listen backlog is limited to 100 connections
your mercy for graceful operations on workers is 60 seconds
mapped 654912 bytes (639 KB) for 8 cores
*** Operational MODE: preforking ***
added /data/readinglist/ to pythonpath.
Loading paste environment: config:/data/readinglist/config/production.ini
--
-- Load pgcrypto for UUID generation
--
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

--
-- Metadata table
--
CREATE TABLE IF NOT EXISTS metadata (
    name VARCHAR(128) NOT NULL,
    value VARCHAR(512) NOT NULL
);
INSERT INTO metadata (name, value) VALUES ('created_at', NOW()::TEXT);
INSERT INTO metadata (name, value) VALUES ('last_heartbeat', '');

--
-- Convert timestamps to integer
--
CREATE OR REPLACE FUNCTION as_epoch(ts TIMESTAMP) RETURNS BIGINT AS $$
BEGIN
    RETURN (EXTRACT(EPOCH FROM ts) * 1000)::BIGINT;
END;
$$ LANGUAGE plpgsql;

DROP CAST IF EXISTS (TIMESTAMP AS BIGINT);
CREATE CAST (TIMESTAMP AS BIGINT)
   WITH FUNCTION as_epoch(TIMESTAMP) AS ASSIGNMENT;

--
-- Actual records
--
CREATE TABLE IF NOT EXISTS records (
    id UUID NOT NULL DEFAULT uuid_generate_v4(),
    user_id VARCHAR(256) NOT NULL,
    resource_name  VARCHAR(256) NOT NULL,
    last_modified TIMESTAMP NOT NULL,
    data JSON NOT NULL DEFAULT '{}',
    UNIQUE (id, user_id, resource_name, last_modified)
);
DROP INDEX IF EXISTS idx_records_user_id;
CREATE INDEX idx_records_user_id ON records(user_id);
DROP INDEX IF EXISTS idx_records_resource_name;
CREATE INDEX idx_records_resource_name ON records(resource_name);
DROP INDEX IF EXISTS idx_records_last_modified;
CREATE INDEX idx_records_last_modified ON records(last_modified);
DROP INDEX IF EXISTS idx_records_id;
CREATE INDEX idx_records_id ON records(id);

--
-- Deleted records
--
CREATE TABLE IF NOT EXISTS deleted (
    id UUID,
    user_id VARCHAR(256) NOT NULL,
    resource_name  VARCHAR(256) NOT NULL,
    last_modified TIMESTAMP NOT NULL,
    UNIQUE (id, user_id, resource_name, last_modified)
);
DROP INDEX IF EXISTS idx_deleted_id;
CREATE UNIQUE INDEX idx_deleted_id ON deleted(id);
DROP INDEX IF EXISTS idx_deleted_user_id;
CREATE INDEX idx_deleted_user_id ON deleted(user_id);
DROP INDEX IF EXISTS idx_deleted_resource_name;
CREATE INDEX idx_deleted_resource_name ON deleted(resource_name);
DROP INDEX IF EXISTS idx_deleted_last_modified;
CREATE INDEX idx_deleted_last_modified ON deleted(last_modified);

--
-- Helpers
--
CREATE OR REPLACE FUNCTION resource_timestamp(uid VARCHAR, resource VARCHAR)
RETURNS TIMESTAMP AS $$
DECLARE
    ts_records TIMESTAMP;
    ts_deleted TIMESTAMP;
BEGIN
    SELECT MAX(last_modified) INTO ts_records
      FROM records
     WHERE user_id = uid
       AND resource_name = resource;

    SELECT MAX(last_modified) INTO ts_deleted
      FROM deleted
     WHERE user_id = uid
       AND resource_name = resource;

    -- Latest of records/deleted or current if empty
    RETURN coalesce(greatest(ts_deleted, ts_records), localtimestamp);
END;
$$ LANGUAGE plpgsql;

--
-- Triggers to set last_modified on INSERT/UPDATE
--
DROP TRIGGER IF EXISTS tgr_records_last_modified ON records;
DROP TRIGGER IF EXISTS tgr_deleted_last_modified ON deleted;

CREATE OR REPLACE FUNCTION bump_timestamp()
RETURNS trigger AS $$
DECLARE
    previous TIMESTAMP;
    current TIMESTAMP;
BEGIN
    previous := resource_timestamp(NEW.user_id, NEW.resource_name);
    current := localtimestamp;

    IF previous >= current THEN
        current := previous + INTERVAL '1 milliseconds';
    END IF;

    NEW.last_modified := current;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tgr_records_last_modified
BEFORE INSERT OR UPDATE ON records
FOR EACH ROW EXECUTE PROCEDURE bump_timestamp();

CREATE TRIGGER tgr_deleted_last_modified
BEFORE INSERT OR UPDATE ON deleted
FOR EACH ROW EXECUTE PROCEDURE bump_timestamp();

must be owner of type timestamp without time zone or type bigint
Traceback (most recent call last):
  File "/data/readinglist/lib/python2.7/site-packages/cliquet-1.2.dev1-py2.7.egg/cliquet/storage/postgresql/__init__.py", line 43, in connect
    yield cursor
  File "/data/readinglist/lib/python2.7/site-packages/cliquet-1.2.dev1-py2.7.egg/cliquet/storage/postgresql/__init__.py", line 121, in _init_schema
    cursor.execute(schema)
  File "/data/readinglist/lib/python2.7/site-packages/psycopg2-2.6-py2.7-linux-x86_64.egg/psycopg2/extras.py", line 120, in execute
    return super(DictCursor, self).execute(query, vars)
ProgrammingError: must be owner of type timestamp without time zone or type bigint

Traceback (most recent call last):
  File "/data/readinglist/lib/python2.7/site-packages/paste/deploy/loadwsgi.py", line 247, in loadapp
    return loadobj(APP, uri, name=name, **kw)
  File "/data/readinglist/lib/python2.7/site-packages/paste/deploy/loadwsgi.py", line 272, in loadobj
    return context.create()
  File "/data/readinglist/lib/python2.7/site-packages/paste/deploy/loadwsgi.py", line 710, in create
    return self.object_type.invoke(self)
  File "/data/readinglist/lib/python2.7/site-packages/paste/deploy/loadwsgi.py", line 146, in invoke
    return fix_call(context.object, context.global_conf, **context.local_conf)
  File "/data/readinglist/lib/python2.7/site-packages/paste/deploy/util.py", line 55, in fix_call
    val = callable(*args, **kw)
  File "/data/readinglist/readinglist/__init__.py", line 26, in main
    initialize_cliquet(config, version=__version__)
  File "/data/readinglist/lib/python2.7/site-packages/cliquet-1.2.dev1-py2.7.egg/cliquet/__init__.py", line 192, in initialize_cliquet
    config.include("cliquet", route_prefix=api_version)
  File "/data/readinglist/lib/python2.7/site-packages/pyramid/config/__init__.py", line 755, in include
    c(configurator)
  File "/data/readinglist/lib/python2.7/site-packages/cliquet-1.2.dev1-py2.7.egg/cliquet/__init__.py", line 168, in includeme
    config.registry.storage = storage.load_from_config(config)
  File "/data/readinglist/lib/python2.7/site-packages/cliquet-1.2.dev1-py2.7.egg/cliquet/storage/postgresql/__init__.py", line 580, in load_from_config
    return PostgreSQL(max_fetch_size=int(max_fetch_size), **conn_kwargs)
  File "/data/readinglist/lib/python2.7/site-packages/cliquet-1.2.dev1-py2.7.egg/cliquet/storage/postgresql/__init__.py", line 83, in __init__
    self._init_schema()
  File "/data/readinglist/lib/python2.7/site-packages/cliquet-1.2.dev1-py2.7.egg/cliquet/storage/postgresql/__init__.py", line 121, in _init_schema
    cursor.execute(schema)
  File "/usr/lib64/python2.7/contextlib.py", line 35, in __exit__
    self.gen.throw(type, value, traceback)
  File "/data/readinglist/lib/python2.7/site-packages/cliquet-1.2.dev1-py2.7.egg/cliquet/storage/postgresql/__init__.py", line 51, in connect
    raise exceptions.BackendError(original=e)
cliquet.storage.exceptions.BackendError 

[leplatrem]

In development and in TravisCI we deploy the app using the postgres user, which is the owner of most internal PostgreSQL objects.

I suppose that the problem you had was using a different user (not mentionned).

I can reproduce the following problem locally:

postgres=# CREATE USER kinto PASSWORD 'kinto';
ALTER ROLE
postgres=# CREATE DATABASE kintodb OWNER kinto;
CREATE DATABASE
postgres=#

$ psql -h 127.0.0.1 -U kinto -d kintodb;
Password for user kinto: 
psql (9.4.1)
kintodb=> CREATE OR REPLACE FUNCTION as_epoch(ts TIMESTAMP) RETURNS BIGINT AS $$
kintodb$> BEGIN
kintodb$>     RETURN (EXTRACT(EPOCH FROM ts) * 1000)::BIGINT;
kintodb$> END;
kintodb$> $$ LANGUAGE plpgsql;
CREATE FUNCTION
kintodb=> 
kintodb=> DROP CAST IF EXISTS (TIMESTAMP AS BIGINT);
NOTICE:  cast from type pg_catalog.timestamp to type pg_catalog.int8 does not exist, skipping
DROP CAST
kintodb=> 
kintodb=> CREATE CAST (TIMESTAMP AS BIGINT)
kintodb->    WITH FUNCTION as_epoch(TIMESTAMP) AS ASSIGNMENT;
ERROR:  must be owner of type timestamp without time zone or type bigint

For convience we create a cast from timestamp to big int, and postgres prevents us from doing it with a user that doesn't own the source/destination types.

We have several solutions (by order of personal preference):

1. Temporary rights: Set our user as superuser temporarily (during schema creation on first app run and during future schema migrations)

postgres=# CREATE DATABASE kintodb OWNER kinto;
CREATE DATABASE
postgres=# ALTER USER kinto SUPERUSER;
ALTER ROLE

<install app>

postgres=# ALTER USER kinto NOSUPERUSER;
ALTER ROLE

2. Using distinct roles: Create the schema with user postgres (using cliquet/storage/postgresq/schema.sql) and give our user some privileges. (Obviously, the less privileges possible the better, but here as an example, and to ease future schema migrations etc. I give almost full privileges)

postgres=# CREATE DATABASE kintodb OWNER kinto;
CREATE DATABASE
postgres=# \?
postgres=# \c kintodb;
You are now connected to database "kintodb" as user "postgres".
kintodb=# \i /path/to/cliquet/storage/postgresql/schema.sql
...
...[ ... ] does not exist, skipping.
...
kintodb=# \d
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | deleted  | table | postgres
 public | metadata | table | postgres
 public | records  | table | postgres
kintodb=#
kintodb=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO kinto;
GRANT

3. Postgresql internals: Set our user as owner of those types temporarily during schema creation (even if it would work I wouldn't mess up with postgres internal stuff)

[Natim]

Thanks @leplatrem for all this informations. I had a similar problem when settings up the readinglist-preprod server. I like the temporary superuser (first) solution.

Can we document this in the documentation?