date:20160905

Re: [GENERAL] postgres question: Views with duplicate field names

2016-09-05 Thread Ryan Murphy

> Because I specifically aliased the first task reference using AS task_1.
>
>
Ok, totally.  I missed that when I first read your query, didn't read it
closely enough.  Thanks.

Re: [GENERAL] postgres question: Views with duplicate field names

2016-09-05 Thread Ryan Murphy

> You're confused about the input vs. the output.  The output columns
> of a view all have to have distinct names, just like you can't do
> "create table foo (f1 int, f1 int)".  They can be reading the same
> values, though.
>
> regards, tom lane
>

Ok, that makes sense. Thanks!

Re: [GENERAL] postgres question: Views with duplicate field names

2016-09-05 Thread Ryan Murphy

> You're confused about the input vs. the output.  The output columns
> of a view all have to have distinct names, just like you can't do
> "create table foo (f1 int, f1 int)".  They can be reading the same
> values, though.
>
> regards, tom lane
>

Ok, that makes sense. Thanks!

Re: [GENERAL] postgres question: Views with duplicate field names

2016-09-05 Thread Tom Lane

Ryan Murphy  writes:
> Interesting, thanks!  Do you know why the first one fails instead of doing
> that renaming process, while your version succeeds?

You're confused about the input vs. the output.  The output columns
of a view all have to have distinct names, just like you can't do
"create table foo (f1 int, f1 int)".  They can be reading the same
values, though.

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] Get date timestamp(3) without time zone column - PGSQL 9.5

2016-09-05 Thread Patrick B

Hi guys,

You were right, there was something wrong with my original query:

SELECT cast(tasks_start as date) FROM "tasks" WHERE (Date(tasks_start) in
> ('2016-08-10');


I was able to get the expected data using the above query...

Cheers
Patrick

Re: [GENERAL] postgres question: Views with duplicate field names

2016-09-05 Thread Adrian Klaver


On 09/05/2016 01:13 PM, Ryan Murphy wrote:

Interesting, thanks!  Do you know why the first one fails instead of
doing that renaming process, while your version succeeds?


Because I specifically aliased the first task reference using AS task_1.



On Monday, September 5, 2016, Adrian Klaver > wrote:

On 09/05/2016 12:55 PM, Ryan Murphy wrote:

Hello, I have a question about views in Postgres.

Given a table like so:

create table todo (
  id serial,
  task text,
  done_time timestamp default null
);

it is legal (though perhaps not advised, by some) to query it
like so:

select task, * from todo;

This gives a result with 2 redundant "task" fields (with
duplicate names):

 task | id | task | done_time
--++--+---
 wash the dog |  1 | wash the dog |

However, if I try to make a view of this I hit a problem: views
can't
have duplicate field names:

create view task2 as   select task, * from todo;

ERROR:  column "task" specified more than once

I understand this may seem like a silly thing to want to do, but my
question is if there is an easy way to automatically de-dup the
columns
of the query so I can create a view from it.  Or is there any
fundamental reason why views can't be allowed to have duplicate
columns,
just like the result set above?


test=> create view task2 as   select task AS task_1 , * from todo;
CREATE VIEW

test=> \d task2
 View "public.task2"

  Column   |Type | Modifiers

---+-+---

 task_1| text|

 id| integer |
 task  | text|
 done_time | timestamp without time zone |



Thanks!

Ryan



--
Adrian Klaver
adrian.kla...@aklaver.com




--
Adrian Klaver
adrian.kla...@aklaver.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] postgres question: Views with duplicate field names

2016-09-05 Thread Ryan Murphy

Interesting, thanks!  Do you know why the first one fails instead of doing
that renaming process, while your version succeeds?

On Monday, September 5, 2016, Adrian Klaver 
wrote:

> On 09/05/2016 12:55 PM, Ryan Murphy wrote:
>
>> Hello, I have a question about views in Postgres.
>>
>> Given a table like so:
>>
>> create table todo (
>>   id serial,
>>   task text,
>>   done_time timestamp default null
>> );
>>
>> it is legal (though perhaps not advised, by some) to query it like so:
>>
>> select task, * from todo;
>>
>> This gives a result with 2 redundant "task" fields (with duplicate names):
>>
>>  task | id | task | done_time
>> --++--+---
>>  wash the dog |  1 | wash the dog |
>>
>> However, if I try to make a view of this I hit a problem: views can't
>> have duplicate field names:
>>
>> create view task2 as   select task, * from todo;
>>
>> ERROR:  column "task" specified more than once
>>
>> I understand this may seem like a silly thing to want to do, but my
>> question is if there is an easy way to automatically de-dup the columns
>> of the query so I can create a view from it.  Or is there any
>> fundamental reason why views can't be allowed to have duplicate columns,
>> just like the result set above?
>>
>
> test=> create view task2 as   select task AS task_1 , * from todo;
> CREATE VIEW
>
> test=> \d task2
>  View "public.task2"
>
>   Column   |Type | Modifiers
>
> ---+-+---
>
>  task_1| text|
>
>  id| integer |
>  task  | text|
>  done_time | timestamp without time zone |
>
>
>
>> Thanks!
>>
>> Ryan
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] postgres question: Views with duplicate field names

2016-09-05 Thread Adrian Klaver


On 09/05/2016 12:55 PM, Ryan Murphy wrote:

Hello, I have a question about views in Postgres.

Given a table like so:

create table todo (
  id serial,
  task text,
  done_time timestamp default null
);

it is legal (though perhaps not advised, by some) to query it like so:

select task, * from todo;

This gives a result with 2 redundant "task" fields (with duplicate names):

 task | id | task | done_time
--++--+---
 wash the dog |  1 | wash the dog |

However, if I try to make a view of this I hit a problem: views can't
have duplicate field names:

create view task2 as   select task, * from todo;

ERROR:  column "task" specified more than once

I understand this may seem like a silly thing to want to do, but my
question is if there is an easy way to automatically de-dup the columns
of the query so I can create a view from it.  Or is there any
fundamental reason why views can't be allowed to have duplicate columns,
just like the result set above?


test=> create view task2 as   select task AS task_1 , * from todo;
CREATE VIEW

test=> \d task2
 View "public.task2" 



  Column   |Type | Modifiers 



---+-+--- 



 task_1| text| 



 id| integer |
 task  | text|
 done_time | timestamp without time zone |




Thanks!

Ryan



--
Adrian Klaver
adrian.kla...@aklaver.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] postgres question: Views with duplicate field names

2016-09-05 Thread Ryan Murphy

Hello, I have a question about views in Postgres.

Given a table like so:

create table todo (
  id serial,
  task text,
  done_time timestamp default null
);

it is legal (though perhaps not advised, by some) to query it like so:

select task, * from todo;

This gives a result with 2 redundant "task" fields (with duplicate names):

 task | id | task | done_time
--++--+---
 wash the dog |  1 | wash the dog |

However, if I try to make a view of this I hit a problem: views can't have
duplicate field names:

create view task2 as   select task, * from todo;

ERROR:  column "task" specified more than once

I understand this may seem like a silly thing to want to do, but my
question is if there is an easy way to automatically de-dup the columns of
the query so I can create a view from it.  Or is there any fundamental
reason why views can't be allowed to have duplicate columns, just like the
result set above?

Thanks!

Ryan

Re: [GENERAL] postgres driver for mysql

2016-09-05 Thread Adrian Klaver


On 09/05/2016 11:38 AM, Mimiko wrote:

Hello to all.

I want to move applications to postgres. But there are applications
which can use only mysql or local mdb or mssql. For now I run a mysql
server to which this applications are connected. Long time ago I've
encountered a federeted module for mysql to allow to store data in
postgres. Now that site doesnot exists.

Can anyone suggest a db federeted plugin for mysql/mariadb to store data
in pg. Changing applications is impossible, they are proprietary and
work only with specific databases only.


The only thing I know of is the other way around:

https://wiki.postgresql.org/wiki/Fdw

Trying to figure out how

1) "I want to move applications to postgres."

and

2) "Changing applications is impossible, they are proprietary and work 
only with specific databases only. "


is going to work out?



Thank you.





--
Adrian Klaver
adrian.kla...@aklaver.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] postgres driver for mysql

2016-09-05 Thread Mimiko


Hello to all.

I want to move applications to postgres. But there are applications 
which can use only mysql or local mdb or mssql. For now I run a mysql 
server to which this applications are connected. Long time ago I've 
encountered a federeted module for mysql to allow to store data in 
postgres. Now that site doesnot exists.


Can anyone suggest a db federeted plugin for mysql/mariadb to store data 
in pg. Changing applications is impossible, they are proprietary and 
work only with specific databases only.


Thank you.


--
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] Restricted access on DataBases

2016-09-05 Thread Charles Clavadetscher


Hello

On 09/05/2016 05:56 PM, Charles Clavadetscher wrote:

Hello

On 09/05/2016 04:19 PM, Adrian Klaver wrote:

On 09/05/2016 05:45 AM, Durumdara wrote:

Dear PG-masters!

We want to put more databases to one server, to "public" schema:
DB_A, DB_B, DB_C.


The PUBLIC schema is contained within a database not the other way
around, so further explanation is necessary.


And users:
US_A, US_B, US_C, and Main_Admin.
We want to setup the environment.
Every simple user can access his database:
DB_A - US_A
DB_B - US_B
DB_C - US_C

They can't access other databases only theirs.


When use speak of "their database", do you mean that they are the owner
of it or that they simply should have specific privileges?

If not, is main_admin the owner of all databases?


Main_Admin can access all databases.


Is Main_Admin created as a superuser?

If not what role attributes does it have?



I'm not sure how to do it perfectly.
We tried to remove "public" role, and add US_A to DB_A.
But the subobjects (table named "teszt") aren't accessable.


How did you specify GRANTing permissions on DB_A to US_A?

You might to want to look at the privileges that are provided to various
objects by GRANT:

https://www.postgresql.org/docs/9.5/static/sql-grant.html


Yes, read this document, it helps a lot.

Pragmatically I find a simple way to restrict access to a database is to
revoke CONNECT on it from public and then GRANT CONNECT and, if
necessary, privileges on objects in that database to the legitimate
user(s):

REVOKE CONNECT ON DATABASE db_a FROM public;
GRANT CONNECT ON DATABASE db_a TO us_a;

This will still not free you from managing the privileges on the objects
created. If main_admin is a superuser it will hav.e access to everything
anyway and you don't need to manage grants for it. If not, as Adrian
said, and assuming in db_a, only us_a will create objects, you will have
to alter the default privileges of us_a to grant privileges to
main_admin. This must be done for each database, i.e. db_b, db_c, etc.



GRANT on Database Objects

For instance

CREATE

For databases, allows new schemas to be created within the database.




I can reown DB_A to US_A, but this revoke all rights from Main_Admin.


Hard to answer until we know what permissions Main_Admin has.



What is the simple way to we can avoid the access from another users,
but give needed rights to DB_[n] and Main_Admin? (Tables, Sequences,
etc).

And how we keep this state later? For example: DB_A creates a new table.
Main_Admin must access this automatically...


Defualt privileges:

https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html




I don't understand this area properly. For me the "public" means "access
for all users", which isn't good (DB_A vs. US_C).


Actually it is not as broad as that.

https://www.postgresql.org/docs/9.5/static/sql-grant.html

"PostgreSQL grants default privileges on some types of objects to
PUBLIC. No privileges are granted to PUBLIC by default on tables,
columns, schemas or tablespaces. For other types, the default privileges
granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for
databases; EXECUTE privilege for functions; and USAGE privilege for
languages. The object owner can, of course, REVOKE both default and
expressly granted privileges. (For maximum security, issue the REVOKE in
the same transaction that creates the object; then there is no window in
which another user can use the object.) Also, these initial default
privilege settings can be changed using the ALTER DEFAULT PRIVILEGES
command.

"


As I think we can't mix the rights (Main_Admin = US_A + US_B  +
US_C...).


Actually you could:

GRANT us_a, us_b, us_c TO main_admin;



Here an example (obviously you will choose secure passwords and 
initialize them using \password . This is just a very simple 
example). I used 9.5 but it would work with earlier versions as well.


-- Create roles and databases

CREATE ROLE main_admin LOGIN PASSWORD 'xxx';

CREATE ROLE us_a LOGIN PASSWORD 'xxx';
CREATE DATABASE db_a;
ALTER DATABASE db_a OWNER TO us_a;

CREATE ROLE us_b LOGIN PASSWORD 'xxx';
CREATE DATABASE db_b;
ALTER DATABASE db_b OWNER TO us_b;

-- Restrict access

REVOKE CONNECT ON DATABASE db_a FROM public;
GRANT CONNECT ON DATABASE db_a TO us_a;

REVOKE CONNECT ON DATABASE db_b FROM public;
GRANT CONNECT ON DATABASE db_b TO us_b;

-- Grant all user rights to main_admin:

GRANT us_a, us_b TO main_admin;

Test:

-- Connect as us_a to db_a:

charles@charles.localhost=# \c db_a us_a
Password for user us_a:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, 
bits: 256, compression: off)

You are now connected to database "db_a" as user "us_a".

-- Create a table and enter some data:

us_a@db_a.localhost=> CREATE TABLE test (id INTEGER, tst TEXT);
CREATE TABLE
us_a@db_a.localhost=> INSERT INTO test VALUES (1,'Blabla');
INSERT 0 1

-- Try to connect as user us_b to db_a:

us_a@db_a.localhost=> \c db_a us_b
Password for user

Re: [GENERAL] Restricted access on DataBases

2016-09-05 Thread rob stone

Hello,
On Mon, 2016-09-05 at 14:45 +0200, Durumdara wrote:
> Dear PG-masters!
> 
> We want to put more databases to one server, to "public" schema:
> DB_A, DB_B, DB_C.
> And users:
> US_A, US_B, US_C, and Main_Admin.
> We want to setup the environment.
> Every simple user can access his database:
> DB_A - US_A
> DB_B - US_B
> DB_C - US_C
> 
> They can't access other databases only theirs.
> 
> Main_Admin can access all databases.
> 
> I'm not sure how to do it perfectly.
> We tried to remove "public" role, and add US_A to DB_A.
> But the subobjects (table named "teszt") aren't accessable.
> 
> I can reown DB_A to US_A, but this revoke all rights from Main_Admin.
> 
> What is the simple way to we can avoid the access from another users,
> but give needed rights to DB_[n] and Main_Admin? (Tables, Sequences,
> etc).
> 

I'm not a pg_master.

Do you mean multiple databases or multiple schemas?
If you have multiple databases then main_admin would have to connect
and disconnect over and over in order to look at each database.

If you are setting this up for students to learn RDBMS skills, then
wouldn't you be better off with a single database instance containing
multiple schemas?

Create all the roles with the necessary privileges, then:-

CREATE SCHEMA db_a AUTHORIZATION us_a;

GRANT ALL ON ALL TABLES IN SCHEMA db_a TO main_admin;

Repeat as necessary for each schema/role combination.

Then main_admin would have to prefix all tables, sequences, etc. with
the schema name in order to run queries, etc.

HTH,
Rob

-- 
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] Restricted access on DataBases

2016-09-05 Thread Charles Clavadetscher


Hello

On 09/05/2016 04:19 PM, Adrian Klaver wrote:

On 09/05/2016 05:45 AM, Durumdara wrote:

Dear PG-masters!

We want to put more databases to one server, to "public" schema:
DB_A, DB_B, DB_C.


The PUBLIC schema is contained within a database not the other way
around, so further explanation is necessary.


And users:
US_A, US_B, US_C, and Main_Admin.
We want to setup the environment.
Every simple user can access his database:
DB_A - US_A
DB_B - US_B
DB_C - US_C

