[GENERAL] Row level security - notes and questions

[Charles Clavadetscher]

Hello

I have been testing the new row level security feature of 9.5 and I have
some notes and questions on it.

This is  a simple table for the test, with 2 rows and a user named john, who
is granted access to the table through a group named users.

CREATE TABLE testrls.accounts (
  id integer,
  username text,
  userinfo text
);

INSERT INTO testrls.accounts
VALUES (1,'john','Main accountant'),
   (2,'fred','Practitioner');

CREATE ROLE users NOLOGIN;
CREATE ROLE john LOGIN PASSWORD 'xxx';

GRANT USAGE ON SCHEMA testrls TO users;
GRANT SELECT, INSERT, UPDATE, DELETE ON testrls.accounts TO users;
GRANT users TO john;

My first test is to enable row level security on the table without a policy
in place. According to the documentation this leads to a general deny
access.

ALTER TABLE testrls.accounts ENABLE ROW LEVEL SECURITY;

When user john tries to interact with the table he receives an empty result
set, instead of a policy violation error. There is no policy yet, so this
may be acceptable. I find it however confusing.

john@test=> SELECT * FROM testrls.accounts ;
 id | username | userinfo 
+--+--
(0 rows)

Since the result set is empty UPDATE and DELETE also do nothing.

In the case of an INSERT john receives a policy violation error. Still there
is no policy yet for the table. This seems not consistent with the behaviour
for the other commands.

john@test=> INSERT INTO testrls.accounts VALUES (3,'lucy','Secretary');
ERROR:  new row violates row level security policy for "accounts"

For the next example I created a policy that allows users to read all rows,
but only change those "belonging" to them, identified by the column
username.

CREATE POLICY accounts_policy ON testrls.accounts
FOR ALL
TO users
USING (true)
WITH CHECK (username = SESSION_USER);

john@test=> SELECT * FROM testrls.accounts ;
 id | username |userinfo 
+--+-
  1 | john | Main accountant
  2 | fred | Practitioner
(2 rows)

john@test.localhost=> INSERT INTO testrls.accounts VALUES
(3,'lucy','Secretary');
ERROR:  new row violates row level security policy for "accounts"
john@test=> UPDATE testrls.accounts SET userinfo = 'Whatever' WHERE id = 2;
ERROR:  new row violates row level security policy for "accounts"
john@test=> UPDATE testrls.accounts SET userinfo = 'Whatever' WHERE username
= 'fred';
ERROR:  new row violates row level security policy for "accounts"

Up to this point everything is fine. The user can, however do the following:

john@test.localhost=> UPDATE testrls.accounts SET username = 'john' WHERE
username = 'fred';
UPDATE 1
john@test.localhost=> SELECT * FROM testrls.accounts ;
 id | username |userinfo 
+--+-
  1 | john | Main accountant
  2 | john | Practitioner
(2 rows)

john@test.localhost=> DELETE FROM testrls.accounts WHERE id = 2;
DELETE 1
john@test.localhost=> SELECT * FROM testrls.accounts ;
 id | username |userinfo 
+--+-
  1 | john | Main accountant
(1 row)

The policy suggests that users can only modify rows where their name is in
the username field. In the UPDATE case the condition is tested against the
new values for the row, leading to a chance for any user to modify and
delete any row.

Obvioulsy there is a number of solutions to this issue. It would be enough
e.g. to modify the UPDATE grant on the table to avoid users in general to
modify this field as in the example in the documentation. In that case
everything would work correctly and the user would get a permission denied
message from the authorization system. I point this out to avoid answers to
my mail suggesting how to solve the problem. What I don't know or don't
understand is the following:

- Why is there not a consistent policy violation message when one would
apply as mentioned above?
- Why is the WITH CHECK condition only used on the values in the new record
in the case of an update?

Thank you.
Charles




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Disconnected but query still running

[Eduardo Piombino]

Hi, list.
I've been running a query from pgAdmin for a couple of hours, just to
realize that the pgAdmin timer that counts ellapsed time had stopped,
but the status bar still said "executing". So I first thought it
could've been some kind of bug with the counter. However, after some
more time, I found that a popup telling me that the connection had
been reset (the timer had stopped almost exactly at 2 hours, 7.210.123
ms to be exact, which makes me think of a 2 hours timeout somewhere).
So I closed the dialog, and then it told me that the connection had
been reset. Ok, status was updated to connection reset, but the query
was (actually is) still running, despite the disconnection (it's still
hammering the disk, and it shows up in one of the pg_views showing
running quesries).

I've read that that a query still running after a disconnection is
normal, since the server won't realize about this disconnection until
it needs to send some data back to the client, which is ok and
understandable. But my question is, what happens when the query
finishes? FYI, the query was an update, in what would be autocommit
mode (no explicit transaction was open). Will the changes be commited?
or rollbacked because it will detect the disconnection later on? In
other words, is it worth waiting for this query to finish (it has been
running for almost 7 hours now), or should I cancel it because it will
irremediably be rolled back when the server finds theres no one on the
other end of the tcp connection?

Thanks.
Eduardo


-- 
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 test SSL cert from CA?

[Jeff Janes]

On Thu, Jul 9, 2015 at 9:29 PM, Francisco Reyes  wrote:

> On 07/09/2015 03:07 PM, Vick Khera wrote:
>
>>
>> On Wed, Jul 8, 2015 at 10:17 PM, Francisco Reyes > > wrote:
>>
>> openssl s_client -connect HOST:PORT -CAfile /path/to/CA.pem
>>
>
> According to this post:
> http://serverfault.com/questions/79876/connecting-to-postgresql-with-ssl-using-openssl-s-client?rq=1
>
> one can not use openssl to test ssl connection to postgresql. True?


If you think the problem might be with the certificate itself, then take
postgresql out of the loop entirely by installing that certificate to be
used by apache (for instance) instead of by postgresql.  Then use s_client
against apache.

Cheers,

Jeff

Re: [GENERAL] regexp_matches for digit

[Ramesh T]

nice i'm looking for this,i thought digit don't work in postgres..
thanks

On Thu, Jul 9, 2015 at 10:21 PM, Chris Mair  wrote:

> > Hi,
> >   in oracle regexp_like(entered
> > date,'[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i')
> >
> > for postgres i have regexp_matches ,But i need how to match [:digit:] in
> > postgres when we pass date..?
> > any help
>
> [:digit:] is Posix syntax, supported by Postgres.
>
> Looks good to me:
>
> graal=# select regexp_matches('2015-07-09',
> '[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i');
>  regexp_matches
> 
>  {2015-07-09}
> (1 row)
>
> graal=# select regexp_matches('2015-x7-09',
> '[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}','i');
>  regexp_matches
> 
> (0 rows)
>
> What do you need, exactly?
>
> Bye,
> Chris.
>
>
>

Re: [GENERAL] Bounded Zone Offset Query

[Robert DiFalco]

On Fri, Jul 10, 2015 at 9:40 AM, John McKown 
wrote:

> On Fri, Jul 10, 2015 at 11:15 AM, Robert DiFalco  > wrote:
>
>> I have a table something like this:
>>
>> CREATE TABLE devices (
>>   owner_idBIGINT NOT NULL,
>>   utc_offset_secs INT,
>>   PRIMARY KEY (uid, platform),
>>   FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
>> );
>>
>>
>> I want to do a query from an application that returns all devices who's
>> time is between 10am or 10pm for a given instant in time.
>>
>> For example:
>>
>> SELECT *
>> FROM devices
>> WHERE :utcSecondsOfDay + utc_offset_secs BETWEEEN 10am AND 10pm
>>
>>
>>
>> In the above query assume the correct "seconds of day" values for 10am
>> and 10pm. The problem is that I have to do addition on each record to do
>> the above query and I can't imagine that would be efficient. Also I think
>> it this example query will only work in some cases. For example what if the
>> utcSecondsOfDay is 360 (i.e. 1am) and the utc_offset_secs is -5 hours?
>>
>> Thanks
>>
>
> I'm not sure exactly what :utSecondsOfDay really is. I guess it is an
> integer which is a "time" value, such as "seconds after midnight" and thus
> would range be from 0 to 24*60*60=86400​ (actually 86399, I guess). In this
> notation, 10 am would be 10*60*60 or 36000 and 10pm would be 22*60*60 or
> 79200. How about calculating, in your application code, two different
> values: utcSecondsLower and utSecondsHigher. utcSecondsLower would be
> 36000-utcSecondsOfDay. utcSecondsHigher would be 79200-utSecondsOfDay.
> Change the SELECT to be:
>
> SELECT *
> FROM devices
> WHERE ut_offsec_secs BETWEEN :utcSecondsLower AND :utcSecondsHigher;
>
> I am not sure, but I think that is legal. Or maybe it gives you another
> approach.
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
>


Thanks John, let me revise my original query to give a CORRECT and working
example, maybe this will help. I've created a query that actually works,
it's just ugly and I'd like to figure out how to make it like the example
you gave (i.e. no math on the utc_offset field, just comparisons).

 select *
 from devices d
 where (now() at time zone 'UTC' + make_interval(hours :=
d.utc_offset))::time
BETWEEN time '10:00' AND time '22:00';

Re: [GENERAL] Bounded Zone Offset Query

[John McKown]

On Fri, Jul 10, 2015 at 11:15 AM, Robert DiFalco 
wrote:

> I have a table something like this:
>
> CREATE TABLE devices (
>   owner_idBIGINT NOT NULL,
>   utc_offset_secs INT,
>   PRIMARY KEY (uid, platform),
>   FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
> );
>
>
> I want to do a query from an application that returns all devices who's
> time is between 10am or 10pm for a given instant in time.
>
> For example:
>
> SELECT *
> FROM devices
> WHERE :utcSecondsOfDay + utc_offset_secs BETWEEEN 10am AND 10pm
>
>
>
> In the above query assume the correct "seconds of day" values for 10am and
> 10pm. The problem is that I have to do addition on each record to do the
> above query and I can't imagine that would be efficient. Also I think it
> this example query will only work in some cases. For example what if the
> utcSecondsOfDay is 360 (i.e. 1am) and the utc_offset_secs is -5 hours?
>
> Thanks
>

