[GENERAL] ERROR: permission denied for database control

[Huang, Suya]

Hi,

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;

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

Thanks,
Suya

Re: [GENERAL] ERROR: permission denied for database control

[Albe Laurenz]

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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ERROR: permission denied for database control

[Tom Lane]

Albe Laurenz laurenz.a...@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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ERROR: permission denied for database control

[Huang, Suya]

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:t...@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.a...@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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general