# GUI queen needs database advice



## Phishfry (Oct 8, 2019)

I want to assemble a freestanding DB + GUI front end for experimentation.
20 years ago I made a Point Of Sale program using Microsoft Access and AccessBasic.
Looking for similar software in FreeBSD. Not wanting LibreOffice's Base but a freestanding DB.

I think I have found a solution but would like opinions.
postgres seems to appeal to me. So for a GUI we have pgadmin3 (somewhat outdated though).
All that seems kosher but I also see a phppgadmin port. What is the difference? Obviously it adds php support.
Do I need that? Will pgadmin suffice for application building?
The world has changed since Access Basic and web applications are the new norm.
Where should I go? For my personal web site server I currently use lighttpd on Linode and love it.
So should I locally serve up my http database application with lightttpd too?
Any advice is welcomed. Even better DB choices are OK. I like the postgres license.


----------



## Sevendogsbsd (Oct 8, 2019)

Throwing some security stuff out there since that's my swim lane : is this for internal use only? Will the web app/pages be on the public Internet? If public, make sure whatever DB you choose is not on the same box as the web server. Web server in DMZ of course.

Pretty sure the DB admin apps you referenced are for just that: admin of the DB itself. As for an app, you will have to write code yourself: Java, php, etc.  Will also need to design the DB accordingly based on your app requirements.

I am not in the web app dev business any longer - been out for well over  a decade. Others can better advise you on the technologies.


----------



## obsigna (Oct 8, 2019)

I cannot give a conclusive answer since I am also looking for a good PostgreSQL-GUI. The problem is that pgAdmin3 is not only outdated, it does not work anymore reliably with recent PostgreSQL server versions. For example, when I open a database hosted by a PostgreSQL 11.2 server, I need to click away tons of error messages before I come to see the db structure, and when I want to open a table, another bunch of error messages about missing meta data fields are displayed.

Using pgAdmin3 with databases hosted on a PostgreSQL 9.6 server is fine, except that I see one message, that only upto 9.5 is supported

The successor is pgAdmin4, however, this is not in the ports, and while pre-compiled installation packages are provided for Linux, Windows and macOS, we need to try to get around with the sources, in case we want to have it on FreeBSD. I did not try this yet.

That said, pgAdmin4 is a totally different animal than ...3. Actually it provides a web interface to the databases, and the desktop version comes with its own web server in order to achieve this - quote from the documentation:


> pgAdmin may be deployed as a desktop application by configuring the application to run in desktop mode and then utilising the desktop runtime to host the program on a supported Windows, Mac OS X or Linux installation.
> 
> The desktop runtime is a system-tray application that when launched, runs the pgAdmin server and launches a web browser to render the user interface. If additional instances of pgAdmin are launched, a new browser tab will be opened and be served by the existing instance of the server in order to minimise system resource utilisation. Clicking the icon in the system tray will present a menu offering options to open a new pgAdmin window, configure the runtime, view the server log and shut down the server.


----------



## Phishfry (Oct 8, 2019)

Sometimes I can't find what I need on Google until I ask correctly...
This search worked best: "MS Access for Linux"








						4 open source alternatives to Microsoft Access
					

When small businesses, community organizations, and similar-sized groups realize they need software to manage their data, they think first of Microsoft Access.




					opensource.com
				




obsigna  I really appreciate the advice about pgAdmin3.
Seeing how postgresql11 is out and postgresql95 is needed for pgAdmin3 I will drop the GUI requirement for database admin.

For building applications I have might just start at Kexi or LibreOffice Base.


Sevendogsbsd said:


> As for an app, you will have to write code yourself: Java, php, etc.


This is where I am now searching around.
AJAX seems to be well supported on lighttpd. Javascript has several IDE's I have found. Geany supports php.
That is my weak spot. Web languages. I want something as simple as my lighttpd webserver.
Perl, modPHP, FastCGI, AJAX.
So many buzz words and so little knowledge.
All I initially need is GUI text input boxes for the DB form and perhaps some dropdown dialog boxes for choices.


----------



## 20-100-2fe (Oct 8, 2019)

The current standard for web or enterprise application development is to create the user interface with a JavaScript-based framework (such as React or VueJS). The front-end uses REST APIs to communicate with a back-end server (written in PHP, Java, etc), which in turn will access your database.

The tool you use to interact with your database (pgAdmin, phpPgAdmin) is not important because you'll use it only for debugging purposes on your development workstation or test server. Database setup (and further evolutions) is simply done by feeding your DBMS command line client with a SQL script.

However, if you're looking for an equivalent of MS Access + VB, I imagine you plan to create a small application. Moreover, I understand from your initial post that you don't even seem to NEED a web application.

A good starting point would be to tell us what you want to do and in which context it will run. Depending on your requirements, you might realize macros in an OpenOffice spreadsheet would be enough, or that a Python application built on top of SQLite would best suit your needs than a web application.


----------



## Phishfry (Oct 8, 2019)

obsigna said:


> The desktop runtime is a system-tray application that when launched


This was where TCL/TK gave me fits.
I created a simple application using `uname` but how to make it a run time executable.
There is a  method they call starkit/starpack which converts it into a freestanding application so tcl/tk is not required to run the app.
I could not find any "kits" in our ports system for tcl/tk.


----------



## obsigna (Oct 8, 2019)

When it comes to web application development you need to do client side programming, and this you would do using HTML5, CSS3, JavaScript and perhaps SVG (the latter for interactive graphics) -- and be assured, you want HTML, you want CSS, and you want JavaScript for the client code.

On the server side you need the web server, and I assume, lighttpd would be fine. AND, you need some programs which respond to requests from the client in a sensible way. For the time being, let's call these programs responders. The responders run on the server, and for this you may use any programming language which you are comfortable with and which can be plugged somehow into lighttpd - usually by CGI or FastCGI. While the requesters run in the client's web browser (e.g. JavaScript, and here you would do yourself a big favour to forget Java, Flash and similar stuff for the client).

So the client sends a request and the server shall respond. And here comes AJAX into play. Without AJAX, the web browser would simply refresh the whole web page, which is fine, if you got no interactive visualisation on the page. With AJAX your JavaScript code may send a single value together with a sort of an ID to the server, and the server could for example update only the respective field of your database and in addition respond with some other action, and that without needing to reload the page.


----------



## twllnbrck (Oct 8, 2019)

obsigna said:


> I cannot give a conclusive answer since I am also looking for a good PostgreSQL-GUI.



Im not an expert for databases and GUI admin tools but for PostgreSQL administration there is also databases/phppgadmin. And you can use Oracle's SQL Developer with the appropriate JDBC driver to connect with your Postgres DB. Oracle provides it as Freeware.


----------



## gpw928 (Oct 8, 2019)

I used to do this sort of stuff with Apache (HTML and CSS), perl CGI, and Oracle.  But times have moved on, and what *obsigna *said is pretty much where it is today.

But the basic security stuff is still the same:

Understand what SQL injection and Cross Site Scripting (XSS) are, and how to defend against them.  Compose and test attacks yourself, so you really do understand.  Investigate why M$ SQL autocomplete is an XSS hacker's delight.
Take advice on the network design.  Leave room for a load balancer.  Design the HTTPS termination point with fore-thought.  Locate your database server as far as possible from the Internet.  
Never leave financial or personal data in the DMZ for a microsecond longer than they need to be there.  Encrypt and ship to a safe place immediately.  Manage the keys actively.  Obliterate passwords, keys, and personal data as soon as they no longer required.


----------



## Phishfry (Oct 9, 2019)

20-100-2fe said:


> A good starting point would be to tell us what you want to do and in which context it will run.


I am going to start with a 'contacts' database for my "Hello World".
First Name
Last Name
Street Address
City
State
Zip Code

Using the 'State' field for my dropdown dialog box test.

I setup postgresql11_5 last night and now comes the hard part. GUI design.
This database will only be available on my LAN, no outside worries.
I did setup pg_hba.conf to allow 192.168.0.0/24 clients.


----------



## shkhln (Oct 9, 2019)

gpw928 said:


> Investigate why M$ SQL autocomplete is an XSS hacker's delight.



That sounds… nonsensical. Where can I learn more about this?


----------



## olli@ (Oct 9, 2019)

I just wanted to mention that I think that Postgres is an excellent choice. I also use Postgres wherever possible, for about 20 years. It's very reliable and robust (nearly impossible to destroy or lose a database by accident), yet has very good performance and one of the best query optimizers on the market.

As for the GUI admin interface, I'm afraid I can't help you there. I prefer to administrate my DBs via CLI.
For the actual DB applications (whether web or not), my language of choice is Python. But that's a different story and wasn't the question here.


----------



## 6502 (Oct 9, 2019)

Why web GUI and not Qt/GTK/etc.?


----------



## gpw928 (Oct 10, 2019)

shkhln said:


> That sounds… nonsensical. Where can I learn more about this?


Sorry I chose my words sloppily.  It's not "auto-complete", it's the '--' in-line comment operator that permits SQL syntax errors to be ignored by the SQL compiler.


----------



## unitrunker (Oct 10, 2019)

Here's what I'm currently playing with ...

1. pgsql as the database (unix socket - no TCP).
2. stored procedures for everything.
3. pgsql access model prevents adhoc queries from the application.
4. light fastcgi wrapper in C++ to call above stored procedures (on "lo" interface).
5. #4 runs as an user permitted to call above stored procs.
6. nginx as the external facing "attack surface" - nginx is well understood and easy to configure.

I end up with a bunch of REST calls that I can easily test using a tool like Postman. That's when the fun starts with html/css/javascript - all served up by nginx.


----------



## kpedersen (Oct 10, 2019)

You mentioned Kexi or LibreOffice Base so you aren't tied to a web page for the GUI? If it is a POS system then that makes sense.

Any specific languages you are most proficient with? If it is C++ or Python, then wxWidgets is nice. If it is Java then the classic javax.swing is OK.

Postgres is good but I have only used it from the CLI; if you want a UI for modifying it, then you could consider using MariaDB *just* because of phpmyadmin being quite good.

A company I worked for used this: https://www.navicat.com/en/products/navicat-for-postgresql
But unfortunately it is proprietary. However their Linux port was just a wine wrapper. I actually made a FreeBSD port for them but I don't know if they maintained it or not.

If the application itself is web based; I am not great in that area. Drag in a bunch of Javascript frameworks seems to be a common strategy XD.


----------



## Phishfry (Oct 10, 2019)

6502 said:


> Why web GUI and not Qt/GTK/etc.?


No particular reason. I thought web 2.0 would be simpler. Obviously I was wrong.
I might try tcl/tk with postgres. I have been buffing up on that.


kpedersen said:


> so you aren't tied to a web page for the GUI? If it is a POS system then that makes sense.


No this is just an excuse for learning. My POS system was for a now defunct dive shop.
Its not like I could re-use any of that code.


----------



## 20-100-2fe (Oct 10, 2019)

Phishfry said:


> No this is just an excuse for learning.



You don't need an excuse to learn, only a purpose. 
Depending on what you want to do next, you'll easily find out what to learn first.
If your purpose is to work as a developer, web technologies are appropriate.
If your purpose is to contribute to an open-source project, the project you'll choose will dictate what you'll need to learn.


----------



## Sevendogsbsd (Oct 10, 2019)

gpw928 said:


> Sorry I chose my words sloppily.  It's not "auto-complete", it's the '--' in-line comment operator that permits SQL syntax errors to be ignored by the SQL compiler.


And it's not XSS, it's SQL injection.


----------



## shkhln (Oct 10, 2019)

