[SQL] Schema's, roles and privileges
I have a database with a schema called ISS. This is where all of our application definitions are stored. We did add 2 contribute modules (citext) and guid generator and both of these by default went to the public schema. It is our intent to not allow any access to public by our users. A few questions 1. Can I reinstall the contrib modules in the ISS schema only or do they need to be in the public schema 2. If they need to stay in the public schema and I don't want to give any insert, update, delete or select access to public, can I revoke those privileges and just give execute on the functions that were added by the contrib module. 3. If I can reinstall the contrib modules in the application schema, can I delete the public schema or does it still need to be there and I would just revoke all except for the superuser id which would be for our installer or tech support if needed. We have a separate userid for the security administrator. All of the functions that the security administrator needs are provided by a application module and they will not be directly accessing the database via a SQL utility at all. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Schema's, roles and privileges
Thanks Mike Gould "Adrian Klaver" wrote: > On Monday 30 November 2009 6:50:27 am Michael Gould wrote: >> I have a database with a schema called ISS. This is where all of our >> application definitions are stored. We did add 2 contribute modules >> (citext) and guid generator and both of these by default went to the public >> schema. It is our intent to not allow any access to public by our users. >> >> A few questions >> >> 1. Can I reinstall the contrib modules in the ISS schema only or do they >> need to be in the public schema >> >> 2. If they need to stay in the public schema and I don't want to give any >> insert, update, delete or select access to public, can I revoke those >> privileges and just give execute on the functions that were added by the >> contrib module. >> >> 3. If I can reinstall the contrib modules in the application schema, can I >> delete the public schema or does it still need to be there and I would just >> revoke all except for the superuser id which would be for our installer or >> tech support if needed. We have a separate userid for the security >> administrator. All of the functions that the security administrator needs >> are provided by a application module and they will not be directly >> accessing the database via a SQL utility at all. >> >> Best Regards >> >> >> -- >> Michael Gould, Managing Partner >> Intermodal Software Solutions, LLC >> 904.226.0978 >> 904.592.5250 fax > > From a quick look it would seem the easiest solution would be to change the > search_path in: > citext.sql.in > uuid-ossp.sql.in > These files are found in the respective contrib directories. Uninstall the > modules. Rerun make and then reinstall. > > From here: > http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html > "There is nothing special about the public schema except that it exists by > default. It can be dropped, too. " > > -- > Adrian Klaver > akla...@comcast.net > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Proper case function
All, Has anyone got a proper case function that they are willing to share. We're moving from SQL Anywhere to PostgreSQL and decided that much of our data is upper case when it really should be proper cased. The columns that we will be putting proper case data in are all defined using the contrib module citext which makes searching very nice. Best Regards Michael Gould -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Proper case function
Gary,
Based on what I read it wouldn't handle cases where the result should be
MacDonald from macdonald. There are other cases such as the sentence below
('it''s crazy! i couldn''t believe kate mcdonald, leo dicaprio, (terrence)
trent d''arby (circa the 80''s), and jada pinkett-smith all showed up to
[cHris o''donnell''s] party...donning l''oreal lIpstick! They''re heading to
o''neil''s pub later on t''nite. the_underscore_test. the-hyphen-test)
This wouldn't handle the quotes and proper case all of the words.
Best Regards
Mike Gould
"Gary Chambers" wrote:
>> Has anyone got a proper case function that they are willing to share.
We're
>> moving from SQL Anywhere to PostgreSQL and decided that much of our data
is
>> upper case when it really should be proper cased. The columns that we
will
>> be putting proper case data in are all defined using the contrib module
>> citext which makes searching very nice.
>
> Why doesn't the initcap function work as you require?
>
> -- Gary Chambers
>
> /* Nothing fancy and nothing Microsoft! */
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Michael Gould, Managing Partner
Intermodal Software Solutions, LLC
904.226.0978
904.592.5250 fax
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Issue with insert
I'm having a issue with a insert INSERT INTO iss.citystateinfo ( citystateinfoid, citystate, zipcode, cityname, statecode ) VALUES ( '31344342-3439-4135-2d32-3044462d3433', 'Orange Park,FL', '32065', 'Orange Park', 'FL' ); This inserts correctly, however when I view the data the citystate is always displayed as Orange Park (FL). Every row is displayed the same. I've used PGAdmin to look at the data, EMS for PostGres and have exported the data to Excel and they all display the data incorrectly. This is a issue for us because we want the user to type in either the zipcode if known to bring back the city and state, or the city,state to bring back the zipcode. Any idea's on why this could be happening. I'm running on Windows 7 x64 using 8.4.2 Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Issue with insert
Here is the table DDL,
To test you can either use the record below or make up anything you want.
I've changed the isscontrib.citext to varchar and text data types and the
results are the same.
Best Regards
Michael Gould
CREATE TABLE "iss"."citystateinfo" (
"citystateinfoid" UUID DEFAULT isscontrib.uuid_generate_v4() NOT NULL,
"citystate" "isscontrib"."citext" NOT NULL,
"zipcode" CHAR(10) NOT NULL,
"cityname" "isscontrib"."citext" NOT NULL,
"statecode" VARCHAR(2) NOT NULL,
"latitude" NUMERIC(12,8),
"longitude" NUMERIC(12,8),
"countrycode" VARCHAR(2),
"activeflag" CHAR(1) DEFAULT 'Y'::bpchar NOT NULL,
"createdatetime" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
"createuser" "isscontrib"."citext" DEFAULT "current_user"(),
"editdatetime" TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
"edituser" "isscontrib"."citext" DEFAULT "current_user"(),
CONSTRAINT "pk_citystateinfo" PRIMARY KEY("citystateinfoid"),
CONSTRAINT "citystateinfo_activeflag_check" CHECK (activeflag = ANY
(ARRAY['N'::bpchar, 'Y'::bpchar]))
) WITHOUT OIDS;
COMMENT ON TABLE "iss"."citystateinfo"
IS 'City State information for Mileage calculations';
COMMENT ON COLUMN "iss"."citystateinfo"."citystateinfoid"
IS 'Global Unique Identifier - CityStateInfo';
COMMENT ON COLUMN "iss"."citystateinfo"."citystate"
IS 'City State Combination in City, State';
COMMENT ON COLUMN "iss"."citystateinfo"."zipcode"
IS 'Postal Code';
COMMENT ON COLUMN "iss"."citystateinfo"."cityname"
IS 'City Name';
COMMENT ON COLUMN "iss"."citystateinfo"."statecode"
IS 'State Code';
COMMENT ON COLUMN "iss"."citystateinfo"."latitude"
IS 'Latitude';
COMMENT ON COLUMN "iss"."citystateinfo"."longitude"
IS 'Longitude';
COMMENT ON COLUMN "iss"."citystateinfo"."countrycode"
IS 'Country Code';
COMMENT ON COLUMN "iss"."citystateinfo"."activeflag"
IS 'Is Item Active?';
COMMENT ON COLUMN "iss"."citystateinfo"."createdatetime"
IS 'Time stamp record created';
COMMENT ON COLUMN "iss"."citystateinfo"."createuser"
IS 'Record Created by User';
COMMENT ON COLUMN "iss"."citystateinfo"."editdatetime"
IS 'Time stamp when record last changed';
COMMENT ON COLUMN "iss"."citystateinfo"."edituser"
IS 'Last User to change record';
CREATE INDEX "i_cityname" ON "iss"."citystateinfo"
USING btree ("citystate" "isscontrib"."citext_ops");
COMMENT ON INDEX "iss"."i_cityname"
IS 'Index by City Name';
CREATE INDEX "i_citystateinfozipcode" ON "iss"."citystateinfo"
USING btree ("zipcode");
COMMENT ON INDEX "iss"."i_citystateinfozipcode"
IS 'Index by ZipCode';
CREATE TRIGGER "insertcitystateinfo" BEFORE INSERT OR UPDATE
ON "iss"."citystateinfo" FOR EACH ROW
EXECUTE PROCEDURE "iss"."insertcitystateinfofunc"();
COMMENT ON TRIGGER "insertcitystateinfo" ON "iss"."citystateinfo"
IS 'Setup cityname column';
CREATE TRIGGER "update_timestamp_citystateinfo" BEFORE UPDATE
ON "iss"."citystateinfo" FOR EACH ROW
EXECUTE PROCEDURE "iss"."timestampfunc"();
"Michael Gould" wrote:
> I'm having a issue with a insert
>
> INSERT INTO
> iss.citystateinfo
> (
> citystateinfoid,
> citystate,
> zipcode,
> cityname,
> statecode
> )
> VALUES (
> '31344342-3439-4135-2d32-3044462d3433',
> 'Orange Park,FL',
> '32065',
> 'Orange Park',
> 'FL'
> );
>
> This inserts correctly, however when I view the data the citystate is
always
> displayed as
>
> Orange Park (FL). Every row is displayed the same. I've used PGAdmin to
> look at the data, EMS for PostGres and have exported the data to Excel and
> they all display the data incorrectly. This is a issue for us because we
> want the user to type in either the zipcode if known to bring back the
city
> and state, or the city,state to bring back the zipcode.
>
> Any idea's on why this
Re: [SQL] Issue with insert
Tom, Thanks, that's exactly what it was. Best Regards Mike Gould "Tom Lane" wrote: > Michael Gould writes: >> Here is the table DDL, > > Can't really test this since you didn't supply the source code for > those triggers: > >> CREATE TRIGGER "insertcitystateinfo" BEFORE INSERT OR UPDATE >> ON "iss"."citystateinfo" FOR EACH ROW >> EXECUTE PROCEDURE "iss"."insertcitystateinfofunc"(); > >> CREATE TRIGGER "update_timestamp_citystateinfo" BEFORE UPDATE >> ON "iss"."citystateinfo" FOR EACH ROW >> EXECUTE PROCEDURE "iss"."timestampfunc"(); > > Personally I'm suspicious that the BEFORE INSERT trigger is changing the > data. > > regards, tom lane > -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] placement of stored procedures and contrib modules
We were trying to use a separate schema to put contrib modules, stored procedures and triggers from our database schema. We called the one where we stored all of the modules isscontrib and the other iss which is where the db is stored. We've fully qualified the names of our items from isscontrib and have also included that schema in the search path. Our goal is to eventually mark isscontrib as execute only. Is there any restrictions to having objects in a separate schema? We are using the citext contrib module. It doesn't appear to work when it's in the isscontrib module. We don't get any error messages however we also don't get the results we are looking for, namely case insensitive search results. However, if we move the contrib module to the public schema, everything appears to work fine. Best Regards Mike Gould -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] strange issue with UUID data types
I'm running Windows 2008 64 bit server with Postgres 8.4.2 (also have tried Windows 7 both 32 and 64 bit). The origin database is SQL Anywhere 10. I've got several tables that have a UUID data type with isscontrib.uuid_generate_v4() as the default value. All of the tables where these are the primary key work just fine and properly create the UUID columns. I also have several child tables that call into the parent table to return the parent tables UUID. All of the child tables column which I'm returning the parent tables UUID are defined as UUID data types and no default value. On all of my machines the UUID returned from the parent tables is always 16 bytes, in fact the numbers are not truncated they don't make any sense or pattern. Now I would think that this could be a problem with my program not having the proper length to hold the temporary data but I've also run this on 2 other machines, one through teamviewer to Windows 2008 standard server with Postgres 8.4.2 loaded and it's schema's loaded from a pgdumpall. They are also running the same SQL Anywhere version 10 that we're converting the data from. When we run the same code on that machine it runs properly and the UUID returned is correct. I've tried this on his remote server and it works. Is there something I'm missing here? Is there somekind of environmental parameter that I need to set? We've been scratching our heads over this for about 10 days now trying to figure out why it works in one location and not others. Best Regards Michael Gould -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
Re: [SQL] strange issue with UUID data types
" > Given my recent history re: uuid's perhaps I shouldn't chime in here, > but can't help myself. > > get/run pg_config to see how postgres was built and installed. It may > be using ossp uuid support? I ran this and it showed that ossp uuid were turned on. This build is the windows installer from EnterpriseDB > 2. Are all the uuid libraries 64 bit? I'm not sure what EnterpriseDB uses and I'm not sure what difference this would make. I can't get it to work on either 32 bit or 64 bit Windows platforms. > 3. Has the programme been migrated from 32 bit? The program is a Win32 program Best Regards Mike Gould -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] fix uuid contrib module for Windows 64
As the subject implies, has this been fixed and is it in a currently released GA build? Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax
