Re: [HACKERS] pg_dump and search_path
On Mon, Aug 10, 2015 at 1:10 PM, Steve Thames sthame...@gmail.com wrote: Please consider making the arbitrary determination of search_path by pg_dump an optional behavior. Or better yet, just have it generate a backup that accurately reflects the database it is backing up. Hmm, I don't think it's a question of making it optional. I think the current behavior is just a bug, and should be fixed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump and search_path
Thank you gentlemen for clarifying this. I found this problem when my database modeling tool saw a change in the database (the nextval() parameters) after a database restore. I guess the tool must be reading adsrc for this information. Cheers, Steve Thames -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Tuesday, August 11, 2015 10:41 AM To: Alvaro Herrera Cc: Steve Thames; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] pg_dump and search_path Alvaro Herrera alvhe...@2ndquadrant.com writes: Don't ever rely on adsrc. It's useless. Use pg_get_expr(adbin) instead. That's safe, for instance, if the sequence gets renamed. It's probably past time we got rid of that column altogether. It just wastes space and cycles. There was an argument for not being too quick to get rid of it, but we deprecated it in 7.2 ... surely people have had more than enough time to fix their applications. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump and search_path
Alvaro Herrera alvhe...@2ndquadrant.com writes: Don't ever rely on adsrc. It's useless. Use pg_get_expr(adbin) instead. That's safe, for instance, if the sequence gets renamed. It's probably past time we got rid of that column altogether. It just wastes space and cycles. There was an argument for not being too quick to get rid of it, but we deprecated it in 7.2 ... surely people have had more than enough time to fix their applications. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump and search_path
Robert Haas robertmh...@gmail.com writes:
On Mon, Aug 10, 2015 at 1:10 PM, Steve Thames sthame...@gmail.com wrote:
Please consider making the arbitrary determination of search_path by pg_dump
an optional behavior. Or better yet, just have it generate a backup that
accurately reflects the database it is backing up.
Hmm, I don't think it's a question of making it optional. I think the
current behavior is just a bug, and should be fixed.
It is not a bug, and as far as I can see what Steve is complaining about
isn't even pg_dump's behavior: it is just how regclass constants work.
regclass_out only qualifies the name if it wouldn't be found in the
current search path. This is a display behavior and has nothing to do
with what the actual value of the constant is:
regression=# create schema s1;
CREATE SCHEMA
regression=# create table s1.t1 (f1 serial);
CREATE TABLE
regression=# \d s1.t1
Table s1.t1
Column | Type | Modifiers
+-+
f1 | integer | not null default nextval('s1.t1_f1_seq'::regclass)
regression=# set search_path = s1;
SET
regression=# \d s1.t1
Table s1.t1
Column | Type |Modifiers
+-+-
f1 | integer | not null default nextval('t1_f1_seq'::regclass)
Now, if pg_dump produced a file that failed to restore this state
of affairs correctly, that would be a bug. But I have seen no
evidence suggesting that it doesn't get it right. The way that the
commands are spelled in the dump file is an implementation detail.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump and search_path
Steve Thames wrote: SELECT a.attnum, n.nspname, c.relname, d.adsrc AS default_value FROM pg_attribute AS a JOIN pg_class AS c ON a.attrelid = c.oid JOIN pg_namespace AS n ON c.relnamespace = n.oid LEFT JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum WHERE a.attnum 0 AND n.nspname = 'testschema' AND c.relname = 'testtable'; Don't ever rely on adsrc. It's useless. Use pg_get_expr(adbin) instead. That's safe, for instance, if the sequence gets renamed. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump and search_path
Steve Thames wrote: Thank you gentlemen for clarifying this. I found this problem when my database modeling tool saw a change in the database (the nextval() parameters) after a database restore. I guess the tool must be reading adsrc for this information. You can tell for sure by setting log_statement=all. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_dump and search_path
I earliest reference I found to this issue is here
http://postgresql.nabble.com/set-search-path-in-dump-output-considered-harm
ful-td1947594.html and refers to the search_path being arbitrarily set in
the file created by pg_dump. This is apparently still the case in 9.4.
I found this issue because I use SERIAL/BIGSERIAL columns and when I created
schema-specific tables in a schema other than the first listed in
search_path the nextval() sequence references were schema-qualified.
When I created a backup file with pg_dump and then restored using psql, the
nextval() sequence references were no longer schema-qualified because the
backup file set my table schema as the first schema in search_path. I saw
the same result with pg_restore.
While the results of \d testschema.testtable shows the schema-qualified
sequence name in nextval():
\d testschema.testtable;
Table testschema.testtable
Column | Type | Modifiers
++--
-
id | integer| not null default
nextval('testschema.testtable_id_seq'::regclass)
The actual default read from pg_attrdef does not:
SELECT a.attnum, n.nspname, c.relname, d.adsrc AS default_value
FROM pg_attribute AS a
JOIN pg_class AS c ON a.attrelid = c.oid
JOIN pg_namespace AS n ON c.relnamespace = n.oid
LEFT JOIN pg_attrdef AS d ON d.adrelid = c.oid AND d.adnum = a.attnum
WHERE a.attnum 0
AND n.nspname = 'testschema'
AND c.relname = 'testtable';
attnum | nspname | relname | default_value
++---+---
1 | testschema | testtable | nextval('testtable_id_seq'::regclass)
2 | testschema | testtable |
This insistency is described here
http://dba.stackexchange.com/questions/21150/default-value-of-serial-fields
-changes-after-restore .
This is not a documented behavior-at least I couldn't find it and I searched
quite a bit. There was no indication to me that when I run pg_dump it will
do something more than I asked it to do and it took me a while to figure out
why. I solved the problem by setting the search_path as pg_dump does when
creating the database so now the restore does not create a different
database than I did.
Certainly it would seem a bug that \d and a direct read from pg_attrdef give
different results even though pg_dump determining on its own what the
search_path should be is no doubt an intended behavior. But it seems to me
this should be an option. I expected pg_dump to do what I asked it to do and
when it did something other than that it was quite a headache.
What's more, I like schema-qualified references. Schemas are an effective
database organization tool and I teach my people to use them and not depend
on the search path as doing so leads to sloppy and inconsistent thinking as
well as coding.
Please consider making the arbitrary determination of search_path by pg_dump
an optional behavior. Or better yet, just have it generate a backup that
accurately reflects the database it is backing up.
BTW, I am a huge fan of PostgreSQL.
Cheers!
[HACKERS] pg_dump schema search_path; selectSourceSchema()
As I mentioned in the last post to a thread in general detailing some of the hurdles of attempting to set up PostgreSQL virtual hosting, we've had to hack pg_dump to achieve the behavior that we want.The modifications we made call into question (for us, anyway) a few design decisions in PostgreSQL utilities, and we're wondering if our modification has unintentional ramifications or if, as an option, it's something that the hackers see value in.The main issue for us is that we are relying on search_path to cause phpPgAdmin and pg_dump to find restricted views into the system catalogs. selectSourceSchema as written breaks our expectations when it is called with pg_catalog as a single argument.Our first attempt to get things working was to remove all calls to selectSourceSchema. Clearly, this is playing with fire, and I'll admit to not understanding all the references to schemas other than the explicit pg_catalog references.What we're currently thinking of doing is adding a flag like --search-path-prefix that would let us prepend any path requirements we have to the built-in calls to selectSourceSchema(). So that if we're putting our user-restricted system catalog views in public (as we're currently doing) or a custom schema (which would be my ideal preference), we can prepend that to the search_path, and pg_dump will behave as expected when run by a user in a hardened environment as long as users haven't done anything foolish. I'm just wondering whether such a patch seems sane and adds value from the perspective of hackers. Are we overlooking any scenarios in which pg_dump specifically requires a more restrictive search_path in order to behave properly?From a bigger picture standpoint, this is mostly necessary because there's no way to configure PostgreSQL to restrict access to the system catalogs in any meaningful way. I'm not really sure how to search for discussions on this topic in the archives, and I'm also not sure whether the forthcoming roles features will go any way toward eliminating the need for such behavior. -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005
Re: [HACKERS] pg_dump schema search_path; selectSourceSchema()
Thomas F. O'Connell [EMAIL PROTECTED] writes: Our first attempt to get things working was to remove all calls to selectSourceSchema. Clearly, this is playing with fire, No, it's breaking it entirely; and so would your proposed change. The reason for the restrictive search path is to ensure that references to stuff in other schemas is dumped with fully qualified names. Anything you add to the search path creates added risk of incorrect recreation of object definitions at reload. If the added schema contains *only* your replacement pg_database and pg_user views, the risk might be relatively small --- but I trust you can see that in general such a thing would be a bug not a feature. On the whole I continue to regard this project as a dead end. It will be a whole lot simpler and more reliable to give each user his own postmaster, if you want airtight separation between users. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
