Re: [HACKERS] A stab at implementing better password hashing, with mixed results
On Thu, Dec 27, 2012 at 5:39 PM, Peter Bex peter@xs4all.nl wrote: On Thu, Dec 27, 2012 at 12:31:08PM -0300, Claudio Freire wrote: On Thu, Dec 27, 2012 at 11:46 AM, Peter Bex peter@xs4all.nl wrote: Implementing a more secure challenge-response based algorithm means a change in the client-server protocol. Perhaps something like SCRAM (maybe through SASL) really is the way forward for this, but that seems like quite a project and it seems to dictate how the passwords are stored; it requires a hash of the PBKDF2 algorithm to be stored. It would be nonsense to do it in any other way... protecting the password store and not the exchange would just shift the weak spot. Yeah, that's why I was being rather pessimistic about the patch I posted. However, SCRAM will only protect the password; SSL is still required to protect against connection hijacking. The thread that ended with http://archives.postgresql.org/message-id/5086cb7a.5040...@gmx.net also tended towards SASL and SCRAM as the best direction for developing password and GSSAPI/Kerberos authentication. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Submission Review: User control over psql error stream
Hi Karl,
Sorry for the slow reply ...
Excerpt from Karl O. Pinc k...@meme.com On Mon, Dec 10, 2012 at 5:00 AM:
I was thinking along the same lines, that case 2) stderr to a file
or pipe needs addressing. I think it's necessary to address the
issue now. Otherwise we risk cluttering up the syntax in
inhospitable ways.
The discussion needs to be a little broader than stdout and stderr,
there are currently three output streams from psql:
- stdout - prompts, not tabular output such as the results from \set and \c
- stderr - errors, notices, ...
- query output - result from queries and \ commands which return
tables such as \l - this is what is currently piped or redirected by
\o
I see a patch like this adding a fourth - diagnostic output. While
this would probably be the same as stderr initially but I think that
the option to make them subtly different should be kept open.
It occurs to me that my reply does not address the issue
of case 3, displaying or not) errors to the terminal in
addition to wherever else errors are sent.
I don't know of any precedent syntax for this - in *nix shells you'd
pipe to tee rather than modify the redirect in some way.
I think you're right, whether or not errors continue to be sent
to stdout when they are directed elsewhere should be a separate
flag. My inclination is to have another special psql variable
to control this but that would break backwards compatibility.
Instead, let's work out a syntax for the rest of the functionality
and try to fit this in.
One nice thing about special psql variables is that they self-report
their state. I mention this since we're adding more state.
It would be nice if the chosen syntax does not preclude some
additional tweaking to report on the state involving the
output streams. (Although I don't think that needs to be
a part of this patch.)
State reporting and reset tend to use the same syntax - the command
without parameters. I think the best route to achieve this would be
for the commands to set a variables which would be reported by \set.
In my initial list of what needed to be specified for output
management I missed one - whether file output should be appended or
should rewrite the file. It's not likely to be particularly useful for
query output but would matter for diagnostics.
Given all of these considerations I would propose changing your
calling syntax to:
\o reset all output redirection
\oq reset query output redirection
\o[q] [] file query output to file
\o[q] file append query output to file
\o[q] | progpipe query output to program
\o[q] {[]|||}+ display query output on stdout in addition to redirection
\od reset query output redirection
\odq diagnostic output to query output stream
\odq + diagnostic output to stderr and query output
stream (same as \odq and \o + in combination)
\od [] file diagnostic output to file
\od file append diagnostic output to file
\od | prog pipe diagnostic output to program
\od {[]|||}+ display diagnostic output on stderr in addition to
redirection
To meet its original goals your patch would need to implement \o, \od,
\odq and \odq +.
Regards,
Alastair.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Submission Review: User control over psql error stream
Hi Karl, I have given the patch a quick review and read the related mails following its initial submission. I agree with that functionality along these lines is desirable. The ability to manage output from within psql at least as richly as is possible with shell redirection - and change it between commands because it is accessible from with psql - would be great. The basics of the review were fine: the patch applied with minimal fuzz and compiled cleanly. The implementation of the functionality - as built for your specific requirement - concerns me in a few ways though: - It's closed ended - there are three things about error output which affect where it's written to: does it go to query output, does it go somewhere else (a file or pipe), does it get displayed as well as going to the other destination. The patch addresses the first and third questions without making allowance for asking or dealing with second one. Internally I think this should be two bools rather than a custom tri-state, mainly because this would allow the addition of the error file option (in another patch, when the time came) with minimum intrusion. - \pset is not the right place for this switch - all the documentation (including \?) indicate that psets are for controlling the display of result tables and this doesn't fit with the other options to \pset. Since this affects what goes into the output file I would think that this should be an option to \o. Maybe \o ? Marked Returned to Author. Regards, Alastair. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch for checking file parameters to psql before password prompt
Patch for the changes discussed in http://archives.postgresql.org/pgsql-hackers/2010-10/msg00919.php attached (eventually ...) In summary: If the input file (-f) doesn't exist or the ouput or log files (-o and -l) can't be created psql exits before prompting for a password. Regards, Alastair. psql_check_file_params_before_login.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] \conninfo and SSL
On Thu, May 31, 2012 at 9:11 PM, Magnus Hagander mag...@hagander.net wrote:
On Thu, May 31, 2012 at 9:04 PM, Bruce Momjian br...@momjian.us wrote:
On startup, psql shows the SSL information:
$ psql 'sslmode=require host=localhost'
psql (9.2beta1)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
However, \conninfo does not mention SSL:
postgres= \conninfo
You are connected to database postgres as user postgres on
host localhost at port 5432.
Should \conninfo mention SSL? Fortunately \c shows SSL information:
postgres= \c
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
You are now connected to database postgres as user postgres.
Well, \c creates a new connection, so that's not really an information
command. It might not connect that trivially, depending on what
authentication method you use.
Including ssl info in \conninfo would be useful, I think.
//Magnus
A one-line change adds the SSL info on its own line like
--
You are connected to database scratch as user scratch on host
127.0.0.1 at port 5432.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
--
Does this need a more integrated presentation, and therefore a broader
change to make it translatable?
Regards,
Alastair.
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*** exec_command(const char *cmd,
*** 312,319
--- 312,322
printf(_(You are connected to
database \%s\ as user \%s\ via socket in \%s\ at port
\%s\.\n),
db, PQuser(pset.db), host,
PQport(pset.db));
else
+ {
printf(_(You are connected to
database \%s\ as user \%s\ on host \%s\ at port \%s\.\n),
db, PQuser(pset.db), host,
PQport(pset.db));
+ printSSLInfo();
+ }
}
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] Interface of Row Level Security
Excerpts from Kohei KaiGai kai...@kaigai.gr.jp wrote on Fri, May 25, 2012 at 11:08 PM: If we assume RLS is applied when user has no privileges on tables, the current ExecCheckRTEPerms() always raises an error towards unprivileged users, prior to execution of queries. Isn't it preferable behavior to allow unprivileged users to reference a table (or columns) when it has RLS policy? Rather than assuming the the RLS checks will be applied when there are no privileges it would make sense to regard RLS as a limitation on the scope of a particular privilege. This makes RLS a property of a particular grant of a privilege rather than of the table. Viewed this way it is possible to control which users are subject to restrictions imposed by the RLS function, which will avoid RLS overhead for operations which don't require it while allowing checks for those that do, provide a mechanism exempting object owners from RLS checks and make it possible to avoid pg_dump calling user defined code. This suggests an alternative declaration syntax, to use the salesman example: GRANT SELECT ON TABLE client_detail TO super_sales_group; GRANT SELECT TABLE ON client_detail TO limited_sales_group WITH (QUALIFICATION FUNCTION sales_view_check); and since more easily usable security features will be used more effectively, a possible future inlining of the condition: GRANT SELECT ON TABLE client_detail TO limited_sales_group WHERE sales_rep = my_sales_rep(); Regards, Alastair. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] Interface of Row Level Security
On Wed, May 23, 2012 at 5:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kohei KaiGai kai...@kaigai.gr.jp writes: Let me have a discussion to get preferable interface for row-level security. My planned feature will perform to append additional conditions to WHERE clause implicitly, to restrict tuples being visible for the current user. For example, when row-level policy uname = getpgusername() is configured on the table T1, the following query: select * from T1 where X 20; should be rewritten to: select * from T1 where (X 20) AND (uname = getpgusername()); Hm. Simple and fairly noninvasive, but ... would this not be subject to the same sorts of information-leak hazards that were addressed in the security views feature? That is, I see no guarantee that the RLS condition will be evaluated before any conditions supplied by the user. So it seems easy to get information out of rows the RLS policy is supposed to prevent access to. It would be far more secure to just use a security view to apply the RLS condition. Since adding a condition to the where clause is a relatively simple operation (compared to the full potential scope of a view) could the RLS rewrite of the query create a CTE with the additional condition[s] rather than adding condition[s] to the user-supplied query? This would provide the forced ordering of the evaluating the conditions, thereby avoiding many of the potential points of leakage. Bell. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Copy/paste from psql - was: Changing the continuation-line prompt in psql?
On Fri, Apr 29, 2011 at 8:11 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark gsst...@mit.edu writes: On Fri, Apr 29, 2011 at 5:45 PM, Christopher Browne cbbro...@gmail.com wrote: The bike shedding that I'd rather have would involve enclosing prompts with /* comments */ so that cut'n'paste could be expected to generate output that could run, without further editing, in another psql session. Mind you, whenever I have configured such, I have been unhappy at how wide that makes the prompt and at the loss of screen space. I would second this precise interest. It really annoys me more often than anything else that when I try to copy/paste an sql query I need to copy each line one by one. It would be different from MySql but I think it would be even clearer to the user: postgres= select 1, /*line 2:*/ 2, /*line 3:*/ 3; This looks promising until you stop to think about either string literals or /* comment blocks being continued across lines ... The copy paste problem also frustrates me, maybe modifying the prompt isn't an effective answer though. Extending the history command (\s) sounds more promising \s- for a reverse ordered history \s[n] for the last n or n-from-last-th (\s1 different from \p in that it shows the last completed query not the one in progress) and most importantly showing full history through a less-style interface like large result sets rather than in the flow of psql Does that sound like a workable answer? Regards, Bell. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superusers are members of all roles?
On Thu, Apr 7, 2011 at 6:49 AM, Andrew Dunstan and...@dunslane.net wrote: On 04/07/2011 12:29 AM, Tom Lane wrote: Robert Haasrobertmh...@gmail.com writes: On Wed, Apr 6, 2011 at 7:54 PM, Stephen Frostsfr...@snowman.net wrote: * Andrew Dunstan (and...@dunslane.net) wrote: The surprising (to me) consequence was that every superuser was locked out of the system. I had not granted them (or anyone) the role, but nevertheless these lines took effect. As I recall, the way we allow superusers to set role to other roles is by considering the superuser to be a member of every role. Now, I agree that such an approach doesn't make sense for pg_hba consideration. See bug #5763, and subsequent emails. Short version: Tom argued it wasn't a bug; Peter and I felt that it was. The problem here is that if Andrew had had the opposite case (a positive-logic hba entry requiring membership in some group to get into a database), and that had locked out superusers, he'd be on the warpath about that too. And with a lot more reason. In such a case I could add the superusers to the role explicitly, or make the rule cover superusers as well. But as the situation is now, any rule covering a group covers superusers, whether I want it to or not. I'd rather have a choice in the matter (and it's clear I'm not alone in that). The introduction of hot standby has made this pattern more likely to occur. It happened here because we have a bunch of users that are allowed to connect to the standby but not to the master, and the rules I was trying to implement were designed to enforce that exclusion. Is the solution possibly to assign positive entries on the basis of the superuser being a member of all groups but require negative entries to explicitly specify that they apply to superuser? That would provide least surprise for the simplistic concept of superuser - a user who can do anything any other user can - and allow for superuser remote access to be restricted if desired. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DELETE with LIMIT (or my first hack)
On Tue, Nov 30, 2010 at 9:24 PM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: On 11/30/2010 02:12 PM, Kevin Grittner wrote: Daniel Loureirodan...@termasa.com.br wrote: to me the key its security - its a anti-DBA-with-lack-of-attention feature. Well, it seems pretty weak to me for that purpose. You still trash data, and you don't have any immediate clue as to what. I agree, that argument is completely misconceived. If the DBA is paying enough attention to use LIMIT, s/he should be paying enough attention not to do damage in the first place. If that were the only argument in its favor I'd be completely against the feature. I don't buy the argument either; why would you put a LIMIT there and delete one row by accident when you could put a BEGIN; in front and not do any damage at all? It is valuable as a DBA carelessness/typo catcher only if it is imposed by default (in line with Kevin's point), and only if it rolls back rather than reduces the number of affected rows (as per Marko). We have implemented a damage limitation solution similar to this with triggers on an MSSQL database, and it has worked for the specific environment it's in. The safety net is basically that the DBA has to set an environment variable before a very large delete or update operation. If the operation is recognised as being beyond the threshold size the enviroment variable is checked - if it is set the transaction passes and the variable is reset, if not the transaction is rolled back. It should be possible to implement something along these lines in triggers, all that would be needed is a structure for defining the (optional) limits on potentially destructive operations. More flexible options or options based on the number of rows in a table will rapidly increase the performance impact of the triggers - but may make them more useful. I'm not sure if there is a way to persist data (like a row count) between per row triggers so that the operation could be aborted at the limit rather than only once all the rows had been updated (potentially a big peformance gain). Alastair Bell Turner Technical Lead ^F5 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] First patch proposal
Hi I am a keen Postgres user and I run my local PUG (JNBPUG in Gauteng, South Africa), but I have found the idea of contributing on a code level daunting. Having read the many warnings along the lines of It's still on the todo because it isn't trivial I have identified what I believe is a manageble task for my first patch and expect to have the time to tackle it at the end of the month. I think the proposed changes are small enough for a first attempt and I don't find anything in the archives suggesting that the outcome I am proposing was deliberately avoided. I am proposing altering psql to raise certain errors and exit before prompting for a password. These errors would have to be on items which didn't leak any information, my current list is: - Does the input file (-f) exist and is it readable - Do paths to the output files ( -o and -l) exist and are they writable - Is the host/socket listening (-h) This is obviously scratching an itch of my own - I end up capturing passwords and then getting errors from mistyped input files on a regular basis. I don't think that I'm the only person to have the problem though (at least I hope not). Does this sound like a sane, desirable set of changes? Regards Alastair Bell Turner Technical Lead ^F5 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] First patch proposal
On Thu, Oct 14, 2010 at 4:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alastair Turner b...@ctrlf5.co.za writes: I am proposing altering psql to raise certain errors and exit before prompting for a password. These errors would have to be on items which didn't leak any information, my current list is: - Does the input file (-f) exist and is it readable - Do paths to the output files ( -o and -l) exist and are they writable - Is the host/socket listening (-h) You could probably do the first two (not sure how badly you'd have to contort the logic in psql, but in principle you could do it). I'm not sure I like/believe the last one though. The prompt for password is already driven by the server demanding one, isn't it? So you won't get one if -h is bad. If you're thinking of altering the behavior when -W is specified, I'd be agin it, because I think the point of that switch is to ensure predictable behavior, ie that the program will ask for a password no matter how the server responds or doesn't. Thanks for the feedback, I'll keep it to the first two then. Regards Bell -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] First patch proposal
Excerpt from Hitoshi Harada umi.tan...@gmail.com - Thu, Oct 14, 2010 at 4:32 PM: Just for information, did you pick this topic from TODO list? If so, could you attach links to the entry or to some related former thread? And in general it is encouraged that you'd better send one feature per a patch, in order for it to be reviewed and committed easily, rather than going all the three you mentioned above. It isn't a TODO item, or related to any previous thread I could find. When I'm making the changes I'll bear in mind the preference for multiple small patches. I have a feeling that the changes would be to the logic structure and that it wouldn't be possible to separate the implementation of each check though. Regards Bell. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
A suggestion, based on what I believe would be ideal default settings for a fully developed SR capability. The thought being that as long as the default behaviour was stable additional knobs could be added across version boundaries without causing trouble. Per slave the master needs to know: - The identity of the slave, even if only to limit who can replicate (this will have to be specified) - Whether to expect an acknowledgement from the slave (as will this) - How long to wait for the acknowledgement (this may be a default) - What the slave is expected to do before acknowledging (I think this should default to remote flush to disk - #3 in the mail which started this thread - since it prevents data loss without exposing the master to the possibility of locking delays) Additionally the process on the master requires: - How many acknowledgments to require before declaring success (defaulted to the number of servers expected to acknowledge since it will cause the fewest surprises when failing over to a replica) - What to do if the number of acknowledgments is not received (defaulting to abort/rollback since this is really what differentiates synchronous from asynchronous replication - the certainty that once data has been committed it can be recovered) So in order to set up synchronous replication all a DBA would have to specify is the slave server, that it is expected to send acknowledgments and possibly a timeout. If this is in fact a desirable state for the default behaviour or minimum settings requirement then I would say it is also a desirable target for the first patch. Alastair Bell Turner ^F5 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronization levels in SR
On Tue, May 25, 2010 at 6:28 PM, Simon Riggs si...@2ndquadrant.com wrote: ... The best parameter we can specify is the number of servers that we wish to wait for confirmation from. That is a definition that easily manages the complexity of having various servers up/down at any one time. It also survives misconfiguration more easily, as well as providing a workaround if replicating across a bursty network where we can't guarantee response times, even of the typical response time is good. This may be an incredibly naive question, but what happens to the transaction on the master if the number of confirmations is not received? Is this intended to create a situation where the master effectively becomes unavailable for write operations when its synchronous slaves are unavailable? Alastair Bell Turner ^F5 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL compatibility reminder: MySQL vs PostgreSQL
2010/3/5 François Pérou francois.pe...@free.fr: Thanks for your answers. To speak frankly: * I wrote the Drupal guide for porting from MySQL to PostgreSQL. * I am also the author of remarks about people should use PostgreSQL to write portable SQL. * I am very surprised by the SQL level of Php developers. The example Drupal developers trying to rewrite SQL queries dynamically adding DISTINCT clause is just an example. So don't expect them to understand the difference between MySQL and PostgreSQL. It is out of reach. They focuse on Php code. * I got banned from Drupal website during 2 days because I opened a bug complaining about a long running SQL query that moved the whole content of a 20.000 rows forum into PHP variables just to display 'Previous' and 'Next' links. I had to write Dries Buytaert to get unbanned. Then Prev and Next features got removed from Drupal. They did not even try to use SELECT FROM ... LIMIT ... OFFSET to find prev and next records. * Php developers analyze database performance using PHP cache. They never read MySQL logging information. I guess they don't have such information, as on some providers, MySQL is configured without logging (for ... speed as MySQL configuration states). So they use Php code to display performance information. All this is true. Nevertheless, I feel my explanations are useless. This is like fighting against the wind. I believe that PostgreSQL should support more MySQLisms in order to BEAT MySQL. Feel free to use my guide on Drupal website. We have to adapt tools to people, not the converse. Kind regards, Jean-Michel Pouré This is confusing advocacy and technical considerations. Yes PHP coders are often deeply ignorant of database issues, many of them deliberately and militantly so. I have had my altercations with the Drupal community over dumb SQL and uncritical praise of document databases as a pancea. MySQL has a set of characteristics (maybe even deliberately) which work well for uptake in that market. One of the reasons I like Postgres is the feeling I get that the product and the community around it would like to make better databases. They would like to use databases better and make it possible for others to use databases better. On that front MySQL is already beaten. Just as the abuse of spreadsheets for data management will probably never be properly vanquished, the permissiveness of MySQL will always present a lower hurdle to some coders. The perception of MySQL as enemy number one does also concern me a bit. Postgres is competing for installed base on a far wider front than that. If installed base is really that meaningful a competition in the first place. Bell. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: listagg aggregate
On Tue, Jan 26, 2010 at 1:08 PM, David E. Wheeler da...@kineticode.com wrote: . Because it's an aggregate that cocatenates values. It's not an aggregate that lists things. I also like concat_agg better than string_agg because it's not limited to acting on strings. . Given that it potentially produces a delimited list, not a straight conacatenation (and that list is unacceptable since it would be descriptive as a noun but not as a verb) would implode_agg not be the most descriptive name? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: listagg aggregate
On Tue, Jan 26, 2010 at 1:23 PM, Alastair Turner b...@ctrlf5.co.za wrote: . Given that it potentially produces a delimited list, not a straight conacatenation (and that list is unacceptable since it would be descriptive as a noun but not as a verb) would implode_agg not be the most descriptive name? Actually, scratch that. The other *agg functions are named for what they produce as output. Not for their process - as per the objection to list_agg and suggestions of conact_agg and implode_agg. string_agg would be consistent, which is a wonderful thing if you can get it in a naming scheme. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
