Обсуждение: postgres function does not handle PUBLIC - expected?
Hello Postgres Gurus,
Is it expected behavior for the has_table_privilege postgres function to not handle the user PUBLIC? Or should I submit a bug?
The has_table_privilege function does not handle PUBLIC – the following queries returns the error:
SELECT has_table_privilege ('PUBLIC','bob.gdb.test1','SELECT') AS SELECT,
has_table_privilege ('PUBLIC','bob.gdb.test1','INSERT') AS INSERT,
has_table_privilege ('PUBLIC','bob.gdb.test1','DELETE') AS DELETE,
has_table_privilege ('PUBLIC','bob.gdb.test1','UPDATE') AS UPDATE;
ERROR: role "PUBLIC" does not exist
SQL state: 42704
SELECT has_table_privilege ('public','bob.gdb.test1','SELECT') AS SELECT,
has_table_privilege ('public','bob.gdb.test1','INSERT') AS INSERT,
has_table_privilege ('public','bob.gdb.test1','DELETE') AS DELETE,
has_table_privilege ('public','bob.gdb.test1','UPDATE') AS UPDATE;
ERROR: role "public" does not exist
SQL state: 42704
Thank you,
Sincerley,
Kasia
Hello Postgres Gurus,
Is it expected behavior for the has_table_privilege postgres function to not handle the user PUBLIC? Or should I submit a bug?
The has_table_privilege function does not handle PUBLIC – the following queries returns the error:
SELECT has_table_privilege ('PUBLIC','bob.gdb.test1','SELECT') AS SELECT,
has_table_privilege ('PUBLIC','bob.gdb.test1','INSERT') AS INSERT,
has_table_privilege ('PUBLIC','bob.gdb.test1','DELETE') AS DELETE,
has_table_privilege ('PUBLIC','bob.gdb.test1','UPDATE') AS UPDATE;
ERROR: role "PUBLIC" does not exist
SQL state: 42704
SELECT has_table_privilege ('public','bob.gdb.test1','SELECT') AS SELECT,
has_table_privilege ('public','bob.gdb.test1','INSERT') AS INSERT,
has_table_privilege ('public','bob.gdb.test1','DELETE') AS DELETE,
has_table_privilege ('public','bob.gdb.test1','UPDATE') AS UPDATE;
ERROR: role "public" does not exist
SQL state: 42704
Thank you,
Sincerley,
Kasia
Szymon,
The PUBLIC role is a default, non login role, that is created with every postgres cluster/instance.
To my knowledge PUBLIC is a standard user in any rdbms. So, existence of public is not the issue here.
Sincerely,
Kasia
From: Szymon Guz [mailto:mabewlun@gmail.com]
Sent: Tuesday, August 10, 2010 12:11 PM
To: Kasia Tuszynska
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] postgres function does not handle PUBLIC - expected?
2010/8/10 Kasia Tuszynska <ktuszynska@esri.com>
Hello Postgres Gurus,
Is it expected behavior for the has_table_privilege postgres function to not handle the user PUBLIC? Or should I submit a bug?
The has_table_privilege function does not handle PUBLIC – the following queries returns the error:
SELECT has_table_privilege ('PUBLIC','bob.gdb.test1','SELECT') AS SELECT,
has_table_privilege ('PUBLIC','bob.gdb.test1','INSERT') AS INSERT,
has_table_privilege ('PUBLIC','bob.gdb.test1','DELETE') AS DELETE,
has_table_privilege ('PUBLIC','bob.gdb.test1','UPDATE') AS UPDATE;
ERROR: role "PUBLIC" does not exist
SQL state: 42704
SELECT has_table_privilege ('public','bob.gdb.test1','SELECT') AS SELECT,
has_table_privilege ('public','bob.gdb.test1','INSERT') AS INSERT,
has_table_privilege ('public','bob.gdb.test1','DELETE') AS DELETE,
has_table_privilege ('public','bob.gdb.test1','UPDATE') AS UPDATE;
ERROR: role "public" does not exist
SQL state: 42704
Thank you,
Sincerley,
Kasia
Do you have any role that is named PUBLIC? I think that you don't have one. Check e.g. in the table pg_user.
regards
Szymon
Excerpts from Kasia Tuszynska's message of mar ago 10 15:08:20 -0400 2010: > Hello Postgres Gurus, > > Is it expected behavior for the has_table_privilege postgres function to not handle the user PUBLIC? Or should I submita bug? > > The has_table_privilege function does not handle PUBLIC - the following queries returns the error: > SELECT has_table_privilege ('PUBLIC','bob.gdb.test1','SELECT') AS SELECT, > has_table_privilege ('PUBLIC','bob.gdb.test1','INSERT') AS INSERT, > has_table_privilege ('PUBLIC','bob.gdb.test1','DELETE') AS DELETE, > has_table_privilege ('PUBLIC','bob.gdb.test1','UPDATE') AS UPDATE; > > ERROR: role "PUBLIC" does not exist Yeah, this is a bug, see http://archives.postgresql.org/message-id/4399EF7A-9419-4F84-B51A-A55C5EFAC12A%40nasby.net -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Szymon,
The PUBLIC role is a default, non login role, that is created with every postgres cluster/instance.
To my knowledge PUBLIC is a standard user in any rdbms. So, existence of public is not the issue here.
Sincerely,
Kasia
Szymon Guz <mabewlun@gmail.com> wrote: > No, there is no PUBLIC default role in ANY rdbms. In PostgreSQL > there is PUBLIC schema, not role. In my PostgreSQL database there > wasn't any such role... but I'll check that now... ok, I've > checked, I've got 15 roles, none is names PUBLIC, what's more, I > don't have any roles that cannot login. > > run simple query: SELECT rolname FROM pg_roles; and check the > existence of the PUBLIC role. Yeah, it's automatically there in a shadowy sort of way. Try this, for example, in your cluster with no PUBLIC role: test=# revoke create on database test from public; REVOKE test=# grant select on pg_class to public; GRANT -Kevin
Szymon Guz <mabewlun@gmail.com> wrote:Yeah, it's automatically there in a shadowy sort of way. Try this,
> No, there is no PUBLIC default role in ANY rdbms. In PostgreSQL
> there is PUBLIC schema, not role. In my PostgreSQL database there
> wasn't any such role... but I'll check that now... ok, I've
> checked, I've got 15 roles, none is names PUBLIC, what's more, I
> don't have any roles that cannot login.
>
> run simple query: SELECT rolname FROM pg_roles; and check the
> existence of the PUBLIC role.
for example, in your cluster with no PUBLIC role:
test=# revoke create on database test from public;
REVOKE
test=# grant select on pg_class to public;
GRANT
Kevin, I know public is there from using it every day, but if it were not for you post I would not know how to prove it, none ofthe views, graphical admin tools etc. display it as a user. We found this issue because we can grant privs to public on a table, but could not revoke them. If I did not "know" thatpublic was there ( because in my experience it is there in oracle, sql server and the Informix dbs) how would I checkfor it's existence on Postgres? Thank you, Kasia -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Tuesday, August 10, 2010 12:41 PM To: Kasia Tuszynska; Szymon Guz Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] postgres function does not handle PUBLIC - expected? Szymon Guz <mabewlun@gmail.com> wrote: > No, there is no PUBLIC default role in ANY rdbms. In PostgreSQL > there is PUBLIC schema, not role. In my PostgreSQL database there > wasn't any such role... but I'll check that now... ok, I've > checked, I've got 15 roles, none is names PUBLIC, what's more, I > don't have any roles that cannot login. > > run simple query: SELECT rolname FROM pg_roles; and check the > existence of the PUBLIC role. Yeah, it's automatically there in a shadowy sort of way. Try this, for example, in your cluster with no PUBLIC role: test=# revoke create on database test from public; REVOKE test=# grant select on pg_class to public; GRANT -Kevin
Kevin,
I know public is there from using it every day, but if it were not for you post I would not know how to prove it, none of the views, graphical admin tools etc. display it as a user.
We found this issue because we can grant privs to public on a table, but could not revoke them. If I did not "know" that public was there ( because in my experience it is there in oracle, sql server and the Informix dbs) how would I check for it's existence on Postgres?
Thank you,
Kasia
Kasia Tuszynska wrote: > Szymon, > > The PUBLIC role is a default, non login role, that is created with every > postgres cluster/instance. > > To my knowledge PUBLIC is a standard user in any rdbms. So, existence > of public is not the issue here. > > Sincerely, > > Kasia > Kasia, I think your assumption is wrong. I've been using postgres for a lot of years and never saw a PUBLIC role created by default in all the installations I've ever done. It's certainly possible that various other RDBMSs may assume the existence of such a role/user, but my experience is that the only default role in "our world" is named postgres. So I think this does have everything to do with your issue. Why don't you give it a shot and try to create a user named PUBLIC? good luck, ~c > > > *From:* Szymon Guz [mailto:mabewlun@gmail.com] > *Sent:* Tuesday, August 10, 2010 12:11 PM > *To:* Kasia Tuszynska > *Cc:* pgsql-admin@postgresql.org > *Subject:* Re: [ADMIN] postgres function does not handle PUBLIC - expected? > > > > 2010/8/10 Kasia Tuszynska <ktuszynska@esri.com <mailto:ktuszynska@esri.com>> > > Hello Postgres Gurus, > > > > Is it expected behavior for the has_table_privilege postgres function to > not handle the user PUBLIC? Or should I submit a bug? > > > > The has_table_privilege function does not handle PUBLIC – the following > queries returns the error: > > SELECT has_table_privilege ('PUBLIC','bob.gdb.test1','SELECT') AS SELECT, > > has_table_privilege ('PUBLIC','bob.gdb.test1','INSERT') AS INSERT, > > has_table_privilege ('PUBLIC','bob.gdb.test1','DELETE') AS DELETE, > > has_table_privilege ('PUBLIC','bob.gdb.test1','UPDATE') AS UPDATE; > > > > ERROR: role "PUBLIC" does not exist > > SQL state: 42704 > > > > SELECT has_table_privilege ('public','bob.gdb.test1','SELECT') AS SELECT, > > has_table_privilege ('public','bob.gdb.test1','INSERT') AS INSERT, > > has_table_privilege ('public','bob.gdb.test1','DELETE') AS DELETE, > > has_table_privilege ('public','bob.gdb.test1','UPDATE') AS UPDATE; > > > > ERROR: role "public" does not exist > > SQL state: 42704 > > Thank you, > > Sincerley, > > Kasia > > > > Do you have any role that is named PUBLIC? I think that you don't have > one. Check e.g. in the table pg_user. > > > > regards > > Szymon >
Kasia Tuszynska <ktuszynska@esri.com> wrote: > We found this issue because we can grant privs to public on a > table, but could not revoke them. Odd. test=# create table t1 (c1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE test=# grant insert on t1 to public; GRANT test=# revoke insert on t1 from public; REVOKE test=# revoke update on t1 from public; REVOKE > If I did not "know" that public was there how > would I check for it's existence on Postgres? You would need to go to the documentation. Unfortunately, we don't mention it on this page: http://www.postgresql.org/docs/current/static/role-membership.html As Andre pointed out, you can get a reasonable explanation on the page describing the GRANT statement. -Kevin
Please ignore my previous response, there's no value there. List traffic in the meantime (since I composed and sent it) has far more good information. sorry, ~c
Per my original email, we were calling the has_table_privilege function to revoke rather than simply revoking. Thank you very much, Sincerely, Kasia -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Tuesday, August 10, 2010 1:00 PM To: Kasia Tuszynska; Szymon Guz Cc: pgsql-admin@postgresql.org Subject: RE: [ADMIN] postgres function does not handle PUBLIC - expected? Kasia Tuszynska <ktuszynska@esri.com> wrote: > We found this issue because we can grant privs to public on a > table, but could not revoke them. Odd. test=# create table t1 (c1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE test=# grant insert on t1 to public; GRANT test=# revoke insert on t1 from public; REVOKE test=# revoke update on t1 from public; REVOKE > If I did not "know" that public was there how > would I check for it's existence on Postgres? You would need to go to the documentation. Unfortunately, we don't mention it on this page: http://www.postgresql.org/docs/current/static/role-membership.html As Andre pointed out, you can get a reasonable explanation on the page describing the GRANT statement. -Kevin