StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
19602050
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-10-26T02:05:42.227
FavoriteCount
0
LastActivityDate
2016-04-20T18:16:22.643
LastEditDate
2016-04-20T18:16:22.643
LastEditorUserId
223478
OwnerUserId
223478
ParentId
2998597
PostTypeId
2
Score
33
ViewCount
0
LastEditorDisplayName
text
Body
<pre><code>--create a user that you want to use the database as: create role neil; --create the user for the web server to connect as: create role webgui noinherit login password 's3cr3t'; --let webgui set role to neil: grant neil to webgui; --this looks backwards but is correct. </code></pre> <code>webgui</code> is now in the <code>neil</code> group, so <code>webgui</code> can call <code>set role neil</code> . However, <code>webgui</code> did not inherit <code>neil</code>'s permissions. Later, login as webgui: <pre><code>psql -d some_database -U webgui (enter s3cr3t as password) set role neil; </code></pre> <code>webgui</code> does not need <code>superuser</code> permission for this. You want to <code>set role</code> at the beginning of a database session and reset it at the end of the session. In a web app, this corresponds to getting a connection from your database connection pool and releasing it, respectively. Here's an example using Tomcat's connection pool and Spring Security: <pre><code>public class SetRoleJdbcInterceptor extends JdbcInterceptor { @Override public void reset(ConnectionPool connectionPool, PooledConnection pooledConnection) { Authentication authentication = SecurityContextHolder.getContext().getAuthentication(); if(authentication != null) { try { /* use OWASP's ESAPI to encode the username to avoid SQL Injection. Can't use parameters with SET ROLE. Need to write PG codec. Or use a whitelist-map approach */ String username = ESAPI.encoder().encodeForSQL(MY_CODEC, authentication.getName()); Statement statement = pooledConnection.getConnection().createStatement(); statement.execute("set role \"" + username + "\""); statement.close(); } catch(SQLException exp){ throw new RuntimeException(exp); } } } @Override public Object invoke(Object proxy, Method method, Object[] args) throws Throwable { if("close".equals(method.getName())){ Statement statement = ((Connection)proxy).createStatement(); statement.execute("reset role"); statement.close(); } return super.invoke(proxy, method, args); } } </code></pre>
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POSwitch role after connecting to database
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USNeil McGuigan
UserOwnerUserId
1. USNeil McGuigan
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.