Re: Permissions with Postgresql
On Wed, 2004-01-07 at 23:09, Bill Moseley wrote:
> I'm very new to Postgres so I'm not sure if this is a Debian issue or not.
> Now that I look I think it's more of a postgres question. Anyway:
>
> Postgresql 7.4.1:
>
> Silly me, I thought I could simply do:
>
> grand ALL on database foo to foouser;
>
> But that doesn't seem to do anything. Seems like I need to grant on
> individual objects. Is that correct?
Yes
> So, let me as the question:
>
> I created a database under my user id, and postgres authenticates via
> IDENT.
>
> Now I want to allow www-data full access to all objects in the database.
>
> As user postgres I first:
>
> cascade=# create user foouser with password 'foopass';
>
>
> Then to allow the user to connect I added to pg_hba.conf:
>
> # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
> local foo foouser md5
> host foo foouser 127.0.0.1 255.255.255.255 md5
>
> And indeed if I su to www-data I can connect with that user and
> password.
>
> But now is there a way to grant access other than trying to figure out
> every object in the database?
>
>
> I can get my tables with this:
>
> select tablename from pg_tables where schemaname = 'public';
>
> what about views and any other objects?
If you use the -E option to psql, it will show you the queries it is
sending to the backend for \dt, \dv and so on.
You can either extract the names into a script file or do
GRANT ALL ON (SELECT ...) TO user,...
If your permissions structures are likely to be complicated, with sets
of users having different permissions, think about using groups and
grant permissions to the groups rather than to individual users.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"And not only so, but we glory in tribulations also;
knowing that tribulation worketh patience; And
patience, experience; and experience, hope; And hope
maketh not ashamed; because the love of God is shed
abroad in our hearts by the Holy Ghost which is given
unto us." Romans 5:3-5
Reply to: