Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to create an admin user for restore database.

how to create an admin user for restore database.

From
Sam Wun
Date:
Hi,

With PG 8.4, I can't find a documentation to show me how to create a
super user or admin user for the PGSQL.

I have crated a user called "liferayadmin" and a database "liferay".

then executed the following command:

GRANT ALL PRIVILEGES ON DATABASE liferay to liferayadmin;

When I launched pgadmin in windows and login as user liferayadmin,
from the tools menu, the restore command is disabled.
I think the user "liferayadmin" does not have the privilege to restore database.

How can I enable a user with restore permission?

Your help is very much appreciated

Thanks
Sam

Re: how to create an admin user for restore database.

From
Adrian Klaver
Date:
On Tuesday 22 June 2010 6:50:28 pm Sam Wun wrote:
> Hi,
>
> With PG 8.4, I can't find a documentation to show me how to create a
> super user or admin user for the PGSQL.
>
> I have crated a user called "liferayadmin" and a database "liferay".
>
> then executed the following command:
>
> GRANT ALL PRIVILEGES ON DATABASE liferay to liferayadmin;
>
> When I launched pgadmin in windows and login as user liferayadmin,
> from the tools menu, the restore command is disabled.
> I think the user "liferayadmin" does not have the privilege to restore
> database.
>
> How can I enable a user with restore permission?
>
> Your help is very much appreciated
>
> Thanks
> Sam

You do say how you installed Postgres. Generally by the default there is a super
user created with name of postgres. Also generally by default there is a local
connection set up with an authentication of trust. You should be able to log in
as postgres and have the necessary permissions.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: how to create an admin user for restore database.

From
Sam Wun
Date:
With user liferayadmin on db liferay, I got the following errors:

03:14:50,558 WARN  [DBUtil:474] ERROR: permission denied for relation
quartz_locks: insert into QUARTZ_LOCKS values('TRIGGER_ACCESS');
03:14:50,567 WARN  [DBUtil:474] ERROR: permission denied for relation
quartz_locks: insert into QUARTZ_LOCKS values('JOB_ACCESS');
03:14:50,582 WARN  [DBUtil:474] ERROR: permission denied for relation
quartz_locks: insert into QUARTZ_LOCKS values('CALENDAR_ACCESS');
03:14:50,593 WARN  [DBUtil:474] ERROR: permission denied for relation
quartz_locks: insert into QUARTZ_LOCKS values('STATE_ACCESS');
03:14:50,608 WARN  [DBUtil:474] ERROR: permission denied for relation
quartz_locks: insert into QUARTZ_LOCKS values('MISFIRE_ACCESS');

...

How can I assigne admin permission to liferayadmin user?

Thanks
sam


On Wed, Jun 23, 2010 at 12:24 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On Tuesday 22 June 2010 6:50:28 pm Sam Wun wrote:
>> Hi,
>>
>> With PG 8.4, I can't find a documentation to show me how to create a
>> super user or admin user for the PGSQL.
>>
>> I have crated a user called "liferayadmin" and a database "liferay".
>>
>> then executed the following command:
>>
>> GRANT ALL PRIVILEGES ON DATABASE liferay to liferayadmin;
>>
>> When I launched pgadmin in windows and login as user liferayadmin,
>> from the tools menu, the restore command is disabled.
>> I think the user "liferayadmin" does not have the privilege to restore
>> database.
>>
>> How can I enable a user with restore permission?
>>
>> Your help is very much appreciated
>>
>> Thanks
>> Sam
>
> You do say how you installed Postgres. Generally by the default there is a super
> user created with name of postgres. Also generally by default there is a local
> connection set up with an authentication of trust. You should be able to log in
> as postgres and have the necessary permissions.
>
> --
> Adrian Klaver
> adrian.klaver@gmail.com
>

Re: how to create an admin user for restore database.

