Postgres Pro
Downloads
Home   >   mailing lists

Thread: Group role not shown in properties > Default Privileges after adding it

Group role not shown in properties > Default Privileges after adding it

From
Aren Cambre
Date:
I added a group role named gis to a database using pgAdmin III 1.14.2 using the Default Privileges tab of the db's properties.

I can confirm it worked because if I click on the db, this shows up at the bottom of its SQL:
ALTER DEFAULT PRIVILEGES 
    GRANT SELECT ON TABLES
    TO gis;

However, if I right-click on the db, go to Properties, then select the Default Privileges, it does not show any group roles:
Inline image 1

Near the bottom of the pgAdmin Object browser, under Group Roles is this role:
Inline image 2

Aren
Attachment

Re: Group role not shown in properties > Default Privileges after adding it

From
Aren Cambre
Date:
One correction. I wrote "group role", but I think it's actually a login role. Here's its SQL:
CREATE ROLE gis LOGIN
  ENCRYPTED PASSWORD 'md5d1eac6a9b9835a37d71bac718a80a7ac'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

On Sun, May 27, 2012 at 9:03 AM, Aren Cambre <aren@arencambre.com> wrote:
I added a group role named gis to a database using pgAdmin III 1.14.2 using the Default Privileges tab of the db's properties.

I can confirm it worked because if I click on the db, this shows up at the bottom of its SQL:
ALTER DEFAULT PRIVILEGES 
    GRANT SELECT ON TABLES
    TO gis;

However, if I right-click on the db, go to Properties, then select the Default Privileges, it does not show any group roles:
Inline image 1

Near the bottom of the pgAdmin Object browser, under Group Roles is this role:
Inline image 2

Aren

Attachment

Re: Group role not shown in properties > Default Privileges after adding it

From
Aren Cambre
Date:
Looks like pgAdmin's user handling is broken or nonsensical. Just as a test, I created a new login role named test. Here's its SQL:
CREATE ROLE test LOGIN
  ENCRYPTED PASSWORD 'md505a671c66aefea124cc08b76ea6d30bb'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

Then I went to the Tables section of one of a db's schemas, started the Grant Wizard, then went to the Privileges tab, and this test account doesn't appear in the Role dropdown. All I see is public. If I type in test, I can't press Add/Change no matter what I select in Privileges.

This doesn't make sense. If I create a login role, it should show up here and allow me to assign it to arbitrary tables.

And I can confirm that pgAdmin is not acting correctly through direct SQL. If I run this:
grant select on txdot_roadways_3081_transform to gis;

(gis is the account I was trying to use earlier.) Then I can select from that table using that account via another program.'

Why does pgAdmin prevents me from assigning any login account?

Aren

On Sun, May 27, 2012 at 4:48 PM, Aren Cambre <aren@arencambre.com> wrote:
One correction. I wrote "group role", but I think it's actually a login role. Here's its SQL:
CREATE ROLE gis LOGIN
  ENCRYPTED PASSWORD 'md5d1eac6a9b9835a37d71bac718a80a7ac'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

On Sun, May 27, 2012 at 9:03 AM, Aren Cambre <aren@arencambre.com> wrote:
I added a group role named gis to a database using pgAdmin III 1.14.2 using the Default Privileges tab of the db's properties.

I can confirm it worked because if I click on the db, this shows up at the bottom of its SQL:
ALTER DEFAULT PRIVILEGES 
    GRANT SELECT ON TABLES
    TO gis;

However, if I right-click on the db, go to Properties, then select the Default Privileges, it does not show any group roles:
Inline image 1

Near the bottom of the pgAdmin Object browser, under Group Roles is this role:
Inline image 2

Aren


Attachment

Re: Re: Group role not shown in properties > Default Privileges after adding it

From
Dave Page
Date:


On Mon, May 28, 2012 at 10:39 AM, Aren Cambre <aren@arencambre.com> wrote:
Looks like pgAdmin's user handling is broken or nonsensical. Just as a test, I created a new login role named test. Here's its SQL:
CREATE ROLE test LOGIN
  ENCRYPTED PASSWORD 'md505a671c66aefea124cc08b76ea6d30bb'
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

Then I went to the Tables section of one of a db's schemas, started the Grant Wizard, then went to the Privileges tab, and this test account doesn't appear in the Role dropdown. All I see is public. If I type in test, I can't press Add/Change no matter what I select in Privileges.

This doesn't make sense. If I create a login role, it should show up here and allow me to assign it to arbitrary tables.

That's intentional (and configurable): see http://www.pgadmin.org/support/faq.php#UserPrivileges
 

And I can confirm that pgAdmin is not acting correctly through direct SQL. If I run this:
grant select on txdot_roadways_3081_transform to gis;

(gis is the account I was trying to use earlier.) Then I can select from that table using that account via another program.'


That's what I'd expect to happen - the GRANT applies to any user interface, not just pgAdmin. 

(I'll leave the default privs stuff for Guillaume to comment on, as he wrote that and I'm not overly familiar with it and am pressed for time right now).

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Re: Group role not shown in properties > Default Privileges after adding it

From
Aren Cambre
Date:

This doesn't make sense. If I create a login role, it should show up here and allow me to assign it to arbitrary tables.

That's intentional (and configurable): see http://www.pgadmin.org/support/faq.php#UserPrivileges

Argh, thanks. Now I remember running into this before.

This needs to be more clearly explained in the UI. I am an IT pro, and I totally understand the best practice of assigning groups and not users. But if you are imposing this rule on us, you need to make this clear in the UI and not send users on a wild goose chase. In this case, neither the UI nor the pgAdmin docs clearly explained this. I'd have to troll through a FAQ to figure it out.

In my case, I am the only user of the DB, and there are a small number of accounts, so it's much easier for me to directly assign users.

In the end, this is just a recommended practice, not a Postgres rule. The UI should not make our lives miserable if we don't want to follow the recommendation.

Aren

Re: Re: Group role not shown in properties > Default Privileges after adding it

From
Guillaume Lelarge
Date:
On Mon, 2012-05-28 at 09:55 -0500, Aren Cambre wrote:
> > This doesn't make sense. If I create a login role, it should show up here
> >> and allow me to assign it to arbitrary tables.
> >>
> >
> > That's intentional (and configurable): see
> > http://www.pgadmin.org/support/faq.php#UserPrivileges
> >
> 
> Argh, thanks. Now I remember running into this before.
> 
> This needs to be more clearly explained in the UI. I am an IT pro, and I
> totally understand the best practice of assigning groups and not users. But
> if you are imposing this rule on us, you need to make this clear in the UI
> and not send users on a wild goose chase. In this case, neither the UI nor
> the pgAdmin docs clearly explained this. I'd have to troll through a FAQ to
> figure it out.
> 

We need to work a lot on the docs (BTW, Dave, did you get in touch with
Susan?). This is something I want to do during the beta period if I find
the time.

About the UI, if you have any idea, I'm listening. Because I have no
idea how to make it better.

> In my case, I am the only user of the DB, and there are a small number of
> accounts, so it's much easier for me to directly assign users.
> 
> In the end, this is just a recommended practice, not a Postgres rule. The
> UI should not make our lives miserable if we don't want to follow the
> recommendation.
> 

Yes, recommended pratice, not a rule. That's why I would be in favor of
making the default to show groups and users, and let the user decide if
he wants to display only groups.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Re: Group role not shown in properties > Default Privileges after adding it

From
maxi0361
Date:
just ran into this same issue, there is something wrong with pgAdmin handling
this in users eyes.

like Aren, I want to create a role and grant privilege to a database. then
only see 'public'.
ok, find this post, it is intentional, then I go to "File > Options", there
is no general tab.
and I think 'public' is a schema, right? if you show 'public' in "Grant
wizard", then why isn't 'public' being seen under Group Roles ? and I
couldn't add my role to 'public'

this whole thing is so confusing. I end up using command line.
please think of a GUI that help users to accomplish this simple requirement.



--
View this message in context:
http://postgresql.nabble.com/Group-role-not-shown-in-properties-Default-Privileges-after-adding-it-tp5710204p5849020.html
Sent from the PostgreSQL - pgadmin support mailing list archive at Nabble.com.