They can't access other databases only theirs.


When use speak of "their database", do you mean that they are the owner 
of it or that they simply should have specific privileges?


If not, is main_admin the owner of all databases?


Main_Admin can access all databases.


Is Main_Admin created as a superuser?

If not what role attributes does it have?



I'm not sure how to do it perfectly.
We tried to remove "public" role, and add US_A to DB_A.
But the subobjects (table named "teszt") aren't accessable.


How did you specify GRANTing permissions on DB_A to US_A?

You might to want to look at the privileges that are provided to various
objects by GRANT:

https://www.postgresql.org/docs/9.5/static/sql-grant.html


Yes, read this document, it helps a lot.

Pragmatically I find a simple way to restrict access to a database is to 
revoke CONNECT on it from public and then GRANT CONNECT and, if 
necessary, privileges on objects in that database to the legitimate user(s):


REVOKE CONNECT ON DATABASE db_a FROM public;
GRANT CONNECT ON DATABASE db_a TO us_a;

This will still not free you from managing the privileges on the objects 
created. If main_admin is a superuser it will hav.e access to everything 
anyway and you don't need to manage grants for it. If not, as Adrian 
said, and assuming in db_a, only us_a will create objects, you will have 
to alter the default privileges of us_a to grant privileges to 
main_admin. This must be done for each database, i.e. db_b, db_c, etc.




GRANT on Database Objects

For instance

CREATE

For databases, allows new schemas to be created within the database.




I can reown DB_A to US_A, but this revoke all rights from Main_Admin.


Hard to answer until we know what permissions Main_Admin has.



What is the simple way to we can avoid the access from another users,
but give needed rights to DB_[n] and Main_Admin? (Tables, Sequences,
etc).

And how we keep this state later? For example: DB_A creates a new table.
Main_Admin must access this automatically...


Defualt privileges:

https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html



I don't understand this area properly. For me the "public" means "access
for all users", which isn't good (DB_A vs. US_C).


Actually it is not as broad as that.

https://www.postgresql.org/docs/9.5/static/sql-grant.html

"PostgreSQL grants default privileges on some types of objects to
PUBLIC. No privileges are granted to PUBLIC by default on tables,
columns, schemas or tablespaces. For other types, the default privileges
granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for
databases; EXECUTE privilege for functions; and USAGE privilege for
languages. The object owner can, of course, REVOKE both default and
expressly granted privileges. (For maximum security, issue the REVOKE in
the same transaction that creates the object; then there is no window in
which another user can use the object.) Also, these initial default
privilege settings can be changed using the ALTER DEFAULT PRIVILEGES
command.

"


As I think we can't mix the rights (Main_Admin = US_A + US_B  + US_C...).


Actually you could:

GRANT us_a, us_b, us_c TO main_admin;

Now, if you have time for it, I would suggest that you take it to read 
about the roles and privileges system in PostgreSQL. This will strongly 
help you understanding what you are doing.


Charles



Thank you for the help. information, or an example!

DD






--
Swiss PostgreSQL Users Group
c/o Charles Clavadetscher
Treasurer
Motorenstrasse 18
CH – 8005 Zürich

http://www.swisspug.org

+---+
|     __  ___   |
|  /)/  \/   \  |
| ( / ___\) |
|  \(/ o)  ( o)   ) |
|   \_  (_  )   \ ) _/  |
| \  /\_/\)/|
|  \/ |
|   _|  |   |
|   \|_/|
|   |
| PostgreSQL 1996-2016  |
|  20 Years of Success  |
|   |
+---+


--
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] Restricted access on DataBases

2016-09-05 Thread Adrian Klaver


On 09/05/2016 05:45 AM, Durumdara wrote:

Dear PG-masters!

We want to put more databases to one server, to "public" schema:
DB_A, DB_B, DB_C.


The PUBLIC schema is contained within a database not the other way 
around, so further explanation is necessary.



And users:
US_A, US_B, US_C, and Main_Admin.
We want to setup the environment.
Every simple user can access his database:
DB_A - US_A
DB_B - US_B
DB_C - US_C

They can't access other databases only theirs.

Main_Admin can access all databases.


Is Main_Admin created as a superuser?

If not what role attributes does it have?



I'm not sure how to do it perfectly.
We tried to remove "public" role, and add US_A to DB_A.
But the subobjects (table named "teszt") aren't accessable.


How did you specify GRANTing permissions on DB_A to US_A?

You might to want to look at the privileges that are provided to various 
objects by GRANT:


https://www.postgresql.org/docs/9.5/static/sql-grant.html

GRANT on Database Objects

For instance;

CREATE

For databases, allows new schemas to be created within the database.




I can reown DB_A to US_A, but this revoke all rights from Main_Admin.


Hard to answer until we know what permissions Main_Admin has.



What is the simple way to we can avoid the access from another users,
but give needed rights to DB_[n] and Main_Admin? (Tables, Sequences, etc).

And how we keep this state later? For example: DB_A creates a new table.
Main_Admin must access this automatically...


Defualt privileges:

https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html



I don't understand this area properly. For me the "public" means "access
for all users", which isn't good (DB_A vs. US_C).


Actually it is not as broad as that.

https://www.postgresql.org/docs/9.5/static/sql-grant.html

"PostgreSQL grants default privileges on some types of objects to 
PUBLIC. No privileges are granted to PUBLIC by default on tables, 
columns, schemas or tablespaces. For other types, the default privileges 
granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for 
databases; EXECUTE privilege for functions; and USAGE privilege for 
languages. The object owner can, of course, REVOKE both default and 
expressly granted privileges. (For maximum security, issue the REVOKE in 
the same transaction that creates the object; then there is no window in 
which another user can use the object.) Also, these initial default 
privilege settings can be changed using the ALTER DEFAULT PRIVILEGES 
command.


"


As I think we can't mix the rights (Main_Admin = US_A + US_B  + US_C...).

Thank you for the help. information, or an example!

DD




--
Adrian Klaver
adrian.kla...@aklaver.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] Restricted access on DataBases

2016-09-05 Thread amul sul

I think, it worth to try pg_hba.conf configuration[1].


 [1]. https://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html

Regards,
Amul Sul

On Mon, Sep 5, 2016 at 6:15 PM, Durumdara  wrote:
> Dear PG-masters!
>
> We want to put more databases to one server, to "public" schema:
> DB_A, DB_B, DB_C.
> And users:
> US_A, US_B, US_C, and Main_Admin.
> We want to setup the environment.
> Every simple user can access his database:
> DB_A - US_A
> DB_B - US_B
> DB_C - US_C
>
> They can't access other databases only theirs.
>
> Main_Admin can access all databases.
>
> I'm not sure how to do it perfectly.
> We tried to remove "public" role, and add US_A to DB_A.
> But the subobjects (table named "teszt") aren't accessable.
>
> I can reown DB_A to US_A, but this revoke all rights from Main_Admin.
>
> What is the simple way to we can avoid the access from another users, but
> give needed rights to DB_[n] and Main_Admin? (Tables, Sequences, etc).
>
> And how we keep this state later? For example: DB_A creates a new table.
> Main_Admin must access this automatically...
>
> I don't understand this area properly. For me the "public" means "access for
> all users", which isn't good (DB_A vs. US_C).
>
> As I think we can't mix the rights (Main_Admin = US_A + US_B  + US_C...).
>
> Thank you for the help. information, or an example!
>
> DD
>


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

[GENERAL] Restricted access on DataBases

2016-09-05 Thread Durumdara

Dear PG-masters!

We want to put more databases to one server, to "public" schema:
DB_A, DB_B, DB_C.
And users:
US_A, US_B, US_C, and Main_Admin.
We want to setup the environment.
Every simple user can access his database:
DB_A - US_A
DB_B - US_B
DB_C - US_C

They can't access other databases only theirs.

Main_Admin can access all databases.

I'm not sure how to do it perfectly.
We tried to remove "public" role, and add US_A to DB_A.
But the subobjects (table named "teszt") aren't accessable.

I can reown DB_A to US_A, but this revoke all rights from Main_Admin.

What is the simple way to we can avoid the access from another users, but
give needed rights to DB_[n] and Main_Admin? (Tables, Sequences, etc).

And how we keep this state later? For example: DB_A creates a new table.
Main_Admin must access this automatically...

I don't understand this area properly. For me the "public" means "access
for all users", which isn't good (DB_A vs. US_C).

As I think we can't mix the rights (Main_Admin = US_A + US_B  + US_C...).

Thank you for the help. information, or an example!

DD

Re: [GENERAL] IDE for function/stored proc development.

2016-09-05 Thread Martijn Tonies (Upscene Productions)

Good morning,
>I looked at your purchase, and did not see any Postgres version. Am I missing 
>(/misunderstanding) something here?

It’s not yet available, please wait until the end of the week 

That being said, the pricing will be the same as for MySQL.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Re: [GENERAL] IDE for function/stored proc development.

2016-09-05 Thread Daevor The Devoted

On Mon, Sep 5, 2016 at 10:02 AM, Martijn Tonies (Upscene Productions) <
m.ton...@upscene.com> wrote:

> Hello Tim,
>
> We will be releasing Database Workbench with PostgreSQL support later this
> week, it’s a Windows application but works fine on Linux/MacOS via Wine.
>
> Here’s the link to our website, feel free to check some screenshots
> http://www.upscene.com/database_workbench/
>
> With regards,
>
> Martijn Tonies
> Upscene Productions
>
>
> *From:* Tim Uckun 
> *Sent:* Saturday, September 03, 2016 2:49 PM
> *To:* Pavel Stehule 
> *Cc:* pgsql-general 
> *Subject:* Re: [GENERAL] IDE for function/stored proc development.
>
> I was hoping there was some IDE which made that process seamless.
> Something like PgAdmin but better editing features and features like "find
> definition" or "find usages" and such.  The jetbrains products come close
> but as I said they are buggy and don't work very well with postgres.
>
> On Sat, Sep 3, 2016 at 11:03 PM, Pavel Stehule 
> wrote:
>
>> Hi
>>
>> 2016-09-03 11:36 GMT+02:00 Tim Uckun :
>>
>>> Does anybody use an IDE for doing heavy duty stored proc development?
>>> PGadmin is decent but I am looking for something better.
>>>
>>> I have tried jetbrains with the db browser plugin and on the surface it
>>> seems like a good choice but it's really buggy when working with procs.
>>>
>>> I also tried datagrip by jetbrains and that too seems to be all over the
>>> place. It has some amazing features for working with the database but some
>>> of the simplest stuff is lacking or half baked.
>>>
>>> I looked at atom and could not find any useful plugins for PG.
>>>
>>> Anybody have experience with something awesome?
>>>
>>
>> I am using the Emacs - but any editor should be ok. There is one rule -
>> edit file first, and import to database as next step. PGadmin is pretty bad
>> tool for maintaing stored procedures.
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>
>
>

Good day, Martijn

I looked at your purchase, and did not see any Postgres version. Am I
missing (/misunderstanding) something here?

Kind regards,
Na-iem Dollie

Re: [GENERAL] IDE for function/stored proc development.

2016-09-05 Thread Martijn Tonies (Upscene Productions)

Hello Tim,

We will be releasing Database Workbench with PostgreSQL support later this 
week, it’s a Windows application but works fine on Linux/MacOS via Wine.

Here’s the link to our website, feel free to check some screenshots
http://www.upscene.com/database_workbench/

With regards,

Martijn Tonies
Upscene Productions



From: Tim Uckun 
Sent: Saturday, September 03, 2016 2:49 PM
To: Pavel Stehule 
Cc: pgsql-general 
Subject: Re: [GENERAL] IDE for function/stored proc development.

I was hoping there was some IDE which made that process seamless. Something 
like PgAdmin but better editing features and features like "find definition" or 
"find usages" and such.  The jetbrains products come close but as I said they 
are buggy and don't work very well with postgres.

On Sat, Sep 3, 2016 at 11:03 PM, Pavel Stehule  wrote:

  Hi


  2016-09-03 11:36 GMT+02:00 Tim Uckun :

Does anybody use an IDE for doing heavy duty stored proc development?  
PGadmin is decent but I am looking for something better. 

I have tried jetbrains with the db browser plugin and on the surface it 
seems like a good choice but it's really buggy when working with procs.

I also tried datagrip by jetbrains and that too seems to be all over the 
place. It has some amazing features for working with the database but some of 
the simplest stuff is lacking or half baked.

I looked at atom and could not find any useful plugins for PG.

Anybody have experience with something awesome? 

  I am using the Emacs - but any editor should be ok. There is one rule - edit 
file first, and import to database as next step. PGadmin is pretty bad tool for 
maintaing stored procedures.


  Regards


  Pavel

Re: [GENERAL] Materialized view auto refresh

2016-09-05 Thread Nguyễn Trần Quốc Vinh

Dear Harry.

You can try this while you are waiting the better solution from Prof. Kevin
Grittner. We did not improve because we do not know if we are in the right
way...

https://www.postgresql.org/message-id/caau9oxsb5gy8lz12kqaa3r1iv19c7famnefixdac1fhrfyt...@mail.gmail.com

You can downloat the source and the binary from here
http://it.ued.udn.vn/myprojects/pgTriggerGen/.

Best regards,

TS. Nguyễn Trần Quốc Vinh
---
Chủ nhiệm khoa Tin học
Trường ĐH Sư phạm - ĐH Đà Nẵng
Website: http://it.ued.vn ; http://www.ued.vn
; http://www.ued.udn.vn
LLKH: http://scv.ued.vn/~ntquocvinh 
ĐT: 0511.6-512-586
DĐ: 0914.78-08-98

Nguyen Tran Quoc Vinh, PhD
Dean
Faculty of Information Technology
Danang University of Education
Website: http://it.ued.udn.vn; http://www.ued.vn ;
http://www.ued.udn.vn
SCV: http://scv.ued.vn/~ntquocvinh 
Phone: (+84) 511.6-512-586
Mobile: (+84) 914.78-08-98

On Tue, Aug 9, 2016 at 8:49 PM, Kevin Grittner  wrote:

> On Tue, Aug 9, 2016 at 4:50 AM, hari.prasath 
> wrote:
>
> >   Is there any tentative schedule for real-time or incremental(only
> > applying delta changes) refresh of materialized views.?.
>
> There is work in progress, but no hard schedule.  Unfortunately, it
> has often been set aside to address more immediate issues for
> particular end users; but I expect to get back to it Real Soon Now.
>
> --
> 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
>

Re: [GENERAL] postgres question: Views with duplicate field names

Re: [GENERAL] postgres question: Views with duplicate field names

Re: [GENERAL] postgres question: Views with duplicate field names

Re: [GENERAL] postgres question: Views with duplicate field names

Re: [GENERAL] Get date timestamp(3) without time zone column - PGSQL 9.5

Re: [GENERAL] postgres question: Views with duplicate field names

Re: [GENERAL] postgres question: Views with duplicate field names

Re: [GENERAL] postgres question: Views with duplicate field names

[GENERAL] postgres question: Views with duplicate field names

Re: [GENERAL] postgres driver for mysql

[GENERAL] postgres driver for mysql

Re: [GENERAL] Restricted access on DataBases

Re: [GENERAL] Restricted access on DataBases

Re: [GENERAL] Restricted access on DataBases

Re: [GENERAL] Restricted access on DataBases

Re: [GENERAL] Restricted access on DataBases

[GENERAL] Restricted access on DataBases

Re: [GENERAL] IDE for function/stored proc development.

Re: [GENERAL] IDE for function/stored proc development.

Re: [GENERAL] IDE for function/stored proc development.

Re: [GENERAL] Materialized view auto refresh

21 matches

Site Navigation

Mail list logo

Footer information