[pgadmin-support] ENUM type script in pgAdmin 1.14.0
Hi All,
I think there is a bug in "CREATE" script for ENUM types created in public
schema.
When You execute command:
CREATE TYPE public."MyEnum" AS ENUM
('first','second', 'middle', 'last');
and You use "Create Script" functionality in pgAdmin, the script looks like
this:
-- Type: " MyEnum "
-- DROP TYPE " MyEnum ";
CREATE TYPE " MyEnum " AS ENUM
(' first ',
' second ',
' middle ',
' last ' );
ALTER TYPE " MyEnum "
OWNER TO postgres;
It seems there is no schema qualified name. It appears regardles search_patch
setting. I've tried in different schema and every thing was OK.
This is my fist post, so please don't yell on me ;)
regards,
Bartek
Re: [pgadmin-support] ENUM type script in pgAdmin 1.14.0
Thanks for reply,
Maybe my post was not clear, but this bug occures only for public schema.
Please try create enum in "public" not in "a b"
Regards,
Bartek
Pozdrawiam,
Bartek
--- Oryginalna wiadomo?? ---
Od: Guillaume Lelarge [mailto: guilla...@lelarge.info]
Wys?ane: Monday, September 26, 2011 09:02 AM
Do: bdmyt...@eranet.pl
Kopia: pgadmin-support@postgresql.org
Temat: Re: [pgadmin-support] ENUM type script in pgAdmin 1.14.0
On Sun, 2011-09-25 at 23:13 +0200, bdmyt...@eranet.pl wrote:
> Hi All,
> I think there is a bug in "CREATE" script for ENUM types created in public
> schema.
> When You execute command:
> CREATE TYPE public."MyEnum" AS ENUM
> ('first','second', 'middle', 'last');
> and You use "Create Script" functionality in pgAdmin, the script looks like
> this:
> -- Type: " MyEnum "
> -- DROP TYPE " MyEnum ";
> CREATE TYPE " MyEnum " AS ENUM
> (' first ',
> ' second ',
> ' middle ',
> ' last ' );
> ALTER TYPE " MyEnum "
> OWNER TO postgres;
> It seems there is no schema qualified name. It appears regardles search_patch
> setting. I've tried in different schema and every thing was OK.
It works for me. I get this:
CREATE TYPE "a b"."a strange datatype" AS ENUM
('a',
'b',
'c',
'd');
if the schema "a b" is not in my search_path (at connection time).
> This is my fist post, so please don't yell on me ;)
We don't yell at people (at lest, most of the time :) ).
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Re: [pgadmin-support] ENUM type script in pgAdmin 1.14.0
My search_path is set to "$user", pg_temp so I think script should contain full definition regardles search_path settings (which could change during DB life cycle). Regards Bartek Pozdrawiam, Bartek --- Oryginalna wiadomo?? --- Od: Guillaume Lelarge [mailto: guilla...@lelarge.info] Wys?ane: Monday, September 26, 2011 09:12 AM Do: bdmyt...@eranet.pl Kopia: pgadmin-support@postgresql.org Temat: Re: [pgadmin-support] ENUM type script in pgAdmin 1.14.0 On Mon, 2011-09-26 at 09:07 +0200, bdmyt...@eranet.pl wrote: > Thanks for reply, > Maybe my post was not clear, but this bug occures only for public schema. > Please try create enum in "public" not in "a b" public is most likely in your search_path, so pgAdmin won't display it. pgAdmin only displays the schema when it's not in your search_path. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
[pgadmin-support] TRUNCATE privilages visibility in pgAdmin
Hello all, I have noticed there is no possibility to add/check/revoke TRUCNATE privilege on table using GUI flags. There is no flag TRUNCATE in privilege list and it is not visible in SQL script window too. The only way to see TRUNCATE privilege is to check table properties and look for "D" privilage in the ACL. It is not possible to manage this privilage using Privilege Wizard (on "Tables" node), but I have noticed it is possible to manage default TRUNCATE privileges for database and schema. pgAdmin 1.14.1 - Windows XP 32bit Regards, Bartek Pozdrawiam, Bartek
[pgadmin-support] Tables stat refresh error
Hi all, I think there is a bug in refresh table stats functionality. Scenario is: 1. Click on "Tables" node in DB objects tree 2. Click on "Stats" tab - You can see correct stats for all tables in schema 3. Click on particular table in DB objects tree (stats has been changed to present selected table stats) 4. Expand selected table node in DB objects 5. Return to "Tables" node in the same schema (stats has been changed to present all tables stats) 6. Click Refresh button in button menu - here comes the bug: Stats are changed to present expanded table stats not all tables - as before refresh Additionally , when You expand other tables nodes (do not select but only expand) stats window will present stats for last expanded table. For me it is confusing because selected node is "Tables" still. pgAdmin 1.14.1 on Win XP sp3 - 32 bit Regards, Bartek
[pgadmin-support] Problem with selection of table column type
Hello All, I found problem with dynamic filtering of column types during tabele creation/column modification using GUI. It is impossible to start typing column type, it is only possible to select column type form the drop down menu. Field is not "read only", but it is not possible to type any letter or modify existing text (e.g. previously selected from dropdown menu) - keyboard entry is ignored by this field. I have noticed this on linux, it works perfect on Windows, I have no idea how it works on other OS. I think dynamic filtering/dynamic text append based on keyboard input (like on Windows) makes devs live easier, espacially when there's a lot of types to scroll to find the correct one. OS: Mandriva Linux 2011 64bit pgAdmin 1.14.1 regards, Bartek
[pgadmin-support] feature request - objects definition comparer
HI All, I think it is good idea to implement object comparer. It could be used to compare objects definitions in developer enviroment and production enviroment. Right now I need to manually extract definitions to separete sql files (or use pg_dump) and then comapre files with external tool. Integrated functionality could allow to sych object definitions accorss databases. Maybe in future data comparer will be implemented also :) Regards, Bartek
[pgadmin-support] column level privilages error
HI all, I think, there is little bug in column privilage mechanism. The bug occures when You try to set column privilages using user name who has no rights to do this. For table there is no tab "Privilages", for columns it exists and accepts configuration. Even when You accept Your configuration (OK button) there is no error/warning message. It looks like new configuration has been fully accepted. Unfortunately I spend hour or even more looking for mistake in privilages (my mistake: forgot the user I have been logged to). PostgreSQL generates warning message in that case. I think it is good idea to show this Warning to pgAdmin user or remove this tab from column properties. Regards, Bartek
Re: [pgadmin-support] column level privilages error
Hi,
thanks for quick reply.
application parameters:
1. pgAdmin 1.14.1 on Windows 7 Enterprise 32 bit
2. Server: PostgreSQL 9.1.2, compiled by Visual C++ build 1500, 64-bit on
Windows Server 2008 R2 Standard
scenario:
1. as user postgres create table
Table script (table is created before by postgres):
CREATE TABLE pdi."tblTest"
(
"RowId" serial NOT NULL),
"RowValue" text,
"NewColumn" text,
CONSTRAINT "tblTest_pkey" PRIMARY KEY ("RowId" )
)
WITH (
OIDS=FALSE
);
ALTER TABLE pdi."tblTest"
OWNER TO postgres;
GRANT ALL ON TABLE pdi."tblTest" TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES ON TABLE pdi."tblTest" TO
"MyGroup";
after table is created ACL looks like:
ACL {postgres=arwdDxt/postgres,MyGroup=arwdx/postgres}
2. log in as different user (bdmytrak) who is member of "MyGroup" and not
member of "dbRoles"
3. navigate to schema "pdi" with granted usage to public.
4. navigate to table "tblTest"
5. as user bdmytrak I try to change column level privilages using Column
properties window on "NewColumn" node.
6. pgAdmin generates script: GRANT ALL("NewColumn") ON pdi."tblTest" TO GROUP
"dbRoles"; - this is of course correct scirpt.
7. press OK button
8. no error/waring message is shown - I assume script has been executed
correctly
9. check ACL or privilages - nothing has been changed, no ACL is displayed for
column, and table script is the same.
to check it works I changed column level privilages using postgres user and:
1. ACL is set to {dbRoles=arwx/postgres}
2. Column script has been changed to
-- Column: "NewColumn"
-- ALTER TABLE pdi."tblTest" DROP COLUMN "NewColumn";
ALTER TABLE pdi."tblTest" ADD COLUMN "NewColumn" text;
GRANT SELECT("NewColumn"), UPDATE("NewColumn"), INSERT("NewColumn"),
REFERENCES("NewColumn") ON pdi."tblTest" TO "dbRoles";
3. Table scrip has been changed to:
-- Table: pdi."tblTest"
-- DROP TABLE pdi."tblTest";
CREATE TABLE pdi."tblTest"
(
"RowId" integer NOT NULL DEFAULT nextval('pdi."tblTest_RowId_seq"'::regclass),
"RowValue" text,
"NewColumn" text,
CONSTRAINT "tblTest_pkey" PRIMARY KEY ("RowId" )
)
WITH (
OIDS=FALSE
);
ALTER TABLE pdi."tblTest"
OWNER TO postgres;
GRANT ALL ON TABLE pdi."tblTest" TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES ON TABLE pdi."tblTest" TO
"MyBroup";
GRANT SELECT("NewColumn"), UPDATE("NewColumn"), INSERT("NewColumn"),
REFERENCES("NewColumn") ON pdi."tblTest" TO "dbRoles";
When You execute script GRANT ALL("NewColumn") ON pdi."tblTest" TO GROUP
"dbRoles" using Query Tool (user bdmytrak) then waring message is dislpayed and
no changes to DB is made;
This is WARNING not ERROR message.
This is described in postgres documentation
(http://www.postgresql.org/docs/9.1/static/sql-grant.html)
" When a non-owner of an object attempts to GRANT privileges on the object, the
command will fail outright if the user has no privileges whatsoever on the
object. As long as some privilege is available, the command will proceed, but
it will grant only those privileges for which the user has grant options. The
GRANT ALL PRIVILEGES forms will issue a warning message if no grant options are
held, while the other forms will issue a warning if grant options for any of
the privileges specifically named in the command are not held. (In principle
these statements apply to the object owner as well, but since the owner is
always treated as holding all grant options, the cases can never occur.) "
Hope this will help You.
Regards,
Bartek
--- Oryginalna wiadomo?? ---
Od: Guillaume Lelarge [mailto: guilla...@lelarge.info]
Wys?ane: Monday, January 23, 2012 09:06 PM
Do: bdmyt...@eranet.pl
Kopia: pgadmin-support@postgresql.org
Temat: Re: [pgadmin-support] column level privilages error
On Thu, 2012-01-19 at 14:13 +0100, bdmyt...@eranet.pl wrote:
[...]
> I think, there is little bug in column privilage mechanism. The bug occures
> when You try to set column privilages using user name who has no rights to do
> this.
> For table there is no tab "Privilages", for columns it exists and accepts
> configuration. Even when You accept Your configuration (OK button) there is
> no error/warning message. It looks like new configuration has been fully
> accepted.
> Unfortunately I spend hour or even more looking for mistake in privilages (my
> mistake: forgot the user I have been logged to).
> PostgreSQL generates warning message in that case. I think it is good idea to
> show this Warning to pgAdmin user or remove this tab from column properties.
I don't think I understand what you mean. I tried to connect as an
unprivileged user, and, if I try to add/change a permission, I always
get the PostgreSQL error denying me to do it.
Maybe I didn't undertand what you meant.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
Re: [pgadmin-support] column level privilages error
Thanks,
I manually edited table script (there was DEFAULT nextaval...) so I missed last
parenthesis - my fault.
I agree pgAdmin shouldn't display all warnings, but I think some of them
shouldn't be ignerd - like this one.
dbRoles is an another role like MyGroup.
Thanks for patch.
Regards,
Bartek
--- Oryginalna wiadomo?? ---
Od: Guillaume Lelarge [mailto: guilla...@lelarge.info]
Wys?ane: Wednesday, January 25, 2012 11:11 PM
Do: bdmyt...@eranet.pl
Kopia: pgadmin-support@postgresql.org
Temat: Re: [pgadmin-support] column level privilages error
On Wed, 2012-01-25 at 13:01 +0100, bdmyt...@eranet.pl wrote:
> Hi,
> thanks for quick reply.
> application parameters:
> 1. pgAdmin 1.14.1 on Windows 7 Enterprise 32 bit
> 2. Server: PostgreSQL 9.1.2, compiled by Visual C++ build 1500, 64-bit on
> Windows Server 2008 R2 Standard
> scenario:
> 1. as user postgres create table
> Table script (table is created before by postgres):
> CREATE TABLE pdi."tblTest"
> (
> "RowId" serial NOT NULL),
There's one harmful parenthesis here.
> "RowValue" text,
> "NewColumn" text,
> CONSTRAINT "tblTest_pkey" PRIMARY KEY ("RowId" )
> )
> WITH (
> OIDS=FALSE
> );
> ALTER TABLE pdi."tblTest"
> OWNER TO postgres;
> GRANT ALL ON TABLE pdi."tblTest" TO postgres;
> GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES ON TABLE pdi."tblTest" TO
> "MyGroup";
> after table is created ACL looks like:
> ACL {postgres=arwdDxt/postgres,MyGroup=arwdx/postgres}
> 2. log in as different user (bdmytrak) who is member of "MyGroup" and not
> member of "dbRoles"
What is dbRoles? first time I heard of it.
> 3. navigate to schema "pdi" with granted usage to public.
> 4. navigate to table "tblTest"
> 5. as user bdmytrak I try to change column level privilages using Column
> properties window on "NewColumn" node.
> 6. pgAdmin generates script: GRANT ALL("NewColumn") ON pdi."tblTest" TO GROUP
> "dbRoles"; - this is of course correct scirpt.
> 7. press OK button
> 8. no error/waring message is shown - I assume script has been executed
> correctly
PostgreSQL itself doesn't send back an error. It just adds a WARNING
message in its log:
WARNING: no privileges were granted for column "NewColumn" of relation
"tblTest"
For the record, pgAdmin doesn't show WARNING message (for example, I
think you'll agree we don't want to bother the user with auto creation
of sequence when a user adds a column of pseudo-type serial, or with the
auto creation of index when a user adds a primary key).
BTW, Dave hacked a quick patch during char(11) this summer to display
such notice. This will be a nice addition to the next release. But in
the meantime, there's nothing I can do.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org
Re: [pgadmin-support] column level privilages error
PostgreSQL provides Error code 01007 ( privilege_not_granted ) and 01006 ( privilege_not_revoked ), is it possible to handle these exceptions? I am not sure if codes are used at present (according to documentation: " Some are not actually used at present, but are defined by the SQL standard ." source: http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html). Or simply remove this tab (like on table level) if user is not allowed to change privilages... Regards, Bartek --- Oryginalna wiadomo?? --- Od: Guillaume Lelarge [mailto: guilla...@lelarge.info] Wys?ane: Thursday, January 26, 2012 10:14 PM Do: bdmyt...@eranet.pl Kopia: pgadmin-support@postgresql.org Temat: Re: [pgadmin-support] column level privilages error On Thu, 2012-01-26 at 08:20 +0100, bdmyt...@eranet.pl wrote: > Thanks, > I manually edited table script (there was DEFAULT nextaval...) so I > missed last parenthesis - my fault. > I agree pgAdmin shouldn't display all warnings, but I think some of > them shouldn't be ignerd - like this one. Well, then, the question is: how do we know which one we should display? trying to guess by comaring sentences won't work. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com PostgreSQL Sessions #3: http://www.postgresql-sessions.org -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
Re: [pgadmin-support] column level privilages error
You handle it somehow for tables (there is no privilage tab in table properies
when You cannot change privilages). I suppose it is done based on ACL for table.
This behaviour is not symmetric - works on tables and does not work on columns.
It leads to misunderstandings, just like in my case. I was sure privilages has
been granted (no error/warning message has been displayed).
I also think it is possible to recognize user ability to change column level
privilages based on ACL (WITH GRANT - signed as star in ACL).
If the user has privilages WITH GRANT OPTION, eg.
GRANT UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE public."tblTest" TO
user;
GRANT SELECT ON TABLE public."tblTest" TO user WITH GRANT OPTION;
he is allowed to grant select on columns of this table for another user.
Interesting thing is that, when You (as "user" from my example) try to execute:
GRANT ALL ("Column1") ON public."tblTest" TO public;
then only SELECT privilage on "Column1" is granted - as it is expected based on
"user" privilages.
BTW PostgreSQL generates NOTICE for auto creation of sequence for pseudo-type
serial not WARNING, so maybe it is good idea to treat WARNINGS in the same way
as ERRORS?
Hope You will find elegant solution.
Regards,
Bartek
--- Oryginalna wiadomo?? ---
Od: Guillaume Lelarge [mailto: guilla...@lelarge.info]
Wys?ane: Monday, January 30, 2012 05:17 PM
Do: bdmyt...@eranet.pl
Kopia: pgadmin-support@postgresql.org
Temat: Re: [pgadmin-support] column level privilages error
On Mon, 2012-01-30 at 13:13 +0100, bdmyt...@eranet.pl wrote:
> PostgreSQL provides Error code 01007 (privilege_not_granted) and 01006
> (privilege_not_revoked), is it possible to handle these exceptions?
We never use the error codes. We simply display the error messages from
the server.
And doing an exception for some error codes doesn't seem a good idea.
> I am not sure if codes are used at present (according to
> documentation: "Some are not actually used at present, but are defined
> by the SQL standard." source:
> http://www.postgresql.org/docs/9.1/static/errcodes-appendix.html).
>
>
> Or simply remove this tab (like on table level) if user is not allowed
> to change privilages...
Well, it would be great to be able to use the privileges and make sure
the user cannot do something in the UI that he's not allowed on the
server. When someone will want to do something like this, he'll need to
have a complete patch. Not only the column level privileges. And it
would be a major work AFAICT.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
PostgreSQL Sessions #3: http://www.postgresql-sessions.org
--
Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-support
[pgadmin-support] Custom Auto Vacuum factors
Hi, I executed SQL like this one: ALTER TABLE "public"."tblTest" SET ( autovacuum_vacuum_scale_factor = 0.002, autovacuum_analyze_scale_factor = 0.001 ); to set custom autovacuum parameters. pgAdmin in table property tab shows 0 for vacuum and analyze scale factors instead of 0.002 and 0.001. Script for table is generated properly: ... WITH ( OIDS=FALSE, autovacuum_enabled=true, autovacuum_vacuum_scale_factor=0.002, autovacuum_analyze_scale_factor=0.001, toast.autovacuum_enabled=true ); pgAdmin III 1.14.1 on Windows 7. Regards, Bartek
Re: [pgadmin-support] Custom Auto Vacuum factors
I tried to capture a screen shot and I changed language to en_US, then pgAdmin displayed proper values. When I changed it back to pl_PL (or default) I saw 0 again. So I think it could be related somehow with localization. TOAST autovacuum has been enabled previously, so my script enables only autovacuum . PostgreSQL 9.1.2, compiled by Visual C++ build 1500, 64-bit on Windows Serwer 2008 R2 Standard. Regards, Bartek --- Oryginalna wiadomo?? --- Od: Guillaume Lelarge [mailto: guilla...@lelarge.info] Wys?ane: Wednesday, February 1, 2012 09:58 AM Do: bdmyt...@eranet.pl Kopia: pgadmin-support@postgresql.org Temat: Re: [pgadmin-support] Custom Auto Vacuum factors On Wed, 2012-02-01 at 09:02 +0100, bdmyt...@eranet.pl wrote: > Hi, > I executed SQL like this one: > ALTER TABLE "public"."tblTest" SET ( > autovacuum_vacuum_scale_factor = 0.002, > autovacuum_analyze_scale_factor = 0.001 > ); > to set custom autovacuum parameters. pgAdmin in table property tab shows 0 > for vacuum and analyze scale factors instead of 0.002 and 0.001. Script for > table is generated properly: > ... > WITH ( > OIDS=FALSE, > autovacuum_enabled=true, > autovacuum_vacuum_scale_factor=0.002, > autovacuum_analyze_scale_factor=0.001, > toast.autovacuum_enabled=true > ); > pgAdmin III 1.14.1 on Windows 7. I don't have the issue. It displays the right value. Widgets are disabled because I didn't set autovacuum_enabled. What I find weird is that you have autovacuum_enabled and toast.autovacuum_enabled set to true with only the SQL script you showed. I don't have that. I only have it if I execute also : ALTER TABLE "public"."tblTest" SET ( autovacuum_enabled = true, autovacuum_vacuum_scale_factor = 0.002, autovacuum_analyze_scale_factor = 0.001, toast.autovacuum_enabled = true ); What version of PostgreSQL do you use? on which OS? -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com PostgreSQL Sessions #3: http://www.postgresql-sessions.org -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
[pgadmin-support] trigger node refresh error
Hi, I found strange GUI behaviour during enabling/disabling trigger. When You disable trigger under the table node (trigger node is extended to prestent trigger function) then the trigger function node is duplicated, when You enable this trigger again (trigger node is still extended) then the trigger function node is duplicated again. This could continue over and over again until trigger node refresh. When You refresh trigger node then "ghost" nodes dispire. All these nodes work as normal node (e.g. present SQL definition of trigger function, double click for details works, etc.). Please take a look at screen shot (trigger has the same name as trigger function - this is intended) pgAdmin 1.14.1 Win7 32bit Regards, Bartek Capture.PNG Description: Binary data -- Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgadmin-support
