[GENERAL] debugging with child process
hi, can any one help me with the 4th and 5th steps of the following link which is to debug with a child process in postgres. *https://wiki.postgresql.org/wiki/Working_with_Eclipse#Debugging_with_child_processes https://wiki.postgresql.org/wiki/Working_with_Eclipse#Debugging_with_child_processes* when I give the following fields in the debug configuration *c/c++ Application : postgres* *Project - psql* *Build Configuration - Use Active* I am getting the following error- * No source available for __kernel_vsyscall() at 0xb726a424 * Is there any better tool other than eclipse to use for editing or knowing about source code in postgres. Thank you
Re: [GENERAL] Re: collecting employees who completed 5 and 10 years in the current month
Right you are David re my first query. That'll be more appropriate if you want to establish if they're in their 5th year, or 10th year. On Mon, Jun 30, 2014 at 6:08 PM, David G Johnston david.g.johns...@gmail.com wrote: Rebecca Clarke-2 wrote create view vw_employee as select * from employees where ((age(joining_date::date) like '5 years%') or (age(joining_date::date) like '10 years%') ) This does not give the correct answer to the poster's question - the LIKE with a trailing % will pick up non-round intervals. create view vw_employee as select * from employees where ((to_char(joining_date, '-MM') = to_char((now() - interval '5 years'), '-MM') ) or (to_char(joining_date, '-MM') = to_char((now() - interval '10 years'), '-MM'))) This works - find out what year-month it was x years ago and compare it to the corresponding year-month of the requested date. If one were to be doing this often it would probably be worth while to either use a functional index or a trigger-maintained field to store the to_char(joining_date) calculation. WHERE joining_date_yearmonth = ANY( ARRAY['2009-06','1999-06']::text[] ); Was also pondering using a VARIADIC function to pass in integer year(s), which would then be converted into the corresponding array. Haven't actually played with the above and so not sure how index-friendly the =ANY(...) construct is but it does allow you to avoid add entire OR clauses and instead simply supply a different comparison array. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/collecting-employees-who-completed-5-and-10-years-in-the-current-month-tp5809762p5809828.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] collecting employees who completed 5 and 10 years in the current month
From my understanding of what you're saying, you want all the employees that have a 5 year, or 10 year anniversary between today and the start of the current month? If that is the case, then this is what I came up with: select employee_name, to_char(current_date, '')::integer - to_char(joining_date::date, '')::integer as milestone, joining_date + (current_date - joining_date) as anniversary_date from employees where ((joining_date::date + interval '5 years') = to_char(current_date, '-MM-1')::date and (joining_date::date + interval '5 years') = current_date) or ((joining_date::date + interval '10 years') = to_char(current_date, '-MM-1')::date and (joining_date::date + interval '10 years') = current_date) Once again, excuse any syntax errors. On Mon, Jun 30, 2014 at 5:15 PM, Arup Rakshit arupraks...@rocketmail.com wrote: On Monday, June 30, 2014 04:52:32 PM Rebecca Clarke wrote: Hi Arup, Two ways come to mind for me. They're pretty much the same as Szymon's, just minus the sample table creation. I would suggest creating a view instead, so you can just select from it whenever you please. create view vw_employee as select * from employees where ((age(joining_date::date) like '5 years%') or (age(joining_date::date) like '10 years%') ) or create view vw_employee as select * from employees where ((to_char(joining_date, '-MM') = to_char((now() - interval '5 years'), '-MM') ) or (to_char(joining_date, '-MM') = to_char((now() - interval '10 years'), '-MM'))) Can this query be set up like :- Consider the below scenarios : Ram completed 5 years on 12/04/2014 Shyam completed 5 years on 21/04/2014 Ayan completed 10 years on 12/04/2014 and so on... Now consider the current month is *march*. I have 12 employees. Out of which above only completed 5 and 10 years. Thus my output should come as Name milestoneswhen Ram 512/04/2014 Shyam 5 21/04/2014 Ayan10 12/04/2014 -- Regards, Arup Rakshit Debugging is twice as hard as writing the code in the first place. Therefore, if you write the code as cleverly as possible, you are, by definition, not smart enough to debug it. --Brian Kernighan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Migration error invalid byte sequence for encoding UTF8: 0xff from mysql 5.5 to postgresql 9.1
When I do migration from Mysql to PostgreSQL: firstly dump data from mysql in cmd(encoding is GBK) is WIN8: mysqldump -v -nt --complete-insert=TRUE --compatible=postgresql --default-character-set=utf8 --skip-add-locks --compact --no-create-info --skip-quote-names -uroot -p test dbdata.sql then load data to postgresql in cmd(encoding is GBK) is WIN8: psql -h localhost -d test -U postgres dbdata.sql I got the error: ERROR: invalid byte sequence for encoding UTF8: 0xff I checked in dbdata.sql using UltraEdit, data 0xff really exists as followings: 蛾4?4抿\0xfF???±??x¤? and I use UltraEdit menu- view - encoding page to get: 936 GBK. DDL in Mysql 5.5 is: CREATE TABLE `personpicture` ( `ID` char(32) NOT NULL, `Picture` mediumblob, ... KEY `personId` (`PersonID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DDL in PostgreSQL 9.1 is: create database test encoding 'utf8'; CREATE TABLE personpicture ( ID char(32) NOT NULL, Picture BYTEA, PRIMARY KEY (ID) ); The error is related to Picture mediumblob and BYTEA, which is stored pic binary data. My Operationg system is WIN8 64bit. I've set postgresql.conf before starting everything, but error still occurs: backslash_quote = on escape_string_warning = off standard_conforming_strings = off How to resolve it? Thanks! peng
Re: [GENERAL] Migration error invalid byte sequence for encoding UTF8: 0xff from mysql 5.5 to postgresql 9.1
sunpeng blueva...@gmail.com wrote: load data to postgresql in cmd(encoding is GBK) is WIN8: psql -h localhost -d test -U postgres dbdata.sql I got the error: ERROR: invalid byte sequence for encoding UTF8: 0xff If the encoding is GBK then you will get errors (or incorrect characters) if it is read as UTF8. Try setting the environment variable PGCLIENTENCODING. http://www.postgresql.org/docs/9.1/static/app-psql.html -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Next steps in debugging database storage problems?
Hi We have a PostgreSQL 9.3.4 running in an LXC container on Debian Wheezy on a Linux 3.10.43 kernel on a Dell R620 server. Data are stored on a XFS file system. We are seeing problems such as: unexpected data beyond EOF in block 2 of relation base/805208133/1238511128 and could not read block 5 in file base/805208348/1259338118: read only 0 of 8192 bytes This seems to occur every few days after the server has been up for 30-40 days. If we reboot the server it'll be another 30-40 days before we see any problems again. The server has been running fine on a Dell R710 for a long time, and was upgraded to a Dell R620 last year, when the problems started. We have tried switching to a different Dell R620, but that did not make a difference. We've seen this with kernels 3.2, 3.4 and 3.10. The two tables that run into these problems are very simple, but rather busy. They are defined like: CREATE TABLE jms_messages ( messageid integer NOT NULL, destination text NOT NULL, txid integer, txop character(1), messageblob bytea ); and CREATE TABLE jms_transactions ( txid integer ); PostgreSQL does complain that it's likely due to a buggy kernel, but then I would have expected to see problems with some of our other machines running this kernel on similar hardware as well. We don't have any systematic way of reproducing the problem at this point, except leaving our database server running for a month and seeing it fail, so I'm hoping that someone here can help me with some next steps in debugging this. We have multiple other PostgreSQL servers running in a similar setup without causing any problems, but this server is probably the busiest of our PostgreSQL servers. I've tried writing a program to simulate a workload that resembles the workload on the problematic tables, but I can't get that to fail. So what should be my next step in debugging this? Best regards Jacob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Validating User Login Within Postgres
I'm developing a new application and want to take advantage of postgres features such as triggers and stored procedures and put as much of the middleware 'business logic' into the database engine as is practical. Is it possible, or practical, to validate a potential user login within the database? What I've done in the past is search the User table for that name and password, return it to the application, and have a Python script see if the entered username and password match that stored in the table. All pointers and suggestions welcome. Rich -- 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] Validating User Login Within Postgres
On Tue, Jul 1, 2014 at 3:58 PM, Rich Shepard rshep...@appl-ecosys.com wrote: I'm developing a new application and want to take advantage of postgres features such as triggers and stored procedures and put as much of the middleware 'business logic' into the database engine as is practical. Is it possible, or practical, to validate a potential user login within the database? What I've done in the past is search the User table for that name and password, return it to the application, and have a Python script see if the entered username and password match that stored in the table. That depends. For example - for system that will have 5 users, and requires strict security policies - it would make sense. On the other hand, for website, with thousands of users, putting them all as actual roles in Pg doesn't make much sense, and could potentially cause problem. When I write apps I tend to create database users per *type* of application that will use it, and then handle application users using table in my database. So, for example, I might have app_dba account (the one that owns all objects, and is used to create tables/views/functions/...), app_website account (the one used by application to handle web requests), app_cronjob (for general cronjobs, or sometimes specialized app_cron_whatever for every cronjob). Also, if you're thinking about security - please consider reading http://www.depesz.com/2007/08/18/securing-your-postgresql-database/ . Best regards, depesz
Re: [GENERAL] how to create multiple databases running in different dirs
Hey, postgres already takes care of multiple client writting/reading, so you don't really need to be afraid of concurrency (for most of the stuff) If it is so your desire, you could also have multiple server on the same machine (althought on different port). This way each server would have its own repository. Cheers, Rémi-C 2014-07-01 4:59 GMT+02:00 John R Pierce pie...@hogranch.com: On 6/30/2014 4:58 PM, frank ernest wrote: Hi, I'm new to postgresql and sql in general. I desired to write a program in C that used an sql data base for IPC and because multiple copies of my program might run on the same machine I wanted a way to ensure that only one copy of each multithreaded program got one database but I'm uncertain how to go about this. As the program might be run several different times and each time it should only get the data base from it's predisesor I can't use the pid as a unique data base name. I thought that I might start multiple pgsql instances but somehow that seems wrong. Any ideas? how would an instance of your program know what to connect to, or which previous instance its 'predecessor' was ? normally, you have ONE database for a given set of applications, and all the applications share the same database tables and such. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] Validating User Login Within Postgres
On Tue, 1 Jul 2014, hubert depesz lubaczewski wrote: That depends. For example - for system that will have 5 users, and requires strict security policies - it would make sense. On the other hand, for website, with thousands of users, putting them all as actual roles in Pg doesn't make much sense, and could potentially cause problem. Hubert, OK. This is not a Web-based application, but the users could number in the dozens. When I write apps I tend to create database users per *type* of application that will use it, and then handle application users using table in my database. There are four roles, each with different privileges. I plan to read about postgres roles today to take advantage of that. So, for example, I might have app_dba account (the one that owns all objects, and is used to create tables/views/functions/...), app_website account (the one used by application to handle web requests), app_cronjob (for general cronjobs, or sometimes specialized app_cron_whatever for every cronjob). From the user perspective there are four roles: one system 'admin' who adds and deletes users and assigns each to one of the other three roles; 'executive' whose interest is in viewing reports and stored documents (such as permits) but who do not otherwise interact with the application; 'manager' who can add, delete, modify data and view all reports; and 'technician' who can add data, analyze results, and generate reports. Also, if you're thinking about security - please consider reading http://www.depesz.com/2007/08/18/securing-your-postgresql-database/ . Will do. Thanks, Rich -- 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] How does the PostgreSQL partition pruning happens ?
Got following answer, http://dba.stackexchange.com/questions/69411/postgresql-partition-pruning/69413#69413 -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-does-the-PostgreSQL-partition-pruning-happens-tp5809921p5809923.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How does the PostgreSQL partition pruning happens ?
Hi , I'm new to postgres and going through it's documentation. I have problem about partition pruning in postgres. In postgres partition is done by child tables. My problem are, 1. when we issue a query to master table , does the query optimizer do partition pruning ? i.e select appropriate child table rather than going through all the child tables. 2. How does the query optimizer do the partition pruning ? My concern is unlike other databases we don't give partition column in postgres partitioning process. So how does the query optimizer knows what is the partition column ? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-does-the-PostgreSQL-partition-pruning-happens-tp5809921.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help debugging database storage problems
Hi We have a PostgreSQL 9.3.4 running in an LXC container on Debian Wheezy on a Linux 3.10.43 kernel on a Dell R620 server. Data are stored on a XFS file system. We are seeing problems such as: unexpected data beyond EOF in block 2 of relation base/805208133/1238511128 and could not read block 5 in file base/805208348/1259338118: read only 0 of 8192 bytes This seems to occur every few days after the server has been up for 30-40 days. If we reboot the server it'll be another 30-40 days before we see any problems again. The server has been running fine on a Dell R710 for a long time, and was upgraded to a Dell R620 last year, when the problems started. We have tried switching to a different Dell R620, but that did not make a difference. We've seen this with kernels 3.2, 3.4 and 3.10. The two tables that run into these problems are very simple, but rather busy. They are defined like: CREATE TABLE jms_messages ( messageid integer NOT NULL, destination text NOT NULL, txid integer, txop character(1), messageblob bytea ); and CREATE TABLE jms_transactions ( txid integer ); PostgreSQL does complain that it's likely due to a buggy kernel, but then I would have expected to see problems with some of our other machines running this kernel on similar hardware as well. We don't have any systematic way of reproducing the problem at this point, except leaving our database server running for a month and seeing it fail, so I'm hoping that someone here can help me with some next steps in debugging this. We have multiple other PostgreSQL servers running in a similar setup without causing any problems, but this server is probably the busiest of our PostgreSQL servers. I've tried writing a program to simulate a workload that resembles the workload on the problematic tables, but I can't get that to fail. So what should be my next step in debugging this? Best regards Jacob -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL]
I have a question regarding two-way encryption data for specific columns. Does anyone have a technique or recommendation for two-way encryption which somehow obfuscates the decrypt key so that it isn't easily retrievable from the database or the application source code? We've already considered (a) letting users hold the decrypt key and (b) obfuscating the decrypt key with the user's own (one-way encrypted) password, but neither of these approaches are viable for us.
[GENERAL] Two-way encryption
I have a question regarding two-way encryption data for specific columns. Does anyone have a technique or recommendation for two-way encryption which somehow obfuscates the decrypt key so that it isn't easily retrievable from the database or the application source code? We've already considered (a) letting users hold the decrypt key and (b) obfuscating the decrypt key with the user's own (one-way encrypted) password, but neither of these approaches are viable for us.
Re: [GENERAL] how to create a role with no privileges?
Thanks for your reply. Actually, AFAICT, revoking usage on the schemas you listed seems to have no effect at all on the minimal role's ability to use \l, \d, \dt, etc. In particular, the minimal role still has access to the shell through \!. YIKES!!! kynn On Mon, Jun 30, 2014 at 5:37 PM, Jerry Sievers gsiever...@comcast.net wrote: Kynn Jones kyn...@gmail.com writes: How does one define the most limited role/user possible in PostgreSQL? Ideally, this role would not be able to do *anything* at all. In particular, this role would not be able to query meta-information about existing tables, functions, etc. with backslash commands such as \dt, \df. Some new role created and not granted anything has only public rights which by default is the lowest level of privilege but as you probably are aware does permit creating objects in public schema and viewing certain system info. But you can revoke usage on schemas; public, pg_catalog, information_schema to create the illusion of even tighter than default perms. You would then need to grant usage on those schemas to some other role and give this role to real new roles/users who are permitted to do those things. (Of course, in practice such a role would not correspond to any real role. Its purpose, rather, is to serve as the starting point for defining more realistic roles by selectively adding the fewest privileges possible). TIA for any pointers! kynn -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
Re: [GENERAL] how to create a role with no privileges?
On Tue, Jul 1, 2014 at 6:13 PM, Kynn Jones kyn...@gmail.com wrote: Actually, AFAICT, revoking usage on the schemas you listed seems to have no effect at all on the minimal role's ability to use \l, \d, \dt, etc. In particular, the minimal role still has access to the shell through \!. YIKES!!! Without seeing your actual commands, it's difficult to know about the schema stuff, but one thing for sure, \! has nothing to do with databases permissions, it is related to local process permisions ( database is not contacted, it is a local shell access ), you have to restrict it with whichever tools your operating system has. It seems you are confussing the postgresql database with psql, which is just a C program linked with the libpq library to access a postgresql database server. You can easily rewrite it in, say, java using jdbc, or, if you read enough docs, in any language which allows socket access. Database permissions will apply to any of them. Francisco Olarte. -- 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] how to create multiple databases running in different dirs
On 7/1/2014 7:19 AM, Rémi Cura wrote: If it is so your desire, you could also have multiple server on the same machine (althought on different port). This way each server would have its own repository. which still doesn't answer the question, how would an instance of his program know which database (port) to connect to? -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] how to create a role with no privileges?
Kynn Jones kyn...@gmail.com writes: In particular, the minimal role still has access to the shell through \!. YIKES!!! What exactly do you find to be yikes about that? It's a shell under the user's own account on the client machine, ie, exactly like the shell account he invoked psql from. It doesn't really have anything to do with Postgres at all. regards, tom lane -- 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] how to create a role with no privileges?
On Tue, Jul 1, 2014 at 12:36 PM, Francisco Olarte fola...@peoplecall.com wrote: Without seeing your actual commands, it's difficult to know about the schema stuff... Well, the actual commands is what the original question was asking for, since I really don't know how to do any of this (I find the documentation very unclear on the matter). At any rate, what I tried was REVOKE ALL ON SCHEMA x FROM nopriv; REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA x FROM nopriv; ...replacing x by public, pg_catalog, and information_schema. kynn
Re: [GENERAL] lock contention, need profiling idea
There was no CREATE INDEX command running on the host. On Mon, Jun 30, 2014 at 5:06 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Tue, Jul 1, 2014 at 7:36 AM, AI Rumman rumman...@gmail.com wrote: I see lots of similar log message at a certain time in a day on Postgresql 9,.1: LOG: process 18855 still waiting for ShareLock on transaction 2856146023 after 1001.209 ms STATEMENT: UPDATE table1 SET time = $1 WHERE id = $2 The table1 size is 17 G. What could be the reason for this lock contention? autovacuum? This may be a CREATE INDEX query taking some time, perhaps combined with an old prepared transaction still holding a lock? Perhaps a cron job running behind that you are not aware of? You should have a look at pg_stat_activity, pg_prepared_xacts and pg_locks to get more information about the transactions running and the locks being taken. -- Michael
Re: [GENERAL] how to create a role with no privileges?
Kynn Jones wrote On Tue, Jul 1, 2014 at 12:36 PM, Francisco Olarte lt; folarte@ gt; wrote: Without seeing your actual commands, it's difficult to know about the schema stuff... Well, the actual commands is what the original question was asking for, since I really don't know how to do any of this (I find the documentation very unclear on the matter). At any rate, what I tried was REVOKE ALL ON SCHEMA x FROM nopriv; REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA x FROM nopriv; ...replacing x by public, pg_catalog, and information_schema. kynn The global PUBLIC pseudo-role, which all roles must inherit from, is giving permission on system catalogs. If you really want to create a role without such permissions - which is not advisable - you would have to REVOKE those permissions from PUBLIC so that they cannot be inherited. The first rule regarding PostgreSQL permissions is that everything is forbidden unless allowed - via GRANT. REVOKE simply undoes whatever has been granted; it does not put up a block to prevent inheritance of granted permissions. As Tom and Francisco noted the client tool psql provides pretty much zero access control capabilities for itself; the slash-commands will simply fail with a database error if the relevant permissions have not been granted to the user. The shell-access command \! allows the user to do anything could have done directly from the shell in which they launched psql. That said, if you allow them to run psql with elevated permissions then they will be able to do shell stuff via psql that they might be forbidden to do otherwise. This is not different than other programs (vim, emacs) that provide embedded shell access. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-create-a-role-with-no-privileges-tp5809861p5810029.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Best way to list a role’s owned objects?
Hi all, What is the best way to list a role’s owned objects in any database? My problem is that I need to be able to “FORCE DROP ROLE” by taking any objects that that role owns, in any database, and reassigning them to the “postgres” user. Even if REASSIGN OWNED BY worked for this purpose (it only works on the current database), I would still need a way to display to the API caller which objects were reassigned. Oh, and this preferably needs to work all the way back to PostgreSQL 8.1. :- Any ideas? Thanks! -Felipe Gasper Houston, TX -- 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] Best way to list a role’s owned objects?
On 7/1/2014 11:08 AM, Felipe Gasper wrote: What is the best way to list a role’s owned objects in any database? query pg_class in each database ? -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] Best way to list a role’s owned objects?
On 7/1/14 1:13 PM, John R Pierce wrote: On 7/1/2014 11:08 AM, Felipe Gasper wrote: What is the best way to list a role’s owned objects in any database? query pg_class in each database ? Every database on the cluster, individually, then? Is there no way to query all databases at once? I mean, *something* under the hood must be doing this because DROP ROLE bugs out if the role owns anything in any DB. -F -- 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] Best way to list a roles owned objects?
Felipe Gasper fel...@felipegasper.com writes: On 7/1/14 1:13 PM, John R Pierce wrote: On 7/1/2014 11:08 AM, Felipe Gasper wrote: What is the best way to list a roles owned objects in any database? query pg_class in each database ? Every database on the cluster, individually, then? Is there no way to query all databases at once? I mean, *something* under the hood must be doing this because DROP ROLE bugs out if the role owns anything in any DB. That is made possible by pg_shdepend catalog which makes note of shared dependencies however it will *not* inform you of what specific objects are depending unless you visit each such DB to find out. As for doing REASSIGN OWNED BY, as you mentioned earlier... A better practice might be to create a special role on your cluster (say orphaned_objects) and let this user take ownership of the depending objects. This makes possible for you to easily identify such items later rather then have them mixed up with everything postgres owns. The assumption is, that many of the things so reassigned are quite possibly junk, given that the real owner has been dropped from the system. -F -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] lock contention, need profiling idea
On Mon, Jun 30, 2014 at 5:36 PM, AI Rumman rumman...@gmail.com wrote: I see lots of similar log message at a certain time in a day on Postgresql 9,.1: LOG: process 18855 still waiting for ShareLock on transaction 2856146023 after 1001.209 ms STATEMENT: UPDATE table1 SET time = $1 WHERE id = $2 The table1 size is 17 G. What could be the reason for this lock contention? autovacuum? Please give some idea. Query pg_stat_activity and pg_locks on intervals and you'll get to the bottom of this. merlin -- 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] Best way to list a roles owned objects?
Jerry Sievers gsiever...@comcast.net writes: Felipe Gasper fel...@felipegasper.com writes: On 7/1/14 1:13 PM, John R Pierce wrote: On 7/1/2014 11:08 AM, Felipe Gasper wrote: What is the best way to list a roles owned objects in any database? query pg_class in each database ? Every database on the cluster, individually, then? Is there no way to query all databases at once? I mean, *something* under the hood must be doing this because DROP ROLE bugs out if the role owns anything in any DB. That is made possible by pg_shdepend catalog which makes note of shared dependencies however it will *not* inform you of what specific objects are depending unless you visit each such DB to find out. Minor clarification... You will get only OID data from pg_shdepend. Assuming that this isn't helpful for a human making a decision whether or not to drop a role and all it's dependencies and/or reassign them... Symbolic information will only be found in the related DB. Furthermore, you need to visit the related DB anyhow to run either of DROP OWNED BY or REASSIGN OWNED BY. HTH As for doing REASSIGN OWNED BY, as you mentioned earlier... A better practice might be to create a special role on your cluster (say orphaned_objects) and let this user take ownership of the depending objects. This makes possible for you to easily identify such items later rather then have them mixed up with everything postgres owns. The assumption is, that many of the things so reassigned are quite possibly junk, given that the real owner has been dropped from the system. -F -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800 -- 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] Best way to list a role s owned objects?
Jerry Sievers gsiever...@comcast.net writes: Felipe Gasper fel...@felipegasper.com writes: Every database on the cluster, individually, then? Is there no way to query all databases at once? I mean, *something* under the hood must be doing this because DROP ROLE bugs out if the role owns anything in any DB. That is made possible by pg_shdepend catalog which makes note of shared dependencies however it will *not* inform you of what specific objects are depending unless you visit each such DB to find out. Yeah. You can identify the kind of object represented by each entry, since classid values are common to all databases; but you can't resolve any more information than that unless you connect to the DB in question. This is because you can only see a given DB's system catalogs when connected to that DB. regards, tom lane -- 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] lock contention, need profiling idea
On Mon, Jun 30, 2014 at 5:06 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Tue, Jul 1, 2014 at 7:36 AM, AI Rumman rumman...@gmail.com wrote: I see lots of similar log message at a certain time in a day on Postgresql 9,.1: LOG: process 18855 still waiting for ShareLock on transaction 2856146023 after 1001.209 ms STATEMENT: UPDATE table1 SET time = $1 WHERE id = $2 The table1 size is 17 G. What could be the reason for this lock contention? autovacuum? This may be a CREATE INDEX query taking some time, perhaps combined with an old prepared transaction still holding a lock? Perhaps a cron job running behind that you are not aware of? Wouldn't that be waiting on the table, not the transaction? I think transaction lock waits are (almost?) always due to tuples, so FOR UPDATE, UPDATE, etc. You should have a look at pg_stat_activity, pg_prepared_xacts and pg_locks to get more information about the transactions running and the locks being taken. In 9.4, the log message will also include info on the blocking process, not just the blocked process, for lock waits. But until then, pg_stat_activity and pg_locks are the best bet. Unless you can afford to turn log_statement to 'all' and dig through the resulting mess. Cheers, Jeff -- 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] how to create a role with no privileges?
On Tue, Jul 1, 2014 at 1:28 PM, David G Johnston david.g.johns...@gmail.com wrote: The first rule regarding PostgreSQL permissions is that everything is forbidden unless allowed - via GRANT. REVOKE simply undoes whatever has been granted; it does not put up a block to prevent inheritance of granted permissions. Thanks!
Re: [GENERAL] lock contention, need profiling idea
On Tue, Jul 1, 2014 at 2:28 PM, Jeff Janes jeff.ja...@gmail.com wrote: You should have a look at pg_stat_activity, pg_prepared_xacts and pg_locks to get more information about the transactions running and the locks being taken. In 9.4, the log message will also include info on the blocking process, not just the blocked process, for lock waits. But until then, pg_stat_activity and pg_locks are the best bet. Unless you can afford to turn log_statement to 'all' and dig through the resulting mess. log_min_duration_statement might be a middle ground -- set it to 1 second plus. on most applications this should cut out most of the chaff. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] hstore to json and back again
Hello, I'm trying to migrate an existing hstore column to json in Postgres 9.3, and I'd like to be able to run the script in reverse. I know not all JSON can turn back into hstore, but since this is coming from an old hstore column, I know the structure is flat (no nesting), and that all values are strings. Here is the SQL I'm using to go hstore - json: UPDATE foo SET datahash_new = to_json(datahash_old) ; Is there any SQL I can use to go backwards?: UPDATE foo SET datahash_old = x(datahash_new) ; I understand why there is not a general-purpose solution, but in my case this should be possible. I've tried to cook something up with json_each_text, but I haven't been able to figure it out. Can anyone offer any help? Thanks, Paul -- _ Pulchritudo splendor veritatis. -- 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/perl and recent perl versions - failing to load internal modules
On Mon, Jun 30, 2014 at 7:47 PM, Toby Corkindale toby.corkind...@strategicdata.com.au wrote: Hi, I've been trying out PostgreSQL 9.3 with pl/perl built against Ubuntu 14.04 LTS' Perl 5.18 (Sourced from apt.postgresql.org) Maybe I'm doing something wrong, but it appears that plperl has become completely useless, as it can't load any new modules, but modern Perl versions have refactored the language into lots of small modules that are automatically loaded as required. Except they can't be loaded as required. For instance, a fairly simple regex will try to load the modules: utf8, utf8_heavy re Since it is forbidden from loading anything, it will fail. pl/perl should be preloading those modules via my $a=chr(0x100); return $a =~ /\\xa9/i. Do you have some example pl/perl code that is broke? It seems to work for me (I only have 5.20 installed, on this machine, but I've used 5.18 on 9.2 and 9.3): $ perl -v This is perl 5, version 20, subversion 0 (v5.20.0) built for x86_64-linux-thread-multi ... $ psql ... baroque= select version(); version ─ PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.9.0 20140604 (prerelease), 64-bit (1 row) Time: 1.033 ms baroque= do $$my $a=chr(0x100); return $a =~ /\\xa9/i$$ language plperl; DO Time: 1.161 ms -- 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] hstore to json and back again
I'm trying to migrate an existing hstore column to json in Postgres
9.3, and I'd like to be able to run the script in reverse.
To answer my own question, this partially solves the problem for me
(foo.datahash_new has json, foo.datahash_old has hstore):
connection.select_rows(-EOQ).each do |id, key, value|
SELECT id,
(json_each_text(datahash_new)).*
FROMfoo
EOQ
key = connection.quote(key)
value = connection.quote(value)
connection.execute -EOQ
UPDATE foo
SET datahash_old = COALESCE(datahash_old, ''::hstore) ||
hstore(#{key}, #{value})
WHERE id = #{id.to_i}
EOQ
end
That is Ruby driving the SQL. So this is a SELECT and then a bunch of
UPDATEs. I'd love to convert this to a single UPDATE, but I couldn't
get that to work. I tried this:
UPDATE foo
SETdatahash_old = COALESCE(datahash_old, ''::hstore) ||
hstore(x.key, x.value)
FROM (SELECT id, (json_each_text(datahash_new)).*
FROM foo) x(id, key, value)
WHERE foo.id = x.id
But that doesn't work, because multiple json key/value pairs for the
same foo.id don't accumulate---instead each one wipes out the previous
one, so the hstore column winds up with just one key/value pair.
Any suggestions for making this one big UPDATE?
Thanks,
Paul
On Tue, Jul 1, 2014 at 3:26 PM, Paul Jungwirth
p...@illuminatedcomputing.com wrote:
Hello,
I'm trying to migrate an existing hstore column to json in Postgres
9.3, and I'd like to be able to run the script in reverse. I know not
all JSON can turn back into hstore, but since this is coming from an
old hstore column, I know the structure is flat (no nesting), and that
all values are strings.
Here is the SQL I'm using to go hstore - json:
UPDATE foo
SET datahash_new = to_json(datahash_old)
;
Is there any SQL I can use to go backwards?:
UPDATE foo
SET datahash_old = x(datahash_new)
;
I understand why there is not a general-purpose solution, but in my
case this should be possible. I've tried to cook something up with
json_each_text, but I haven't been able to figure it out. Can anyone
offer any help?
Thanks,
Paul
--
_
Pulchritudo splendor veritatis.
--
_
Pulchritudo splendor veritatis.
--
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/perl and recent perl versions - failing to load internal modules
Hi Alex,
= do $$ \N{U+263A} =~ /[[:punct:]]/ $$ language plperl;
ERROR: Unable to load utf8.pm into plperl at line 1.
BEGIN failed--compilation aborted.
CONTEXT: PL/Perl anonymous code block
- Original Message -
From: Alex Hunsaker bada...@gmail.com
To: Toby Corkindale toby.corkind...@strategicdata.com.au
Cc: pgsql-general pgsql-general@postgresql.org
Sent: Wednesday, 2 July, 2014 9:28:52 AM
Subject: Re: [GENERAL] pl/perl and recent perl versions - failing to load
internal modules
On Mon, Jun 30, 2014 at 7:47 PM, Toby Corkindale
toby.corkind...@strategicdata.com.au wrote:
Hi,
I've been trying out PostgreSQL 9.3 with pl/perl built against Ubuntu 14.04
LTS' Perl 5.18
(Sourced from apt.postgresql.org)
Maybe I'm doing something wrong, but it appears that plperl has become
completely useless, as it can't load any new modules, but modern Perl
versions have refactored the language into lots of small modules that are
automatically loaded as required. Except they can't be loaded as required.
For instance, a fairly simple regex will try to load the modules: utf8,
utf8_heavy re
Since it is forbidden from loading anything, it will fail.
pl/perl should be preloading those modules via my $a=chr(0x100);
return $a =~ /\\xa9/i. Do you have some example pl/perl code that is
broke? It seems to work for me (I only have 5.20 installed, on this
machine, but I've used 5.18 on 9.2 and 9.3):
$ perl -v
This is perl 5, version 20, subversion 0 (v5.20.0) built for
x86_64-linux-thread-multi
...
$ psql ...
baroque= select version();
version
─
PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.9.0 20140604 (prerelease), 64-bit
(1 row)
Time: 1.033 ms
baroque= do $$my $a=chr(0x100); return $a =~ /\\xa9/i$$ language plperl;
DO
Time: 1.161 ms
--
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/perl and recent perl versions - failing to load internal modules
Hi Alex,
your example (chr(0x100) =~ /\\xa9/) works on my instance (pg 9.3.4, plperl
5.18)
However the following code fails:
= do $$ \N{U+263A} =~ /[[:punct:]]/$$ language plperl;
ERROR: Unable to load utf8.pm into plperl at line 1.
BEGIN failed--compilation aborted.
CONTEXT: PL/Perl anonymous code block
Oddly, even if I add utf8 to plperl.oninit, I then get an error about being
unable to load the 're' (regex) module. I don't understand how the example you
gave does manage to work!
- Original Message -
From: Alex Hunsaker bada...@gmail.com
To: Toby Corkindale toby.corkind...@strategicdata.com.au
Cc: pgsql-general pgsql-general@postgresql.org
Sent: Wednesday, 2 July, 2014 9:28:52 AM
Subject: Re: [GENERAL] pl/perl and recent perl versions - failing to load
internal modules
On Mon, Jun 30, 2014 at 7:47 PM, Toby Corkindale
toby.corkind...@strategicdata.com.au wrote:
Hi,
I've been trying out PostgreSQL 9.3 with pl/perl built against Ubuntu 14.04
LTS' Perl 5.18
(Sourced from apt.postgresql.org)
Maybe I'm doing something wrong, but it appears that plperl has become
completely useless, as it can't load any new modules, but modern Perl
versions have refactored the language into lots of small modules that are
automatically loaded as required. Except they can't be loaded as required.
For instance, a fairly simple regex will try to load the modules: utf8,
utf8_heavy re
Since it is forbidden from loading anything, it will fail.
pl/perl should be preloading those modules via my $a=chr(0x100);
return $a =~ /\\xa9/i. Do you have some example pl/perl code that is
broke? It seems to work for me (I only have 5.20 installed, on this
machine, but I've used 5.18 on 9.2 and 9.3):
$ perl -v
This is perl 5, version 20, subversion 0 (v5.20.0) built for
x86_64-linux-thread-multi
...
$ psql ...
baroque= select version();
version
─
PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.9.0 20140604 (prerelease), 64-bit
(1 row)
Time: 1.033 ms
baroque= do $$my $a=chr(0x100); return $a =~ /\\xa9/i$$ language plperl;
DO
Time: 1.161 ms
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] what specifically does vacuum have to scan / why does it need to rescan the same indexes many, many times
What does vacuum have to scan to be able to reclaim space, and how many times does it need to scan to finalize ? More specifically, my VACUUM VERBOSE table is taking a long time and seems to be rescanning the same indexes / fields multiple times without finishing. db=# vacuum verbose testruns; INFO: vacuuming public.testruns INFO: scanned index testruns_pkey to remove 2795912 row versions DETAIL: CPU 11.92s/20.58u sec elapsed 55.07 sec. INFO: scanned index index_testruns_on_build_os to remove 2795912 row versions DETAIL: CPU 13.66s/87.02u sec elapsed 127.30 sec. ... INFO: scanned index testruns_custom_data_type to remove 2795913 row versions DETAIL: CPU 103.91s/38.58u sec elapsed 625.51 sec. INFO: scanned index testruns_lower_machine to remove 2795913 row versions DETAIL: CPU 1.22s/1.75u sec elapsed 4.83 sec. INFO: testruns: removed 2795913 row versions in 846930 pages DETAIL: CPU 12.83s/34.68u sec elapsed 79.09 sec. INFO: scanned index testruns_pkey to remove 2795913 row versions DETAIL: CPU 11.17s/17.62u sec elapsed 42.38 sec. INFO: scanned index index_testruns_on_build_os to remove 2795913 row versions DETAIL: CPU 11.20s/16.26u sec elapsed 52.65 sec. ... This has now occurred at least half a dozen times: % pbpaste | grep testruns_pkey INFO: scanned index testruns_pkey to remove 2795912 row versions INFO: scanned index testruns_pkey to remove 2795914 row versions INFO: scanned index testruns_pkey to remove 2795912 row versions INFO: scanned index testruns_pkey to remove 2795913 row versions INFO: scanned index testruns_pkey to remove 2795913 row versions INFO: scanned index testruns_pkey to remove 2795913 row versions INFO: scanned index testruns_pkey to remove 2795912 row versions INFO: scanned index testruns_pkey to remove 2795914 row versions I'm aware that certain things like creating concurrent indexes needs to scan a table multiple times for completeness, but I'm not sure why vacuum needs to rescan all the fields and indexes over and over again without any seeming progress. This is Postgres 9.3.3 on OS X 10.9.* thanks, ~ john
Re: [GENERAL] what specifically does vacuum have to scan / why does it need to rescan the same indexes many, many times
On Tuesday, July 1, 2014, john gale j...@smadness.com wrote: What does vacuum have to scan to be able to reclaim space, and how many times does it need to scan to finalize ? More specifically, my VACUUM VERBOSE table is taking a long time and seems to be rescanning the same indexes / fields multiple times without finishing. vacuum can only memorize one dead tuple for every 6 bytes of maintenance_work_mem. If there are more dead tuples than that, it needs to make multiple passes over the indexes. Increase maintenance_work_mem to improve the efficiency. Cheers, Jeff
Re: [GENERAL] Two-way encryption
- Original Message - From: Patrick Simcoe patricksimco...@gmail.com To: pgsql-general@postgresql.org Sent: Wednesday, 2 July, 2014 1:42:04 AM Subject: [GENERAL] Two-way encryption I have a question regarding two-way encryption data for specific columns. Does anyone have a technique or recommendation for two-way encryption which somehow obfuscates the decrypt key so that it isn't easily retrievable from the database or the application source code? We've already considered (a) letting users hold the decrypt key and (b) obfuscating the decrypt key with the user's own (one-way encrypted) password, but neither of these approaches are viable for us. If you want the application to be able to decrypt the data automatically, then it has to hold the decryption key somewhere. There's really no way around that. (Except getting humans to enter the key, but they get bored of typing passwords pretty quickly, and then post-it notes and keyboard macros end up storing your secret keys instead of relatively-secure server filesystems) -- 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/perl and recent perl versions - failing to load internal modules
On Tue, Jul 1, 2014 at 6:39 PM, Toby Corkindale
toby.corkind...@strategicdata.com.au wrote:
Hi Alex,
your example (chr(0x100) =~ /\\xa9/) works on my instance (pg 9.3.4, plperl
5.18)
However the following code fails:
= do $$ \N{U+263A} =~ /[[:punct:]]/$$ language plperl;
ERROR: Unable to load utf8.pm into plperl at line 1.
BEGIN failed--compilation aborted.
CONTEXT: PL/Perl anonymous code block
Oddly, even if I add utf8 to plperl.oninit, I then get an error about being
unable to load the 're' (regex) module. I don't understand how the example
you gave does manage to work!
Strange, seems to works for me with 5.20:
= do $$ \N{U+263A} =~ /[[:punct:]]/$$ language plperl;
DO
Time: 12.928 ms
= show plperl.on_init;
plperl.on_init
(1 row)
Time: 0.786 ms
= show plperl.on_plperl_init;
plperl.on_plperl_init
───
(1 row)
One thing you might try is plperl.on_plperl_init = require
'utf8_heavy.pl'; require unicore/Heavy.pl'; or maybe just
plperl.on_plperl_init = 'use Unicode::UCD;'
If that still fails, You might find the attached useful, it tires to
preload all the unicore files. You should be able to stick it
somewhere and plperl.on_plperl_init = '/path/to/unicore_preload.pm'.
However, hopefully one of the first suggestions will work..
unicore_preload.pm.gz
Description: GNU Zip compressed data
--
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/perl and recent perl versions - failing to load internal modules
On Tue, Jul 1, 2014 at 7:31 PM, Alex Hunsaker bada...@gmail.com wrote:
On Tue, Jul 1, 2014 at 6:39 PM, Toby Corkindale
toby.corkind...@strategicdata.com.au wrote:
Hi Alex,
However the following code fails:
= do $$ \N{U+263A} =~ /[[:punct:]]/$$ language plperl;
ERROR: Unable to load utf8.pm into plperl at line 1.
BEGIN failed--compilation aborted.
CONTEXT: PL/Perl anonymous code block
One thing you might try is plperl.on_plperl_init = require
'utf8_heavy.pl'; require unicore/Heavy.pl'; or maybe just
plperl.on_plperl_init = 'use Unicode::UCD;'
If you are up to recompiling postgres, you might try this patch. Its
untested, but the basic idea is to always allow require 'utf8.pm' to
work in plperl. Near as I can tell utf8.pm is a strange package, when
we look to see if we already have it loaded it, we can't find it for
example. Which would explain why plperl.on_init = ' use utf8;' does
not work for you. I don't have the time to dig through a bunch of perl
versions to find out whats going on with utf8.pm.
I suspect this will make it fail on loading a different module (my gut
says utf8 is going to want to load unicore stuff once its loaded) but
it would be interesting to see nonetheless.
diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c
index 2f7a12f..62cf116 100644
--- a/src/pl/plperl/plperl.c
+++ b/src/pl/plperl/plperl.c
@@ -881,6 +881,17 @@ pp_require_safe(pTHX)
if (svp *svp != PL_sv_undef)
RETPUSHYES;
+ /*
+ * whitelist utf8.pm, perl 5.18 and maybe others needs to try and load this
+ * probably for unicode/unicore stuff
+ */
+ if (strncmp(utf8.pm, name, len) == 0) {
+ PUSHMARK(SP);
+ PUSHs(sv);
+ PUTBACK;
+ return pp_require_orig(aTHX);
+ }
+
DIE(aTHX_ Unable to load %s into plperl, name);
/*
--
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] Validating User Login Within Postgres
On 07/01/2014 06:03 PM, Rich Shepard wrote: On Tue, 1 Jul 2014, hubert depesz lubaczewski wrote: That depends. For example - for system that will have 5 users, and requires strict security policies - it would make sense. On the other hand, for website, with thousands of users, putting them all as actual roles in Pg doesn't make much sense, and could potentially cause problem. Hubert, OK. This is not a Web-based application, but the users could number in the dozens. Using database user authentication is great for corporate use. If you have an LDAP (or active directory) you can define the authentication to use that. See the pg_hba file for details on various authentication methods. Our application is written in python as well (desktop modules in wxPython and web modules in django) and we use LDAP authentication. sim
