Обсуждение: postgres function does not handle PUBLIC - expected?

Поиск
Список
Период
Сортировка

postgres function does not handle PUBLIC - expected?

От
Kasia Tuszynska
Дата:

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

Re: postgres function does not handle PUBLIC - expected?

От
Szymon Guz
Дата:
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

Re: postgres function does not handle PUBLIC - expected?

От
Kasia Tuszynska
Дата:

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

Re: postgres function does not handle PUBLIC - expected?

От
Alvaro Herrera
Дата:
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

Re: postgres function does not handle PUBLIC - expected?

От
Szymon Guz
Дата:
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
 

Re: postgres function does not handle PUBLIC - expected?

От
"Kevin Grittner"
Дата:
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

Re: postgres function does not handle PUBLIC - expected?

От
Szymon Guz
Дата:


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


Re: postgres function does not handle PUBLIC - expected?

От
Kasia Tuszynska
Дата:
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


Re: postgres function does not handle PUBLIC - expected?

От
Szymon Guz
Дата:


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."


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

 

Re: postgres function does not handle PUBLIC - expected?

От
charlie derr
Дата:
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
>


Re: postgres function does not handle PUBLIC - expected?

От
"Kevin Grittner"
Дата:
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

Re: postgres function does not handle PUBLIC - expected?

От
charlie derr
Дата:
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

Re: postgres function does not handle PUBLIC - expected?

От
Kasia Tuszynska
Дата:
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