Re: isnumeric() function?

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

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.

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.

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.

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

Pallav Kalva <pkalva(at)deg(dot)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

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.

Tom Lane wrote:

>Pallav Kalva <pkalva(at)deg(dot)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(at)postgresql(dot)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)

--- Yudie <yudie(at)axiontech(dot)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(at)postgresql(dot)org)

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

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

Yudie

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

--- Yudie <yudie(at)axiontech(dot)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(at)postgresql(dot)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

Pallav Kalva <pkalva(at)deg(dot)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

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

Tom Lane wrote:

>Pallav Kalva <pkalva(at)deg(dot)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)

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

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

sds

Eric Anderson
CPD Via Net SAO
11-66432800

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

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

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(at)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(at)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=>

Pallav Kalva <pkalva(at)deg(dot)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

>>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(at)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(at)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(at)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=>

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
>

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.

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)