[GENERAL] Help! Database restored with disabled triggers
I have database backup schema+data in text (non-compressed) format. Backup is created using "pg_dump -i -h ... -U ... -f dump.sql". I run it with "psql http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help! Database restored with disabled triggers
On Thu, Sep 24, 2009 at 12:02 AM, Adrian Klaver wrote: > On Tuesday 22 September 2009 7:28:03 pm Joe Kramer wrote: >> I have database backup schema+data in text (non-compressed) format. >> Backup is created using "pg_dump -i -h ... -U ... -f dump.sql". >> I run it with "psql > >> I can't use this text dump with pg_restore because it only accept >> archived dumps. And I am not sure that using pg_restore will solve >> disabled triggers problem. >> I need to have the backup in text format so I can open and edit it. >> >> There was a recipe earlier in this mailing list that involves writing >> a function that will enable all triggers one-by-one. But I want to do >> it a proper way, without such "hacking". >> >> What would be the solution for me? >> >> Thanks. > > What version of Postgres are you dumping from, restoring to? Which version of > pg_dump are you using? > > -- I am using client 8.1.9 to dump from server 8.3.0 (unable to use client 8.3.x) Importing to server 8.3.7. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help! Database restored with disabled triggers
On Thu, Sep 24, 2009 at 12:53 AM, Tom Lane wrote: > Joe Kramer writes: >> I have database backup schema+data in text (non-compressed) format. >> Backup is created using "pg_dump -i -h ... -U ... -f dump.sql". >> I run it with "psql > You sure they weren't disabled in the source database? AFAICS pg_dump > just duplicates the trigger state it sees in the source. > > regards, tom lane > Yes, I'm absolutely sure they are not disabled. And in the SQL dump file there are no commands that would disable them. It simply goes on to creating triggers, but in the end they are all disabled. Regards. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help! Database restored with disabled triggers
On Thu, Sep 24, 2009 at 1:33 AM, Tom Lane wrote: > Joe Kramer writes: >> On Thu, Sep 24, 2009 at 12:53 AM, Tom Lane wrote: >>> You sure they weren't disabled in the source database? > >> Yes, I'm absolutely sure they are not disabled. And in the SQL dump >> file there are no commands that would disable them. > > Better take another look for "ALTER TABLE foo DISABLE TRIGGER bar" > commands. Given the information that this is a pre-8.3 pg_dump, > that's exactly the behavior I'd expect, because it's not going to > understand the values it finds in pg_trigger.tgenabled in an 8.3 > server. > Thanks, I found DISABLE TRIGGER commands and deleted them, but wish I could find a way to make pg_dump not to add them! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to generate unique hash-type id?
Hello, I need to generate unique id which is not guessable unlike serial(integer) type. I need an id in format like md5 hash of random number. On top of that I need this id to be unique across multiple tables. Anyone had to solve this problem before? Can you post any recipes or best practices please? My questions: 1. Avoiding collisions. If I make an UNIQUE constraint and do generation of id triggered on INSERT. What if collision happens? DO I nee d to check if unique hash already exists and if not- regenerate. This looks too primitive. Is there a readily available function or methodology to do that? 2. Generating global unique id across multiple tables. How to do that? My only idea is to have separate table to keep all hashes and compare for collision against that table. Is there a better way? Maybe by creating some special serial type that is not integer but varchar? 3. what function to use to generate 64-bit random hash without much overhead to CPU? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to generate unique hash-type id?
We have bunch of servers running the app and rebuilding postgres with support for ossp_uuid on all servers is time consuming. Is there a way of doing it without third party dependency like ossp_uuid? Should I just run md5(random number), will itbe the same ?. According to description it seems that all uuid_generate_v4() does is simply generate md5 of random number. Thanks. On Fri, Jan 29, 2010 at 8:31 PM, Adrian von Bidder wrote: > > Hi, > > On Friday 29 January 2010 09.20:33 Joe Kramer wrote: >> I need to generate unique id which is not guessable unlike >> serial(integer) type. I need an id in format like md5 hash of random >> number. >> On top of that I need this id to be unique across multiple tables. > > Have a look at http://www.postgresql.org/docs/8.3/static/uuid-ossp.html > > The usual approach is that (given a sensible random generator[1]) uuid are > assumed to be unique[2]. So you don't need to check because the probability > of collisions is so small that for practical purposes you can just ignore > it. > > (If your engineer's mind balks at this, consider that you're trusting this > already when you use digital cryptography / signatures, for example https > certificates.) > > I haven't looked at this module myself, but from the experience with > generating gpg keys on an appliance: if you need lots of randomness, the > geneation of random numbers might be your bottleneck. OTOH, our platform > didn't have disks and usually there was no network traffic while your > average db server has both, and on many systems there is a hardware random > generator, so this might not be an issue. > > cheers > -- vbi > > > [1] like, for example: http://www.dilbert.com/strips/comic/2001-10-25/ > [2] you'll want v4 uuids > -- > Linus has opinions, I have opinions, everybody else has opinions, and > the only consistency here is that most of us are wrong most of the time. > -- Andrew Morton, OLS 2004 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to generate unique hash-type id?
Thanks for the answer, I am unable to use ossp_uuid due to package install and/or server rebuild requirement. So I am trying to roll my own, and digest(quote_literal(random()+random()), 'sha256'), 'hex') doesn't work: I have created this table and inserted 20 rows (two million). This is more or less now my application looks now. It uses bigserial. And I need to add some unique hash: CREATE TABLE item ( item_id bigserial NOT NULL, title character varying, CONSTRAINT pk PRIMARY KEY (item_id) ) WITH ( OIDS=FALSE ); Now I add the hash column: ALTER TABLE item ADD COLUMN hash1 character varying NOT NULL DEFAULT encode(digest(quote_literal(random()+random()), 'sha256'), 'hex'); ALTER TABLE item ADD UNIQUE (hash1); When I executed this two statements, ALTER TABLE ADD COUMN, ADD UNIQUE, after 20 seconds I got this message: NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "item_hash1_key" for table "item" ERROR: could not create unique index "item_hash1_key" DETAIL: Table contains duplicated values. * Error ** ERROR: could not create unique index "item_hash1_key" SQL state: 23505 Detail: Table contains duplicated values. So this means random()+random() is not random even within 2,000,000 iterations! If you suggest accessing /dev/urandom directly- I cannot do that because my application runs on mac,windows and linux. It would be maintenance nightmare. Any suggestions? Thanks. On Fri, Jan 29, 2010 at 10:20 PM, Adrian von Bidder wrote: > On Friday 29 January 2010 11.21:00 Joe Kramer wrote: >> We have bunch of servers running the app and rebuilding postgres with >> support for ossp_uuid on all servers is time consuming. >> Is there a way of doing it without third party dependency like >> ossp_uuid? Should I just run md5(random number), will itbe the same ? > > If you're building your own: at least use sha1 instead of md5. > > (Even md5 *should* be safe in the absence of malicious attacks, but md5 is > generally not recommended anymore.) > > Everything depends on the quality of your random numbers. I don't know how > much randomness pg's random() delivers, and as I've said I haven't looked > what the uuid module does. > > (To give you an example: if random() only delivers a random 32 bit float > value, the 160 bits of SHA-1 will not be used. You'll only use 4 billion > different values and you *will* soon get collisions.) > > If I were to roll my own, I'd just use 256 bit of /dev/random (or, depending > on the application, possibly /dev/urandom and take the risk that my values > aren't that random.) Since it's random anyway, there's no need to use a > hash. (Not sure: can a SQL function read arbitrary binary files or will a C > module be necessary?) > > Speed: just did a quick test on one machine. reading 1kB from /dev/random > takes about 1s. (constant 5MB/s disk activity with lots of seeking going > on, no hw random device.) So you'd get ca. 32 id values per second. Don't > know if that's a lot or not for your application. > > Magnus: can you elaborate on uuid not being secure? AFAICT v4 uuid are > supposed to be essentially a random number formatted in a certain way. > > cheers > -- vbi > > > -- > featured product: GNU Privacy Guard - http://gnupg.org > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to generate unique hash-type id?
Thanks for that link Depesz!
It worked, I've run ALTER TABLE with your function and didn't have collisions.
I guess it's more bulletproof because random() is called not once, but
for every character therefore reducing possibility of collision by
multitude of number of bytes in hash.
CREATE OR REPLACE FUNCTION make_random_string(string_length INT4)
RETURNS TEXT
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
possible_chars TEXT =
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
output TEXT = '';
i INT4;
pos INT4;
BEGIN
FOR i IN 1..string_length LOOP
pos := 1 + cast( random() * ( length(possible_chars) - 1) as INT4 );
output := output || substr(possible_chars, pos, 1);
END LOOP;
RETURN output;
END;
$BODY$;
CREATE TABLE item
(
item_id bigserial NOT NULL,
title character varying,
CONSTRAINT pk PRIMARY KEY (item_id)
)
WITH (
OIDS=FALSE
);
LOOP
INSERT INTO item(
title)
VALUES ('title1');
count = count+1;
EXIT WHEN count > 1000;
END LOOP;
ALTER TABLE item ADD COLUMN hash1 character varying NOT NULL DEFAULT
make_random_string(64);
ALTER TABLE item ADD UNIQUE (hash1);
Query returned successfully with no result in 2120670 ms.
It worked! No collisions on 10 million records.
Now a question. Is it okay to add calculated column this way by
specifying DEFAULT. Or I'm better using INSERT trigger? is DEFAULT
basically an internal insert trigger?
Thanks.
On Fri, Jan 29, 2010 at 10:50 PM, hubert depesz lubaczewski
wrote:
> On Fri, Jan 29, 2010 at 07:20:33PM +1100, Joe Kramer wrote:
>> I need to generate unique id which is not guessable unlike
>> serial(integer) type. I need an id in format like md5 hash of random
>> number.
>
> check this blogpost:
> http://www.depesz.com/index.php/2007/06/25/random-text-record-identifiers/
>
>> On top of that I need this id to be unique across multiple tables.
>
> just add table id to the generated id.
>
> for example: id "xxx" in table users, is globally unique (for your
> database) when you write it: "users:xxx"
>
> if, for some weird reason, you don't want to put table name on its own
> in your key (why?) then just use some dictionary table, that will link
> those keys with your table.
>
> Best regards,
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
> jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Best practice for file storage?
Hi, I need to store a lot of large files (thousands of 10-100 MB files) uploaded through my web application and I find that storing them in database as bytea field is not practical for backup purposes. My database has full backup performed every 12 hours and backup is encrypted and copied to server on another continent. Having all the heavy binary data in database will make backups impossible. So I am thinking of having a table just for metadata and file id sequence and storing the file on file system: CREATE TABLE business_logo ( file_id bigserial NOT NULL, file_name varchar, file_date timestamp, ); Storing file in path composed from serial id, e.g. file with id 2345 will be stored in /webapp/files/2/3/4/5/2345 So I can backup files separately and database backup is still quick and painless. This is very simplistic and straightforward method. I suppose there are better ways of doing it, using some virtual file system? Anyone had a similar issue with avoiding of storing large files in database, how did you solve it? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unable to access table named "user"
Hi, I have table named user (lower case) in public schema. I can access it using Pgadmin as SELECT * from "user"; SELECT * from "public.user"; SELECT * FROM public.user; I can't find any way to access this table from Jdbc. Tried to access as public.user, user in single and double quotes, nothing helps. I get exceptions like ERROR: relation "public.user" does not exist ERROR: relation "user" does not exist Also tried to execute "SET search_path TO public" beforehand in same jdbc connection, makes no difference. Is there any way? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unable to access table named "user"
Don't help either. Jdbc statement: SELECT * from "public"."user" ; Exception: ERROR: relation "public.user" does not exist On Tue, May 12, 2009 at 5:55 PM, Scott Marlowe wrote: > On Tue, May 12, 2009 at 1:26 AM, Joe Kramer wrote: >> Hi, >> >> I have table named user (lower case) in public schema. >> I can access it using Pgadmin as >> SELECT * from "user"; SELECT * from "public.user"; SELECT * FROM public.user; > > Try "public"."user" > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgAdmin III v1.6 Beta 1 Released
Thank you for Mac OSX Universal support at last ! Now the last PowerPc application is gone Universal on my Mac. Pgadmin now 4 times faster as Universal on Intel! Thanks for your great work! On 9/14/06, Dave Page wrote: I'm pleased to announce the first beta version of pgAdmin III v1.6.0 is available for download in Source, Windows, Slackware and Mac OSX formats from: http://www.pgadmin.org/download/ This is a beta version and may contain bugs - please test at your own risk and report any bugs found to [EMAIL PROTECTED] There are a huge number of improvements in pgAdmin 1.6 - for a complete list, please see the changelog at: http://www.pgadmin.org/development/changelog.php Please note that translation work is still ongoing, therefore beta releases may not include all the languages that pgAdmin eventually ships with. If you are able to assist with the translation effort, please see the status and HOWTO pages linked from: http://www.pgadmin.org/translation/ Don't forget to let me know that you are working on a translation before you start!! Regards Dave -- Dave Page pgAdmin Project Lead ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Is it possible to return custom type as proper ROW?
Pgsql 8.1.4.
I want return custom type from function as row, not as values in brackets (1,2).
I have following type and function:
CREATE TYPE new_item_return_type AS
(item_id bigint,
last_update timestamp without time zone);
CREATE OR REPLACE FUNCTION new_item( new_title int8, new_user_id int8)
RETURNS new_item_return_type AS
$BODY$
DECLARE
ret new_item_return_type%ROWTYPE;
BEGIN
INSERT INTO item (user_id,title) VALUES (new_user_id,new_title) ;
ret.item_id:= currval('item_id_seq');
SELECT time_last_update INTO ret.last_update FROM item WHERE id
=ret.item_id;
RETURN ret;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Seems like in DECLARE ret new_item_return_type%ROWTYPE;
%ROWTYPE is ignored.
When I run SELECT public.new_item(3,2);
I get :
new_item_return_type
-
"(32,"2006-10-11 10:14:39")"
I want to get:
item_id | last_update
-
32 | 1234-12-12 12:12:12
Is it possible ? I am using the wrong approach?
Thanks.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] timestamp as primary key?
Hello, I have table for online chat system that keep messages sent between users. CREATE TABLE chat_message ( message_time timestamp without time zone NOT NULL DEFAULT now(), message_body text, user_id_from bigint, user_id_to bigint, CONSTRAINT chat_message_pkey PRIMARY KEY (message_time) ) WITHOUT OIDS; I don't want to add int primary key because I don't ever need to find messages by unique id. Question: is it okay to use timestamp as primary key, or there is possibility of collision? (Meaning two processes may INSERT into table within same millisecond.) It is a web application. Thanks. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pgcrypto sha256/384/512 don't work on Redhat. Please help!
Hello,Pgcrypto SHA 256/384/512 algorithm don't work on RedHat:
db=# SELECT digest('test', 'sha1');
digest
--
\251J\217\345\314\261\233\246\034L\010s\323\221\351\207\230/\273\323
(1 row)
db=# SELECT digest('test', 'sha256');
ERROR: Cannot use "sha256": No such hash algorithmI have standard Fedora Core 4 installed with standard PostgreSQL 8.1.3Pgcrypto documentation says that it has built-in SHA256 and it should work when OpenSSL is not found:
==Without OpenSSL, public-key encryption does not work, as pgcrypto doesnot yet contain math functions for large integers. Functionality built-in OpenSSL
MD5 yes yes SHA1 yes yes SHA256/384/512 yes since 0.9.8 =Fedora core has OpenSSL 0.9.7 installed by default. And it's not possible to install
0.9.8 because of glibc conflict.I suspect pgcrypto looks for SHA256 in OpeSSL lib when it should use built-in.SHA256 is working fine on Windows but on Redhat it fails. Maybe because windows pgcrypto DLL is compiled statically with OpenSSL?
How to make pgcrypto use built-in sha256?Thanks.
Re: [GENERAL] pgcrypto sha256/384/512 don't work on Redhat. Please help!
Thanks, but I need it to work out-of-the-box, with standard installation of RedHat or Gentoo and standard PostgreSQL rpm. I am developing application with PortgreSQL and I can't tell customer to "Recompile PostgreSQL and see if it works then try to use non-openssl pgcrypto or try to compile openSSL 0.9.8." Pgcrypto depends on OpenSSL 0.9.8. And 0.9.8 is not "Stable" in RedHat and Gentoo. So you need to recompile pgcrypto/openssl anyway if you want to use it. Can I report this in PostgreSQL bug system? Regards.On 5/9/06, Marko Kreen <[EMAIL PROTECTED]> wrote: On 5/9/06, Joe Kramer <[EMAIL PROTECTED]> wrote:> Fedora core has OpenSSL 0.9.7 installed by default. And it's not possible to> install 0.9.8 because of glibc conflict. >> I suspect pgcrypto looks for SHA256 in OpeSSL lib when it should use> built-in.> SHA256 is working fine on Windows but on Redhat it fails. Maybe because> windows pgcrypto DLL is compiled statically with OpenSSL? OpenSSL vs. builtin is a compile-time decision, so the builtin codecannot be fallback at runtime.Windows code is using either builtin code or newer OpenSSL.> How to make pgcrypto use built-in sha256? You need to recompile PostgreSQL. Don't try to use non-OpenSSLpgcrypto with OpenSSL PostgreSQL, it will crash due to symbolconflict.Another variant is to try to compile separate OpenSSL 0.9.8 and compile PostgreSQL against that. So you don't need to upgradesystem OpenSSL.--marko
Re: [GENERAL] pgcrypto sha256/384/512 don't work on Redhat. Please help!
On 5/9/06, Marko Kreen <[EMAIL PROTECTED]> wrote: On 5/9/06, Joe Kramer <[EMAIL PROTECTED]> wrote:> Thanks, but I need it to work out-of-the-box, with standard installation of> RedHat or Gentoo and standard PostgreSQL rpm. >> I am developing application with PortgreSQL and I can't tell customer to> "Recompile PostgreSQL and see if it works then try to use non-openssl> pgcrypto or try to compile openSSL 0.9.8 .">> Pgcrypto depends on OpenSSL 0.9.8. And 0.9.8 is not "Stable" in RedHat and> Gentoo. So you need to recompile pgcrypto/openssl anyway if you want to use> it. Can I report this in PostgreSQL bug system? What bug are you refering to? Only bug that I can see is thesymbol-conflict problem, but as 8.1 pgcrypto uses always samesetting as core postgres, it should not be a big deal.The fact that Fedora pgcrypto is linked with OpenSSL that does not support SHA256 is not a bug, just a fact.It's not Fedora only, same problem with Gentoo/portage.I think it's problem for all distros. You need recompile pgcrypto or install openssl 0.9.8 which is considered as "unstable" by most distros. Maybe pgcrypto should use built-in algorithms until OpenSSL 0.9.8 is mainstream/default install. OTOH, the nicest solution to your problem would be self-compiledpgcrypto, that would work with stock PostgreSQL. As the conflicthappens with only (new) SHA2 functions, I can prepare a patch forsymbol conflict, would that be satisfactory for you? Ideally, would be great if pgcrypto could fallback to built-in algorithm of OpenSSL don't support it.But since it's compile switch, completely seld-compiled pgcrypto would be great.Thanks a lot!
Re: [GENERAL] pgcrypto sha256/384/512 don't work on Redhat. Please help!
On 5/9/06, Marko Kreen <[EMAIL PROTECTED]> wrote: On 5/9/06, Joe Kramer <[EMAIL PROTECTED]> wrote:> On 5/9/06, Marko Kreen <[EMAIL PROTECTED]> wrote:> > The fact that Fedora pgcrypto is linked with OpenSSL that does not > > support SHA256 is not a bug, just a fact.>> It's not Fedora only, same problem with Gentoo/portage.> I think it's problem for all distros. You need recompile pgcrypto or install> openssl 0.9.8 which is considered as "unstable" by most distros.>> Maybe pgcrypto should use built-in algorithms until OpenSSL 0.9.8 is> mainstream/default install.To be honest, pgcrypto actually falls back on built-in code for AES, in case old OpenSSL that does not have AES. Thats because AESshould be "always there", together with md5/sha1/blowfish.I do not consider SHA2 that important (yet?), so they don'tget same treatment. Right on! SHA2 should fallback the same as AES!> Ideally, would be great if pgcrypto could fallback to built-in algorithm of > OpenSSL don't support it.> But since it's compile switch, completely seld-compiled pgcrypto would be> great.Attached is a patch that re-defines SHA2 symbols so that they would notconflict with OpenSSL. Now that I think about it, if your OpenSSL does not contain SHA2, thenthere should be no conflict. But ofcourse, if someone upgrades OpenSSL,server starts crashing. So I think its best to always apply this patch. That was my thought too. Old OpenSSL doesn't have SHA2 so why SHA2 is still blocked in pgcrypto? Is that by design or bug?Thanks.
Re: [GENERAL] GUI Interface
We've been using EMS PostgreSQL admin.Pros:It has great visual tools for building views. Like in Enterprise manager or MS Access. pgAdmin don't have that.Great import/export abilities. PGAdmin don't have much flexibility. Cons:Too bad it don't support UNICODE and not known if it will.Problems with dollar-quoted procedures. But that's PostresSQL long-standing problem. I mean if you have procedures you may create then only in command-line psql or pgAdmin. Other tools that are not developed by Postgres team will choke and report something about unterminated constant. On 5/12/06, Bart Butell <[EMAIL PROTECTED]> wrote: Is there a GUI interface to the database like Enterprise Manager for Microsoft SQL Server? Thanks Bart Butell Sasquatch Engineering email:[EMAIL PROTECTED] cell: 503 703-0044
[GENERAL] Best practice to grant all privileges on all bjects in database?
Hello,
I need to grant all privileges on all objects in database. Without
using SUPERUSER.
It's strange that GRANT ALL PRIVILEGES ON DATABASE is useless, it
don't grant privileges on tables.
I've found out this "best practice", (more like ugly workaround):
select 'grant all on '||schemaname||'.'||tablename||' to
\\\"$USER\\\";' from pg_tables where schemaname in ('public');
select 'grant all on '||schemaname||'.'||viewname||' to
\\\"$USER\\\";' from pg_views where schemaname in ('public');
and same for functions,sequences etc.
Is there nicer, more friendly way? Maybe there is something like
contrib module or procedure that does that in user-friendly way?
If not, anyone has a better version of above grant script?
Thanks.
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Best practice to grant all privileges on all bjects in database?
On a related note, which objects need to be GRANTed specifically?
There is a saying that following objects can have permissions GRANTed:
1. TABLE
2. DATABASE
3. FUNCTION
4. LANGUAGE
5. SCHEMA
6. TABLESPACE
What about SEQUENCE, TRIGGER? PostgreSQL manual has no mention about this.
Thanks.
On 5/20/06, John DeSoi <[EMAIL PROTECTED]> wrote:
You can find some helpful grant scripts here:
http://pgedit.com/tip/postgresql/access_control_functions
On 5/19/06, Joe Kramer <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I need to grant all privileges on all objects in database. Without
> using SUPERUSER.
>
> It's strange that GRANT ALL PRIVILEGES ON DATABASE is useless, it
> don't grant privileges on tables.
>
> I've found out this "best practice", (more like ugly workaround):
>
> select 'grant all on '||schemaname||'.'||tablename||' to
> \\\"$USER\\\";' from pg_tables where schemaname in ('public');
> select 'grant all on '||schemaname||'.'||viewname||' to
> \\\"$USER\\\";' from pg_views where schemaname in ('public');
>
> and same for functions,sequences etc.
>
>
> Is there nicer, more friendly way? Maybe there is something like
> contrib module or procedure that does that in user-friendly way?
>
> If not, anyone has a better version of above grant script?
>
>
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Free 1000 Rupee bank note
What the hell is Rupees? On 5/29/06, AKHILESH GUPTA <[EMAIL PROTECTED]> wrote: Hi there, Help me by taking this survey. We can both get 1000 Rupees! Click here: http://rewards.popstarnetworkpanel.com/?r=EVEkOCgmiSJTBGsFDi0O&i=gmail&p=4&z=1&tc=2 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pgcrypto sha256/384/512 don't work on Redhat. Please help!
If it was commited to HEAD, it will appear in 8.1.5, right? On 5/30/06, Bruce Momjian wrote: Patch applied to CVS HEAD and 8.1.X. Thanks. --- Marko Kreen wrote: > On 5/9/06, Joe Kramer <[EMAIL PROTECTED]> wrote: > > On 5/9/06, Marko Kreen <[EMAIL PROTECTED]> wrote: > > > The fact that Fedora pgcrypto is linked with OpenSSL that does not > > > support SHA256 is not a bug, just a fact. > > > > It's not Fedora only, same problem with Gentoo/portage. > > I think it's problem for all distros. You need recompile pgcrypto or install > > openssl 0.9.8 which is considered as "unstable" by most distros. > > > > Maybe pgcrypto should use built-in algorithms until OpenSSL 0.9.8 is > > mainstream/default install. > > To be honest, pgcrypto actually falls back on built-in code for AES, > in case old OpenSSL that does not have AES. Thats because AES > should be "always there", together with md5/sha1/blowfish. > > I do not consider SHA2 that important (yet?), so they don't > get same treatment. > > > > OTOH, the nicest solution to your problem would be self-compiled > > > pgcrypto, that would work with stock PostgreSQL. As the conflict > > > happens with only (new) SHA2 functions, I can prepare a patch for > > > symbol conflict, would that be satisfactory for you? > > > > Ideally, would be great if pgcrypto could fallback to built-in algorithm of > > OpenSSL don't support it. > > But since it's compile switch, completely seld-compiled pgcrypto would be > > great. > > Attached is a patch that re-defines SHA2 symbols so that they would not > conflict with OpenSSL. > > Now that I think about it, if your OpenSSL does not contain SHA2, then > there should be no conflict. But ofcourse, if someone upgrades OpenSSL, > server starts crashing. So I think its best to always apply this patch. > > I think I'll send the patch to 8.2 later, not sure if it's important > enough for 8.1. > > -- > marko [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Large database design advice
Hello, I am designing database for a web product with large number of data records. - Few tables but number of objects is tens-hundreds of thousands. - less than 100 queries per second. The application has basically tens thousands of (user) accounts, every account has associated hundreds of items. My initial thought is to design it like this: Table: account - account_id BIGSERIAL Table: item - account_id BIGINT item_id INT Questions: Should table account be designed with BIGSERIAL key, or if it's going to have six-digit number of records, other method should be used? Should I use compound key for table item (account_id+item_id) or item_id should be BIGSERIAL and global sequence with key being only item_id? How generally this design will hold up against this amount of data? Thanks. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
