pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtwo questions about pg 9.0
7+ messages / 2 participants
[nested] [flat]
* two questions about pg 9.0
@ 2010-10-12 17:25 Kasia Tuszynska <ktuszynska@esri.com>
2010-10-12 17:59 ` Re: two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 7+ messages in thread
From: Kasia Tuszynska @ 2010-10-12 17:25 UTC (permalink / raw)
To: pgsql-admin
Hi Everybody,
My environment:
Windows 2008 32 bit
Postgres 9.0
I have two unrelated questions:
1. Permissions:
When I create a login role with superuser privileges in pgAdminIII I have the option of indicating that the superuser will also have the permission to: "can modify catalog directly", when I look at the sql that is generated by that gui, I do not see a corresponding sql permission, I see:
"CREATE ROLE kasia LOGIN ENCRYPTED PASSWORD 'md5ff0508b7bb33909c096d1b447a17d09b'
SUPERUSER VALID UNTIL 'infinity';"
A. What does "can modify catalog directly" actually do? I looked it up and I found : "This option determines whether the role can modify system catalog directly or not." But that really does not tell me much.
B. Why is there no sql level equivalent? When I revoke the "can modify catalog directly" permission I get the following sql: UPDATE pg_authid SET rolcatupdate=false WHERE OID=17331::oid;
2. Postgres client dlls on windows
I am trying to identify the dlls that are considered postgres client libraries
In 8.3.8 and 8.4.1 they were:
Libpq.dll, comerr32.dll, gssapi32.dll, k5sprt32.dll, libeay32.dll, libiconv2.dll, libintl3.dll, ssleay32.dll
In 9.0 they seem to be:
Libpq.dll, ssleay32.dll, libeay32.dll, libintl-8.dll - IS THIS CORRECT?
- Looking at the libpq dependencies the rest seem to be windows os dll
Thank you,
Sincerely,
Kasia
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: two questions about pg 9.0
2010-10-12 17:25 two questions about pg 9.0 Kasia Tuszynska <ktuszynska@esri.com>
@ 2010-10-12 17:59 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-10-13 20:21 ` Re: two questions about pg 9.0 Kasia Tuszynska <ktuszynska@esri.com>
0 siblings, 1 reply; 7+ messages in thread
From: Guillaume Lelarge @ 2010-10-12 17:59 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; +Cc: pgsql-admin
Hi,
Le 12/10/2010 19:25, Kasia Tuszynska a écrit :
> [...]
> 1. Permissions:
> When I create a login role with superuser privileges in pgAdminIII I have the option of indicating that the superuser will also have the permission to: "can modify catalog directly", when I look at the sql that is generated by that gui, I do not see a corresponding sql permission, I see:
> "CREATE ROLE kasia LOGIN ENCRYPTED PASSWORD 'md5ff0508b7bb33909c096d1b447a17d09b'
> SUPERUSER VALID UNTIL 'infinity';"
> A. What does "can modify catalog directly" actually do? I looked it up and I found : "This option determines whether the role can modify system catalog directly or not." But that really does not tell me much.
Yeah, we kinda suck at documenting things :)
It refers to the rolcatupdate column in the pg_authid system table.
Which is a permission to update the system catalogs (system tables).
> B. Why is there no sql level equivalent? When I revoke the "can modify catalog directly" permission I get the following sql: UPDATE pg_authid SET rolcatupdate=false WHERE OID=17331::oid;
You create a superuser, which already has this capacity. So we don't
need to add something else. Of course, if you uncheck it, we'll have to
update the system table to set it to false.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: two questions about pg 9.0
2010-10-12 17:25 two questions about pg 9.0 Kasia Tuszynska <ktuszynska@esri.com>
2010-10-12 17:59 ` Re: two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
@ 2010-10-13 20:21 ` Kasia Tuszynska <ktuszynska@esri.com>
2010-10-13 21:20 ` Re: two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 7+ messages in thread
From: Kasia Tuszynska @ 2010-10-13 20:21 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: pgsql-admin
Hi Guillaume,
Thanks for your reply I did a bit more testing with the superuser priv issue, and now I came to the conclusion that pgAdminIII may be doing something silly.
I created a user: bob
In pgAdminIII I checked off the box for: can inherit from parent role, can create db object, superuser
Got the following sql:
CREATE ROLE bob LOGIN
ENCRYPTED PASSWORD 'md51e9484aace238e7cb2609130fd87646e'
SUPERUSER INHERIT CREATEDB NOCREATEROLE;
UPDATE pg_authid SET rolcapupdate=false WHERE rolname='bob';
Than I created bobb
In pgAdminIII I checked off the box for: can inherit from parent role, can create db object, superuser, Can modify catalog directly
Got the following sql:
CREATE ROLE bobb LOGIN
ENCRYPTED PASSWORD 'md51e9484aace238e7cb2609130fd87646e'
SUPERUSER INHERIT CREATEDB NOCREATEROLE;
Conclusion:
Sql level superuser = pgAdminIII superuser + can modify catalog directly
This is misleading, I would call it a pgAdminIII bug but who knows maybe it is a feature...
Sincerely,
Kasia
-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume@lelarge.info]
Sent: Tuesday, October 12, 2010 10:59 AM
To: Kasia Tuszynska
Cc: pgsql-admin
Subject: Re: [ADMIN] two questions about pg 9.0
Hi,
Le 12/10/2010 19:25, Kasia Tuszynska a écrit :
> [...]
> 1. Permissions:
> When I create a login role with superuser privileges in pgAdminIII I have the option of indicating that the superuser will also have the permission to: "can modify catalog directly", when I look at the sql that is generated by that gui, I do not see a corresponding sql permission, I see:
> "CREATE ROLE kasia LOGIN ENCRYPTED PASSWORD 'md5ff0508b7bb33909c096d1b447a17d09b'
> SUPERUSER VALID UNTIL 'infinity';"
> A. What does "can modify catalog directly" actually do? I looked it up and I found : "This option determines whether the role can modify system catalog directly or not." But that really does not tell me much.
Yeah, we kinda suck at documenting things :)
It refers to the rolcatupdate column in the pg_authid system table.
Which is a permission to update the system catalogs (system tables).
> B. Why is there no sql level equivalent? When I revoke the "can modify catalog directly" permission I get the following sql: UPDATE pg_authid SET rolcatupdate=false WHERE OID=17331::oid;
You create a superuser, which already has this capacity. So we don't
need to add something else. Of course, if you uncheck it, we'll have to
update the system table to set it to false.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: two questions about pg 9.0
2010-10-12 17:25 two questions about pg 9.0 Kasia Tuszynska <ktuszynska@esri.com>
2010-10-12 17:59 ` Re: two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
2010-10-13 20:21 ` Re: two questions about pg 9.0 Kasia Tuszynska <ktuszynska@esri.com>
@ 2010-10-13 21:20 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-10-14 10:35 ` Re: [ADMIN] two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
2010-10-15 22:24 ` Re: two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 2 replies; 7+ messages in thread
From: Guillaume Lelarge @ 2010-10-13 21:20 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; +Cc: pgsql-admin
Hi Kasia,
Le 13/10/2010 22:21, Kasia Tuszynska a écrit :
> [...]
> Thanks for your reply I did a bit more testing with the superuser priv issue, and now I came to the conclusion that pgAdminIII may be doing something silly.
>
Sure, that happens more than I would like.
> I created a user: bob
>
> In pgAdminIII I checked off the box for: can inherit from parent role, can create db object, superuser
> Got the following sql:
> CREATE ROLE bob LOGIN
> ENCRYPTED PASSWORD 'md51e9484aace238e7cb2609130fd87646e'
> SUPERUSER INHERIT CREATEDB NOCREATEROLE;
> UPDATE pg_authid SET rolcapupdate=false WHERE rolname='bob';
>
> Than I created bobb
> In pgAdminIII I checked off the box for: can inherit from parent role, can create db object, superuser, Can modify catalog directly
> Got the following sql:
> CREATE ROLE bobb LOGIN
> ENCRYPTED PASSWORD 'md51e9484aace238e7cb2609130fd87646e'
> SUPERUSER INHERIT CREATEDB NOCREATEROLE;
>
> Conclusion:
> Sql level superuser = pgAdminIII superuser + can modify catalog directly
>
> This is misleading, I would call it a pgAdminIII bug but who knows maybe it is a feature...
>
I would not call it a feature. I find this misleading too. I'm too tired
right now to work on a fix, but it'll be easy and quick to do.
I have a few things to commit tomorrow. I'll try to work on this at the
same time.
Thanks for your inputs.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [ADMIN] two questions about pg 9.0
2010-10-12 17:25 two questions about pg 9.0 Kasia Tuszynska <ktuszynska@esri.com>
2010-10-12 17:59 ` Re: two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
2010-10-13 20:21 ` Re: two questions about pg 9.0 Kasia Tuszynska <ktuszynska@esri.com>
2010-10-13 21:20 ` Re: two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
@ 2010-10-14 10:35 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-10-15 22:23 ` Re: [ADMIN] two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
1 sibling, 1 reply; 7+ messages in thread
From: Guillaume Lelarge @ 2010-10-14 10:35 UTC (permalink / raw)
To: pgadmin-hackers <pgadmin-hackers@postgresql.org>; +Cc: Kasia Tuszynska <ktuszynska@esri.com>
Le 13/10/2010 23:20, Guillaume Lelarge a écrit :
> Le 13/10/2010 22:21, Kasia Tuszynska a écrit :
>> [...]
>> Thanks for your reply I did a bit more testing with the superuser priv issue, and now I came to the conclusion that pgAdminIII may be doing something silly.
>>
>
> Sure, that happens more than I would like.
>
>> I created a user: bob
>>
>> In pgAdminIII I checked off the box for: can inherit from parent role, can create db object, superuser
>> Got the following sql:
>> CREATE ROLE bob LOGIN
>> ENCRYPTED PASSWORD 'md51e9484aace238e7cb2609130fd87646e'
>> SUPERUSER INHERIT CREATEDB NOCREATEROLE;
>> UPDATE pg_authid SET rolcapupdate=false WHERE rolname='bob';
>>
>> Than I created bobb
>> In pgAdminIII I checked off the box for: can inherit from parent role, can create db object, superuser, Can modify catalog directly
>> Got the following sql:
>> CREATE ROLE bobb LOGIN
>> ENCRYPTED PASSWORD 'md51e9484aace238e7cb2609130fd87646e'
>> SUPERUSER INHERIT CREATEDB NOCREATEROLE;
>>
>> Conclusion:
>> Sql level superuser = pgAdminIII superuser + can modify catalog directly
>>
>> This is misleading, I would call it a pgAdminIII bug but who knows maybe it is a feature...
>>
>
> I would not call it a feature. I find this misleading too. I'm too tired
> right now to work on a fix, but it'll be easy and quick to do.
>
> I have a few things to commit tomorrow. I'll try to work on this at the
> same time.
>
OK, was really simple to fix. See the patch attached.
Any comments on this patch? I'm ready to commit it on 1.12 and master,
if no-one objects.
Thanks.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
Attachments:
0001-Fix-the-setting-of-Can-update-catalog-checkbox.patch (text/x-diff, 0B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [ADMIN] two questions about pg 9.0
2010-10-12 17:25 two questions about pg 9.0 Kasia Tuszynska <ktuszynska@esri.com>
2010-10-12 17:59 ` Re: two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
2010-10-13 20:21 ` Re: two questions about pg 9.0 Kasia Tuszynska <ktuszynska@esri.com>
2010-10-13 21:20 ` Re: two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
2010-10-14 10:35 ` Re: [ADMIN] two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
@ 2010-10-15 22:23 ` Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 7+ messages in thread
From: Guillaume Lelarge @ 2010-10-15 22:23 UTC (permalink / raw)
To: pgadmin-hackers <pgadmin-hackers@postgresql.org>; +Cc: Kasia Tuszynska <ktuszynska@esri.com>
Le 14/10/2010 12:35, Guillaume Lelarge a écrit :
> Le 13/10/2010 23:20, Guillaume Lelarge a écrit :
>> Le 13/10/2010 22:21, Kasia Tuszynska a écrit :
>>> [...]
>>> Thanks for your reply I did a bit more testing with the superuser priv issue, and now I came to the conclusion that pgAdminIII may be doing something silly.
>>>
>>
>> Sure, that happens more than I would like.
>>
>>> I created a user: bob
>>>
>>> In pgAdminIII I checked off the box for: can inherit from parent role, can create db object, superuser
>>> Got the following sql:
>>> CREATE ROLE bob LOGIN
>>> ENCRYPTED PASSWORD 'md51e9484aace238e7cb2609130fd87646e'
>>> SUPERUSER INHERIT CREATEDB NOCREATEROLE;
>>> UPDATE pg_authid SET rolcapupdate=false WHERE rolname='bob';
>>>
>>> Than I created bobb
>>> In pgAdminIII I checked off the box for: can inherit from parent role, can create db object, superuser, Can modify catalog directly
>>> Got the following sql:
>>> CREATE ROLE bobb LOGIN
>>> ENCRYPTED PASSWORD 'md51e9484aace238e7cb2609130fd87646e'
>>> SUPERUSER INHERIT CREATEDB NOCREATEROLE;
>>>
>>> Conclusion:
>>> Sql level superuser = pgAdminIII superuser + can modify catalog directly
>>>
>>> This is misleading, I would call it a pgAdminIII bug but who knows maybe it is a feature...
>>>
>>
>> I would not call it a feature. I find this misleading too. I'm too tired
>> right now to work on a fix, but it'll be easy and quick to do.
>>
>> I have a few things to commit tomorrow. I'll try to work on this at the
>> same time.
>>
>
> OK, was really simple to fix. See the patch attached.
>
> Any comments on this patch? I'm ready to commit it on 1.12 and master,
> if no-one objects.
>
Commited.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: two questions about pg 9.0
2010-10-12 17:25 two questions about pg 9.0 Kasia Tuszynska <ktuszynska@esri.com>
2010-10-12 17:59 ` Re: two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
2010-10-13 20:21 ` Re: two questions about pg 9.0 Kasia Tuszynska <ktuszynska@esri.com>
2010-10-13 21:20 ` Re: two questions about pg 9.0 Guillaume Lelarge <guillaume@lelarge.info>
@ 2010-10-15 22:24 ` Guillaume Lelarge <guillaume@lelarge.info>
1 sibling, 0 replies; 7+ messages in thread
From: Guillaume Lelarge @ 2010-10-15 22:24 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; +Cc: pgsql-admin
Le 13/10/2010 23:20, Guillaume Lelarge a écrit :
> Hi Kasia,
>
> Le 13/10/2010 22:21, Kasia Tuszynska a écrit :
>> [...]
>> Thanks for your reply I did a bit more testing with the superuser priv issue, and now I came to the conclusion that pgAdminIII may be doing something silly.
>>
>
> Sure, that happens more than I would like.
>
>> I created a user: bob
>>
>> In pgAdminIII I checked off the box for: can inherit from parent role, can create db object, superuser
>> Got the following sql:
>> CREATE ROLE bob LOGIN
>> ENCRYPTED PASSWORD 'md51e9484aace238e7cb2609130fd87646e'
>> SUPERUSER INHERIT CREATEDB NOCREATEROLE;
>> UPDATE pg_authid SET rolcapupdate=false WHERE rolname='bob';
>>
>> Than I created bobb
>> In pgAdminIII I checked off the box for: can inherit from parent role, can create db object, superuser, Can modify catalog directly
>> Got the following sql:
>> CREATE ROLE bobb LOGIN
>> ENCRYPTED PASSWORD 'md51e9484aace238e7cb2609130fd87646e'
>> SUPERUSER INHERIT CREATEDB NOCREATEROLE;
>>
>> Conclusion:
>> Sql level superuser = pgAdminIII superuser + can modify catalog directly
>>
>> This is misleading, I would call it a pgAdminIII bug but who knows maybe it is a feature...
>>
>
> I would not call it a feature. I find this misleading too. I'm too tired
> right now to work on a fix, but it'll be easy and quick to do.
>
> I have a few things to commit tomorrow. I'll try to work on this at the
> same time.
>
I commited a fix. It should be available when 1.12.2 will be released.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-10-15 22:24 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-12 17:25 two questions about pg 9.0 Kasia Tuszynska <ktuszynska@esri.com>
2010-10-12 17:59 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-10-13 20:21 ` Kasia Tuszynska <ktuszynska@esri.com>
2010-10-13 21:20 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-10-14 10:35 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-10-15 22:23 ` Guillaume Lelarge <guillaume@lelarge.info>
2010-10-15 22:24 ` Guillaume Lelarge <guillaume@lelarge.info>
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