[BUGS] BUG #7791: create database with owner, owner does not get usage on schema
The following bug has been logged on the website:
Bug reference: 7791
Logged by: Brice Breeden
Email address: zardozwild...@hotmail.com
PostgreSQL version: 9.1.7
Operating system: windows x64
Description:
This database creation script was executed via the postgres superuser.
Executes create database and the "with owner" clause.
Create database assigns ownership to a role (myta_admin_role). This is not a
login role.
After database creation, script creates a schema. I later discovered that
the role never gets usage permission on the schema. Even though it is the
database owner.
My workaround was to remove the "with owner" clause. And explicitly set
schema permissions on the role.
I am providing a portion of the script for reference.
do
$$
begin
if (not exists (select * from information_schema.enabled_roles where
lower(role_name)=lower('myta_admin_role'))) then
create role myta_admin_role superuser createdb createrole
replication;
end if;
end;
$$;
do
$$
begin
if (not exists (select * from information_schema.enabled_roles where
lower(role_name)=lower('myta_svc_role'))) then
create role myta_svc_role;
end if;
end;
$$;
-- Database creation can't be made conditional in postgres.
-- NOTE: Declaring one of our roles as owner doesn't seem to work.
-- Postgres doesn't give myta_admin_role usage rights on the myta schema.
-- Even though it's declared as the owner.
-- So we'll let ownership default to the superuser. And grant
-- the rights ourselves.
create database mytadb
with owner=myta_admin_role
encoding='UTF8'
tablespace=pg_default
LC_COLLATE='English_United States.1252'
LC_CTYPE='English_United States.1252'
CONNECTION LIMIT = -1;
\connect mytadb
do
$$
begin
if (not exists (select * from pg_namespace where nspname='myta')) then
create schema myta;
end if;
end;
$$;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #7790: null filters in CTEs don't work
The following bug has been logged on the website:
Bug reference: 7790
Logged by: Luisa Francisco
Email address: luisa.j.franci...@gmail.com
PostgreSQL version: 9.2.2
Operating system: 32-bit Windows 7 SP1
Description:
Expected output should have no nulls in it, but it does:
CREATE TABLE item_tree(
id text PRIMARY KEY,
parent_id text
);
INSERT INTO item_tree (id, parent_id) VALUES
('body', null),
('head', 'body'),
('mouth', 'head'),
('eye', 'head'),
('tooth', 'mouth'),
('tongue', 'mouth'),
('sclera', 'eye'),
('cornea', 'eye')
;
WITH RECURSIVE t(id, parent_id) AS (
SELECT id, parent_id
FROM item_tree i
WHERE parent_id IS NOT NULL
AND id NOT IN (
SELECT parent_id
FROM item_tree
WHERE parent_id IS NOT NULL)
UNION ALL
SELECT t.id, i.parent_id
FROM item_tree i
JOIN t
ON i.id = t.parent_id
)
SELECT * FROM t ORDER BY id;
---
Output is as follows:
id parent_id
-- -
cornea eye
cornea NULL
cornea head
cornea body
sclera eye
sclera head
sclera NULL
sclera body
tongue body
tongue head
tongue NULL
tongue mouth
tooth body
tooth head
tooth mouth
tooth NULL
However, enclosing the query with a outer select-null-filter works even if
all the inner filters were deleted as below:
-
SELECT * FROM (
WITH RECURSIVE t(id, parent_id) AS (
SELECT id, parent_id
FROM item_tree i
UNION ALL
SELECT t.id, i.parent_id
FROM item_tree i
JOIN t
ON i.id = t.parent_id
)
SELECT * FROM t ORDER BY id;
) t1 WHERE parent_id IS NOT NULL
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #7794: pg_dump: errors when using pipes/streams
The following bug has been logged on the website: Bug reference: 7794 Logged by: Stefan Reiser Email address: s.rei...@tu-bs.de PostgreSQL version: 9.1.7 Operating system: Windows 7 Pro 64-Bit Description: I intend to stream the output of pg_dump into a zip file (along with some other files and comments). pg_dump shows one or more warnings when used with non-seekable streams: "pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used" Examples (on Windows 7 Pro 64 Bit, PG 9.1.7 and 9.1.6, 64 Bit) 1) pg_dump --format=custom --schema-only mydatabase | zip -0 TEST.ZIP - 2) pg_dump --format=custom --schema-only mydatabase > NUL 3) pg_dump --format=custom --schema-only --file TEST3 mydatabase 4) pg_dump --format=custom --schema-only mydatabase > TEST4 1 and 2 print warnings, while pg_dump's exit code is 0 (as can be verified with example 2), 3 and 4 are ok. I've used "--schema-only" to emphasize that it's not a question of file size (without "--schema-only" the number of warnings increases, supposedly one per table). The "zip" in example 1 is Info-ZIP 3.0 - but you can as well use anything that consumes STDIN, like gzip, ...) Files TEST3 and TEST4 are of the same size, the file packaged in TEST.ZIP is slightly bigger. With all three generated files pg_restore produces exactly the same script, so the backup seems ok - but why the warnings then? (And what do they mean, anyway?) Are those backups reliable or could they as well become corrupted? (after all, it seems pg_dump wants to write something somewhere for some reason, but can't...) A similar problem has been reported as "BUG #6726" (http://archives.postgresql.org/pgsql-bugs/2012-07/msg00074.php) but I couldn't find any replies ... so I filed this new report. (Formats tar and plain don't seem to produce these warnings, so I'll work around the problem by using the tar format.) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #7792: pg_dump does not treat -c flag correctly when using tar format
The following bug has been logged on the website: Bug reference: 7792 Logged by: Eli Mesika Email address: emes...@redhat.com PostgreSQL version: 9.1.7 Operating system: Fedora 16 Description: steps to reproduce (aasuming database name is : test) 1) pg_dump -F t -U postgres -f test.tar test 2) tar xvf test.tar to any directory 3) vi restore.sql * restore.sql includes DROP statements for each object even tough -c flag was not given repeat the above using plain-text format 1) pg_dump -F p -U postgres -f test.sql test 2) vi test.sql This time test.sql does not include DROP staements for each object * pg_dump should not produce DROP statements for each object if -c flag was not given to the command -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #7793: tsearch_data thesaurus size limit
The following bug has been logged on the website: Bug reference: 7793 Logged by: David Boutin Email address: dav...@gmail.com PostgreSQL version: 9.1.7 Operating system: Ubuntu 12.04 LTS 64bits Description: Hi all, I like working with thesaurus files with specific text search configuration to ease search with synonyms. Today I tried to create a thesaurus of artist names (using musicbrainz database) including their synonyms/aliases. This thesaurus file is about 1M lines. And I realized it is impossible to use it with FTS, I got unexpected error with "plainto_tsquery" and even segmentation fault for some names according to postgresql log file. I then tried to reduce the size of this file several times to arrive to a final file of 65535 lines which works fine whereas a 65536 lines file crash my queries. Is there any way to increase this thesaurus size limit? Many thanks in advance for your help Kind regards David -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7791: create database with owner, owner does not get usage on schema
zardozwild...@hotmail.com writes: > This database creation script was executed via the postgres superuser. > Executes create database and the "with owner" clause. > Create database assigns ownership to a role (myta_admin_role). This is not a > login role. > After database creation, script creates a schema. I later discovered that > the role never gets usage permission on the schema. Even though it is the > database owner. This isn't a bug. A database owner just owns the database (and hence can rename or drop it). The owner doesn't magically have additional permissions on the objects therein. It'd be a security hole if he did, at least for superuser-owned objects such as the core functions and operators ... and I gather that you created this schema as superuser. We could argue about whether database owners should have extra privileges on objects belonging to ordinary users, but it'd be rather inconsistent to do that IMO. It makes more sense for the object owner to have to grant privileges to the database owner, if the latter is to be able to access the object. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7794: pg_dump: errors when using pipes/streams
s.rei...@tu-bs.de writes: > pg_dump shows one or more warnings when used with non-seekable streams: > "pg_dump: [custom archiver] WARNING: ftell mismatch with expected position > -- ftell used" > Examples (on Windows 7 Pro 64 Bit, PG 9.1.7 and 9.1.6, 64 Bit) > 1) pg_dump --format=custom --schema-only mydatabase | zip -0 TEST.ZIP - Hm. Apparently, pg_dump's checkSeek() function is succeeding even though later attempts to seek the file don't work. We had a previous go-round with issues of this sort, http://archives.postgresql.org/pgsql-hackers/2010-06/msg01355.php but apparently Windows is "helpful" enough to let a no-op SEEK_SET call succeed too. (Gee thanks, Microsoft.) Anyone know how to test for seekability of a file in a way that works reliably on Windows? > With all three generated files pg_restore produces exactly the same script, > so the backup seems ok - but why the warnings then? My guess is that it's failing to update the table of contents on completion, which will cause parallel pg_restore to fail, but it's harmless for ordinary pg_restore. If we fix checkSeek the warnings would go away, but you'd still have an archive that's not usable for parallel restores. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7792: pg_dump does not treat -c flag correctly when using tar format
emes...@redhat.com writes: > 1) pg_dump -F t -U postgres -f test.tar test > 2) tar xvf test.tar to any directory > 3) vi restore.sql > * restore.sql includes DROP statements for each object even tough -c flag > was not given I believe this is intentional - at least, pg_backup_tar.c goes out of its way to make it happen. (The forcible setting of ropt->dropSchema in _CloseArchive is the cause, and it's hard to see why that would be there unless the author intended this effect.) Perhaps we should remove that, but it would be an incompatible change. Arguing for or against it really requires a model of what people would be doing with the restore.sql script. I'm not entirely convinced that it should be considered equivalent to what you'd get from a plain dump run. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7793: tsearch_data thesaurus size limit
dav...@gmail.com writes: > [ thesaurus dictionary fails for more than 64K entries ] I see a whole bunch of uses of "uint16" in src/backend/tsearch/dict_thesaurus.c. It's not immediately clear which of these would need to be widened to support more entries, or what the storage cost of doing that would be. We probably should at least put in a range check so that you get a clean failure instead of a crash though. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7790: null filters in CTEs don't work
luisa.j.franci...@gmail.com writes:
> Expected output should have no nulls in it, but it does:
It's not apparent to me why you think the first query shouldn't produce
any rows with null parent_id? AFAICS, the recursive query will "crawl
up the tree" producing a row for every parent level above the given
base-case rows. Eventually you'll get up to a match to the row
('body', null), and there's nothing to stop that from being displayed.
It's a bit easier to see what's happening if you leave off the "ORDER
BY" so that the rows are printed in generation order:
regression=# SELECT id, parent_id
FROM item_tree i
WHERE parent_id IS NOT NULL
AND id NOT IN (
SELECT parent_id
FROM item_tree
WHERE parent_id IS NOT NULL);
id | parent_id
+---
tooth | mouth
tongue | mouth
sclera | eye
cornea | eye
(4 rows)
regression=# WITH RECURSIVE t(id, parent_id) AS (
SELECT id, parent_id
FROM item_tree i
WHERE parent_id IS NOT NULL
AND id NOT IN (
SELECT parent_id
FROM item_tree
WHERE parent_id IS NOT NULL)
UNION ALL
SELECT t.id, i.parent_id
FROM item_tree i
JOIN t
ON i.id = t.parent_id
)
SELECT * FROM t;
id | parent_id
+---
tooth | mouth
tongue | mouth
sclera | eye
cornea | eye
tooth | head
tongue | head
sclera | head
cornea | head
tooth | body
tongue | body
sclera | body
cornea | body
tooth |
tongue |
sclera |
cornea |
(16 rows)
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7781: pgagent incorrect installation
The ini file clearly says that the pgAgent has been installed in /Library/pgAgent. Can you please share install-pgagent.log from your system temp (/tmp)? On Sat, Jan 5, 2013 at 7:56 PM, Aleksander Shniperson < alex.shniper...@gmail.com> wrote: > Sorry for late answer > > Here is ini-file: > [PostgreSQL/9.1] > Branding=PostgreSQL 9.1 > DataDirectory=/Library/PostgreSQL/9.1/data > Description=PostgreSQL 9.1 > DisableStackBuilder=0 > InstallationDirectory=/Library/PostgreSQL/9.1 > Locale=ru_RU.UTF-8 > Port=5432 > Serviceaccount=postgres > ServiceID=postgresql-9.1 > Shortcuts=1 > Superuser=postgres > Version=9.1.7.1 > > [PostgreSQL/9.2] > Branding= > DataDirectory= > Description= > DisableStackBuilder= > InstallationDirectory= > Locale= > Port= > Serviceaccount= > ServiceID= > Shortcuts= > Superuser= > Version= > > [sql-profiler/PG_9.1] > Description= > InstallationDirectory= > Version= > > [TuningWizard] > Branding= > Description= > InstallationDirectory= > Username= > Version= > > [pgAgent] > Description=pgAgent is a job scheduler for PostgreSQL which may be managed > using pgAdmin. > InstallationDirectory=/Library/pgAgent > PGDATABASE=postgres > PGHOST=localhost > PGPORT=5432 > PGUSER=postgres > ServiceManager=postgres > UpgradeMode=0 > Version=3.2.1-1 > > 04.01.2013, в 20:53, Dave Page написал(а): > > On Fri, Jan 4, 2013 at 4:35 PM, Aleksander Shniperson > wrote: > > sudo find / -name "uninstall-pgagent.app" > > > gives no result. > > > What's in /etc/postgres-reg.ini? > > > -- > Dave Page > Blog: http://pgsnake.blogspot.com > Twitter: @pgsnake > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > > -- Sandeep Thakkar Senior Software Engineer EnterpriseDB Corporation The Enterprise Postgres Company Phone: +91.20.30589523 Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
