Thread: Group role not shown in properties > Default Privileges after adding it
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.
![Inline image 2](/media/maillist_attaches/pgadmin-support/2012/05/27/CAA1mBrriYR3q11qZtjZ0KGM3RXK0Dp=RDfjKB1VWOx63aNujPA@mail.gmail.com/image.png)
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](/media/maillist_attaches/pgadmin-support/2012/05/27/CAA1mBrriYR3q11qZtjZ0KGM3RXK0Dp=RDfjKB1VWOx63aNujPA@mail.gmail.com/image-1.png)
![Inline image 1](/media/maillist_attaches/pgadmin-support/2012/05/27/CAA1mBrriYR3q11qZtjZ0KGM3RXK0Dp=RDfjKB1VWOx63aNujPA@mail.gmail.com/image-1.png)
Near the bottom of the pgAdmin Object browser, under Group Roles is this role:
![Inline image 2](/media/maillist_attaches/pgadmin-support/2012/05/27/CAA1mBrriYR3q11qZtjZ0KGM3RXK0Dp=RDfjKB1VWOx63aNujPA@mail.gmail.com/image.png)
Aren
Attachment
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 PRIVILEGESGRANT SELECT ON TABLESTO gis;However, if I right-click on the db, go to Properties, then select the Default Privileges, it does not show any group roles:Near the bottom of the pgAdmin Object browser, under Group Roles is this role:Aren
Attachment
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 LOGINENCRYPTED 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 PRIVILEGESGRANT SELECT ON TABLESTO gis;However, if I right-click on the db, go to Properties, then select the Default Privileges, it does not show any group roles:Near the bottom of the pgAdmin Object browser, under Group Roles is this role: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 LOGINENCRYPTED 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
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.