Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2014

    Unanswered: unable to connect to an application

    Hello i am new to the forum and i am just beginning to learn things for my universtiy. the roblem is the following

    I have already installed ubuntu 14.04 adn lappstack that has Postgresql 9.3.3

    The reason is tha i have a project that : I have to imrove things (visualization etc) in an already made application. Unfortunatelly i didnt have any help on the hand over of the project an d i m trying to figure out things.
    I have a folder tha creates daatabases and there it is a login.php file. To see the file in a browser on and to get in to the applicatgion i had to connect to Postgresql (i did that through phpgpadmin) and run a command \i 'folder path/initial.sql'. I attach the initial.sql and the login.php file. The thing is that the initial .sql creates a role postgres and a db and user, BUT when i connect to postgress the role Postgres is already there. I dont know what to do anymore . if anyone can help....i would very much appreciate it
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    The postgres user name is the default superuser user name, created in every database.

    You do not want to try to add this user again.

    I see you've got a table cloudusers, which contains a user name and password, along with database login information.

    What is the purpose of your code? Is the concept to create an individual database for a 'cloud user' as needed? Then, when the 'cloud user' logs on, they get connected to their own, individual database? Or, are you just creating a table for each user?

    A good place to start is at the beginning (Note that this tutorial assumes you are connecting at the server, and not using web access, but the SQL is the same 'behind the set'

    Note that if each user gets their own, separate database, then you could grant them superuser rights to the database. But, if they do not, masking access through the clouduser table is safer. Just remember to take precautions in your php front end to block SQL injection attacks.

    Assuming that a single database is being used, and a user table is being created for each user, it would make even MORE sense (it would be 'safer') to avoid using user postgres (or other superuser roles) at all from your php front end. Instead it would be better to log on as a user with create table rights, and read-write access to the cloudusers table and other tables that the cloud users need read-write access to, and any other table that the cloud users need to be able to read from. It would also make sense to have the new tables created automatically within the database, rather than from an externally applied php script.

    log on as postgres (using phppgadmin), create a generic database login role that does NOT have superuser rights, but does have access rights to read/write to user tables, as well as the cloudusers table.

    Add a trigger function which will create a user table, using the NEW cloudusers record data. Grant execute privilege to the login role earlier created.

    Then, add an after insert (FOR EACH ROW) trigger to the cloud users table, which will call the trigger function you just created. Now, each time you create a new user record in the cloudusers table, the trigger function above will create a new table is created for them automatically, entirely within the database.

    If you ever drop a user from the clouduser table, and you need to drop the associated table, add a similar after delete trigger to handle that case. Likewise, if the user name could ever be changed, you would need an after update trigger for this case.

    By placing the table create code within the database, if you add a user via php front end, or you add a group of a hundred (or a thousand) users by importing a csv file into your database, the user table(s) is created automatically.
    Last edited by loquin; 08-26-14 at 18:26.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts