resources
Differences
This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
resources [2011/11/14 20:53] k2patel [Admin SQL] |
resources [2020/08/10 02:35] (current) |
||
|---|---|---|---|
| Line 62: | Line 62: | ||
| == 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> | ||
| CREATE ROLE carol; | CREATE ROLE carol; | ||
| ALTER ROLE carol WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN ENCRYPTED PASSWORD 'password'; | 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> | </code> | ||
resources.1321303987.txt.gz · Last modified: 2020/08/10 02:29 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
