GPS coordinates problem

[Timmy Siu]

Dear All Postgre Users,

I want to develop a new project on Lavarel 5 and Postgresql 11.

Now, I need Global Position System coordinates as a data type. How do I 
define it in Postgresql 11 or 12?



Thanks for your help.

Timmy

Allowing client access

[Timmy Siu]

Dear All Users,

 * How do I allow an external client IP address access to my pgsql server?

According to my own experience and test, if I set the external client IP 
address to, for example, 111.222.333.444 in the file 
/etc/postgresql/11/main/pg_hba.conf, it will not be able to connect to 
the server:

#TYPE?? DATABASE?? ?? USER?? ?? ?? ADDRESS?? ?? 
?? METHOD
host?? all?? ?? ?? testuser 111.222.333.444 md5

I must set "address" to "any" as in the following:
#TYPE?? DATABASE?? ?? USER?? ?? ?? ADDRESS?? ?? 
METHOD
host?? all?? ?? ?? testuser  ?? 0.0.0.0/0?? ?? md5


 * Can I limit access to my pgsql server through TCP Wrapper? What is
   the related executable?


Regards,
Timmy

Re: Allowing client access

[Timmy Siu]

Dear Bob,
Thank you.?? hostssl works out of the box!?? It does not require extra 
configuration.?? I can connect to my own pgsql server via pgadmin 4.?? I 
personally feel that Postgresql v11 is much clever than Mysql v5.7 (I 
haven't tried its v8).


I also have tested postgres against TCP Wrappers but it is not compiled 
against TCP wrappers library.?? May I suggest the community to have 
postgres to work with TCP wrappers.?? Its security will be better.


Regards,
Timmy



Hi Timmy

You need to use CIDR form in your pg_hba.conf.  So:

host all testuser  111.222.333.444/32  md5

Most likely you would probably want to ensure ssl connection if coming
over untrusted network.  So, at minimum, this is better:

hostssl all testuser  111.222.333.444/32  md5

This is better still:

hostssl testdb testuser  111.222.333.444/32  md5

Better still (IMHO) is to keep it local and use ssh tunnel, but I
understand that might be difficult and not necessarily desirable,
depending on the context.

Regards
Bob

Minimum privilege for Backup and replication

[Timmy Siu]

Dear All Postgresql Users,
I am new to pgsql. I have an installation on my Ubuntu 18.04.

Now, my question is -
What is the Minimum Privilege of a pgsql Backup or Replication user?

I know how to do it for mysql but not pgsql.?? I begin to switch to pgsql 
v11 from mysql v5.7 after knowing it many nice features.?? Mysql seems 
very old-fashioned.?? Oracle and IBM DB2 are just too $$$ to me.


Thanks for your opinions.

Timmy