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] Problem in SQL Trigger
Can you tell us about the postgresql versions in 7.3 and 9.0 also post the actuall error message from postgresql. regds mallah. Ramesh Patel wrote: Hi i have one problem in Trigger. this trigger alread work on Red Hat Linux 7.3 but now i shift to RHL9.0 in RHL 9.0 not working . in this problem in ROUND function. but how to i slove this i dont know. so please help me. This is Function and Trigger. /// Function Start// CREATE FUNCTION add_issue_fun() returns opaque as' BEGIN UPDATE mtrl_mst set balstk_cs = balstk_cs - NEW.issueqty_cs, balstk_mt = round( cast((balstk_mt - NEW.issueqty_mt) as numeric ),4) where mtrl_mst.mtrl_code = NEW.mtrl_code; UPDATE rcpt_detail set consqty_cs=consqty_cs + NEW.issueqty_cs WHERE rcpt_detail.batch_code = NEW.batch_code and rcpt_detail.mtrl_code = NEW.mtrl_code and rcpt_detail.loc_code = NEW.loc_code; return NULL; END;' language 'plpgsql'; /// Function End// ///Trigger Start // CREATE TRIGGER add_issue_trg AFTER INSERT ON issue_detail FOR EACH ROW EXECUTE PROCEDURE add_issue_fun(); /// Trigger End// Thanking Ramesh Patel Computer Dept. Banasdairy, Palanpur ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] isnumeric() function?
--- Yudie <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]) __ 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
Re: [SQL] isnumeric() function?
Great the function works, but what does it means? SELECT $1 ~ ''^[0-9]+$'' Yudie - Original Message - From: "Jeff Eckermann" <[EMAIL PROTECTED]> To: "Yudie" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, April 30, 2004 9:11 AM Subject: Re: [SQL] isnumeric() function? --- Yudie <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]) __ 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
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] isnumeric() function?
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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
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] Performance issue
"Michael L. Hostbaek" <[EMAIL PROTECTED]> writes: > I cron script is being run every night (while very low db activity), > that deletes all rows from the table, and injects a bunch of new data... You should vacuum in between ... or even better, do the deletion with TRUNCATE. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Use arrays or not?
Just some comments from my experience:
PgSQL Arrays are mainly for modeling arrays not relations.
Arrays can be very useful if they are not ever gonna be treated
as relations and if performance is an issue (e.g. dynamic graphs, on the
fly statistics etc..).
Also (besides other solutions) int[] arrays is a handy way of implementing
tree structures in a database.
For your case as you set it the XML arrays will make your life difficult.
O kyrios Josh Berkus egrapse stis Apr 29, 2004 :
> Roelant,
>
> Yours is not a performance question, so I'm crossing it over to SQL for advice
> on database design.
>
> > I am building an application using postgresql to store XML-records. There
> > is a debate within the group of developers about the best way to store our
> > data. I hope you can help us make a decision.
> >
> > The data consists of XML-records, with a lot of XML-fields. I want to store
> > the XML as it is, so taking the information from the XML-records and then
> > storing it in a different-from-XML-format is not an option.
> >
> > Each XML-record describes data about one book. If an update of bookdata
> > comes, the XML itself is not changed, but a new XML-record is stored with
> > the updated data. Via a complex scheme of combining a base record and its
> > updates, the final dataset is produced that is used in the application.
> >
> > There are different XML-formats that need to be combined. Right now, we can
> > handle three different XML-formats, each with its own structure (but all
> > describing book-data).
> >
> > Searching is done via a simple table lookup on three different fields:
> > title, author and subject. The data for these fields is extracted from the
> > database. Each book has a unique identifier (EAN13, derivative of ISBN).
> >
> > Here is one way to organize the database:
> > table title:
> > TITLE | EAN13, indexing on TITLE
> >
> > table author:
> > AUTHOR | EAN13, indexing on AUTHOR
> >
> > table subject:
> > SUBJECT | EAN13, indexing on SUBJECT.
>
> This is a *very* strange way of setting up your database. Are you new to
> Relational Databases and SQL? If so, I'd recommend starting with a book on
> relational database design.
>
> Either that, or you're a victim of UML design.
>
> If only one author, title and subject are allowed per book, you should have:
>
> table books
> EAN13 | TITLE | AUTHOR | SUBJECT
>
> > Finally:
> > table record:
> > EAN13 | ARRAY OF XML-records.
> >
> > It's the last table that I am most curious (and worried) about, the
> > question being mainly what the optimal way of structuring that table is.
> > Option 1 is the given option: adding/deleting an XML-record for the same
> > book requires adding/deleting it to/from the array of XML-records.
> >
> > Option 2 would be something like this:
> > EAN13 | XML-record
> > where, if a book has several records describing it, there are multiple
> > entries of the EAN13|XML-record - pair. Adding an XML-record for the same
> > book, requires adding a new entry to the table as a whole.
>
> In my mind, there is no question that this is the best way to do things. It
> is a normalized data structure, as opposed to the arrays, which are now.
>
> >
> > So, option 1-tables look like this:
> > EAN13 | ARRAY OF XML-records
> > 0001 | {..., ..., ...}
> > 0002 | {..., ..., ...}
> >
> > Option-2 tables look like this:
> > EAN13 | ARRAY OF XML-records
> > 0001 | ...
> > 0001 | ...
> > 0002 | ...
> > 0002 | ...
> >
> > We can't decide which one is best. These are some issues we can think of:
> >
> > Indexing: For option 1, the EAN13-index remains unique, even if you have
> > multiple XML-records; for option 2 it does not, since multiple XML-records
> > are stored as multiple tuples. On the other hand, an additional internal
> > index can be used to link the several tuples of option 2 to the information
> > in the `lookup'-tables (author, title, keyword). Does any of these two
> > options increase query efficiency, ie. speed?
> >
> > Database growth: On average, the information about a book is updated three
> > times per year. In option 1, this means that the length of the table does
> > not increase, but the width does. If we choose option 2, if we have three
> > updates per book each year, the length of the table triples, but the width
> > does not. What is more costly to store for postgres, long arrays or long
> > tables?
> >
> > Integrity: Option 1 means that our software needs to keep track of all the
> > bookkeeping for arrays, since such support is quite rudimentary in
> > postgres. For example, it is hard to take out a record from the middle of
> > an array. Also, a multidimensional array, which contains for each record
> > the record itself and its type, is even harder to maintain. Option 2 has a
> > simpler datatype, so integrity can be easier inforced using the standard
> > postgres-machinery of variable-types etc.
> >
> > Arrays are non-standard SQL, and I hear that PHP-support for postgres
Re: [SQL] Check a value in array
Il gio, 2004-04-29 alle 19:56, Rod Taylor ha scritto: > On Thu, 2004-04-29 at 13:37, Marco Lazzeri wrote: > > Hi all. > > > > I have to check if a value is in an array. > > > > I've got a date array in a table and I would like to perform queries > > like: > > > > SELECT * FROM table WHERE date IN dates_array; > > If you're using 7.4 or later, try: > > SELECT * FROM table WHERE date = ANY(dates_array); > > This will work without the contrib package. Thank you Rod, I forgot to specify I'm using PostgreSQL 7.3.5. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