Indeed, XSS usually refers to the improperly escaped/encoded HTML output. Assuming it is potentially exploitable, of course. (By the way, where do I report one? I don't want to talk to XenForo myself.)


----------



## Sevendogsbsd (Oct 10, 2019)

Exactly - normally involves Javascript, thus the term Cross-site-"scripting"...SQL Injection is entirely different.


----------



## D-FENS (Oct 10, 2019)

Phishfry said:


> I want to assemble a freestanding DB + GUI front end for experimentation.
> 20 years ago I made a Point Of Sale program using Microsoft Access and AccessBasic.
> Looking for similar software in FreeBSD. Not wanting LibreOffice's Base but a freestanding DB.
> 
> ...



I personally use MySQL. It's quite easy to setup on FreeBSD.
If you need a GUI for management, you can install an Apache web server with PHPMyAdmin app. All of them are available as packages.





						How To Install an Apache, MySQL, and PHP (FAMP) Stack on FreeBSD 10.1  | DigitalOcean
					

A FAMP stack, which is similar to a LAMP stack on Linux, is a group of open source software that is typically installed together to enable a FreeBSD server t…




					www.digitalocean.com
				




It goes something like this:

```
pgk install mariadb104-server apache24 phpMyAdmin-php74
sysrc mysql_enable="YES"
sysrc apache24_enable="YES"
service start mysql
service start apache24
mysql_secure_installation

firefox 'http://localhost/phpmyadmin'
```

For sure, you could also go SSL with the Apache, you need to create certificates and keys with openssl if you want to, but if your firewall stops traffic from outside, your webserver should be secure.


----------



## gpw928 (Oct 10, 2019)

Sevendogsbsd said:


> And it's not XSS, it's SQL injection.


Quite so. I wrote XSS, but meant SQL injection, as implied in my second post.  XSS in no way requires javascript.


----------



## Sevendogsbsd (Oct 11, 2019)

I know, I write XSS and SQLi exploit every day for work.


----------



## obsigna (Oct 11, 2019)

Inspired by this thread, I found yesterday in the afternoon databases/kexi.


> Kexi is an open source visual database applications creator,
> a long-awaited competitor for programs like MS Access
> or Filemaker.
> 
> WWW: http://www.kexi-project.org/



Somewhere I read, that it can deal with MySQL, PostgreSQL and SQLITE3 backends, so I installed it on a system, in order to see whether this can be used as a GUI client for the said database backends. It cannot, it uses the backends for its own storage necessities. So, Kexi won’t serve my purposes. However, in case I understood the objectives of Phishfry correctly, then Kexi might be exactly what he’s looking for, namely a Visual Database Applications Creator. I’m not quite impressed about MS Access, however, if Kexi can really do about 75 % of what FileMaker can do, then this is something.

Building from the ports took it’s time, though, 3 h on a Quad-i7@4,2 GHz. If there were a competition among open source projects which drag-in the most dependencies, then Kexi would for sure win a trophy.


----------



## PMc (Dec 19, 2019)

obsigna said:


> The successor is pgAdmin4, however, this is not in the ports, and while pre-compiled installation packages are provided for Linux, Windows and macOS, we need to try to get around with the sources, in case we want to have it on FreeBSD. I did not try this yet.
> 
> That said, pgAdmin4 is a totally different animal than ...3. Actually it provides a web interface to the databases, and the desktop version comes with its own web server in order to achieve this - quote from the documentation:



Working on this matter now.
As I consider postgres being just perfect - it never failed me, and also working with the dev guys on the list is wonderfully constructive - and finding pgadmin3 the magic looking glass to see whats going on inside, I'm currently figuring out how to tackle with that pgadmin4.

What I can say so far: pgadmin4 is designed with an entirely different architecture in mind. It is not an application to be installed on some computer and then run by the user(s) on that computer.
Instead, it seems to be an almost fullsize Web Application Server, designed to run on an arbitrary application server machine, being connected as the middleware by a frontend Web Server (probably running on some other machine), utilizing it's own user administration (just like a website) and then accessing some postgres installations (likely yet on other machines).
The underlying Application Framework appears to be Flask utilizing sqlite3 as it's database backend. (A web application server is the middleware between the frontend web server and the backend database. The sqlite3 here is the backend for the application's persistent storage demands, while the postgres to be administered is the application's payload to work with.)
There is no application to compile, as it is all python. (Neverteless the python module prereqs do contain libaries in native C; these are compiled automatically on package install - when things go well.)
Such an application server is usually not installed by root into the system paths. It is rather installed by some user into their homedirs, putting all the (ever changing) module dependencies right alongside into the application.
Certainly such an architecture can also be concentrated to run on a single machine for a single user - but that's not the most easy way to approach it.
It is probably quite difficult to roll such a beast as a port. It is also kind of sisyphos work, because with every release the module dependencies will change - which would normally be no problem as the application itself carries a list of it's dependencies and can install these per script: just put it into a new directory and throw away the old one.

There is also a more serious problem with it: we have the Kerberos integrated in the base system, and postgres also has the Kerberos integrated. So we would never need passwords for the database (and then we cannot store our production database passwords within the application onto github, like other people seem to do). The pgadmin3 would just let libpg grab the Kerberos tickets as when connecting the database directly, so the single-sign-on works transparently, no password needed.
But now pgadmin4 would split this one securely authenticated connection into two unauthenticated and dubious halfes, and we're quite back to herding passwords, for pgadmin4 AND for the database.


----------



## Nicola Mingotti (Dec 19, 2019)

the real question is, how much time can you put into it Phishfry ? If you have 20-30 days than you can try to pickup the whole Javascript stack. That is, use Node as web server, and Javascript+html+css for the interface. You will easily find libraries to call whatever db from Node. I did a few projects with this technology, it is good and well understandable if you avoid big frameworks and limit yourself to E.g. jQuery.


----------



## 20-100-2fe (Dec 20, 2019)

Nicola Mingotti said:


> if you avoid big frameworks and limit yourself to E.g. jQuery.



For the purpose of learning, I would recommend React instead of jQuery.
jQuery is clearly obsolete, most of its features are nowadays useless and the rest (e.g. jQuery UI) can be done in a simple way with modern frameworks such as React with the notable benefit of a much easier application maintenance.

I usually explain React basics in 10 min., so it cannot be called a 'big' framework.  It takes more time to learn how to use npm, package.json and webpack than to create your first React form.


----------



## Nicola Mingotti (Dec 20, 2019)

20-100-2fe said:


> For the purpose of learning, I would recommend React instead of jQuery.
> jQuery is clearly obsolete, most of its features are nowadays useless and the rest (e.g. jQuery UI) can be done in a simple way with modern frameworks such as React with the notable benefit of a much easier application maintenance.
> 
> I usually explain React basics in 10 min., so it cannot be called a 'big' framework.  It takes more time to learn how to use npm, package.json and webpack than to create your first React form.



I am sorry, my preference is in the complete opposite direction. I consider frameworks for non-programmers . Easy to start with, but giving zero understanding of the subject and mangling creative thought. If somebody does not know jQuery i would seriously doubt he uses javascript at all. JQuery UI, not a big fan of that. I prefer to build pieces that i need and have full control. 

Said that, it is true that @phisfry could solve his problem in (say) 1/5 of the time with a framework. One the other side he would understand nothing of what is happening. Just copy in StackOverflow, fill some templates, reboot, cross your finger, hope it works. If it does not, back to StackOverflow. IMHO This is the cancer of programming. --> If he liked that he would be a Windows guy more than a BSD one ;P


----------



## Nicola Mingotti (Dec 20, 2019)

extra thought. Framework have their reson d'etre which is crystal clear. Most of the web is just a template, change color-font-picture and put in a bit if text. So, they make a lot if sense. It is just not my corner


----------



## shkhln (Dec 20, 2019)

React is inspired by the functional reactive UI idea, although, ironically, it's not a proper FRP UI implementation because it's "merely" functional* and not reactive. Still, it's pretty hardcore, so show some respect.

* if you squint hard enough; to the extent possible in JavaScript.


----------



## 20-100-2fe (Dec 21, 2019)

Nicola Mingotti said:


> Most of the web is just a template



You're right, but this has nothing to do with frameworks. Frameworks are primarily intended for enterprise application development.

In the beginning, professional developers did what you like to do: developing everything themselves. This led to the development of company-specific frameworks because at some point, you get tired of copy-paste and try and make your life easier by factorizing redundant code and developing coding procedures - which is all a framework is about.

Over time, some developers left the company - retiring, dying of a cancer or taking a new responsibility elsewhere - and new products had to be developed. Both factors make it necessary to hire new developers and in spite of their knowledge of the programming language, it still took them a long time (6 months to a year is usual) to get to know the company's frameworks enough to produce functional and reliable code.

In the meantime, their boss had to pay them without added value in return, while customer unsatisfaction often increases due to the many bugs added to the application as part of the new joiners' learning curve.

But the worse is most certainly when someone (or some team) develops an application in a cryptic and/or dirty fashion and hands its maintenance over to another team (yours) when in production. If the original developer is still in the company, you're very much tempted to have him experience how creative you can be to design new torture techniques. 

This is why companies use widespread (and generally also open-source) frameworks as much as possible so they can reduce the learning curve of new joiners, increase the reliability and maintainability of applications, and reduce risk factors in their projects.

Because it is nowadays very important for most companies, they DO NOT want their developers to copy-paste code from Stack Overflow, so they get their developers trained instead. And this is precisely my job, to train developers.

Don't forget that there's a lot of money invested in IT projects and that management positions are most coveted. If you fail to manage your teams and your projects so they create value for your company, someone else will soon be sitting behind your former desk.


----------



## PMc (Dec 21, 2019)

20-100-2fe said:


> But the worse is most certainly when someone (or some team) develops an application in a cryptic and/or dirty fashion and hands its maintenance over to another team (yours) when in production. If the original developer is still in the company, you're very much tempted to have him experience how creative you can be to design new torture techniques.



Oh, thats nice!  Didn't know that style is still contemporary in the new age. It reminds me of good old times - my first boss, back in the 80's, was very fond of keelhauling the people who wrote the software he indended to work with, and that was a wonderful time and a good management style, only I fear it wouldn't be p.c. anymore in these new times.

BTW: Strill trying to get that pgadmin4 into proper shape - anyone ever heard of a document that would have defined an 'X-Script-Name' entity? Looks quite clandestine to me...



20-100-2fe said:


> And this is precisely my job, to train developers.



You kiddin'.


----------



## Nicola Mingotti (Dec 21, 2019)

20-100-2fe said:


> You're right, but this has nothing to do with frameworks. Frameworks are primarily intended for enterprise application development.



We could discuss this for months You have some points and certainly see them. It is expecially true that industry wants hire juniors and pretend them to be a regular developer.  
Node+Javascript+JQuery+HTML+CSS+Postgres let me build all kind of web application I can imagine (well 95% of them). They are solid, well documented, and change slowly enough to make the learn an investment. If I add a layer more (call it framework or whatever) it is just obfuscation. 

Learning all the stuff it is a big investment in time/effort. But offers big rewards. If you can envision something, then you can do it. If your graphic-person come with a crazy idea never seen before [they do it] you can make it real (here you need to know some math). 

I think poor knowledge of basic structure and infrastructure is the root of many web evils. Look at this forum page for example, I am at Frankfurth airport right now, on the phone, a vulgar iPhone used by millions, the top part layout of the page is horribly mangled. And this why? Poor command of Javascript I say. 

Boarding !!!! I hope i did not make to many typos  
bye


----------



## shkhln (Dec 21, 2019)

Nicola Mingotti said:


> I think poor knowledge of basic structure and infrastructure is the root of many web evils. Look at this forum page for example, I am at Frankfurth airport right now, on the phone, a vulgar iPhone used by millions, the top part layout of the page is horribly mangled. And this why? Poor command of Javascript I say.



Careful there, you not far from Spartrekus' territory. This is a typical "responsive design" resizing behavior.


----------



## 20-100-2fe (Dec 21, 2019)

PMc said:


> BTW: Strill trying to get that pgadmin4 into proper shape - anyone ever heard of a document that would have defined an 'X-Script-Name' entity? Looks quite clandestine to me...



I've just had a glimpse at pgadmin4's source code and it seems to be a mixture of Python and JavaScript... 

You might be interested in phppgadmin, it just needs Apache + PHP + Postgresql and is already available as a FreeBSD package.


----------



## kpedersen (Dec 21, 2019)

shkhln said:


> Careful there, you not far from Spartrekus' territory.



Have you guys ever thought that instead of actually developing a web application for your users, it would be better to have a great plan and leave it at that?

A perfect, bloat free plan!


----------



## Nicola Mingotti (Dec 22, 2019)

shkhln said:


> Careful there, you not far from Spartrekus' territory. This is a typical "responsive design" resizing behavior.



I have not been much present in the last months, but if i remember well Spartrekus was a C language admirer, last time I talked to him he was trying to build a spreadsheet for the terminal. He may have converted to JS in the meanwhile  

I discussed some of the graphical issues of the forum with Trihexagonal a long time ago. But we didn't actually push for a change. As far as I am concerned the forum is fine as it is, well organized, good content, collaborative people. So, well, these graphical glitches are really a minor thing. It was just an handy example.


----------



## PMc (Dec 27, 2019)

20-100-2fe said:


> You might be interested in phppgadmin, it just needs Apache + PHP + Postgresql and is already available as a FreeBSD package.



Putting it short: does it support Kerberos5/GSSAPI/SPNEGO ticket proxying?

Background: if you run postgreSQL with K5 auth, you never need a passwort, and neither a certificate. It's single-sign-on: at login, the user enters their password and get a "ticket". That ticket automatically authenticates them for some time (e.g. a workday), without further action.
But changing to a web application gives a couple of additional tasks with this:

the webserver must be able to receive and handle such tickets.
Apache does, with www/mod_auth_kerb2 or www/mod_auth_gssapi.
the application must be able to receive that ticket and understand it.
the application must be able to talk to postgreSQL authenticated with that ticket on the user's behalf.
This gets interesting, as we will have multiple users acting simultaneously, and the respectively correct ticket must be associated with each PGconnect() invocations.

For pgadmin4, the developers seem to not have fully understood the task, as Stephen Frost here has quite well pinpointed it.)
That was two years ago, alright, and I have not yet figured the current state of affairs, although my guts say it will not have got much better. 
For phppgadmin I did not find any informations.

But, looking a bit closer, it seems nowadays people believe in SSL. And they do that by offering the user a private filespace *on the webapp-server*. And then, to *upload* the certificate *and certificate KEY. Onto the webapp-server.* Well, obviousely, as that's the only way to get such a scheme to work. 
But then also, from that point onwards such webapp-server is in fact a *security device* (carrying secret keys)! 
And this is exactly the way how Rome was lost.


----------



## abishai (Dec 28, 2019)

I use java/intellij-ultimate to develop backend and database schema (ultimate edition contains https://www.jetbrains.com/datagrip/ ) with https://flywaydb.org/ to roll it out. https://ktor.io/ with a nginx ssl terminator to host it. Database is postgreSQL, obviously.


----------



## 20-100-2fe (Dec 28, 2019)

PMc said:


> Putting it short: does it support Kerberos5/GSSAPI/SPNEGO ticket proxying?



I don't know if it does, but going back to the start of the thread, such a setup doesn't seem to match the OP's need, which seemed to be more about learning, if I understood correctly. And learning is easier if you start with something simple and add more and more complexity as your knowledge grows.


----------



## PMc (Dec 28, 2019)

20-100-2fe said:


> I don't know if it does, but going back to the start of the thread, such a setup doesn't seem to match the OP's need, which seemed to be more about learning, if I understood correctly. And learning is easier if you start with something simple and add more and more complexity as your knowledge grows.



Ack on that. But since You quoted my efforts in making pgadmin4 work, I erroneously supposed You were addressing me. Sorry for misunderstanding.

Anyway, krb5 is a native part of FreeBSD as well as postgreSQL, so it's rather legit to consider if it's supported. And we have a high ranking on google here, so we are writing for a large base of invisible readers as well.


----------

