Thread: Suggestion: Implicit permissions on implicitly generated objects

In the past (PgAdmin II :) ) when a Sequence was created implicitly by the use
of a "serial" column type, the permissions that were applied to the table at
creation time were applied to the sequence. The column definition was changed
to read something like

id int4 NOT NULL DEFAULT nextval('implicitlygenerated_id_seq'::text);

Now it reads: "id serial NOT NULL" which isn't bad, but is not as specific as
the translated column definition. Permissions aren't propagated as they once
were.

It is also required to manually create a primary key constraint... If the same
table create statement was issued using the query tool, the implicit sequence
would be created, a primary key constraint would be created and an index would
be created implicitly as well. One would still have to add proper permissions
to the sequence if anyone other than the owner/superuser wanted to mess with
it.

"We always used to do it that way ... " is not a good enough reason to change
how PgAdmin III behaves. I'm wondering if these behaviors ares intentional, or
if they are potential TODO items.

CG







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

> -----Original Message-----
> From: Chris Gamache [mailto:cgg007@yahoo.com]
> Sent: 29 April 2004 20:47
> To: pgadmin-hackers@postgresql.org
> Subject: [pgadmin-hackers] Suggestion: Implicit permissions
> on implicitly generated objects
>
> In the past (PgAdmin II :) ) when a Sequence was created
> implicitly by the use of a "serial" column type, the
> permissions that were applied to the table at creation time
> were applied to the sequence. The column definition was
> changed to read something like
>
> id int4 NOT NULL DEFAULT nextval('implicitlygenerated_id_seq'::text);
>
> Now it reads: "id serial NOT NULL" which isn't bad, but is
> not as specific as the translated column definition.


Hi Chris,

Both pgAdmin II and III create serial columns in the same way - ie. by
executing a query like 'CREATE TABLE foo (bar serial);'. pgAdmin III
will reverse engineer the SQL a little more intelligently though - if
the default value matches "nextval('<schema>.<table>_<column>_seq')"
then it automatically rewrites the default into a 'serial' column.

> Permissions aren't propagated as they once were.

pgAdmin never propagated permissions.

> It is also required to manually create a primary key
> constraint... If the same table create statement was issued
> using the query tool, the implicit sequence would be created,
> a primary key constraint would be created and an index would
> be created implicitly as well. One would still have to add
> proper permissions to the sequence if anyone other than the
> owner/superuser wanted to mess with it.

Sorry, you've lost me there.

Regards, Dave.

--- Dave Page <dpage@vale-housing.co.uk> wrote:
> Both pgAdmin II and III create serial columns in the same way - ie. by
> executing a query like 'CREATE TABLE foo (bar serial);'. pgAdmin III
> will reverse engineer the SQL a little more intelligently though - if
> the default value matches "nextval('<schema>.<table>_<column>_seq')"
> then it automatically rewrites the default into a 'serial' column.

Since PostgreSQL 7.3+ creates a dependency for sequences such that if a table
is dropped the sequence is dropped as well, then the table (re)creation would
require vartype "serial" for the implicit generation of the required sequence.
Is that a good shot at the reason for the change? My only cause for concern is
that I have to guess at the sequence name, based on table name. If the table
name changes (which sometimes has to happen), the sequence name will not change
to match. Would you consider placing the sequence name in a "--" comment on the
same or subsequent line?

> pgAdmin never propagated permissions.

Please accept my apologies. They say the second thing to go is the memory... :)
Wouldn't it be nice if PgAdmin III did propagate permissions for implicitly
created objects? (Unless you can think of a reason that it wouldn't make sense
to do such a thing...)

>
> > It is also required to manually create a primary key
> > constraint... If the same table create statement was issued
> > using the query tool, the implicit sequence would be created,
> > a primary key constraint would be created and an index would
> > be created implicitly as well. One would still have to add
> > proper permissions to the sequence if anyone other than the
> > owner/superuser wanted to mess with it.
>
> Sorry, you've lost me there.

I'll try to do better:

There's a simple checkbox to make a column a primary key in PgAdmin II. There's
a more complicated procedure to create a primary key in PgAdmin III within the
constraint tab. I understand that "We always used to do it that way ... " is
not a good reason to keep something the same. I guess I can see how putting all
of your constraints together in one tab is more logical than a checkbox and two
tabs (a la PgAdmin II). Was that what you were going for? ...

As I study it more, I can see how the tool flows. Since the tabs are meant to
build one-to-the-next, perhaps instead of an "OK" at the bottom of the window,
a "Next" might be just as good. Since "Primary Key" now lives in the Constraint
Tab, I'll have to visit every tab anyway.  When you get to the SQL Def you can
verify that the SQL reflects what you want done, and then you can click "OK" or
"Back" or a specific tab to make changes. You could apply that methodology to
all of the New Object Dialog Boxes. What do you think?

CG




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

> -----Original Message-----
> From: Chris Gamache [mailto:cgg007@yahoo.com]
> Sent: 30 April 2004 14:46
> To: Dave Page; pgadmin-hackers@postgresql.org
> Subject: RE: [pgadmin-hackers] Suggestion: Implicit
> permissions on implicitly generated objects
>
> --- Dave Page <dpage@vale-housing.co.uk> wrote:
> > Both pgAdmin II and III create serial columns in the same
> way - ie. by
> > executing a query like 'CREATE TABLE foo (bar serial);'.
> pgAdmin III
> > will reverse engineer the SQL a little more intelligently
> though - if
> > the default value matches "nextval('<schema>.<table>_<column>_seq')"
> > then it automatically rewrites the default into a 'serial' column.
>
> Since PostgreSQL 7.3+ creates a dependency for sequences such
> that if a table is dropped the sequence is dropped as well,
> then the table (re)creation would require vartype "serial"
> for the implicit generation of the required sequence.
> Is that a good shot at the reason for the change? My only
> cause for concern is that I have to guess at the sequence
> name, based on table name. If the table name changes (which
> sometimes has to happen), the sequence name will not change
> to match. Would you consider placing the sequence name in a
> "--" comment on the same or subsequent line?

The main reason for this is to hide the implementation details. 99% of
users don't care how a serial column works, merely that it does.

> > pgAdmin never propagated permissions.
>
> Please accept my apologies. They say the second thing to go
> is the memory... :) Wouldn't it be nice if PgAdmin III did
> propagate permissions for implicitly created objects? (Unless
> you can think of a reason that it wouldn't make sense to do
> such a thing...)

I think that is the job of the server to be honest.

> I'll try to do better:
>
> There's a simple checkbox to make a column a primary key in
> PgAdmin II. There's a more complicated procedure to create a
> primary key in PgAdmin III within the constraint tab. I
> understand that "We always used to do it that way ... " is
> not a good reason to keep something the same. I guess I can
> see how putting all of your constraints together in one tab
> is more logical than a checkbox and two tabs (a la PgAdmin
> II). Was that what you were going for? ...

Andreas refactored that bit, but I guess there are 2 main reasons -
first, it's more logical as you suggest, and secondly, the coding is a
lot more elegant.

> As I study it more, I can see how the tool flows. Since the
> tabs are meant to build one-to-the-next, perhaps instead of
> an "OK" at the bottom of the window, a "Next" might be just
> as good. Since "Primary Key" now lives in the Constraint Tab,
> I'll have to visit every tab anyway.  When you get to the SQL
> Def you can verify that the SQL reflects what you want done,
> and then you can click "OK" or "Back" or a specific tab to
> make changes. You could apply that methodology to all of the
> New Object Dialog Boxes. What do you think?

That would make them wizards rather than dialogues. I'm not against
having wizards as well, but I don't think we should have a Next button
to iterate through tabs (pga2 did in some places, but that was for
technical reasons and you couldn't click those tabs anyway) - Crystal
Reports does that and it's just plain /wrong/ :-)

Regards, Dave.