ERROR: permission denied for database control
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
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 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