New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Cannot create default admin user on postgres database #9260

Closed
iinuwa opened this Issue Sep 15, 2017 · 18 comments

Comments

Projects
None yet
@iinuwa
Copy link

iinuwa commented Sep 15, 2017

Hello,

I am having a problem starting Grafana for the first time because of an issue creating the admin user, which I believe is related to a PostgreSQL datatype error.

I am running Oracle Linux 6.8, Grafana 4.5.0 (installed from grafana-4.5.0-1.x86_64.rpm), and Postgres 9.6.3.

Right after installing, I configured the database type as postgres in /etc/grafana/grafana.ini and set up the database credentials. After running service grafana-server start, the Grafana service runs just fine, but I could not log in with the default credentials (admin/admin).

I can see in the database all the tables that were created, and after looking at the logs, I can confirm that most of the database is initialized properly for the first setup. However, I saw a line that looks like this (I don't currently have the full line verbatim, but I will try to reproduce the error and update this issue):

"Failed to create default admin user (EXTRA *pq.Error=pq: date/time field value out of range: "0000-12-31 18:00:00")"

I tried adding the line
datesyle = 'YMD'
in postgresql.conf, but that did not have any effect; the error still appeared when I restarted the PostgreSQL instance and the Grafana server.

After switching to a MySQL database, the default admin user was created correctly, and I was able to log in.

@freeseacher

This comment has been minimized.

Copy link

freeseacher commented Sep 16, 2017

got the same behaviour. Debian 8+, grafana 4.5.1, postgres 9.4.14
my config is

...
[database]

type = postgres
host = debianone:5432
name = grafana
user = grafana
password = 'grafana'

...
[session]
...
provider_config = user=grafana password='grafana' host=debianone port=5432 dbname=grafana sslmode=disable

...
#################################### Auth Proxy ##########################
[auth.proxy]
enabled = true
header_name = Remote-User
header_property = username
auto_sign_up = true
ldap_sync_ttl = 60
whitelist =

i have the same error

t=2017-09-16T10:36:34-0400 lvl=eror msg="Failed to create user specified in auth proxy header" logger=context error="pq: date/time field value out of range: \"0000-12-31 19:00:00\""
@torkelo

This comment has been minimized.

Copy link
Member

torkelo commented Sep 18, 2017

strange, tested with postgres 9.4.14 and 9.6.3 (using official docker containers), and grafana starts and the SQL migration runs without issue and the default admin user is created without error.

@jwodder

This comment has been minimized.

Copy link

jwodder commented Sep 18, 2017

I am also experiencing the same problem with Grafana 4.5.1 and PostgreSQL 9.3+154ubuntu1 (the current version in the official Ubuntu Trusty repositories).

@torkelo

This comment has been minimized.

Copy link
Member

torkelo commented Sep 19, 2017

any you using any custom postgres settings?

@jwodder

This comment has been minimized.

Copy link

jwodder commented Sep 19, 2017

The only configuration changes made to the PostgreSQL instance were to enable external connections. The database encoding is set to Unicode, and the locale is set to C. Since we're dealing with timestamps without time zones, it may be relevant that the system timezone is set to America/New_York. That's pretty much it.

@jeanfrancoisgratton

This comment has been minimized.

Copy link

jeanfrancoisgratton commented Sep 21, 2017

Same issue here:
postgresql-9.2.23-1.el7_4.x86_64
grafana-4.5.1-1.x86_64

Running on CentOS 7.4 (1708)

In a previous install (about 2weeks) ago on the same PGSQL version it worked. Unaware if grafana-server changed version in the meantime. Now, I provision a new VM and I get this, with the same config files as before. Since my puppet/ansible setup ensures that I get the latest packages, but keeps the same config files, I'm assuming there was a version bump along the line that introduced that regression.

@jeanfrancoisgratton

This comment has been minimized.

Copy link

jeanfrancoisgratton commented Sep 21, 2017

More details: provisioned a new VM (same OS, same grafana-server version), with a new version of PGSQL:
[root@vm5 ~]# rpm -qa |grep postgresql|grep server
postgresql96-server-9.6.5-1PGDG.rhel7.x86_64

Same error, as show from systemctl status grafana-server:
``
Sep 21 12:14:39 vm5 grafana-server[1056]: t=2017-09-21T12:14:39-0400 lvl=info msg="Skipping migration condition not fulfilled" logger=migrator id="copy data account_user to org_user"
Sep 21 12:14:39 vm5 grafana-server[1056]: t=2017-09-21T12:14:39-0400 lvl=eror msg="Failed to create default admin user%!(EXTRA *pq.Error=pq: date/time field value out of range: "0000... 19:00:00")

@CodeSturgeon

This comment has been minimized.

Copy link

CodeSturgeon commented Sep 21, 2017

I have also just run in to this issue. After a little investigating, It seems that postgres does not think that the year 0000 is valid.

Here is some chatter on the subject from the postgres mailing list:
https://www.postgresql.org/message-id/20051006181512.GG28948@phlogiston.dyndns.org

This seems to be born out with some quick tests:

grafana=# select '0001-01-01'::date;
    date
------------
 0001-01-01
(1 row)

grafana=# select '0000-01-01'::date;
ERROR:  date/time field value out of range: "0000-01-01"
LINE 1: select '0000-01-01'::date;
@CodeSturgeon

This comment has been minimized.

Copy link

CodeSturgeon commented Sep 21, 2017

This issue seems to have come in with 4.5.0. I tested multiple versions and 4.4.3 was the last version that did not trigger this issue for me.

All tests done on CentOS7 and postgresql 9.2.23. All grafana RPMs from the yum repo detailed here:
http://docs.grafana.org/installation/rpm/

@kroov

This comment has been minimized.

Copy link

kroov commented Sep 25, 2017

Have same issue on CentOS 7.4.1708
Same version of grafana and pg.
postgresql-9.2.23-1.el7_4.x86_64
grafana-4.5.1-1.x86_64

in journal
Sep 25 11:03:18 monitortest grafana-server[19301]: t=2017-09-25T11:03:18-0400 lvl=eror msg="Failed to create default admin user%!(EXTRA *pq.Error=pq: date/time field value out of range: "0000-12-31 19:00:00")"

@GRB

This comment has been minimized.

Copy link

GRB commented Sep 26, 2017

I have the same issue:

Centos 6.8
Postgres 9.6

after restarting the server, or start the server, i have this.

t=2017-09-26T09:23:03+0100 lvl=info msg="Shutdown started" logger=server code=0 reason="system signal: terminated"
t=2017-09-26T09:23:03+0100 lvl=info msg="stopped http server" logger=http.server
t=2017-09-26T09:23:05+0100 lvl=info msg="Starting Grafana" logger=server version=4.5.2 commit=ec2b0fe compiled=2017-09-22T09:34:03+0100
t=2017-09-26T09:23:05+0100 lvl=info msg="Config loaded from" logger=settings file=/usr/share/grafana/conf/defaults.ini
t=2017-09-26T09:23:05+0100 lvl=info msg="Config loaded from" logger=settings file=/etc/grafana/grafana.ini
t=2017-09-26T09:23:05+0100 lvl=info msg="Config overridden from command line" logger=settings arg="default.paths.data=/var/lib/grafana"
t=2017-09-26T09:23:05+0100 lvl=info msg="Config overridden from command line" logger=settings arg="default.paths.logs=/var/log/grafana"
t=2017-09-26T09:23:05+0100 lvl=info msg="Config overridden from command line" logger=settings arg="default.paths.plugins=/var/lib/grafana/plugins"
t=2017-09-26T09:23:05+0100 lvl=info msg="Path Home" logger=settings path=/usr/share/grafana
t=2017-09-26T09:23:05+0100 lvl=info msg="Path Data" logger=settings path=/var/lib/grafana
t=2017-09-26T09:23:05+0100 lvl=info msg="Path Logs" logger=settings path=/var/log/grafana
t=2017-09-26T09:23:05+0100 lvl=info msg="Path Plugins" logger=settings path=/var/lib/grafana/plugins
t=2017-09-26T09:23:05+0100 lvl=info msg="Writing PID file" logger=server path=/var/run/grafana-server.pid pid=21071
t=2017-09-26T09:23:05+0100 lvl=info msg="Initializing DB" logger=sqlstore dbtype=postgres
t=2017-09-26T09:23:05+0100 lvl=info msg="Starting DB migration" logger=migrator
t=2017-09-26T09:23:05+0100 lvl=info msg="Executing migration" logger=migrator id="copy data account to org"
t=2017-09-26T09:23:05+0100 lvl=info msg="Skipping migration condition not fulfilled" logger=migrator id="copy data account to org"
t=2017-09-26T09:23:05+0100 lvl=info msg="Executing migration" logger=migrator id="copy data account_user to org_user"
t=2017-09-26T09:23:05+0100 lvl=info msg="Skipping migration condition not fulfilled" logger=migrator id="copy data account_user to org_user"
t=2017-09-26T09:23:05+0100 lvl=eror msg="Failed to create default admin user%!(EXTRA *pq.Error=pq: date/time field value out of range: "0000-12-31 23:23:15")"
t=2017-09-26T09:23:05+0100 lvl=info msg="Starting plugin search" logger=plugins
t=2017-09-26T09:23:05+0100 lvl=info msg="Initializing Alerting" logger=alerting.engine
t=2017-09-26T09:23:05+0100 lvl=info msg="Initializing CleanUpService" logger=cleanup
t=2017-09-26T09:23:05+0100 lvl=info msg="Initializing Stream Manager"
t=2017-09-26T09:23:05+0100 lvl=info msg="Initializing HTTP Server" logger=http.server address=0.0.0.0:8090 protocol=http subUrl=/grafana socket=

@TyBrown

This comment has been minimized.

Copy link

TyBrown commented Oct 10, 2017

This is also affecting Grafana installations that have LDAP enabled... the first time a user tries to sign into Grafana and Grafana auto-creates their profile it causes an error and new users can not login... this issue doesn't affect users that already exist.

@zemek

This comment has been minimized.

Copy link

zemek commented Oct 10, 2017

Can confirm this is broken in 4.5.2 and works in 4.4.3

@zemek

This comment has been minimized.

Copy link

zemek commented Oct 10, 2017

It looks like this is caused by the system time zone not being UTC. Switching from UTC-4 to UTC fixed it

or it might be caused by the system time zone not matching the db time zone

@RudW0lf

This comment has been minimized.

Copy link

RudW0lf commented Oct 11, 2017

I have the same issue:

$ rpm -qa | grep postgre
postgresql96-server-9.6.5-1PGDG.rhel7.x86_64
postgresql96-9.6.5-1PGDG.rhel7.x86_64
postgresql96-libs-9.6.5-1PGDG.rhel7.x86_64

$ rpm -qa | grep grafa
grafana-4.5.2-1.x86_64

log output:
t=2017-10-11T03:34:56-0400 lvl=info msg="Starting DB migration" logger=migrator
t=2017-10-11T03:34:56-0400 lvl=info msg="Executing migration" logger=migrator id="copy data account to org"
t=2017-10-11T03:34:56-0400 lvl=info msg="Skipping migration condition not fulfilled" logger=migrator id="copy data account to org"
t=2017-10-11T03:34:56-0400 lvl=info msg="Executing migration" logger=migrator id="copy data account_user to org_user"
t=2017-10-11T03:34:56-0400 lvl=info msg="Skipping migration condition not fulfilled" logger=migrator id="copy data account_user to org_user"
t=2017-10-11T03:34:56-0400 lvl=eror msg="Failed to create default admin user%!(EXTRA *pq.Error=pq: date/time field value out of range: "0000-12-31 19:00:00")"
t=2017-10-11T03:34:56-0400 lvl=info msg="Starting plugin search" logger=plugins
t=2017-10-11T03:34:56-0400 lvl=info msg="Initializing CleanUpService" logger=cleanup

@torkelo torkelo closed this in 2bc57d8 Oct 11, 2017

@torkelo torkelo added this to the 4.6/5.0 milestone Oct 11, 2017

@torkelo

This comment has been minimized.

Copy link
Member

torkelo commented Oct 11, 2017

I hope 2bc57d8 fixes it by setting lastSeenDate when creating users (to a date 10 years in the past instead of an empty date)

@TyBrown

This comment has been minimized.

Copy link

TyBrown commented Oct 11, 2017

Any chance we can get this released as a patch in the 4.5.x series to fix installations where this is a problem? I'm not sure how far out the 4.6/5.0 release is going to be, so hoping a patch will be possible if it's going to be a while.

@torkelo

This comment has been minimized.

Copy link
Member

torkelo commented Oct 11, 2017

we are very close to 4.6 beta (friday or monday)

@bergquist bergquist modified the milestones: 4.6/5.0, 4.6 Oct 12, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment