pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgres function does not handle PUBLIC - expected?
13+ messages / 5 participants
[nested] [flat]
* postgres function does not handle PUBLIC - expected?
@ 2010-08-10 19:08 Kasia Tuszynska <ktuszynska@esri.com>
0 siblings, 2 replies; 13+ messages in thread
From: Kasia Tuszynska @ 2010-08-10 19:08 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres function does not handle PUBLIC - expected?
@ 2010-08-10 19:10 Szymon Guz <mabewlun@gmail.com>
parent: Kasia Tuszynska <ktuszynska@esri.com>
1 sibling, 3 replies; 13+ messages in thread
From: Szymon Guz @ 2010-08-10 19:10 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres function does not handle PUBLIC - expected?
@ 2010-08-10 19:26 Kasia Tuszynska <ktuszynska@esri.com>
parent: Szymon Guz <mabewlun@gmail.com>
2 siblings, 1 reply; 13+ messages in thread
From: Kasia Tuszynska @ 2010-08-10 19:26 UTC (permalink / raw)
To: Szymon Guz <mabewlun@gmail.com>; +Cc: pgsql-admin
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<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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres function does not handle PUBLIC - expected?
@ 2010-08-10 19:34 Alvaro Herrera <alvherre@commandprompt.com>
parent: Kasia Tuszynska <ktuszynska@esri.com>
1 sibling, 0 replies; 13+ messages in thread
From: Alvaro Herrera @ 2010-08-10 19:34 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; +Cc: pgsql-admin
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 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
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres function does not handle PUBLIC - expected?
@ 2010-08-10 19:35 Szymon Guz <mabewlun@gmail.com>
parent: Kasia Tuszynska <ktuszynska@esri.com>
0 siblings, 1 reply; 13+ messages in thread
From: Szymon Guz @ 2010-08-10 19:35 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; +Cc: pgsql-admin
2010/8/10 Kasia Tuszynska <ktuszynska@esri.com>
> 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
>
>
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.
regards
Szymon Guz
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres function does not handle PUBLIC - expected?
@ 2010-08-10 19:37 charlie derr <cderr@simons-rock.edu>
parent: Szymon Guz <mabewlun@gmail.com>
2 siblings, 0 replies; 13+ messages in thread
From: charlie derr @ 2010-08-10 19:37 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres function does not handle PUBLIC - expected?
@ 2010-08-10 19:40 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Szymon Guz <mabewlun@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Kevin Grittner @ 2010-08-10 19:40 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; Szymon Guz <mabewlun@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres function does not handle PUBLIC - expected?
@ 2010-08-10 19:49 Szymon Guz <mabewlun@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 13+ messages in thread
From: Szymon Guz @ 2010-08-10 19:49 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Kasia Tuszynska <ktuszynska@esri.com>; pgsql-admin
2010/8/10 Kevin Grittner <Kevin.Grittner@wicourts.gov>
> 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
>
>
well... surprisingly that works... does the public role exist in the SQL
standard? why it is not in the pg_roles table?
Szymon
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres function does not handle PUBLIC - expected?
@ 2010-08-10 19:50 Kasia Tuszynska <ktuszynska@esri.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 2 replies; 13+ messages in thread
From: Kasia Tuszynska @ 2010-08-10 19:50 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; Szymon Guz <mabewlun@gmail.com>; +Cc: pgsql-admin
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
-----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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres function does not handle PUBLIC - expected?
@ 2010-08-10 19:56 Szymon Guz <mabewlun@gmail.com>
parent: Kasia Tuszynska <ktuszynska@esri.com>
1 sibling, 0 replies; 13+ messages in thread
From: Szymon Guz @ 2010-08-10 19:56 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
2010/8/10 Kasia Tuszynska <ktuszynska@esri.com>
> 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
"The key word PUBLIC refers to the implicitly defined group of all roles."
http://www.postgresql.org/docs/8.4/static/sql-revoke.html
<http://www.postgresql.org/docs/8.4/static/sql-revoke.html>So PUBLIC seems
to be a kind of magical role. I didn't know that, sorry for my previous, a
little bit confusing email.
regards
Szymon
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres function does not handle PUBLIC - expected?
@ 2010-08-10 20:00 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Kasia Tuszynska <ktuszynska@esri.com>
1 sibling, 1 reply; 13+ messages in thread
From: Kevin Grittner @ 2010-08-10 20:00 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; Szymon Guz <mabewlun@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres function does not handle PUBLIC - expected?
@ 2010-08-10 20:00 charlie derr <cderr@simons-rock.edu>
parent: Szymon Guz <mabewlun@gmail.com>
2 siblings, 0 replies; 13+ messages in thread
From: charlie derr @ 2010-08-10 20:00 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: postgres function does not handle PUBLIC - expected?
@ 2010-08-10 22:02 Kasia Tuszynska <ktuszynska@esri.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 0 replies; 13+ messages in thread
From: Kasia Tuszynska @ 2010-08-10 22:02 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2010-08-10 22:02 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-10 19:08 postgres function does not handle PUBLIC - expected? Kasia Tuszynska <ktuszynska@esri.com>
2010-08-10 19:10 ` Szymon Guz <mabewlun@gmail.com>
2010-08-10 19:26 ` Kasia Tuszynska <ktuszynska@esri.com>
2010-08-10 19:35 ` Szymon Guz <mabewlun@gmail.com>
2010-08-10 19:40 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-10 19:49 ` Szymon Guz <mabewlun@gmail.com>
2010-08-10 19:50 ` Re: postgres function does not handle PUBLIC - expected? Kasia Tuszynska <ktuszynska@esri.com>
2010-08-10 19:56 ` Szymon Guz <mabewlun@gmail.com>
2010-08-10 20:00 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-08-10 22:02 ` Re: postgres function does not handle PUBLIC - expected? Kasia Tuszynska <ktuszynska@esri.com>
2010-08-10 19:37 ` charlie derr <cderr@simons-rock.edu>
2010-08-10 20:00 ` charlie derr <cderr@simons-rock.edu>
2010-08-10 19:34 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox