From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-sql@postgresql.org
Cc: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
Cc: imad <immaad@gmail.com>
Cc: Pgadmin-Support <pgadmin-support@postgresql.org>
Subject: Re: [SQL] Grants
Date: Fri, 1 Dec 2006 07:42:10 -0800
Message-ID: <200612010742.10553.aklaver@comcast.net> (raw)
In-Reply-To: <55c095e90612010603r11d2b1e0ibecc0fdee900f679@mail.gmail.com>
References: <55c095e90611301141n16b8f278n43f65ce5613e663d@mail.gmail.com>
<1f30b80c0611301224r135849ffg2426291a42afa29@mail.gmail.com>
<55c095e90612010603r11d2b1e0ibecc0fdee900f679@mail.gmail.com>
On Friday 01 December 2006 06:03 am, Ezequias Rodrigues da Rocha wrote:
>
> 2006/11/30, imad <immaad@gmail.com>:
> > You did not grant access privileges to schema.
> > Also GRANT administrators on the base schema as you did for the table.
> >
> > --Imad
> > www.EnterpriseDB.com
> >
> > On 12/1/06, Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com> wrote:
> > > Hi list,
> > >
> > > I am having problem with grants and users on PostgreSQL.
> > >
> > > I am using pgAdmin to connect like other user to test my permissions.
> > >
> > > As the owner of the database I have criated two roles:
> > >
> > > administrators (cannot connect)
> > > ezequias (can connect)
> > >
> > > I give permissions to a table I have:
> > > GRANT SELECT, UPDATE, INSERT ON TABLE base.table1 TO administrators;
> > >
> > >
> > > My user:
> > > CREATE ROLE ezequias LOGIN
> > > NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
> > > GRANT administradores TO ezequias;
> > >
> > > My group
> > > CREATE ROLE administradores
> > > NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;
> > >
> > > When I try to access the table base.table1 with ezequias login the
> > > pgAdmin reports:
> > > (see attached image)
> > >
> > > Could someone tell me what I did wrong ?
> > > Ezequias
> > >
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 5: don't forget to increase
> > > your free space map settings
> ####Schema grants####
>
> CREATE SCHEMA base
> AUTHORIZATION root;
> GRANT ALL ON SCHEMA base TO root;
> GRANT USAGE ON SCHEMA base TO administrators;
>
> ####Table grants####
> GRANT ALL ON TABLE base."local" TO root;
> GRANT SELECT, UPDATE, INSERT ON TABLE base."local" TO administrators;
>
> Still the same problem. :(
Two things I see.
The first may only be a translation artifact. You have CREATE ROLE
administradores and then GRANT to administrators on the table.
Second in the CREATE ROLE ezequias you have NOINHERIT. This means ezequias
does not automatically assume the privileges of the ROLES it belongs to. To
acquire the privileges you have to do a SET ROLE administrators at the
connection.
--
Adrian Klaver
aklaver@comcast.net
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: aklaver@comcast.net, ezequias.rocha@gmail.com, immaad@gmail.com, pgadmin-support@postgresql.org
Subject: Re: [SQL] Grants
In-Reply-To: <200612010742.10553.aklaver@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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