Re: [GENERAL] Unable to select a table as postgres user

2015-10-30 6:56 GMT+01:00 rajan : > I have a database in which tables are created for each user when they > create > an account. These auto generated tables are used for generating analytics > using a Function. This function is defined as a SECURITY DEFINER. So that > any user

Re: [GENERAL] Unable to select a table as postgres user

2015-10-30 7:56 GMT+01:00 rajan : > Yes. I agree that the superuser cannot be restricted with any access. > > But my scenarios is, I am executing a function(VOLATILE SECURITY DEFINER) > using the superuser and it function fails with unable to select a > particular > table. >

Re: [GENERAL] Unable to select a table as postgres user

On 10/29/2015 10:56 PM, rajan wrote: I have a database in which tables are created for each user when they create an account. These auto generated tables are used for generating analytics using a Function. This function is defined as a SECURITY DEFINER. So that any user who tries to execute this

Re: [GENERAL] Unable to select a table as postgres user

Yes. I agree that the superuser cannot be restricted with any access. But my scenarios is, I am executing a function(VOLATILE SECURITY DEFINER) using the superuser and it function fails with unable to select a particular table. At the same time, I am able to select the table as a superuser, by

Re: [GENERAL] Unable to select a table as postgres user

Hi What error do you get? - Permission denied or table does not exist? Latter would indicate a problem with the search_path and you should fully qualify the table name in the function body. - Did you create the function as postgres user? Bye Charles On 30/10/2015 07:56, rajan wrote: Yes.

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 4:36 PM, Andy Colson wrote: On 10/30/2015 3:47 PM, David Blomstrom wrote: No, I get the same T_FUNCTION error. Someone commented that the function... create function tax_rank(id integer) returns text as $$ select case id when 1 then 'Classes'

Re: [GENERAL] Hierarchical Query Question (PHP)

Thanks for the tips. In pgAdmin III, I can create a table step-by-step by choosing Edit > Object > New Table But is there a pace for me to past in a block of code that creates the table with just one process? If I click on the SQL icon, a SQL window opens up, and I can past the code into SQL

Re: [GENERAL] Hierarchical Query Question (PHP)

On 30/10/2015 22:10, David Blomstrom wrote: > Just so I understand what's going on, I can create a lookup table by > pasting this code... > > create table taxon ( > taxonid serial, > descr text > ); > create table gz_life_mammals ( > id serial, > taxonid integer, -- use the lookup table >

Re: [GENERAL] Hierarchical Query Question (PHP)

No, I get the same T_FUNCTION error. Someone commented that the function... create function tax_rank(id integer) returns text as $$ select case id when 1 then 'Classes' when 2 then 'Orders' when 3 then 'Families' when 4 then 'Genera'

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:10 PM, David Blomstrom wrote: Just so I understand what's going on, I can create a lookup table by pasting this code... create table taxon ( taxonid serial, descr text ); create table gz_life_mammals ( id serial, taxonid integer, -- use the lookup table parentid

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:10 PM, David Blomstrom wrote: Just so I understand what's going on, I can create a lookup table by pasting this code... create table taxon ( taxonid serial, descr text ); create table gz_life_mammals ( id serial, taxonid integer, -- use the lookup table parentid

Re: [GENERAL] Hierarchical Query Question (PHP)

On 30/10/2015 22:29, David Blomstrom wrote: > Thanks for the tips. > > In pgAdmin III, I can create a table step-by-step by choosing Edit > > Object > New Table > > But is there a pace for me to past in a block of code that creates the > table with just one process? If I click on the SQL icon, a

Re: [GENERAL] Hierarchical Query Question (PHP)

The field descr would presumably hold the values I originally had in the field Taxon - e.g. the names of various taxons, like 'Mammalia' and 'Canis-lupus.' The field id is just a numerical key, and I already have the numerical values for parentid. But what am I supposed to put in the field

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:55 PM, David Blomstrom wrote: The field descr would presumably hold the values I originally had in the field Taxon - e.g. the names of various taxons, like 'Mammalia' and 'Canis-lupus.' The field id is just a numerical key, and I already have the numerical values for parentid.

