Re: [SQL] drop PW
Mark, As Joshua said, you can modify pg_hba.conf. What's happening is PostgreSQL is configured to require a password for the IP address that user is connecting from, but you're not supplying one. Set that user and the IP address they connect from to "trust" security in pg_hba.conf and bounce PostgreSQL. An alternative approach is to add the required password to your user's .pgpass file however I've never used those so cannot comment. Andy Mark Fenbers wrote: I have created a new 8.3 version DB and populated it. A specific user of this database (george) has been setup with a password, so that every time I use psql or some other utility, I need to supply this password. So I want to drop the password authentication. I tried rerunning createuser (and just pressing Enter when prompted for the new password), but it complains that the user already exists. I can't drop the user because this user owns the DB and all the tables. My postgresql books are all for 7.x, and suggests altering the pg_shadow table (which seems risky to me). I tried: ALTER USER george PASSWORD ''; and that looked like it succeeded, but running psql again prompted me and when I just hit Enter, it complained that no password was supplied. So how do I turn off being prompted for a password for george. (I am aware of the security risks...) Mark -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PHP pg_escape_string
Hi, Zdravko Balorda wrote: there are both pg_(un)escape_bytea() functions but only one pg_escape_string()... I wonder if I may be missing something here? Yeah, I think you are. pg_escape_string (funnily enough) escapes string data which is then stored in the database. You would use this for escaping things like apostrophes in a text field so PostgreSQL wouldn't think the apostrophe in the field is the "end of data" marker. However this string is *not* stored in the database in an escaped form, as it's only escaped for the SQL command, therefore it makes no sense to unescape it. bytea columns on the other hand, are a way of sending and receiving binary data as a textual representation to/from the database server. The data you send and receive is both encoded, therefore you need to unescape it to read it back out. For example a null byte (byte value 0) cannot be sent or received in a SQL command, because a null byte represents an end-of-string in C. Other byte values similarly cannot be sent in a string because they cannot be converted to a character (e.g. ASCII newline/linefeed.) Regards, Andy -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] CHECK constraint removing brackets
Hi, I notice this had been raised as a bug (and subsequently over-ruled) so I'm asking how I can achieve the following business rule. I have an order table which has an invoice_id column that links to an invoice table (an order can only have 1 invoice, but one invoice can have multiple orders.) An order can have either an unconfirmed state, or any other state after it's been confirmed. If an order has the state unconfirmed, the invoice_id column must be null, as an invoice won't have been created yet. If an order has any other state except unconfirmed, the invoice_id must not be null. With the above in mind, I decided on the following check to enforce this: (state = 'Unconfirmed'::client.order_state AND invoice_id = NULL) OR (state != 'Unconfirmed'::client.order_state AND invoice_id != NULL) However PostgreSQL (8.4.2) converts this to the following: state = 'Unconfirmed'::client.order_state AND invoice_id = NULL::integer OR state <> 'Unconfirmed'::client.order_state AND invoice_id <> NULL::integer This allows both an order state of "unconfirmed" and a non-null invoice_id, and an order state of "confirmed" and a NULL invoice_id. How can I achieve the above? Thanks, Andy -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] CHECK constraint removing brackets
Hi Tom and Scott, > > I think your real problem is that you're trying to use "= NULL" and > "!= NULL" where you should say IS NULL or IS NOT NULL. Argh such a school-boy error! This is the first bit of database programming I've done for about 2 months, and I hadn't switched my C++ brain off. I know about the <> and !=, for some reason != has always made better sense to me to read, so I tend to write it that way. Cheers, Andy -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Storing null bytes in bytea
Hi all,
I was going to post this on the pgsql-php list but I think the issue is
more on the PostgreSQL side of things.
I'm using PHP 5.2.9 connected to a PostgreSQL 8.3.7 server running on
Solaris 10 to try to store the session data for an application using a
custom session handler class. The session data (objects/class instances
etc) is serialized into raw bytes in PHP and contains a few nul/zero
bytes (which are meaningful to PHP when it comes to deserializing the data.)
Because of the nul bytes, I've set the session_data column to be a bytea
column in my database table. However I cannot get PostgreSQL to read
past the first nul byte on an insert, so the unserialize call fails when
it reads it back out the database and the remaining data is omitted.
An example of such query is this:
INSERT INTO system.session (user_id, session_key, session_name,
client_browser, date_created, date_expires, ip_address, session_data)
VALUES (NULL, '4pc4sjciahoc4fuk1bt4kohe91'::character varying(32),
'AppName'::character varying(50), 'Mozilla/5.0 (Windows; U; Windows NT
6.0; en-GB; rv:1.9.0.9) Gecko/2009040821 Firefox/3.0.9 (.NET CLR
3.5.30729)'::character varying(200),
public.get_pg_timestamp(1240853862::integer),
public.get_pg_timestamp(1240854162::integer), '192.168.0.8'::inet,
E'IsLoggedIn|b:1;CurrentUser|O:17:"Class_SystemUser":4:{s:26:"\\000Class_SystemUser}'::bytea);
All other columns are fine, but when it comes to the session_data
column, all I end up with is
'IsLoggedIn|b:1;CurrentUser|O:17:"Class_SystemUser":4:{s:26:"'.
Everything past the first "\\" byte sequence is ignored.
I've tried this with and without the 'E' at the beginning of the value
string.
Any pointers as to what I'm doing wrong?
Thanks,
Andy
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Storing null bytes in bytea
Hi Tom,
Your example works fine in psql:
regression=# create table t1 (f1 bytea);
CREATE TABLE
regression=# insert into t1 values
(E'IsLoggedIn|b:1;CurrentUser|O:17:"Class_SystemUser":4:{s:26:"\\000Class_SystemUser}'::bytea);
INSERT 0 1
regression=# select * from t1;
f1
---
IsLoggedIn|b:1;CurrentUser|O:17:"Class_SystemUser":4:{s:26:"\000Class_SystemUser}
(1 row)
I suspect what is happening is that some layer on the client side is
doubling (or perhaps undoubling?) the backslashes for you. Exactly
what are you doing with that literal as you build the query? It might
help to turn on log_statements so that you can see just what the
server is getting.
Many thanks for your reply. I was a bit naive about the quality of the
client I was using (Navicat 8.1.) It turns out in the "Memo" view it
doesn't show anything past the first zero byte. However switch it to
hex view and you see the full bytes.
I think my issue that it wasn't working on the client side (and caused
me to examine it in more detail in the database) was because it wasn't
unescaping correctly in the application code when it read the data back
out of the database.
Next time I won't be so lazy and try it out in psql first...
Regards,
Andy
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Obtaining a limited number of records from a long query
Hi Oliveiros Certainly! What you're looking for is the LIMIT...OFFSET syntax. Some examples: SELECT ... LIMIT 10 - return the first 10 records only. SELECT ... LIMIT 10 OFFSET 10 - return 10 records, starting with record 11. Manual page: http://www.postgresql.org/docs/8.3/interactive/queries-limit.html Regards, Andy Oliveiros Cristina wrote: Dear List, Is there any way to force a query to return just a few records? For ex, returning just the first ten rows from a long ORDER BY query ? And then, eventually, obtaining the remaining records at a later time, or in background? Thanks in advance for your help, Best, Oliveiros -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
