Differences

This shows you the differences between two versions of the page.

--- resources [2011/11/14 20:53] – [Admin SQL] k2patel
+++ resources [2020/08/10 02:35] (current) – external edit 127.0.0.1
@@ Line 62: / Line 62: @@
 == 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>
 CREATE ROLE carol;
 ALTER ROLE carol WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN ENCRYPTED PASSWORD 'password';
+GRANT SELECT ON ALL TABLES IN SCHEMA public TO carol;
+</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 ====
+<code sql>
+select inet_server_addr();
+</code>
+Following is the available procedures / variable.
+<code text>
+Name				Description
+current_catalog			name of current database (called "catalog" in the SQL standard)
+current_database()		name of current database
+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
+current_query()			text of the currently executing query, as submitted by the client (might contain more than one statement)
+pg_backend_pid()		Process ID of the server process attached to the current session
+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
+inet_server_addr()		address of the local connection
+inet_server_port()		port of the local connection
+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
+pg_conf_load_time()		configuration load time
+session_user			session user name
+user				equivalent to current_user
+version()			PostgreSQL version information
 </code>