I'm not sure exactly what :utSecondsOfDay really is. I guess it is an
integer which is a "time" value, such as "seconds after midnight" and thus
would range be from 0 to 24*60*60=86400​ (actually 86399, I guess). In this
notation, 10 am would be 10*60*60 or 36000 and 10pm would be 22*60*60 or
79200. How about calculating, in your application code, two different
values: utcSecondsLower and utSecondsHigher. utcSecondsLower would be
36000-utcSecondsOfDay. utcSecondsHigher would be 79200-utSecondsOfDay.
Change the SELECT to be:

SELECT *
FROM devices
WHERE ut_offsec_secs BETWEEN :utcSecondsLower AND :utcSecondsHigher;

I am not sure, but I think that is legal. Or maybe it gives you another
approach.


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[GENERAL] Bounded Zone Offset Query

[Robert DiFalco]

I have a table something like this:

CREATE TABLE devices (
  owner_idBIGINT NOT NULL,
  utc_offset_secs INT,
  PRIMARY KEY (uid, platform),
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
);


I want to do a query from an application that returns all devices who's
time is between 10am or 10pm for a given instant in time.

For example:

SELECT *
FROM devices
WHERE :utcSecondsOfDay + utc_offset_secs BETWEEEN 10am AND 10pm



In the above query assume the correct "seconds of day" values for 10am and
10pm. The problem is that I have to do addition on each record to do the
above query and I can't imagine that would be efficient. Also I think it
this example query will only work in some cases. For example what if the
utcSecondsOfDay is 360 (i.e. 1am) and the utc_offset_secs is -5 hours?

Thanks

Re: [GENERAL] Dynamic multi dimensional arrays in SQL

[Pavel Stehule]

Hi

I am afraid, so this functionality is supported only in unreleased
PostgreSQL 9.5

postgres=# SELECT ARRAY(SELECT ARRAY[true::text,false::text] FROM
generate_series(1,2));
┌─┐
│array│
╞═╡
│ {{true,false},{true,false}} │
└─┘
(1 row)

It is one from new features there.

In previous versions you can to write own custom aggregate function.

Regards

Pavel Stehule


2015-07-10 16:52 GMT+02:00 Dane Foster :

> Hello,
>
> I'm trying to dynamically construct a multi dimensional array where the
> outer most array's elements are binary arrays. My initial attempt was
> something to the effect of:
> SELECT ARRAY(SELECT ARRAY[true::text,false::text] FROM
> generate_series(1,2));
>
> My expectation is a result of the form: {{true,false},{true,false}}
>
> But it doesn't work. I get the following error instead: "could not find
> array type for data type text[]".
>
> The only way I've gotten it to work is to concatenate strings and
> explicitly cast the string to an array. It's ugly. I'm hoping the issue is
> that I'm doing something wrong and not that string concatenation or a
> procedural language are the only solutions.
>
> Any insight into what I'm doing wrong would be appreciated.
>
> Regards,
> ​
>
> Dane
>

[GENERAL] Dynamic multi dimensional arrays in SQL

[Dane Foster]

Hello,

I'm trying to dynamically construct a multi dimensional array where the
outer most array's elements are binary arrays. My initial attempt was
something to the effect of:
SELECT ARRAY(SELECT ARRAY[true::text,false::text] FROM
generate_series(1,2));

My expectation is a result of the form: {{true,false},{true,false}}

But it doesn't work. I get the following error instead: "could not find
array type for data type text[]".

The only way I've gotten it to work is to concatenate strings and
explicitly cast the string to an array. It's ugly. I'm hoping the issue is
that I'm doing something wrong and not that string concatenation or a
procedural language are the only solutions.

Any insight into what I'm doing wrong would be appreciated.

Regards,
​

Dane

Re: [GENERAL] How to test SSL cert from CA?

[Tom Lane]

Francisco Reyes  writes:
> On 07/09/2015 03:07 PM, Vick Khera wrote:
>> openssl s_client -connect HOST:PORT -CAfile /path/to/CA.pem

> According to this post: 
> http://serverfault.com/questions/79876/connecting-to-postgresql-with-ssl-using-openssl-s-client?rq=1
> one can not use openssl to test ssl connection to postgresql. True?

I should think you can't; it wouldn't know to send the initial packet
that asks the server to initiate SSL mode.

I found this in the man page for s_client mode:

   -starttls protocol
   send the protocol-specific message(s) to switch to TLS for
   communication.  protocol is a keyword for the intended protocol.
   Currently, the only supported keywords are "smtp", "pop3", "imap",
   and "ftp".

So they've certainly heard of such issues, and you could imagine adding
a "-starttls postgresql" variant, but it's not there now ... at least
not in the OpenSSL version that ships in RHEL6.

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