Horton

Thread

  1. Permissions not working

    Pallav Kalva <pkalva@deg.cc> — 2004-04-29T20:53:10Z

    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 ?

 Appreciate your help.

Thanks!
Pallav

  2. Re: Permissions not working

    scott.marlowe <scott.marlowe@ihs.com> — 2004-04-29T21:26:01Z

    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.

  3. Re: Permissions not working

    Pallav Kalva <pkalva@deg.cc> — 2004-04-29T21:45:12Z

    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.

  4. Re: Permissions not working

    scott.marlowe <scott.marlowe@ihs.com> — 2004-04-29T21:59:11Z

    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.

  5. isnumeric() function?

    Yudie <yudie@axiontech.com> — 2004-04-29T22:46:21Z

    What is isnumeric function in postgresql? 
I'm using psql version 7.2.2
thanks
Yudie

  6. Re: Permissions not working

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-04-29T23:07:27Z

    Pallav Kalva <pkalva@deg.cc> 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

  7. Re: isnumeric() function?

    CoL <col@mportal.hu> — 2004-04-30T07:14:32Z

    hi,

Yudie wrote:

 > What is isnumeric function in postgresql?
 > I'm using psql version 7.2.2
 > thanks

probably somebody write a function called isnumeric for you :) So it 
must be a user defined function.

C.

  8. Re: Permissions not working

    Pallav Kalva <pkalva@deg.cc> — 2004-04-30T13:37:25Z

    Tom Lane wrote:

>Pallav Kalva <pkalva@deg.cc> 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 majordomo@postgresql.org
>
>  
>
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)

  9. Re: isnumeric() function?

    Jeff Eckermann <jeff_eckermann@yahoo.com> — 2004-04-30T14:11:45Z

    --- Yudie <yudie@axiontech.com> wrote:
> What is isnumeric function in postgresql? 
> I'm using psql version 7.2.2
> thanks
> Yudie

I don't think that function is included as such.  But
you could do something like:

CREATE FUNCTION isnumeric(text) RETURNS boolean AS '
SELECT $1 ~ ''^[0-9]+$''
' LANGUAGE 'sql';

Note that you would need to create this function for
every parameter datatype that you would intend to use,
or else be prepared to cast your input datatype as
appropriate.

> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)



	
		
__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs  
http://hotjobs.sweepstakes.yahoo.com/careermakeover

  10. Re: isnumeric() function?

    Yudie <yudie@axiontech.com> — 2004-04-30T15:29:16Z

    Great the function works, but what does it means?
SELECT $1 ~ ''^[0-9]+$''

Yudie 

----- Original Message ----- 
From: "Jeff Eckermann" <jeff_eckermann@yahoo.com>
To: "Yudie" <yudie@axiontech.com>; <pgsql-sql@postgresql.org>
Sent: Friday, April 30, 2004 9:11 AM
Subject: Re: [SQL] isnumeric() function?


--- Yudie <yudie@axiontech.com> wrote:
> What is isnumeric function in postgresql? 
> I'm using psql version 7.2.2
> thanks
> Yudie

I don't think that function is included as such.  But
you could do something like:

CREATE FUNCTION isnumeric(text) RETURNS boolean AS '
SELECT $1 ~ ''^[0-9]+$''
' LANGUAGE 'sql';

Note that you would need to create this function for
every parameter datatype that you would intend to use,
or else be prepared to cast your input datatype as
appropriate.

> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)





__________________________________
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs  
http://hotjobs.sweepstakes.yahoo.com/careermakeover 

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

  11. Re: Permissions not working

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-04-30T16:19:39Z

    Pallav Kalva <pkalva@deg.cc> 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

  12. Re: isnumeric() function?

    Frank Bax <fbax@sympatico.ca> — 2004-04-30T16:24:03Z

    At 11:29 AM 4/30/04, Yudie wrote:
>Great the function works, but what does it means?
>SELECT $1 ~ ''^[0-9]+$''
>
>Yudie


The ~ is a pattern matching operator.
         ^ matches beginning of string
         [0-9] matches any numeric digit 0 thru 9.
         + matches one or more occurrences of what came before (digits in 
this case)
         $ matches end of string
The ^ and $ are important - if they were left out, the pattern would match 
a string containing both numeric and non-numeric data.

You can change the + to * if you decide that an empty string should be 
considered numeric.

Frank

  13. Re: Permissions not working

    Pallav Kalva <pkalva@deg.cc> — 2004-04-30T18:00:51Z

    Tom Lane wrote:

>Pallav Kalva <pkalva@deg.cc> 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)

  14. Re: Permissions not working

    Peter Eisentraut <peter_e@gmx.net> — 2004-05-01T21:13:45Z

    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;.

  15. \D TO FILE

    Eric Anderson Vianet SAO <eric@vianet-express.com.br> — 2004-05-03T11:49:03Z

        How could I record the ´ \d table ´ command to a file?

    sds


    Eric Anderson
    CPD Via Net SAO
    11-66432800

  16. Re: \D TO FILE

    Geoffrey <esoteric@3times25.net> — 2004-05-03T12:32:40Z

    Eric Anderson Vianet SAO wrote:
>     How could I record the ´ \d table ´ command to a file?

echo '\d' | psql rnd > outputfile

-- 
Until later, Geoffrey                     Registered Linux User #108567
Building secure systems in spite of Microsoft

  17. Re: \D TO FILE

    Paul Thomas <paul@tmsl.demon.co.uk> — 2004-05-03T12:34:34Z

    On 03/05/2004 12:49 Eric Anderson Vianet SAO wrote:
>     How could I record the ´ \d table ´ command to a file?

\o file

HTH


-- 
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for 
Business             |
| Computer Consultants         | 
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

  18. Re: [SPAM] Re: Permissions not working

    Pallav Kalva <pkalva@deg.cc> — 2004-05-03T13:33:01Z

    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 .

---------------------------------------------------------------------------------------
[pkalva@timmy 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
[pkalva@timmy 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=>

  19. Re: [SPAM] Re: Permissions not working

    Tom Lane <tgl@sss.pgh.pa.us> — 2004-05-03T14:23:23Z

    Pallav Kalva <pkalva@deg.cc> 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

  20. Re: [SPAM] Re: Permissions not working

    Pallav Kalva <pkalva@deg.cc> — 2004-05-03T15:06:01Z

    >>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.

--------------------------------------------------------------------------------------------------------
[pkalva@timmy 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
[pkalva@timmy 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
[pkalva@timmy 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=>

  21. Re: \D TO FILE

    Arne Stölck <stolck@web.de> — 2004-05-03T15:36:51Z

    Autre variante

psql nom_base -c '\d nom_table' > nom_fichier

	Arne


On Mon, 3 May 2004, Paul Thomas wrote:

>
> On 03/05/2004 12:49 Eric Anderson Vianet SAO wrote:
> >     How could I record the ´ \d table ´ command to a file?
>
> \o file
>

  22. Re: [SPAM] Re: Permissions not working

    Peter Eisentraut <peter_e@gmx.net> — 2004-05-03T17:55:56Z

    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.

  23. Re: [SPAM] Re: Permissions not working

    Pallav Kalva <pkalva@deg.cc> — 2004-05-03T18:28:19Z

    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)