Re: [GENERAL] Question about permissions on database.
On 2012-09-22 23:47, Ryan Kelly wrote: On Sat, Sep 22, 2012 at 11:35:00PM +0300, Condor wrote: Hello, I wanna ask: is there a short way to giver permission to one user to select/insert (all privileges) on whole database ? Im create a user and try to give him all permission on existing database, but when I try to select always got: ERROR: permission denied for relation table_name I simple do: GRANT ALL PRIVILEGES ON DATABASE my_db TO my_user; and when I do that my idea and what Im trying to do is to give all privileges on for select, insert, update ... using sequences, exec functions to one user, but when I try to select, I receive error message: ERROR: permission denied for relation table_name I look at documentation and remained less scarred about how many grants I should do for tables, for sequences, execution. Im using postgresql 9.2 You don't want to GRANT on the database. That doesn't do what you think it does. You, however, can do: GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user; Before I write first email I try this query: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user; but result was the same error: ERROR: permission denied for relation table_name This is documented clearly here: http://www.postgresql.org/docs/9.2/static/sql-grant.html This is generally a bad idea. You can alternatively make the user a super user: ALTER ROLE your_user WITH SUPERUSER; But this is an even worse idea. If one role owns all the tables in that database, you can make your role a member of that role: GRANT owner_role TO your_role; But are you really sure that your user needs permissions on everything? Yes, well not for everything but for 90% of the tables. The person before me use postgres user in front end apache to storing and fetching data from tables. Front end work with whole database, insert new data on tables, fetching. I want to replace usage of postgres super user. Last night when read documentation I understand it exact as what David J explain in second mail on the list. Why I say "little scared" because tables in database is over 150 plus sequences .. almost 200 objects. Giving separate permission on every single table to user, this it's seems bad idea for me, because in future if I need to change something or add/remove new tables I always should give permissions and if I forgot front end will display errors. From other side is a very good that I can say what user in which tables and fields have permissions, but it's seems in this case I can't use it. Last think that coming in my mind is to change owner to all tables in database my_db from current user (postgres) to my new user (my_user). Any advice what I can do ? Cheers, C -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RFE: Column aliases in WHERE clauses
On Sat, Sep 22, 2012 at 12:23 AM, Jasen Betts wrote: > On 2012-09-18, Rafal Pietrak wrote: > > > > Actual Tom's example(1): > > SELECT 1/x AS inverse FROM data WHERE x <> 0; > > extended to (2): > > SELECT 1/x AS inverse FROM data WHERE x <> 0 AND 1/x > 20; > > could be written by user as (3): > > SELECT 1/x AS inverse FROM data WHERE x <> 0 AND inverse > 20; > > but token/replaced to its form (2) before WHERE evaluation. > > Macros are confusing: > > select random()*10 as confusion from generate_series(1,10) > where confusion > 5; > > Also you can already do this: CREATE FUNCTION inverse(data) RETURNS NUMERIC LANGUAGE SQL IMMUTABLE AS $$ select case when $1.x = 0 then null else 1/$1.x end; $$; Then it can be used as a macro: SELECT d.inverse FROM data d WHERE d.x <> 0 AND d.inverse > 0.5; Wondering if we want to support something like this, essentially anonymous functions, if we shouldn't extend the WITH clause to support something like WITH FUNCTION for cases where you don't want your macro to persist. I don't know though. Are there cases where you don't want the macro to persist? Best Wishes, Chris Travers
Re: [GENERAL] pg_dump slow on windows
On 2012-09-06, Kobus Wolvaardt wrote: > Something that is curios is that if a DB takes long, it really takes > horribly long like some kind of a lock is holding it. It would sit at a few > kb dump size for 20 minutes en then run a bit and get stuck again (as far > as we can tell), what we do know is that it is way to slow for some IO or > cpu starvation. We have seen a 5GB backup sitting at 1Gb after 12hours and > then we stop it. > Any siggestions? Can autovacume or lack thereof cause this? It seems noone > has been doing any maintenance on the DB (it does look like autovacuum is > running), so any suggestions would be nice. perhaps some sort of DML could cause this, I've seen DML hang during dumps turn on logging of slow queries and check the logs after the next speed disaster. -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] RFE: Column aliases in WHERE clauses
On 2012-09-18, Rafal Pietrak wrote: > > Actual Tom's example(1): > SELECT 1/x AS inverse FROM data WHERE x <> 0; > extended to (2): > SELECT 1/x AS inverse FROM data WHERE x <> 0 AND 1/x > 20; > could be written by user as (3): > SELECT 1/x AS inverse FROM data WHERE x <> 0 AND inverse > 20; > but token/replaced to its form (2) before WHERE evaluation. Macros are confusing: select random()*10 as confusion from generate_series(1,10) where confusion > 5; -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] On Ubuntu 12.04 i do have two psql one of those isn't working
On 2012-09-18, Raymond O'Donnell wrote: > On 18/09/2012 16:10, Yvon Thoraval wrote: >> I've found the prob. >> In my postgresql.conf file the default port is setup to 5433 instead of >> 5432 as previously... > > OK - you probably had two versions of PG installed at some point - when > you install a second version of PG alongside an existing one on Debian & > Ubuntu, it automatically puts it on 5433. Debian's pg_upgradecluster will leave you with the new cluster running on 5433. (it's not pg_upgrade, it's some sort of script) ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about permissions on database.
On 09/23/2012 04:35 AM, Condor wrote: I look at documentation and remained less scarred about how many grants I should do for tables, for sequences, execution. You probably need to read this: http://stackoverflow.com/questions/11599533/postgresql-8-4-grant-dml-privileges-on-all-tables-to-a-role That while that question is about 8.4 so it doesn't cover ALTER DEFAULT PRIVILEGES (http://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html), which is the right way to to solve this going forward. It should be useful, though. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about permissions on database.
On Sun, Sep 23, 2012 at 6:47 AM, Ryan Kelly wrote: > On Sat, Sep 22, 2012 at 11:35:00PM +0300, Condor wrote: >> Hello, >> I wanna ask: is there a short way to giver permission to one user to >> select/insert (all privileges) on whole database ? >> Im create a user and try to give him all permission on existing >> database, but when I try to select always got: >> ERROR: permission denied for relation table_name > You don't want to GRANT on the database. That doesn't do what you think > it does. You, however, can do: > > GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user; > > This is generally a bad idea. > > You can alternatively make the user a super user: > > ALTER ROLE your_user WITH SUPERUSER; > > But this is an even worse idea. I have a similar situation; I want to have a userid for doing the regular backups, which therefore (for pg_dump) needs SELECT privilege on all tables. Is: GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_userid; GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup_userid; the right way to do this? (Obviously I need to also do this for every other schema I use.) Alternatively, is there some better way to do backups? I'd rather not snapshot the entire data directory; we burn to disc, so a larger backup requires more media and slower backup/restore process. ChrisA -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about permissions on database.
On Sep 22, 2012, at 16:35, Condor wrote: > Hello, > I wanna ask: is there a short way to giver permission to one user to > select/insert (all privileges) on whole database ? > Im create a user and try to give him all permission on existing database, but > when I try to select always got: > ERROR: permission denied for relation table_name > > I simple do: > GRANT ALL PRIVILEGES ON DATABASE my_db TO my_user; > and when I do that my idea and what Im trying to do is to give all privileges > on for select, insert, update ... using sequences, exec functions > to one user, but when I try to select, I receive error message: > ERROR: permission denied for relation table_name > > I look at documentation and remained less scarred about how many grants I > should do for tables, for sequences, execution. > Im using postgresql 9.2 > Each object type needs to be handled independently. A database is a distinct object type with its own actions to allow/disallow (mainly connect, usage?). You need to issue similar commands for schemas, tables, functions, etc... Would suggest creating group (i.e., non-login) roles and grant to those then make the user (i.e., login) roles a member of the appropriate group role(s). David J. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about permissions on database.
On Sat, Sep 22, 2012 at 11:35:00PM +0300, Condor wrote: > Hello, > I wanna ask: is there a short way to giver permission to one user to > select/insert (all privileges) on whole database ? > Im create a user and try to give him all permission on existing > database, but when I try to select always got: > ERROR: permission denied for relation table_name > > I simple do: > GRANT ALL PRIVILEGES ON DATABASE my_db TO my_user; > and when I do that my idea and what Im trying to do is to give all > privileges on for select, insert, update ... using sequences, exec > functions > to one user, but when I try to select, I receive error message: > ERROR: permission denied for relation table_name > > I look at documentation and remained less scarred about how many > grants I should do for tables, for sequences, execution. > Im using postgresql 9.2 You don't want to GRANT on the database. That doesn't do what you think it does. You, however, can do: GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user; This is documented clearly here: http://www.postgresql.org/docs/9.2/static/sql-grant.html This is generally a bad idea. You can alternatively make the user a super user: ALTER ROLE your_user WITH SUPERUSER; But this is an even worse idea. If one role owns all the tables in that database, you can make your role a member of that role: GRANT owner_role TO your_role; But are you really sure that your user needs permissions on everything? -Ryan Kelly -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about permissions on database.
Hello, I wanna ask: is there a short way to giver permission to one user to select/insert (all privileges) on whole database ? Im create a user and try to give him all permission on existing database, but when I try to select always got: ERROR: permission denied for relation table_name I simple do: GRANT ALL PRIVILEGES ON DATABASE my_db TO my_user; and when I do that my idea and what Im trying to do is to give all privileges on for select, insert, update ... using sequences, exec functions to one user, but when I try to select, I receive error message: ERROR: permission denied for relation table_name I look at documentation and remained less scarred about how many grants I should do for tables, for sequences, execution. Im using postgresql 9.2 C. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL debugger and PostgreSQL 9.2
Is there link to download pldbgapi.dll on postgresql 9.2 x64 on windows Thanks in advance Tuan Hoang Anh On Fri, Sep 14, 2012 at 9:29 PM, Albe Laurenz wrote: > Fabrízio de Royes Mello wrote: > >> Has it moved somewhere else? > > > Look at the pldebugger git repository in [1]. > > Thanks! > > Laurenz Albe > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] Curosity question regarding "LOCK" NOWAIT
On 22 September 2012 05:08, John R Pierce wrote: > On 09/21/12 7:43 PM, David Johnston wrote: >> >> Has there been any discussion regarding adding a time-limited version of >> NOWAIT, say: “WAITONLY 50” (milliseconds), when dealing the explicit LOCK >> TABLE or the SELECT…FOR(SHARE|UPDATE) commands? > > > is this a feature in any other major databases? There may be other/better ways to do this, I'm only a casual user of at least one of these, but here is what I could find for the big three: Oracle: Error if locks can't be obtained immediately: SELECT ... FOR UPDATE NOWAIT Error if locks can't be obtained in : SELECT ... FOR UPDATE WAIT Skip rows that can't be locked immediately: SELECT ... FOR UPDATE SKIP LOCKED Microsoft SQL Server: Error if locks can't be obtained immediately: SELECT ... FOR UPDATE WITH (NOWAIT) Error if locks can't be obtained in : SET LOCK_TIMEOUT SELECT ... FOR UPDATE Skip rows that can't be locked immediately: SELECT ... FOR UPDATE WITH (READPAST) IBM DB2 Error if locks can't be obtained immediately: SET CURRENT LOCK TIMEOUT NOWAIT SELECT ... FOR UPDATE Error if locks can't be obtained in : SET CURRENT LOCK TIMEOUT WAIT SELECT ... FOR UPDATE Skip rows that can't be locked immediately: -- currently z/OS version only SELECT ... FOR UPDATE SKIP LOCKED ROWS > is this in the sql spec? My understanding is that the SQL 1992 spec doesn't talk about locking directly, it talks about isolation levels and updatability (and I don't have accesss to the 2003 spec to check if that has changed). Although it does standardise FOR UPDATE ( which is an optional part of a ), it's not for explicit locking, it specifies that a cursor is updatable. Locking is an implementation matter (and the use of FOR UPDATE outside of a , ie in a , may be non-standard anyway). NOWAIT is not an ANSI SQL keyword, and WAIT is a keyword reserved for future use. Regards, Thomas Munro -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.1 vs 8.4 performance
On 09/21/2012 10:32 PM, salah jubeh wrote: I am running queries sequentially on each machine using a database dumped from a life server , and 9.1 server is much slower than 8.4. https://wiki.postgresql.org/wiki/Slow_Query_Questions More detail needed. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curosity question regarding "LOCK" NOWAIT
2012-09-22 06:08 keltezéssel, John R Pierce írta:
On 09/21/12 7:43 PM, David Johnston wrote:
Has there been any discussion regarding adding a time-limited version of NOWAIT, say:
“WAITONLY 50” (milliseconds), when dealing the explicit LOCK TABLE or the
SELECT…FOR(SHARE|UPDATE) commands?
is this a feature in any other major databases?
Yes, at least Informix has it in the form of SET LOCK MODE TO { NOT WAIT | WAIT
[N] }.
is this in the sql spec?
No.
what do you expect to happen if these timeouts expire? return an error, and abort the
transaction?
--
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curosity question regarding "LOCK" NOWAIT
2012-09-22 07:55 keltezéssel, Josh Kupershmidt írta: On Fri, Sep 21, 2012 at 7:43 PM, David Johnston wrote: Has there been any discussion regarding adding a time-limited version of NOWAIT, say: “WAITONLY 50” (milliseconds), when dealing the explicit LOCK TABLE or the SELECT…FOR(SHARE|UPDATE) commands? I think you could do this by issuing SET statement_timeout = 50; and then attempting the LOCK TABLE or SELECT ... FOR UPDATE command, without NOWAIT. You'll either get an "ERROR: canceling statement due to statement timeout" or the command should succeed. This is not the same as the handling of a timeout waiting for a lock. The statement_timeout may also trigger when returning large result sets without locking, i.e. in a plain SELECT case. Best regards,, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Curosity question regarding "LOCK" NOWAIT
Hi, 2012-09-22 04:43 keltezéssel, David Johnston írta: Has there been any discussion regarding adding a time-limited version of NOWAIT, say: "WAITONLY 50" (milliseconds), when dealing the explicit LOCK TABLE or the SELECT...FOR(SHARE|UPDATE) commands? David J. we have a proposed patch for 9.3 at http://archives.postgresql.org/message-id/50339b3d.8040...@cybertec.at that adds "SET lock_timeout = N;" to have a time-limited wait for locks. Best regards, Zoltán Böszörményi -- -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/
