Re: [SQL] Encryption in the tables of a Postgres 7.3.2 database
jtayamai wrote: > Hi, > > I've a Postgres DB release 7.3.2 and some developed C++ and PHP code > so as to communicate with it. Most important thing: upgrade this version of PostgreSQL. You can't even get 7.3 from the main site any more, so you'll have to look on ftp://ftp-archives.postgresql.org/ The last release was 7.3.21 - work through the release notes and then download as soon as possible. > Currently, the data I've in the different tables is stored with no > encription at all, and I was wondering if there would be an easy way > to get it encrypted without major changes in the already developed C++/ > PHP code. > > I mean, I want to replace the non-encrypted database with an encrypted > version of it without C++/PHP programmes noticing it. > > Has anyone any suggestion about the easiest way to do it? Well, you don't want to be making any changes to a PostgreSQL version that old - it will be impossible to find anyone to help with it. I'd suggest encrypting a filesystem and putting your database on that. That will impact performance of course. If you only want to encrypt a few tables, you could look at splitting where the files are stored. That might involve setting up symlinks, I can't remember if 7.3 supported tablespaces. If you do put some files on the encrypted filesystem and some on the unencrypted one make sure you don't start PG until both are available. I don't know what would happen if one wasn't but it's unlikely to be fun. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] about pg_proc (column pronamespace)
i all. Looking for a way to change some functions to 'security
definer'. This is only going to happen in some of the schemas.I found
that pg_catalog.pg_proc have a bool column (prosecdef), which contains
if some function is defined as 'security definer'. So good. It also
contains a column named pronamespace, which contains (or should) the
schema.
After this
SELECT distinct pronamespace from pg_catalog.pg_proc;
pronamespace
--
11
2200
11313
1901391
1901393
1901396
1901397
1901398
1901399
i would say that, in pg_catalog.pg_namespace i should be able to found
the schema for a particular function. But nope:
glyms_f_test=# SELECT * from pg_catalog.pg_namespace ;
nspname | nspowner | nspacl
+--+-
pg_catalog | 10 | {postgres=UC/postgres,=U/postgres}
pg_toast | 10 |
pg_temp_1 | 10 |
pg_toast_temp_1| 10 |
information_schema | 10 | {postgres=UC/postgres,=U/postgres}
[snip]
public | 10 | {postgres=UC/postgres,=UC/postgres}
(15 rows)
Obviously im missing something, i cant find any relation between
pg_proc.pronamespace and pg_namespace.
Im using PG 8.3.5 (Also tested in 8.2.5 with same results)
Any hints?
Thanks!
Gerardo
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] about pg_proc (column pronamespace)
Gerardo Herzig writes: > Obviously im missing something, i cant find any relation between > pg_proc.pronamespace and pg_namespace. You didn't look at pg_namespace.oid, which is a "system column" meaning SELECT * doesn't show it. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] about pg_proc (column pronamespace)
Tom Lane wrote: > Gerardo Herzig writes: >> Obviously im missing something, i cant find any relation between >> pg_proc.pronamespace and pg_namespace. > > You didn't look at pg_namespace.oid, which is a "system column" > meaning SELECT * doesn't show it. > > regards, tom lane > > Oh, yeah. A little surprised, but yes there it is! Thanks Tom. Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] its posible to use a 2 column returning function as a subquery?
Hi All. My poor english doest not allow me to get a better subject. Here is the thing: I have a function who returns a two column result, that is is used inside another function, like this CREATE FUNCTION show_result((some args), OUT shoe varchar, OUT desc varchar , OUT price numeric) ... as $$ select shoe, (get_desc_and_price(shoe)).desc, (get_desc_and_price(shoe)).price from ... $$ language sql; This is the only way i get this thing working. According to the timming, it looks like get_desc_and_price() is being called twice. There is some syntax for calling get_desc_and_price only once? As getting the 'description' and the 'price' share most of the logic, it looks ok to write a single function that does the job. Thanks! Gerardo -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] its posible to use a 2 column returning function as a subquery?
Gerardo Herzig writes: > There is some syntax for calling get_desc_and_price only once? Do something like select (t).* from (select get_desc_and_price(shoe) as t from ...) ss; If you don't have get_desc_and_price marked as volatile, you'll probably also need to add "offset 0" to the sub-select to keep the planner from flattening the sub-select and producing multiple calls of the function. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