Re: [GENERAL] Hierarchical Query Question (PHP)

Yes, I guess it does make sense to keep a copy of your actions. In the meantime, I now have two new tables with the following schema: -- Table: public.taxon -- DROP TABLE public.taxon; CREATE TABLE public.taxon ( taxonid integer NOT NULL DEFAULT nextval('taxon_taxonid_seq'::regclass),

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 03:29 PM, David Blomstrom wrote: Thanks for the tips. In pgAdmin III, I can create a table step-by-step by choosing Edit > Object > New Table But is there a pace for me to past in a block of code that creates the table with just one process? If I click on the SQL icon, a SQL

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:55 PM, David Blomstrom wrote: The field descr would presumably hold the values I originally had in the field Taxon - e.g. the names of various taxons, like 'Mammalia' and 'Canis-lupus.' The field id is just a numerical key, and I already have the numerical values for parentid.

Re: [GENERAL] Hierarchical Query Question (PHP)

Sorry, I don't think I specified that at the beginning. My original/master table has several fields, four of which are relevant to this hierarchical stuff - id, taxon, parent, parent_id. The first is a numerical key, from 1 to probably somewhere around 8,000 for mammals, 1 to 10,000 for birds,

Re: [GENERAL] Hierarchical Query Question (PHP)

I think I answered my question @ http://www.the-art-of-web.com/sql/lookup-table/ It sounds like the field taxon_id is similar to the field id - it's just automatically populated by a numerical key. So if I add a new taxon on row 5, then the taxon_id for the following row with change from 5 to 6

Re: [GENERAL] Unable to select a table as postgres user

On 10/30/2015 10:27 AM, rajan wrote: > The function is created as postgres user. > > And I get a permission denied error. If you want help solving this, I suggest you post a minimal, self contained test case (i.e. SQL statements) which anyone can run to reproduce your issue. HTH, Joe --

Re: [GENERAL] Unable to select a table as postgres user

postgres owns the function Schema | public Name| testuserfunction Result data type| x Argument data types | Type| normal Security| definer Volatility | volatile Owner | postgres Language| plpgsql

[GENERAL] Selectively Importing Data

First consider the following table: create table taxon ( taxonid serial, descr text ); As I understand it, "serial" means that column will automatically populate with a numerical key. If I want to fill the field 'descr' with a list of scientific names stored in a spreadsheet, then how would

[GENERAL] Upgrade from 9.3 to 9.4 issue

I have just upgraded from Fedora 21 to 22. This included an upgrade of PostgreSQL from 9.3 to 9.4 which causes postmaster to fail because the existing databases are still at 9.3. As suggested, I then ran postgresql-setup --upgrade but this failed with: Performing Consistency Checks

Re: [GENERAL] Upgrade from 9.3 to 9.4 issue

Stephen Davies writes: > I have just upgraded from Fedora 21 to 22. > This included an upgrade of PostgreSQL from 9.3 to 9.4 which causes > postmaster > to fail because the existing databases are still at 9.3. > As suggested, I then ran postgresql-setup --upgrade but this

Re: [GENERAL] Selectively Importing Data

On 10/30/2015 07:24 PM, David Blomstrom wrote: First consider the following table: create table taxon ( taxonid serial, descr text ); As I understand it, "serial" means that column will automatically populate with a numerical key. If I want to fill the field 'descr' with a list of

Re: [GENERAL] mysql_fdw trouble

On 10/29/2015 05:38 PM, Dane Foster wrote: Hello, I think I've tripped over another mysq_fdw bug. I've filed a bug report on github already but just in case the problem is w/ my query I figured I would post it here in case someone sees something obvious. The error message I get is: null value

Re: [GENERAL] ftell mismatch with expected position

On 10/29/2015 02:51 AM, Eelke Klein wrote: I am getting the following warning when our program runs pg_dump.exe and the output is in custom format and send to standard out which is connected to a pipe (Windows platform). pg_dump: [custom archiver] WARNING: ftell mismatch with expected position

Re: [GENERAL] Domain check constraint not honored?

On 10/29/15 5:29 PM, Eric Schwarzenbach wrote: I'm just now converting that path to use a custom domain (along with custom operators) instead of just being a string. (The custom operators allow the paths to be sorted properly without each segment needing to be filled with zeros to a fixed

Re: [GENERAL] Hierarchical Query Question (PHP)

David, Does wrapping the transaction with BEGIN; COMMIT; work as you would expect? $sql = "BEGIN; with recursive hier(taxon,parent_id) as ( select m.taxon, null::integer from gz_life_mammals m where taxon='Mammalia' --<< substitute me union all select m.taxon, m.parent_id from hier,

Re: [GENERAL] Domain check constraint not honored?

Thank you! (Slapping head) Your regexp seems to do the trick. On 10/29/2015 01:49 PM, Rob Sargent wrote: On 10/29/2015 11:41 AM, Eric Schwarzenbach wrote: I have created a custom type as a domain based on text, which adds a check constraint using a regexp to limit it to containing digits and

Re: [GENERAL] mysql_fdw trouble

On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver wrote: > On 10/29/2015 05:38 PM, Dane Foster wrote: > >> Hello, >> >> I think I've tripped over another mysq_fdw bug. I've filed a bug report >> on github already but just in case the problem is w/ my query I figured >> I

Re: [GENERAL] mysql_fdw trouble

On 10/30/2015 07:21 AM, Dane Foster wrote: On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver > wrote: On 10/29/2015 05:38 PM, Dane Foster wrote: Hello, I think I've tripped over another mysq_fdw bug. I've filed a

Re: [GENERAL] mysql_fdw trouble

On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver wrote: > On 10/30/2015 07:21 AM, Dane Foster wrote: > >> >> On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver >> > wrote: >> >> On 10/29/2015 05:38 PM, Dane

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 3:47 PM, David Blomstrom wrote: No, I get the same T_FUNCTION error. Someone commented that the function... create function tax_rank(id integer) returns text as $$ select case id when 1 then 'Classes' when 2 then 'Orders' when 3

Re: [GENERAL] Hierarchical Query Question (PHP)

Ah, yes - "Execute SQL." It created the table this time. Awesome. One other question - when I close the SQL window, it asks me if I want to save the file. Is there any special reason for saving it? It looks like it simply saved a copy of the query I executed. On Fri, Oct 30, 2015 at 3:36 PM,

Re: [GENERAL] mysql_fdw trouble

On 10/30/2015 08:13 AM, Dane Foster wrote: On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver > wrote: On 10/30/2015 07:21 AM, Dane Foster wrote: On Fri, Oct 30, 2015 at 9:40 AM, Adrian Klaver

Re: [GENERAL] mysql_fdw trouble

On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver wrote: > On 10/30/2015 08:13 AM, Dane Foster wrote: > >> >> On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver >> > wrote: >> >> On 10/30/2015 07:21 AM, Dane

Re: [GENERAL] pgxs/config/missing is... missing

On Oct 29, 2015, at 7:22 PM, Jim Nasby wrote: > I'm not sure if this is the right way to go about it, but this patch at least > installs the file. Which seems like a decent idea. I’d like a way to know when Perl is missing, though. What does `missing` do? D

Re: [GENERAL] pgxs/config/missing is... missing

David E. Wheeler wrote: > On Oct 29, 2015, at 7:22 PM, Jim Nasby wrote: > > > I'm not sure if this is the right way to go about it, but this patch at > > least installs the file. > > Which seems like a decent idea. I’d like a way to know when Perl is missing, >

Re: [GENERAL] mysql_fdw trouble

On 10/30/2015 09:36 AM, Dane Foster wrote: On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver > wrote: On 10/30/2015 08:13 AM, Dane Foster wrote: On Fri, Oct 30, 2015 at 10:57 AM, Adrian Klaver

Re: [GENERAL] mysql_fdw trouble

On Fri, Oct 30, 2015 at 1:02 PM, Adrian Klaver wrote: > On 10/30/2015 09:55 AM, Dane Foster wrote: > >> On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver >> > wrote: >> >> On 10/30/2015 09:36 AM, Dane

Re: [GENERAL] mysql_fdw trouble

On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver wrote: > On 10/30/2015 09:36 AM, Dane Foster wrote: > >> On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver >> > wrote: >> >> On 10/30/2015 08:13 AM, Dane

Re: [GENERAL] mysql_fdw trouble

On 10/30/2015 09:55 AM, Dane Foster wrote: On Fri, Oct 30, 2015 at 12:52 PM, Adrian Klaver > wrote: On 10/30/2015 09:36 AM, Dane Foster wrote: On Fri, Oct 30, 2015 at 12:26 PM, Adrian Klaver

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/29/2015 7:18 PM, David Blomstrom wrote: Can anyone tell me how to write the query described @ http://stackoverflow.com/questions/33402831/count-descendants-in-hierarchical-query ? The answer's very thorough, but I don't know how to string two queries and a function together like that.

Re: [GENERAL] Hierarchical Query Question (PHP)

Just so I understand what's going on, I can create a lookup table by pasting this code... create table taxon ( taxonid serial, descr text ); create table gz_life_mammals ( id serial, taxonid integer, -- use the lookup table parentid integer -- use the lookup table ); ...into pgAdmin

Re: [GENERAL] Hierarchical Query Question (PHP)

On 10/30/2015 04:38 PM, David Blomstrom wrote: Ah, yes - "Execute SQL." It created the table this time. Awesome. One other question - when I close the SQL window, it asks me if I want to save the file. Is there any special reason for saving it? It looks like it simply saved a copy of the

Re: [GENERAL] Domain check constraint not honored?

On 10/30/15 12:50 PM, Eric Schwarzenbach wrote: On 10/30/2015 09:53 AM, Jim Nasby wrote: On 10/29/15 5:29 PM, Eric Schwarzenbach wrote: I'm just now converting that path to use a custom domain (along with custom operators) instead of just being a string. (The custom operators allow the paths

Re: [GENERAL] Configure Different Databases on One Server

On 10/29/15 5:01 PM, Thomas Kellerer wrote: So no harm in keeping it enabled - plus this smells like premature optimization. I would not touch this unless you _really_ see a performance problem that is cause by autovacuum on that database. Moreso, if you think the problem is autovacuum (in

Re: [GENERAL] Unable to select a table as postgres user

The function is created as postgres user. And I get a permission denied error. -- View this message in context: http://postgresql.nabble.com/Unable-to-select-a-table-as-postgres-user-tp5872036p5872055.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via

Re: [GENERAL] pgxs/config/missing is... missing

On 10/30/15 11:13 AM, Alvaro Herrera wrote: David E. Wheeler wrote: On Oct 29, 2015, at 7:22 PM, Jim Nasby wrote: I'm not sure if this is the right way to go about it, but this patch at least installs the file. Which seems like a decent idea. I’d like a way to

Re: [GENERAL] Domain check constraint not honored?

On 10/30/2015 09:53 AM, Jim Nasby wrote: On 10/29/15 5:29 PM, Eric Schwarzenbach wrote: I'm just now converting that path to use a custom domain (along with custom operators) instead of just being a string. (The custom operators allow the paths to be sorted properly without each segment needing

Re: [GENERAL] pgxs/config/missing is... missing

On Oct 30, 2015, at 11:38 AM, Jim Nasby wrote: > Given what pgTap's Makefile is using perl for, perhaps the best bet is to > just ignore whatever PGXS has to say about it. So add a check to see if it ends in “missing perl”? Suggested Makefile-foo for that? D

Re: [GENERAL] pgxs/config/missing is... missing

On 10/30/15 2:55 PM, David E. Wheeler wrote: On Oct 30, 2015, at 11:38 AM, Jim Nasby wrote: Given what pgTap's Makefile is using perl for, perhaps the best bet is to just ignore whatever PGXS has to say about it. So add a check to see if it ends in “missing perl”?