| Both sides previous revisionPrevious revisionNext revision | Previous revision |
| resources [2011/11/15 15:40] – [Admin SQL] k2patel | resources [2020/08/10 02:35] (current) – external edit 127.0.0.1 |
|---|
| |
| == Read Only User == | == Read Only User == |
| This only allow user to access database but can not write to it. | This only allow user to access database but can not write to it.\\ |
| | <note important>Before you grant make sure you selected database else it could not work</note> |
| |
| <code sql> | <code sql> |
| GRANT SELECT ON ALL TABLES IN SCHEMA public TO carol; | GRANT SELECT ON ALL TABLES IN SCHEMA public TO carol; |
| </code> | </code> |
| | |
| | <note tip>If there is ERROR : pg_dump: The command was: LOCK SEQUENCE public.client_group IN ACCESS SHARE MODE See next Section </note> |
| | |
| | <code sql> |
| | \dp public.client_group; # check permission |
| | GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup; |
| | </code> |
| | |
| | <note tip>Even you can use above commands to create backup user for specific database.</note> |
| |
| ==== Getting Connection Information ==== | ==== Getting Connection Information ==== |
| Following is the available procedures / variable. | Following is the available procedures / variable. |
| |
| | <code text> |
| {| style="border-spacing:0;" | Name Description |
| ! <center>Name</center> | current_catalog name of current database (called "catalog" in the SQL standard) |
| ! <center>Return Type</center> | current_database() name of current database |
| ! <center>Description</center> | current_schema[()] name of current schema |
| | current_schemas(boolean) names of schemas in search path, optionally including implicit schemas |
| |- | current_user user name of current execution context |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>current_catalog</tt> | current_query() text of the currently executing query, as submitted by the client (might contain more than one statement) |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>name</tt> | pg_backend_pid() Process ID of the server process attached to the current session |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| name of current database (called "catalog" in the SQL standard) | pg_listening_channels() channel names that the session is currently listening on |
| | inet_client_addr() address of the remote connection |
| |- | inet_client_port() port of the remote connection |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>current_database()</tt> | inet_server_addr() address of the local connection |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>name</tt> | inet_server_port() port of the local connection |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| name of current database | pg_my_temp_schema() OID of session's temporary schema, or 0 if none |
| | pg_is_other_temp_schema(oid) boolean is schema another session's temporary schema? |
| |- | pg_postmaster_start_time() server start time |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>current_schema<nowiki>[()]</nowiki></tt> | pg_conf_load_time() configuration load time |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>name</tt> | session_user session user name |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| name of current schema | user equivalent to current_user |
| | version() PostgreSQL version information |
| |- | </code> |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>current_schemas(boolean)</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt><nowiki>name[]</nowiki></tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| names of schemas in search path, optionally including implicit schemas | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>current_user</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>name</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| user name of current execution context | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>current_query()</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>text</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| text of the currently executing query, as submitted by the client (might contain more than one statement) | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>pg_backend_pid()</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>int</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| Process ID of the server process attached to the current session | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>pg_listening_channels()</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>setof text</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| channel names that the session is currently listening on | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>inet_client_addr()</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>inet</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| address of the remote connection | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>inet_client_port()</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>int</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| port of the remote connection | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>inet_server_addr()</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>inet</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| address of the local connection | |
| | |
| |- | |
| | style="background-color:#efefef;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>inet_server_port()</tt> | |
| | style="background-color:#efefef;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>int</tt> | |
| | style="background-color:#efefef;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| port of the local connection | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>pg_my_temp_schema()</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>oid</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| OID of session's temporary schema, or 0 if none | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>pg_is_other_temp_schema(oid)</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>boolean</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| is schema another session's temporary schema? | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>pg_postmaster_start_time()</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>timestamp with time zone</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| server start time | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>pg_conf_load_time()</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>timestamp with time zone</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| configuration load time | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>session_user</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>name</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| session user name | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>user</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>name</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| equivalent to <tt>current_user</tt> | |
| | |
| |- | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>version()</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:none;padding:0.0194in;"| <tt>text</tt> | |
| | style="background-color:#ffffff;border-top:none;border-bottom:0.0361in double #808080;border-left:0.0361in double #808080;border-right:0.0361in double #808080;padding:0.0194in;"| PostgreSQL version information | |
| | |
| |} | |
| | |
| | |
