Thread: Confused about rights

Confused about rights

From
Perry The Cynic
Date:
Greetings,

I am just starting to use postgresql (7.0.2, on Debian), and I am rather
confused about how to use its access rights (grant/revoke). Two questions
in particular:

(1) I created a database and table as a super-user, then started to GRANT
away. The first thing I noticed is that UPDATE and DELETE rights are
actually the same in postgresql - did I get that right? So I can't actually
give the right to change anything in a table without also giving the right
to completely erase all rows from it?

(2) The COPY command documentation says that COPY ... FROM STDIN requires
"insert OR update" rights to the table. I tried to set up a non-privileged
user with just INSERT rights on a table, but COPY rejects an attempt to
COPY unless I (also, or just) grant UPDATE rights to that user. I can use
INSERT commands just fine with just INSERT rights, but not COPY. Is that a
feature or a bug? (It is definitely annoying - I wanted to set up a "least
privileged" account for a web server to just dump data into the table, so
it can't possibly change or delete anything once it's stored there.)

I tried to browse/search the mailing list archives and "interactive
documentation", but I guess I don't know the magic keywords to look for...

Thanks
  -- perry
---------------------------------------------------------------------------
Perry The Cynic                                             perry@cynic.org
To a blind optimist, an optimistic realist must seem like an Accursed Cynic.
---------------------------------------------------------------------------


sequence primary key

From
Virginie Garcia
Date:
Hi all,

my question is about sequences and primary key of a table.
More details : I create a sequence and a table with these lines :

CREATE SEQUENCE "my_id" INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1
CACHE 1;

CREATE TABLE "toto" (
    "toto_id" integer PRIMARY KEY DEFAULT nextval('"my_id"'),
    "db" text);

in a a sql file.
In fact, when I fill database I would like "toto_id" field to be filled
directly by sgdb by using sequence "my_id" but not by me.
This way doesn't work. It's perhaps a big mistake !!!
How is-it possible, so ?
Thanks a lot.

Virginie.

RE: sequence primary key

From
"Robby Slaughter"
Date:
Virginie,

Sounds like you could just user the SERIAL data type. Try:

CREATE TABLE toto (
   toto_id  SERIAL PRIMARY KEY
   db       TEXT);

Then whenver you do an insert...

  INSERT INTO toto(db) VALUES ('and your little dog too!');

...you'll find that toto_id automagically increments for you.

Good luck. Hope this helps.

-Robby

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Virginie Garcia
Sent: Tuesday, July 17, 2001 10:44 AM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] sequence primary key


Hi all,

my question is about sequences and primary key of a table.
More details : I create a sequence and a table with these lines :

CREATE SEQUENCE "my_id" INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 START 1
CACHE 1;

CREATE TABLE "toto" (
    "toto_id" integer PRIMARY KEY DEFAULT nextval('"my_id"'),
    "db" text);

in a a sql file.
In fact, when I fill database I would like "toto_id" field to be filled
directly by sgdb by using sequence "my_id" but not by me.
This way doesn't work. It's perhaps a big mistake !!!
How is-it possible, so ?
Thanks a lot.

Virginie.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: sequence primary key

From
Jason Earl
Date:
You are *very* close to having what you want.  The
trick is to remember that the a "default" value is
simply the value that will be inserted into your new
record if you don't supply one yourself.  In other
words, you need to make sure that you don't specify a
toto_id in your insert query.  For example your query
should look something like:

INSERT INTO toto (db) VALUES ('SOME VALUE')

And then when you select from the table you will find
that the toto_id column was filled automagically.

processdata=> SELECT * FROM toto;
 toto_id |     db
---------+------------
       1 | SOME VALUE
(1 row)

I hope this was helpful,
Jason

--- Virginie Garcia
<Virginie.Garcia@pmtg.u-bordeaux2.fr> wrote:
> Hi all,
>
> my question is about sequences and primary key of a
> table.
> More details : I create a sequence and a table with
> these lines :
>
> CREATE SEQUENCE "my_id" INCREMENT 1 MINVALUE 1
> MAXVALUE 2147483647 START 1
> CACHE 1;
>
> CREATE TABLE "toto" (
>     "toto_id" integer PRIMARY KEY DEFAULT
> nextval('"my_id"'),
>     "db" text);
>
> in a a sql file.
> In fact, when I fill database I would like "toto_id"
> field to be filled
> directly by sgdb by using sequence "my_id" but not
> by me.
> This way doesn't work. It's perhaps a big mistake
> !!!
> How is-it possible, so ?
> Thanks a lot.
>
> Virginie.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/

Re: sequence primary key

From
Tom Lane
Date:
Jason Earl <jdearl@yahoo.com> writes:
> ... you need to make sure that you don't specify a
> toto_id in your insert query.  For example your query
> should look something like:

> INSERT INTO toto (db) VALUES ('SOME VALUE')

> And then when you select from the table you will find
> that the toto_id column was filled automagically.

> processdata=> SELECT * FROM toto;
>  toto_id |     db
> ---------+------------
>        1 | SOME VALUE
> (1 row)

One way to make this easier is to put the columns that are normally
filled by a DEFAULT at the end of the table, not the start.  For
example, if you'd made "db" the first column then it'd have sufficed
to write

    INSERT INTO toto VALUES ('SOME VALUE')

with the same results as above.

However, a lot of people say it is good practice to write an explicit
list of the column names you are supplying in *every* INSERT, whether
you think you are skipping over defaulted columns or not.  This makes
your code less likely to break when you rearrange the database layout.

            regards, tom lane