From
Guillaume Lelarge
Date:
Le 23/06/2010 05:17, Sam Wun a écrit :
> With user liferayadmin on db liferay, I got the following errors:
>
> 03:14:50,558 WARN  [DBUtil:474] ERROR: permission denied for relation
> quartz_locks: insert into QUARTZ_LOCKS values('TRIGGER_ACCESS');
> 03:14:50,567 WARN  [DBUtil:474] ERROR: permission denied for relation
> quartz_locks: insert into QUARTZ_LOCKS values('JOB_ACCESS');
> 03:14:50,582 WARN  [DBUtil:474] ERROR: permission denied for relation
> quartz_locks: insert into QUARTZ_LOCKS values('CALENDAR_ACCESS');
> 03:14:50,593 WARN  [DBUtil:474] ERROR: permission denied for relation
> quartz_locks: insert into QUARTZ_LOCKS values('STATE_ACCESS');
> 03:14:50,608 WARN  [DBUtil:474] ERROR: permission denied for relation
> quartz_locks: insert into QUARTZ_LOCKS values('MISFIRE_ACCESS');
>
> ...
>
> How can I assigne admin permission to liferayadmin user?
>

ALTER ROLE liferayadmin SUPERUSER;

as the fine manual says (see
http://www.postgresql.org/docs/8.4/interactive/sql-alterrole.html).


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

Re: how to create an admin user for restore database.

From
Raymond O'Donnell
Date:
On 23/06/2010 02:50, Sam Wun wrote:

> When I launched pgadmin in windows and login as user liferayadmin,
> from the tools menu, the restore command is disabled.
> I think the user "liferayadmin" does not have the privilege to restore database.

Something else worth checking is whether pgAdmin has been told where the
pg_dump and pg_restore binaries are residing on your local system.... If
it hasn't, or if the path it has is incorrect, then the "Backup" and
"Restore" options will be greyed out.

Look in Options -> General for "PG bin path".

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: how to create an admin user for restore database.

From
Raymond O'Donnell
Date:
On 23/06/2010 12:21, Raymond O'Donnell wrote:
> On 23/06/2010 02:50, Sam Wun wrote:
>
>> When I launched pgadmin in windows and login as user liferayadmin,
>> from the tools menu, the restore command is disabled.
>> I think the user "liferayadmin" does not have the privilege to restore database.
>
> Something else worth checking is whether pgAdmin has been told where the
> pg_dump and pg_restore binaries are residing on your local system.... If
> it hasn't, or if the path it has is incorrect, then the "Backup" and
> "Restore" options will be greyed out.
>
> Look in Options -> General for "PG bin path".

Actually, I just tried it myself, and apparently if no value is entered
in "PG bin path" then the backup and restore options disappear
altogether from the right-click menu.... I'm on pgAdmin 1.10.1.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Re: how to create an admin user for restore database.

From
Adrian Klaver
Date:
On Wednesday 23 June 2010 12:45:14 am Guillaume Lelarge wrote:
> Le 23/06/2010 05:17, Sam Wun a écrit :
> > With user liferayadmin on db liferay, I got the following errors:
> >
> > 03:14:50,558 WARN  [DBUtil:474] ERROR: permission denied for relation
> > quartz_locks: insert into QUARTZ_LOCKS values('TRIGGER_ACCESS');
> > 03:14:50,567 WARN  [DBUtil:474] ERROR: permission denied for relation
> > quartz_locks: insert into QUARTZ_LOCKS values('JOB_ACCESS');
> > 03:14:50,582 WARN  [DBUtil:474] ERROR: permission denied for relation
> > quartz_locks: insert into QUARTZ_LOCKS values('CALENDAR_ACCESS');
> > 03:14:50,593 WARN  [DBUtil:474] ERROR: permission denied for relation
> > quartz_locks: insert into QUARTZ_LOCKS values('STATE_ACCESS');
> > 03:14:50,608 WARN  [DBUtil:474] ERROR: permission denied for relation
> > quartz_locks: insert into QUARTZ_LOCKS values('MISFIRE_ACCESS');
> >
> > ...
> >
> > How can I assigne admin permission to liferayadmin user?
>
> ALTER ROLE liferayadmin SUPERUSER;
>
> as the fine manual says (see
> http://www.postgresql.org/docs/8.4/interactive/sql-alterrole.html).
>
>
> --
> Guillaume
>  http://www.postgresql.fr
>  http://dalibo.com

True, but you have to be the superuser to do this. See my previous post on how
to become the superuser and then do the above.

--
Adrian Klaver
adrian.klaver@gmail.com