Postgresql and setting schema search path other than public

Example DB Server: 192.168.1.10
Example DB: company_live_prod
Default Schema: public
Other Schema: com_retail_store

Upon logging on postgres cli, default schema of public will take effect. The ff: will set to change the schema so you can check the tables under that other schema.


assuming that I am now login as postgres and able to access the db on default public schema.

company_live_prod=# \dt
                                List of relations
 Schema |                        Name                        | Type  |   Owner   
--------+----------------------------------------------------+-------+------------
 public | access_levels                                      | table | xuser
 public | to_for                 | table | xuser
 public | super_hierarchy                             | table | xuser
 public | sked01                         | table | xuser






so that I can see the tables for the other schema, below command will do.


company_live_prod=# SET search_path TO  com_retail_store;
SET
ces_live_final=# \dt
                    List of relations
     Schema      |         Name         | Type  |  Owner 
-----------------+----------------------+-------+---------
 com_retail_store | com_assign_module    | table | xuser
 com_retail_store | com_cart             | table | xuser
 com_retail_store | com_category         | table | xuser
 com_retail_store | com_item_storage     | table | xuser
 com_retail_store | com_manual_upload    | table | xuser
 com_retail_store | com_point_metrics    | table | xuser

(06 rows)







Default schema is now the none public schema.

How to Reset mysql root password if you forgot it

1. Login as root on the server
2. Stop the mysql service
3. start the mysql service on safemode
           command: mysqld_safe --skip-grant-tables
4. Login or if you have a ready alternate console, you can now do mysql command which will login with out password then do the sql command below:

           update mysql.user set Password=PASSWORD('NeWPassWord') WHERE User='root';


5. Exit and restart the mysql service as normal restart.
6. You can now login using the new password.

MYSQL Securing users password

A note on securing users password using hash on mysql

Login inside mysql shell

once login, issue the command

select password('internet');




assuming internet is the clear password

result below for the command:

mysql> select password('internet');
+-------------------------------------------+
| password('internet')                      |
+-------------------------------------------+
| *797420C584EBF42750EB523104268BA0FD87FBC8 |
+-------------------------------------------+                                                                                                               
1 row in set (0.00 sec)                


*797420C584EBF42750EB523104268BA0FD87FBC8 secure password that can be use

upon granting DB rights.



mysql> grant select,insert,update on dummy-db.* to 'testuser'@'%.%.%.%' identified by password '*797420C584EBF42750EB523104268BA0FD87FBC8';
Query OK, 0 rows affected (0.00 sec)


Query above will encrypt the defined password of user testuser on access to dummy-db and able to access from any remote ip. If you verify by using the mysql DB and select * from user;



| %.%.%.%            | testuser | *797420C584EBF42750EB523104268BA0FD87FBC8 | 

 the cleartext password would be "internet".

Using proxy on yum and wget

1. do the command "export http_proxy=xxx.xxx.xxx.xxx:yyyy" assuming that you will be using ip address as your proxy and you are login as user or root, the proxy will be exported on your environment.

example:

export http_proxy=192.168.1.1:8080

2.  for directly set the proxy at yum configuration. Append the line

proxy=http://192.168.1.1:8080

at /etc/yum.conf

that way, proxy is directly set at yum

thanks to those document found when googling. this is just my reference.

Dump Certain table from postgresql Server-A to postgresql Server-B

Problem:

Need to Dump Table user_data_login and restore it at Server-B from Server-A
DataBase Name: users_DB
User: postgres

At Server A

as postgres user

pg_dump --table=user_data_login users_DB -f /tmp/users_data.sql

At Server B

assuming that the users_DB.sql has been copied at /tmp/users_data.sql

execute the command below as postgres user

psql -d users_DB -f /tmp/users_data.sql

that's it.

 

grant readonly access on selected table - Postgres 8.4

Task: To grant select only on tables inside database MYDB01 under schema warehouse_data to user READONLY

On postgres 8.x, Im not sure if its my problem only, but I cannot do one grant command on all tables, so searching the web and found that it can be done this way.


for i in `bin/psql MYDB01-A -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'warehouse_data';"`; do bin/psql ces_live_final -c "GRANT SELECT ON warehouse_data.$i TO READONLY;"; done

The above is one line only, and it solves my task.

Creating a startup script on Fedora 17 - Gnome-shell

- open at your terminal

gnome-session-properties


then you can add here your created application or script to run when you login on your gnome-shell.