[HACKERS] Need help on pgcrypto
Trying to find a documentation which will make me understand how to secure a password column. I want the encryption to be one way and it should not be decrypted. I am able to find discrete documents but nothing of them gets me there. Please help. - -- Thanks, Rajan. -- View this message in context: http://postgresql.nabble.com/Need-help-on-pgcrypto-tp5881477.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- 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] Disabling START TRANSACTION for a SuperUser
Thanks Amit... :-) -- View this message in context: http://postgresql.nabble.com/Disabling-START-TRANSACTION-for-a-SuperUser-tp5871630p5871739.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- 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] Disabling START TRANSACTION for a SuperUser
Thanks for clarifying my doubt... -- Muthiah Rajan On Wed, Oct 28, 2015 at 6:19 PM, Kevin Grittner <kgri...@ymail.com> wrote: > On Tuesday, October 27, 2015 10:57 PM, Muthiah Rajan <vgmon...@gmail.com> > wrote: > > On 27-Oct-2015 7:37 PM, "Kevin Grittner" <kgri...@ymail.com> wrote: > > >> It is more problematic where a shop wants to use serializable > >> transactions to ensure data integrity. > > > This may be a trivial thing But what do you mean by shops? I > > actually can't get it :-) > > http://www.merriam-webster.com/dictionary/shop > > I was using "shop" in the sense of the second of the short noun > definitions ("the place where a specified kind of worker works : a > worker's place of business") or number 5a under the long noun > definitions ("a business establishment: office"). When used in > that sense the type of business is usually used ("an I.T. shop"), > but where it is implied or obvious it is often dropped. The > dictionary doesn't list it as a colloquialism, but it is rather > informal -- approaching the colloquial. As I used it I was > intending to convey a group of I.T. professionals under the same > management with a common set of policies, working on the same set > of hardware and/or software. > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
[HACKERS] Disabling START TRANSACTION for a SuperUser
Hi, I have created a readonly user by executing the following statements, CREATE USER backupadm SUPERUSER password 'mypass'; ALTER USER backupadm set default_transaction_read_only = on; But the backupadm user is able to create/update table when using START TRANSACTION READ WRITE and then COMMIT; Is there any way to block/disabling an User from running Transactions? Thanks in advance. -- View this message in context: http://postgresql.nabble.com/Disabling-START-TRANSACTION-for-a-SuperUser-tp5871630.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- 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] Disabling START TRANSACTION for a SuperUser
Hey Craig, Thanks for your response. Seems like the workaround is difficult. I am trying to understand " ExecutorStart_hook and ProcessUtility_hook, implemented with a C extension. You can find an example of one in pg_stat_statements, sepgsql, and in the BDR source code. The latter uses it for a similar purpose to what you describe - to limit what commands can be run. " Let me see what i can do... Thanks again... -- View this message in context: http://postgresql.nabble.com/Disabling-START-TRANSACTION-for-a-SuperUser-tp5871630p5871645.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- 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] Disabling START TRANSACTION for a SuperUser
Hello Kevin, This may be a trivial thing But what do you mean by shops? I actually can't get it :-) On 27-Oct-2015 7:37 PM, "Kevin Grittner" <kgri...@ymail.com> wrote: > On Tuesday, October 27, 2015 8:52 AM, Craig Ringer <cr...@2ndquadrant.com> > wrote: > > On 27 October 2015 at 21:19, rajan <vgmon...@gmail.com> wrote: > > >> Thanks for your response. Seems like the workaround is difficult. > >> > >> I am trying to understand > >> " > >> ExecutorStart_hook and ProcessUtility_hook > > > Doing what you want will require being willing to spend a fair bit of > > time becoming familiar with PostgreSQL's innards, writing extensions, > > etc. It's not a simple "download, compile, run" process. You will need > > to be confident with C programming and reading source code. > > > If this is going way too deep, perhaps you should post to > > pgsql-general with a description of the underlying problem you are > > trying to solve, i.e. *why* you want to be able to have a superuser > > who can alter users but can't select, etc. What's the problem you're > > trying to solve with this? > > This is a question I have seen before, as well as slight variations > on it related to transaction isolation level. Right now you can > implement a read-only user by granting only SELECT rights to tables > and also by setting the default_transaction_read_only = on. The > problem is that the latter is essentially just a suggestion, not an > order. I actually don't think it's as big a problem with read-only > users, since that can still be accomplished (with enough work) by > using the GRANT/REVOKE commands. (Think how much faster and easier > it could be if there is a role that allows the appropriate set of > SELECTs but also allows some DML -- just set a read-only rule for > the user and the existing role could work.) > > It is more problematic where a shop wants to use serializable > transactions to ensure data integrity. The only way to prevent > someone from subverting the business rules is to code a lot of > triggers on a lot of objects that throw an error if the isolation > level is wrong. It would be a boon to big shops if they could > declare (preferably with the option to set it at a role level) that > specific default_transaction_* settings could not be overridden. > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
Re: [HACKERS] Disabling START TRANSACTION for a SuperUser
Thanks Craig, There are a lot of details and its overwhelming :-) Let me digest and will post for any help On 27-Oct-2015 7:21 PM, "Craig Ringer" <cr...@2ndquadrant.com> wrote: > On 27 October 2015 at 21:19, rajan <[hidden email] > <http:///user/SendEmail.jtp?type=node=5871647=0>> wrote: > > Hey Craig, > > > > Thanks for your response. Seems like the workaround is difficult. > > > > I am trying to understand > > " > > ExecutorStart_hook and ProcessUtility_hook > > Doing what you want will require being willing to spend a fair bit of > time becoming familiar with PostgreSQL's innards, writing extensions, > etc. It's not a simple "download, compile, run" process. You will need > to be confident with C programming and reading source code. > > Here's some code that filters allowable commands. It doesn't care > which user id is used, but it's pretty simple to add a check to only > run the filter when a particular user ID is the active user. This > won't do what you want, but serves as a rough example of how you can > filter statements based on the parsed statement data: > > https://github.com/2ndQuadrant/bdr/blob/bdr-plugin/next/bdr_commandfilter.c > > and also: > > http://www.postgresql.org/docs/current/static/xfunc-c.html > http://www.postgresql.org/docs/current/static/extend-extensions.html > http://www.postgresql.org/docs/current/static/extend-pgxs.html > > Note that BDR's command filter doesn't do anything to > insert/update/delete/select. For that you'd *also* need an > ExecutorStart_hook or similar. > > If this is going way too deep, perhaps you should post to > pgsql-general with a description of the underlying problem you are > trying to solve, i.e. *why* you want to be able to have a superuser > who can alter users but can't select, etc. What's the problem you're > trying to solve with this? > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > > > -- > Sent via pgsql-hackers mailing list ([hidden email] > <http:///user/SendEmail.jtp?type=node=5871647=1>) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > > > -- > If you reply to this email, your message will be added to the discussion > below: > > http://postgresql.nabble.com/Disabling-START-TRANSACTION-for-a-SuperUser-tp5871630p5871647.html > To unsubscribe from Disabling START TRANSACTION for a SuperUser, click > here > <http://postgresql.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code=5871630=dmdtb25uZXRAZ21haWwuY29tfDU4NzE2MzB8MTg2MjE3MzA5Nw==> > . > NAML > <http://postgresql.nabble.com/template/NamlServlet.jtp?macro=macro_viewer=instant_html%21nabble%3Aemail.naml=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> >
Re: [HACKERS] SuperUser check in pg_stat_statements
Hey Lukas, Thanks. Able to see the queries from all users. Can you explain the monitoring.get_stat_statements()? -- View this message in context: http://postgresql.nabble.com/SuperUser-check-in-pg-stat-statements-tp5870589p5870733.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SuperUser check in pg_stat_statements
Hello, When monitoring using pg_stat_satements I see that postgres by default conceals queries executed by other users from the user who is selecting the pg_stat_statements view. I have edited the pg_stat_statements.c by disabling the superuser check function so that all queries will be visible to all users. Can this be posted as a patch to postgresql? -- View this message in context: http://postgresql.nabble.com/SuperUser-check-in-pg-stat-statements-tp5870589.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- 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] SuperUser check in pg_stat_statements
Thanks Stephen and Shulgin for your response. Will go through the patch and will try to solve my problem using that. My scenario is that i need to have an user who cannot be a super user but a monitor user, who will be able to see all the queries executed by all users. -- View this message in context: http://postgresql.nabble.com/SuperUser-check-in-pg-stat-statements-tp5870589p5870639.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- 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] SuperUser check in pg_stat_statements
Jim, I already tried to create a view upon the pg_stat_statements, but no luck. -- View this message in context: http://postgresql.nabble.com/SuperUser-check-in-pg-stat-statements-tp5870589p5870683.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers