Re: [SQL] Permissions not working
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
I have a database for ex: 'ups' and it was owned previously by
'postgres(superuser)' but now i have changed the ownership to new user
'ups' all the tables are owned by these user 'ups'.
That isn't a supported operation. How did you do it exactly? I suspect
that you got it wrong somehow ...
Sorry, I wasnt clear on this. First I created a database called 'ups'
and made user 'ups' as its owner. Then
I dumped the database from backup, the dumped database backup was
owned by 'postgres ' user but all the
tables in this database are owned by 'ups' user.
I dont want user 'test' to access any tables from the 'ups'
database, i tried revoking permissions it still doesnt work.
What did you revoke? What does psql's "\z" command show for the problem
tables?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Here is the output from the \z . I tried to revoke all the privileges
from the PUBLIC and user 'test'.
usps=> \z citystate_alias
Access privileges
for database "usps"
Schema | Table
| Access privileges
+-+---
public | citystate_alias |
{postgres=a*r*w*d*R*x*t*/postgres,=r/postgres,usps=arwdRxt/postgres,"group
100=r/usps","group ea_development=r/usps"}
(1 row)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Permissions not working
Pallav Kalva <[EMAIL PROTECTED]> writes:
> usps=> \z citystate_alias
> Access privileges
> for database "usps"
> Schema | Table
> | Access privileges
> +-+---
> public | citystate_alias |
> {postgres=a*r*w*d*R*x*t*/postgres,=r/postgres,usps=arwdRxt/postgres,"group
> 100=r/usps","group ea_development=r/usps"}
> (1 row)
It looks to me like (a) this table is owned by postgres not usps, and
(b) postgres has granted SELECT permission to PUBLIC (that's what the
"=r/postgres" part means). The usps user isn't going to be able to
revoke that because he doesn't own the table.
It does seem like you've found a bug of some kind though: the above
shows that user usps does not have GRANT OPTION rights of any kind
(there are no stars in his privilege list). So how was he able to grant
SELECT rights to those two groups? Do you have the exact sequence of
GRANT and REVOKE operations that were performed on this table? What
PG version is this, exactly?
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Permissions not working
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
usps=> \z citystate_alias
Access privileges
for database "usps"
Schema | Table
| Access privileges
+-+---
public | citystate_alias |
{postgres=a*r*w*d*R*x*t*/postgres,=r/postgres,usps=arwdRxt/postgres,"group
100=r/usps","group ea_development=r/usps"}
(1 row)
It looks to me like (a) this table is owned by postgres not usps, and
(b) postgres has granted SELECT permission to PUBLIC (that's what the
"=r/postgres" part means). The usps user isn't going to be able to
revoke that because he doesn't own the table.
It does seem like you've found a bug of some kind though: the above
shows that user usps does not have GRANT OPTION rights of any kind
(there are no stars in his privilege list). So how was he able to grant
SELECT rights to those two groups? Do you have the exact sequence of
GRANT and REVOKE operations that were performed on this table? What
PG version is this, exactly?
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
I am using Postgres 7.4.2 version. I dont have exact sequence of GRANT
and REVOKE
commands. and as i told earlier I created the database first and then
dumped it from the backups.
The table infact is owned by 'usps' user here is the output from \dt for
that table
usps=> \dt
List of relations
Schema | Name | Type | Owner
+--+---+---
public | citystate_alias | table | usps
Also here is the privileges information from information_schema tables.
Is there a way to REVOKE these
privileges ?
usps=> select * from information_schema.table_privileges where
table_name = 'citystate_alias';
grantor |grantee | table_catalog | table_schema |
table_name| privilege_type | is_grantable | with_hierarchy
--++---+--+-++--+
postgres | usps | usps | public |
citystate_alias | SELECT | NO | NO
postgres | PUBLIC | usps | public |
citystate_alias | SELECT | NO | NO
usps | ea_development | usps | public |
citystate_alias | SELECT | NO | NO
postgres | usps | usps | public |
citystate_alias | DELETE | NO | NO
postgres | usps | usps | public |
citystate_alias | INSERT | NO | NO
postgres | usps | usps | public |
citystate_alias | UPDATE | NO | NO
postgres | usps | usps | public |
citystate_alias | REFERENCES | NO | NO
postgres | usps | usps | public |
citystate_alias | RULE | NO | NO
postgres | usps | usps | public |
citystate_alias | TRIGGER| NO | NO
(9 rows)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Permissions not working
Pallav Kalva wrote: > Also here is the privileges information from information_schema > tables. Is there a way to REVOKE these > privileges ? You need to log in as the user that has granted the privilege you want to revoke. In this case, log in as postgres and do REVOKE ALL FROM PUBLIC;. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Permissions not working
On Thu, 29 Apr 2004, Pallav Kalva wrote: > Hi , > > I am having some problems with setting up permissions in Postgres. I > have a database for ex: 'ups' and it was owned previously by > 'postgres(superuser)' but now i have changed the ownership to new user > 'ups' all the tables are owned by these user 'ups'. This database doesnt > have any schemas except for 'Public'. I have created another user lets > say 'test' and i didnt give 'test' user any permissions to access the > tables owned by 'ups' but still when i login to 'ups' database as psql > ups test and run a select on the tables owned by 'ups' database it > goes through. >I dont want user 'test' to access any tables from the 'ups' > database, i tried revoking permissions it still doesnt work. Can anyone > tell me what is wrong here ? Log in as the superuser (usually postgres) and see what you get from this query: select usesuper from pg_shadow where usename='test'; if usesuper is t, then test is a superuser and can do anything he wants. You need to issue the command: alter user test with nocreateuser; If that isn't the problem, let us know. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Permissions not working
scott.marlowe wrote: On Thu, 29 Apr 2004, Pallav Kalva wrote: Hi , I am having some problems with setting up permissions in Postgres. I have a database for ex: 'ups' and it was owned previously by 'postgres(superuser)' but now i have changed the ownership to new user 'ups' all the tables are owned by these user 'ups'. This database doesnt have any schemas except for 'Public'. I have created another user lets say 'test' and i didnt give 'test' user any permissions to access the tables owned by 'ups' but still when i login to 'ups' database as psql ups test and run a select on the tables owned by 'ups' database it goes through. I dont want user 'test' to access any tables from the 'ups' database, i tried revoking permissions it still doesnt work. Can anyone tell me what is wrong here ? Log in as the superuser (usually postgres) and see what you get from this query: select usesuper from pg_shadow where usename='test'; if usesuper is t, then test is a superuser and can do anything he wants. You need to issue the command: alter user test with nocreateuser; If that isn't the problem, let us know. Thanks! for the quick reply, I ran the above query and it is 'f' for the 'test' user, 'test' is not a super user. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Permissions not working
On Thu, 29 Apr 2004, Pallav Kalva wrote: > scott.marlowe wrote: > > >On Thu, 29 Apr 2004, Pallav Kalva wrote: > > > > > > > >>Hi , > >> > >>I am having some problems with setting up permissions in Postgres. I > >>have a database for ex: 'ups' and it was owned previously by > >>'postgres(superuser)' but now i have changed the ownership to new user > >>'ups' all the tables are owned by these user 'ups'. This database doesnt > >>have any schemas except for 'Public'. I have created another user lets > >>say 'test' and i didnt give 'test' user any permissions to access the > >>tables owned by 'ups' but still when i login to 'ups' database as psql > >>ups test and run a select on the tables owned by 'ups' database it > >>goes through. > >> I dont want user 'test' to access any tables from the 'ups' > >>database, i tried revoking permissions it still doesnt work. Can anyone > >>tell me what is wrong here ? > >> > >> > > > >Log in as the superuser (usually postgres) and see what you get from this > >query: > > > >select usesuper from pg_shadow where usename='test'; > > > >if usesuper is t, then test is a superuser and can do anything he wants. > >You need to issue the command: > > > >alter user test with nocreateuser; > > > >If that isn't the problem, let us know. > > > > > Thanks! for the quick reply, I ran the above query and it is 'f' for the > 'test' user, 'test' is not a super user. Ok, then what does \z tablename where tablename is one of the tables you don't want test to access. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Permissions not working
Pallav Kalva <[EMAIL PROTECTED]> writes: > I have a database for ex: 'ups' and it was owned previously by > 'postgres(superuser)' but now i have changed the ownership to new user > 'ups' all the tables are owned by these user 'ups'. That isn't a supported operation. How did you do it exactly? I suspect that you got it wrong somehow ... > I dont want user 'test' to access any tables from the 'ups' > database, i tried revoking permissions it still doesnt work. What did you revoke? What does psql's "\z" command show for the problem tables? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SPAM] Re: [SQL] Permissions not working
Peter Eisentraut wrote: Pallav Kalva wrote: Also here is the privileges information from information_schema tables. Is there a way to REVOKE these privileges ? You need to log in as the user that has granted the privilege you want to revoke. In this case, log in as postgres and do REVOKE ALL FROM PUBLIC;. I did the same, still doesnt work . Here is the sequence of what I did . --- [EMAIL PROTECTED] pkalva]$ psql usps postgres Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit usps=# revoke all on database usps from public,test; REVOKE usps=# \q [EMAIL PROTECTED] pkalva]$ psql usps test Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit usps=> select * from citystate_alias limit 1; detailcode | zipcode | aliasstreetpredir | aliasstreetname | aliasstreetsuffix | aliasstreetpostdir | streetpredir | streetname | streetsuffix | streetpostdir | typecode | century | year | month | day | lownumber | highnumber | oddoreven | filler | entrydate +-+---+--+---++--++--+---+--+-+--+---+-+---++---++--- A | 00501 | | INTERNAL REVENUE SERVICE | || | WAVERLY| AVE | | O| 19 | 94 | 05| 01 | || || 2004-04-22 14:51:45.497651-04 (1 row) usps=> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SPAM] Re: [SQL] Permissions not working
Pallav Kalva <[EMAIL PROTECTED]> writes: > I did the same, still doesnt work . Here is the sequence of what I did . > usps=# revoke all on database usps from public,test; You seem to think that that translates to revoking all privileges to objects within the database. It doesn't. It only revokes privileges directly associated with the database object, which are the rights to create new schemas and temp tables within the database. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SPAM] Re: [SQL] Permissions not working
usps=# revoke all on database usps from public,test; You seem to think that that translates to revoking all privileges to objects within the database. It doesn't. It only revokes privileges directly associated with the database object, which are the rights to create new schemas and temp tables within the database. regards, tom lane Hi Tom, I tried both database privileges and table privileges (all and select) it still doesnt work. Not sure what is wrong here, I tried logging in as both postgres and usps user and both them doesnt work. [EMAIL PROTECTED] pkalva]$ psql usps postgres Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit usps=# revoke all privileges on citystate_alias from public,test; REVOKE usps=# \q [EMAIL PROTECTED] pkalva]$ psql usps usps Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit usps=> revoke all privileges on citystate_alias from public,test; REVOKE usps=> revoke select on citystate_alias from public,test; REVOKE usps=> \q [EMAIL PROTECTED] pkalva]$ psql usps test Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit usps=> select * from citystate_alias limit 1; detailcode | zipcode | aliasstreetpredir | aliasstreetname | aliasstre etsuffix | aliasstreetpostdir | streetpredir | streetname | streetsuffix | stree tpostdir | typecode | century | year | month | day | lownumber | highnumber | od doreven | filler | entrydate +-+---+--+-- -++--++--+-- -+--+-+--+---+-+---++--- ++--- A | 00501 | | INTERNAL REVENUE SERVICE | || | WAVERLY| AVE | | O| 19 | 94 | 05| 01 | | | || 2004-04-22 14:51:45.497651-04 (1 row) usps=> ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SPAM] Re: [SQL] Permissions not working
Pallav Kalva wrote: > I tried both database privileges and table privileges (all and > select) it still doesnt work. Not sure > what is wrong here, I tried logging in as both postgres and usps user > and both them doesnt work. What about all those groups that have privileges? Please post the output of \z when you try a revoke so we can verify what's going on. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SPAM] Re: [SQL] Permissions not working
Peter Eisentraut wrote:
Pallav Kalva wrote:
I tried both database privileges and table privileges (all and
select) it still doesnt work. Not sure
what is wrong here, I tried logging in as both postgres and usps user
and both them doesnt work.
What about all those groups that have privileges? Please post the
output of \z when you try a revoke so we can verify what's going on.
here is the output. This is the same output, this output doesnt change
at all.
usps=> \z citystate_alias
Access privileges
for database "usps"
Schema | Table
| Access privileges
+-+---
public | citystate_alias |
{postgres=a*r*w*d*R*x*t*/postgres,=r/postgres,usps=arwdRxt/postgres,"group
100=r/usps","group ea_development=r/usps"}
(1 row)
---(end of broadcast)---
TIP 8: explain analyze is your friend
