Thread: ERROR: permission denied for database control

Suya Huang wrote:
> I’ve encountered a weird problem in PostgreSQL :
>
> postgres=> create user test password ‘test’;
> 
> postgres=> grant select on pg_catalog.pg_database_size to test;

This statement produces an error:
ERROR:  relation "pg_catalog.pg_database_size" does not exist

> postgres=> grant execute on function pg_catalog.pg_database_size(name) to test;
> 
> --login as user test
> postgres=> select current_user;
> current_user
> --------------
> test
> (1 row)
> 
> 
> --query individual database size is fine
> postgres=> select pg_database_size('postgres');
> pg_database_size
> ------------------
>          25611884
> (1 row)
> 
> --fails if try to get all db size in one sql
> postgres=> SELECT current_date,pg_database_size(pg_database.datname) from pg_database;
> ERROR:  permission denied for database control

Works for me.

It seems like you have a database called "control" for which user "test"
has no connect privilege.

Yours,
Laurenz Albe

Albe Laurenz <laurenz.albe@wien.gv.at> writes:
> Suya Huang wrote:
>> --fails if try to get all db size in one sql
>> postgres=> SELECT current_date,pg_database_size(pg_database.datname) from pg_database;
>> ERROR:  permission denied for database control

> It seems like you have a database called "control" for which user "test"
> has no connect privilege.

Yeah, this failure is unsurprising.  But it looks like we forgot to
document the restriction :-(.  I see a mention of it in the 8.3 release
notes, but there's nothing in the documentation of the functions
themselves.  Will fix.

            regards, tom lane

Thank you Tom and Albe. After grant connect database privilege to user test, the query runs without problem.

Thanks,
Suya

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, May 08, 2014 12:13 AM
To: Albe Laurenz
Cc: Huang, Suya; 'pgsql-general General'
Subject: Re: [GENERAL] ERROR: permission denied for database control

Albe Laurenz <laurenz.albe@wien.gv.at> writes:
> Suya Huang wrote:
>> --fails if try to get all db size in one sql postgres=> SELECT
>> current_date,pg_database_size(pg_database.datname) from pg_database;
>> ERROR:  permission denied for database control

> It seems like you have a database called "control" for which user "test"
> has no connect privilege.

Yeah, this failure is unsurprising.  But it looks like we forgot to document the restriction :-(.  I see a mention of
itin the 8.3 release notes, but there's nothing in the documentation of the functions themselves.  Will fix. 

            regards, tom lane